Redmine - Bulk Import for LDAP users

If you configure LDAP for authentication, users are not visible unless they have at least logged in once. This is not ideal in terms of usability. You have to tell your users to login but they can't do anything because there are not member of any project (yet) and you can't add them because they don't exist (chicken and egg).


To solve this we created a PHP script to implement Bulk Import in Redmine database.

/* ----------------- PHP script ------------------- */





//Postgres Variables

$pg_sqldb_host = "pghost";
$pg_sqldb_user = "pguser";
$pg_sqldb_pass = "pgpass";
$pg_sqldb_name = "redmine";


// LDAP variables
$ldaphost = "ldap.server.com";  // your ldap servers
$ldapport = 389;  // your ldap server's port number


// Connecting to LDAP
$ldapconn = ldap_connect($ldaphost, $ldapport) or die("Could not connect to $ldaphost");


ldap_set_option($ldapconn, LDAP_OPT_PROTOCOL_VERSION, 3);
ldap_set_option($ldapconn, LDAP_OPT_REFERRALS, 0);


$login = ldap_bind( $ldapconn, "ldapuser", "ldappass" );


$attributes = array("uid", "displayname", "mail");
$filter = "(uid=*)";


$result = ldap_search($ldapconn, $search_base, $filter, $attributes) or die("error in query") ;


$data = ldap_get_entries($ldapconn, $result);


$redmine_link = pg_connect("host=$pg_sqldb_host port=5432 dbname=$pg_sqldb_name user=$pg_sqldb_user password=$pg_sqldb_pass") or die("Could not connect to Redmine: " . pg_last_error());




$query = "select MAX(id)+1 from users";
$result = pg_query($redmine_link, $query);
$nextid = pg_fetch_result($result, 0, 0);


for ($i=0; $i<=$data["count"];$i++) {


$query = "select id from users where login='" . $data[$i]["uid"][0] . "'";
$result = pg_query($redmine_link, $query);


$rows = pg_num_rows($result);

if($rows == 0 )
{
$name = split(" " , $data[$i]["displayname"][0]);


$query = "INSERT INTO users (id, login, firstname, lastname, mail,        mail_notification, admin, status, language, auth_source_id, created_on, type)
VALUES($nextid,'" . $data[$i]["uid"][0] . "','" . $name[0] . "','" . $name[1] . "','" . $data[$i]["mail"][0] . "',false,false,1,'en',$auth_source_id,CURRENT_TIMESTAMP,'User')";


$result = pg_query($redmine_link, $query);
$rows = pg_affected_rows($result);


//user doesn't exists add user
echo ("User " . $data[$i]["uid"][0] . " added successfully
");
$nextid++;
}
else
{
echo ("User " . $data[$i]["uid"][0] . " already exists
");
}
}


ldap_unbind($login);
ldap_close($ldapconn);


print("Users imported successfully
");


?>

/* ----------------- end of script ------------------- */

You may add script in schedule to run every 24 hours.

Linux : add php script in crontab. 

0 0 * * * *  root /user/bin/php /path/phpscript >> /var/log/logfile

Windows: add php script in windows scheduler.

Comments are most welcome.

Official website of Shrinathji Temple, Nathdwara

Shrinathji Temple, Nathdwara has launched official website. Website provides latest information about Shrinathji Temple, along with online services like Donation, Darshan Timings, News, Events, Cottage Booking for New Cottage and Dhirajdham.

www.nathdwaratemple.org

Subversion + Redmine + LDAP automatically generate AuthzSVNAccessFile

Subversion + Redmine + LDAP automatically generate AuthzSVNAccessFile

While implementing Redmine we came across difficulty integrating Redmine and Subversion using LDAP. One of the main reason was subversion uses AuthzSVNAccess file to authorize users. We don’t want all LDAP users to access SVN, only users added in respective Redmine project should able to access SVN repositories.

To solve this we end up creating PHP scripts to auto generate AuthzSVNAccessFile based on Redmine database. Script can be added to crontab to run periodically. Below are detail steps and script.

1) Install Redmine

2) Install Subversion

3) Automatically create SVN repository when a new project is added in Redmine

Add this script in crontab on Subvserion server (in case your subversion is hosted on different server). Below script is scheduled to run every 10 minutes.

10 * * * * root ruby /root/redmine-1.0.0/extra/svn/reposman.rb --redmine-host http://my.redmine./ --svn-dir /data/svn/ --url my.svn.server --key=mykey --owner apache --verbose >> /var/log/reposman.log

4) Configure Apache to use LDAP for subversion

ErrorDocument 404 default

DAV svn

Require valid-user

SVNParentPath /data/svn/

SVNListParentPath off

#LDAP configuration

AuthType Basic

AuthName "USVN"

AuthBasicProvider "ldap"

AuthLDAPURL "ldap://ldapserver"

authzldapauthoritative on

AuthLDAPBindDN "DN"

AuthLDAPBindPassword "LDAP Password"

Require valid-user

# Path to AuthzSVNAccessFile which will be auto generated using script

AuthzSVNAccessFile /etc/svn-acl-conf

5) PHP script to automatically generate AuthzSVNAccessFile

$authfile = "/etc/svn-acl-conf"; //path of AuthzSVNAccessFile

//redmine database detail. I have used Postgres.

$pg_sqldb_host = "localhost";

$pg_sqldb_user = "redmine";

$pg_sqldb_pass = "redmine";

$pg_sqldb_name = "redmine";

$fp = fopen($authfile, "w");

fwrite($fp, "# Subversion authorization file created by script \n");

$redmine_link = pg_connect("host=$pg_sqldb_host port=5432 dbname=$pg_sqldb_name user=$pg_sqldb_user password=$pg_sqldb_pass") or die("Could not connect to Redmine: " . pg_last_error());

#section [groups]

fwrite($fp, "[groups]\n");

#create groups from redmine projects

$query = "select id,name,identifier from projects";

$result = pg_query($redmine_link, $query);

while ($row = pg_fetch_array($result,NULL,PGSQL_ASSOC)) {

$group_name = $row["identifier"];

$group_id = $row["id"];

$group_users = NULL;

//get users for this group

$query = "select b.id, b.login from members a, users b where a.user_id=b.id AND a.project_id=$group_id";

$usvn_users_result = pg_query($redmine_link, $query);

while ($userrow = pg_fetch_array($usvn_users_result,NULL,PGSQL_ASSOC)) {

$group_users = $group_users . ($group_users != NULL ? ", " : "") . $userrow["login"];

}

pg_free_result($usvn_users_result);

fwrite($fp, "$group_name = $group_users \n");

}

pg_free_result($result);

#section [project]

fwrite($fp, "\n\n#Projects from Redmine \n");

$query = "select id,name,identifier from projects";

$result = pg_query($redmine_link, $query);

while ($row = pg_fetch_array($result,NULL,PGSQL_ASSOC)) {

$project_name = $row["name"];

$project_id = $row["id"];

$project_identifier = $row["identifier"];

fwrite($fp, "#Project $project_name \n");

fwrite($fp, "[$project_identifier:/] \n");

fwrite($fp, "@$project_identifier = rw \n\n");

}

pg_free_result($result);

pg_close($redmine_link);

fclose($fp);

print("SVN Auth file $authfile generated sucessfully");

?>

//End of code

- Make sure apache user has write access to AuthzSVNAccessFile- Add PHP script in crontab to run every 10 minutes10 * * * * /usr/bin/php /path/myscript.php

Clicking AutoCompleteExtender scrollbar causes postback

Clicking the AutoCompleteExtender scrollbar causes postback when attached to a TextBox where AutoPostBack="true", below is a workaround to resolve issue.

Workaround is just to check whether focus is on extender popup. If focus is on extender do not sumit (postback) form.

See example below

<script language=javascript>
function checkFocusOnExtender()
{
//check if focus is on productcode extender
if ( $find('aceProductCode')._flyoutHasFocus )
return false;
}
</script>


<form id="form1" runat="server" onsubmit="return checkFocusOnExtender();">

<asp:TextBox ID="txtProductCode" runat="server" CssClass="textbox" AutoCompleteType="disabled"
AutoPostBack="true" OnTextChanged="txtProductCode_TextChanged"></asp:TextBox>
<ajaxToolkit:AutoCompleteExtender ID="aceProductCode" runat="server" TargetControlID="txtProductCode"
UseContextKey="true" ServiceMethod="GetProductCode" CompletionInterval="10" EnableCaching="true"
CompletionSetCount="100" MinimumPrefixLength="1" DelimiterCharacters=";, :" CompletionListCssClass="autocomplete_completionListElement"
CompletionListItemCssClass="autocomplete_listItem" CompletionListHighlightedItemCssClass="autocomplete_highlightedListItem"">
</ajaxToolkit:AutoCompleteExtender>

</form>

Override Price - Opportunity Product MSCRM 4.0

During implanting Microsoft CRM 4.0 I came across an issue to override price on opportunity product. Out of box CRM4.0 doesn’t allow to override price on opportunity product. I tried couple of options like setting ispriceoverridden=true using javascript, a server side plugin to change price but no luck. Then a workaround worked which I would like to share with all of you. Workaround is a trick to use outbox box “Manual Discount” field (only allowed field where user can write something) to implement override price.

Out of box calculation of ExtendedAmount is
ExtendedAmount = (PricePerUnit * Qty) – ManualDiscount

Example (Out of Box calculation):-
Product: XYZ
Price (Pricelist): $15
Qty: 10
Manual Discount: $10
Extended Amount: $140 = (($15 * 10) - $10)

In above example you cannot override price as calculation is done on server. Any changes in Price here will be overridden with pricelist on server.

Below are steps to allow price override opportunity product.

a) Add custom fields on Opportunity Product

a. New_priceoverridden (bit)– custom field to know is price overriddden

b. New_priceperunit (money) – custom field to save priceperunit

c. New_manualdiscount (money) – custom field to save manual discount.

d. New_amount (money)– custom field to save amount

b) Add above custom fields on form and move out of box fields to hidden tab

c) Add finally a database trigger which does a trick

/****** Object: Trigger [dbo].[UpdateOpportunityProductPrice]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[UpdateOpportunityProductPrice]
ON [dbo].[OpportunityProductExtensionBase]
AFTER INSERT, UPDATE
AS

DECLARE @opportunityProductId uniqueidentifier

SELECT @opportunityProductId = i.OpportunityProductId FROM inserted i;

IF @opportunityProductId IS NOT NULL
BEGIN

DECLARE @new_priceoverridden bit
DECLARE @new_manualdiscount money
DECLARE @new_priceperunit money
DECLARE @new_amount money

SELECT @new_priceoverridden = i.new_priceoverridden,
@new_manualdiscount = i.new_manualdiscount,
@new_priceperunit = i.new_priceperunit,
@new_amount = i.qty * i.priceperunit
FROM inserted i

IF @new_priceoverridden = 0
BEGIN

UPDATE dbo.OpportunityProductBase
SET new_amount = @new_amount, manualdiscountamount = @new_manualdiscount,
extendedamount = isnull(baseamount,0) - isnull(manualdiscountamount,0)
WHERE opportunityProductId = @opportunityProductId

END
ELSE
BEGIN

UPDATE dbo.OpportunityProductBase
SET new_amount = @new_amount, manualdiscountamount = (isnull(baseamount,0) - isnull(@new_amount,0)) + isnull(@new_manualdiscount,0),
extendedamount = isnull(@new_amount,0) - isnull(@new_manualdiscount,0)
WHERE opportunityProductId = @opportunityProductId

END
END

Verifying above example after implementing workaround

Example:-
Product: XYZ
Price Overridden (Custom field): Yes
Price (Custom Field): $25 (price in pricelist is $15)
Qty: 10
Manual Discount (Custom Field): $10

calculation takes place in database trigger
Manual Discount (out of box) = -$110 = $150 - (($25 * $10) + $10))
Extended Amount: $260 = (($15 * 10) - (-$110))

Product: XYZ
Price Overridden (Custom field): No
Price (Custom Field): $15 (price from pricelist)
Qty: 10
Manual Discount (Custom Field): $10

calculation takes place in database trigger
Manual Discount (out of box) = $10
Extended Amount: $140 = (($15 * 10) - $10)

d) Change Opportunity Product – Quote Product relationship mapping to map custom fields instead of outbox fields. Unofficial way to change relationship mapping as Mapping is not displayed in Opportunity Product – Quote Product relationship

a) Get EntityMapId from database

SELECT EntityMapId, TargetEntityName, SourceEntityName FROM EntityMap WHERE (TargetEntityName like '%quotedetail%') AND (SourceEntityName like '%opportunityproduct%');

b) Copy EntityMapId from above query result

http://crmurl/Tools/SystemCustomization/Relationships/Mappings/mappingList.aspx?mappingId=D3644E14-657B-DD11-9769-001E0B4882E2

Please make sure above mappingid will be EntityMapId from query result.

Sorting with Objects on multiple fields

Introduction

This article demonstrates how to apply sorting on objects. It is useful when you need to apply sorting on objects e.g. Object Person(Name, Age) and you want to apply sorting on Person->Name, or you may wish to apply sorting on multiple fields i.e. Person->name DESC + person->age DESC.

It is very useful when you want to apply multiple column sorting on GridViews with ObjectDataSource.

Using the code

Using Reflection its very easy to sort objects. I had used Lists to sort objects and ObjectComparer class is inherited from IComparer

ObjectComparer supports Single as well as Multiple sorting.

Collapse

//
//
//Test.cs
//Created an array of Person object, for which i wish to apply sorting

Person[] personArray = new Person[] {
new Person("Ritesh", 26),
new Person("Arpan", 20),
new Person("Arpan", 23),
new Person("Hiren", 22),
new Person("Ankit", 22),
new Person("Dhaval", 23),
new Person("Gaurav", 25)
};

//Sort array on field Name in Ascending Order
Array.Sort(personArray, new ObjectComparer<Person>("Name"));

//Sort array on field Name Decending and Age Ascending
Array.Sort(personArray, new ObjectComparer<Person>("Name DESC, Age ASC",true));

ObjectComparer.cs

[Serializable]
public class ObjectComparer<ComparableObject> : IComparer<ComparableObject>
{
  #region Constructor
  public ObjectComparer()
  {
  }

  public ObjectComparer(string p_propertyName)
  {
      //We must have a property name for this comparer to work
        this.PropertyName = p_propertyName;
  }

  public ObjectComparer(string p_propertyName, bool p_MultiColumn)
  {
      //We must have a property name for this comparer to work
        this.PropertyName = p_propertyName;
      this.MultiColumn = p_MultiColumn;
  }
  #endregion

  #region Property
  private bool _MultiColumn;
  public bool MultiColumn
  {
      get { return _MultiColumn; }
      set { _MultiColumn = value; }
  }

  private string _propertyName;
  public string PropertyName
  {
      get { return _propertyName; }
      set { _propertyName = value; }
  }
  #endregion

  #region IComparer<ComparableObject> Members
  /// <summary>
    /// This comparer is used to sort the generic comparer
    /// The constructor sets the PropertyName that is used
    /// by reflection to access that property in the object to
    /// object compare.
    /// </summary>
    /// <param name="x"></param>
    /// <param name="y"></param>
    /// <returns></returns>
    public int Compare(ComparableObject x, ComparableObject y)
  {
      Type t = x.GetType();
      if (_MultiColumn) // Multi Column Sorting
        {
          string[] sortExpressions = _propertyName.Trim().Split(',');
          for (int i = 0; i < sortExpressions.Length; i++)
          {
              string fieldName, direction = "ASC";
              if (sortExpressions[i].Trim().EndsWith(" DESC"))
              {fieldName = sortExpressions[i].Replace(" DESC", "").Trim();
              direction = "DESC";
          }
          else
          {
              fieldName = sortExpressions[i].Replace(" ASC", "").Trim();
          }

          //Get property by name
            PropertyInfo val = t.GetProperty(fieldName);
          if (val != null)
          {
              //Compare values, using IComparable interface of the property's type
                int iResult = Comparer.DefaultInvariant.Compare(val.GetValue(x, null), val.GetValue(y, null));
              if (iResult != 0)
              {
                  //Return if not equal
                    if (direction == "DESC")
                  {
                      //Invert order
                        return -iResult;
                  }
                  else
                  {
                      return iResult;
                  }
              }
          }
          else
          {
              throw new Exception(fieldName + " is not a valid property to sort on. It doesn't exist in the Class.");
          }
      }
      //Objects have the same sort order
        return 0;
  }
  else
  {
      PropertyInfo val = t.GetProperty(this.PropertyName);
      if (val != null)
      {
          return Comparer.DefaultInvariant.Compare(val.GetValue(x, null), val.GetValue(y, null));
      }
      else
      {
          throw new Exception(this.PropertyName + " is not a valid property to sort on. It doesn't exist in the Class.");
      }
  }
}
#endregion

}

//

Bind Nested Tree view from database - C#

Introduction

This is an example of how to bind a nested tree from database.

Very Simple algorithm to bind tree.

method insertnodes is called recursively which will bind a nested tree at n level

create one table in access with following fields

module_id integer

module_name varchar(50)

parent_module integer

place one treeview control, and call this method with first parameter null

insertnodes(null,0);

private void insertnodes ( TreeNode n , int module_id )

{

OleDbConnection con=new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\tree.mdb");

con.Open();

OleDbCommand cmd=new OleDbCommand( "SELECT * FROM [module] WHERE parent_module=" + module_id,con);

OleDbDataReader rdr=cmd.ExecuteReader();

while(rdr.Read())

{

TreeNode t=new TreeNode(rdr["module_name"].ToString());

insertnodes(t,Convert.ToInt16(rdr["module_id"].ToString()));

if(n==null)

treeView1.Nodes.Add(t);

else

n.Nodes.Add(t);

}

rdr.Close();

}

Regards,
Ritesh