Mattersphere Developers Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.
Mattersphere Developers Forum

This is a forum for developers of the case management software Mattersphere. Share your experiences and code with other like-minded developers.


Mattersphere DOCID format to reduce length of numbers

Mattersphere Developers Forum :: SDK Development :: Miscellaneous
Go down
avatar
Admin
Admin
Posts : 122
Join date : 2018-12-17
https://mattersphere-devs.forumotion.com
+

Mattersphere DOCID format to reduce length of numbers Empty Mattersphere DOCID format to reduce length of numbers

Tue 18 Dec 2018 - 11:50
Original post by Janet Cook: janet.cook@lbmw.com


Dear All

Does anyone know please whether it is possible to change the format of the document ID number each year to reduce the number of digits in the DOCID. At the moment we have the DocID set to number sequentially but in a few years time the number will be large. It will not look very good in the letterhead footers if the number is too big. We were hoping to restart the numbering so that it would read for 2019: 1901, 1902 etc and for 2020 2001, 2002 etc.

Thanks in advance.

Janet
avatar
Admin
Admin
Posts : 122
Join date : 2018-12-17
https://mattersphere-devs.forumotion.com
+

Mattersphere DOCID format to reduce length of numbers Empty Re: Mattersphere DOCID format to reduce length of numbers

Tue 18 Dec 2018 - 11:54
Original post by Steven Rodney: srodney@kingsleynapley.co.uk


The docid is created by SQL Server NOT Mattersphere. It's an incrementing number called IDENTITY.

Rather than breaking the entire document table and it's children, why not add extended data on to the document object and call it [computedDocID]

Assumptions
Your working have cloned a customised save wizard called. [udSCRDOCNEW - Document Save Wizard]



  1. Create extended data table for OMSdocument object called [uEDCExtraInfo] using below SQL code... called [uEDCExtraInfo.sql]
  2. Add attached C# code to Document Save Wizard [udSCRDOCNEW.cs]
  3. Alter your letterhead footers to point to the new field.


The C# code has not been completely check.

Code:
IF EXISTS (
 SELECT a.name
 , a.type
 FROM sysobjects a
 WHERE a.name = 'uEDCExtraInfo'
 AND a.type = 'U'
 )
BEGIN
 DROP TABLE [dbo].[uEDCExtraInfo]
END
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (
 SELECT a1.name
 FROM sys.indexes a1
 WHERE a1.name = 'IX_uEDCExtraInfo_computedDocID'
 )
BEGIN
 DROP INDEX IX_uEDCExtraInfo_computedDocID ON [dbo].[uEDCExtraInfo]
END

CREATE TABLE [dbo].[uEDCExtraInfo] (
 docID BIGINT NOT NULL
 , computedDocID NVARCHAR(255)
 , CONSTRAINT PK_uEDCExtraInfo_fileID PRIMARY KEY CLUSTERED (docID) WITH (IGNORE_DUP_KEY = OFF)
 , CONSTRAINT UQ_uEDCExtraInfo_computedDocID UNIQUE (computedDocID)
 )

IF NOT EXISTS (
 SELECT a1.major_id
 FROM sys.database_permissions a1
 INNER JOIN sys.objects b1 ON b1.object_id = a1.major_id
 INNER JOIN sys.database_principals c1 ON c1.principal_id = a1.grantee_principal_id
 WHERE b1.name = 'uEDCExtraInfo'
 AND b1.type = 'U'
 )
BEGIN
 GRANT SELECT
 , DELETE
 , INSERT
 , UPDATE
 ON [uEDCExtraInfo]
 TO [OMSApplicationRole]
END
GO

CREATE NONCLUSTERED INDEX IX_uEDCExtraInfo_computedDocID ON uEDCExtraInfo (computedDocID)
GO

/*

SELECt * FROM uEDCExtraInfo

*/

Code:
private string GetNextComputedDocID(FWBS.OMS.OMSDocument doc)
{
    string mySql = string.Empty;
    long docID = doc.ID;
    DateTime docCreatedDate = Convert.ToDateTime(doc.GetExtraInfo("created"));
    string lastTwoDigitsYear = docCreatedDate.ToString("yy");
    
    //********************************************************
    // Reportimg Server Licence name
    //********************************************************
    const string LICENCE_NAME = @"FWBS Limited 2005";

    mySql = "SELECT COUNT(docID) AS [nextComputedDocID] FROM dbo.dbDocument WHERE 1 = 1 AND YEAR([created]) = YEAR(GETUTCDATE()) AND docid <= @docid";
    
    //********************************************************
    //Set up database reporting server
    //********************************************************
    FWBS.OMS.ReportingServer myReportingServer = new FWBS.OMS.ReportingServer(LICENCE_NAME);
    System.Collections.Generic.List<System.Data.IDataParameter> myParams = new System.Collections.Generic.List<System.Data.IDataParameter>();
    myParams.Add(myReportingServer.Connection.AddParameter("docid", docID));
    
    System.Data.DataTable myResults = myReportingServer.Connection.ExecuteSQLTable(mySql, null, myParams.ToArray());
    
    if (myResults.Rows.Count >= 1)
    {
        if(myResults.Rows[0]["nextComputedDocID"] != DBNull.Value)
        {
            return lastTwoDigitsYear + Convert.ToString(myResults.Rows[0]["nextComputedDocID"]);
        }
    }
    
    return string.Empty;
}

private void InsertComputedDocID()
{
    FWBS.OMS.OMSDocument doc = EnquiryForm.Enquiry.Object as FWBS.OMS.OMSDocument;
    
    if((doc.ExtendedData["uEDCExtraInfo"].GetExtendedData("computedDocID")) != DBNull.Value)
    {
        string computedDocID = GetNextComputedDocID(doc);
        
        doc.ExtendedData["uEDCExtraInfo"].SetExtendedData("computedDocID", computedDocID);
        
        doc.Update();
    }
}
        
protected override void Finished(object sender, System.EventArgs e)
{
    InsertComputedDocID();
}
Back to top
Permissions in this forum:
You cannot reply to topics in this forum