/// <summary>
/// Load client object
/// </summary>
/// <param name="reader"></param>
private static void LoadClientObject(MySqlDataReader reader, ClientExtraInformation clientExtraInfo)
{
clientExtraInfo.UID = Convert.ToInt32(reader[FCMDBFieldName.ClientExtraInformation.UID]);
try { clientExtraInfo.ActionPlanDate = Convert.ToDateTime(reader[FCMDBFieldName.ClientExtraInformation.ActionPlanDate].ToString()); }
catch { clientExtraInfo.ActionPlanDate = Utils.MinDate; }
try { clientExtraInfo.CertificationTargetDate = Convert.ToDateTime(reader[FCMDBFieldName.ClientExtraInformation.CertificationTargetDate].ToString()); }
catch { clientExtraInfo.CertificationTargetDate = Utils.MinDate; }
try { clientExtraInfo.DateToEnterOnPolicies = Convert.ToDateTime(reader[FCMDBFieldName.ClientExtraInformation.DateToEnterOnPolicies].ToString()); }
catch { clientExtraInfo.DateToEnterOnPolicies = Utils.MinDate; }
clientExtraInfo.FKClientUID = Convert.ToInt32(reader[FCMDBFieldName.ClientExtraInformation.FKClientUID]);
clientExtraInfo.OperationalMeetingsFrequency = (reader[FCMDBFieldName.ClientExtraInformation.OperationalMeetingsFrequency].ToString());
clientExtraInfo.ProjectMeetingsFrequency = (reader[FCMDBFieldName.ClientExtraInformation.ProjectMeetingsFrequency].ToString());
clientExtraInfo.RegionsOfOperation = (reader[FCMDBFieldName.ClientExtraInformation.RegionsOfOperation].ToString());
clientExtraInfo.ScopeOfServices = (reader[FCMDBFieldName.ClientExtraInformation.ScopeOfServices].ToString());
clientExtraInfo.TimeTrading = (reader[FCMDBFieldName.ClientExtraInformation.TimeTrading].ToString());
// Audit Info
//
try { clientExtraInfo.UpdateDateTime = Convert.ToDateTime(reader[FCMDBFieldName.ClientExtraInformation.UpdateDateTime].ToString()); }
catch { clientExtraInfo.UpdateDateTime = DateTime.Now; }
try { clientExtraInfo.CreationDateTime = Convert.ToDateTime(reader[FCMDBFieldName.ClientExtraInformation.CreationDateTime].ToString()); }
catch { clientExtraInfo.CreationDateTime = DateTime.Now; }
try { clientExtraInfo.IsVoid = reader[FCMDBFieldName.ClientExtraInformation.IsVoid].ToString(); }
catch { clientExtraInfo.IsVoid = "N"; }
try { clientExtraInfo.UserIdCreatedBy = reader[FCMDBFieldName.ClientExtraInformation.UserIdCreatedBy].ToString(); }
catch { clientExtraInfo.UserIdCreatedBy = "N"; }
try { clientExtraInfo.UserIdUpdatedBy = reader[FCMDBFieldName.ClientExtraInformation.UserIdCreatedBy].ToString(); }
catch { clientExtraInfo.UserIdCreatedBy = "N"; }
clientExtraInfo.RecordVersion = Convert.ToInt32(reader[FCMDBFieldName.ClientExtraInformation.RecordVersion]);
}
/// <summary>
/// Get Client Extra Information
/// </summary>
public static ResponseStatus Read(ClientExtraInformation clientExtraInfo)
{
//
// EA SQL database
//
using (var connection = new MySqlConnection(ConnString.ConnectionString))
{
var commandString =
" SELECT " +
ClientFieldString() +
" FROM ClientExtraInformation" +
" WHERE FKClientUID = @UID";
using (var command = new MySqlCommand(
commandString, connection))
{
command.Parameters.Add("@UID", MySqlDbType.Int32).Value = clientExtraInfo.FKClientUID;
connection.Open();
MySqlDataReader reader = command.ExecuteReader();
if (reader.Read())
{
try
{
LoadClientObject(reader, clientExtraInfo);
}
catch (Exception)
{
clientExtraInfo.UID = 0;
}
}
else
{
return(new ResponseStatus()
{
ReturnCode = 1, ReasonCode = 2, Message = "Not found", XMessageType = MessageType.Warning
});
}
}
}
return(new ResponseStatus());
}
/// <summary>
/// Constructor
/// </summary>
/// <param name="callingForm"></param>
public UIClientDetails(Form callingForm)
{
_callingForm = callingForm;
InitializeComponent();
original = new Client(HeaderInfo.Instance);
setCurrentRowPosition(0);
clientContractList = new List <ClientContract>();
clientContract = new ClientContract();
clientList = new List <Client>();
// bindingSourceClient.DataSource = clientList;
bsClient.DataSource = clientList;
client = new Client(HeaderInfo.Instance);
clientExtraInformation = new ClientExtraInformation();
// Load User ID
//
var ua = UserAccess.List();
foreach (var user in ua)
{
comboUserID.Items.Add(user.UserID);
}
// Load Contractor Size
//
DocumentSetList dsl = new DocumentSetList();
dsl.List();
foreach (var documentSet in dsl.documentSetList)
{
comboContractorSize.Items.Add(documentSet.UIDNameDisplay);
}
// ucClientOtherInfo1.PopulateData(Utils.ClientID);
}
/// <summary>
/// Add SQL Parameters
/// </summary>
/// <param name="_uid"></param>
/// <param name="command"></param>
/// <param name="action"></param>
private static void AddSQLParameters(MySqlCommand command, string action, ClientExtraInformation clientExtraInfo)
{
command.Parameters.Add("@UID", MySqlDbType.Int32).Value = clientExtraInfo.UID;
command.Parameters.Add("@FKClientUID", MySqlDbType.VarChar).Value = clientExtraInfo.FKClientUID;
command.Parameters.Add("@ActionPlanDate", MySqlDbType.Date).Value = clientExtraInfo.ActionPlanDate;
command.Parameters.Add("@CertificationTargetDate", MySqlDbType.Date).Value = clientExtraInfo.CertificationTargetDate;
command.Parameters.Add("@DateToEnterOnPolicies", MySqlDbType.Date).Value = clientExtraInfo.DateToEnterOnPolicies;
command.Parameters.Add("@OperationalMeetingsFrequency", MySqlDbType.VarChar).Value = clientExtraInfo.OperationalMeetingsFrequency;
command.Parameters.Add("@ProjectMeetingsFrequency", MySqlDbType.VarChar).Value = clientExtraInfo.ProjectMeetingsFrequency;
command.Parameters.Add("@RegionsOfOperation", MySqlDbType.VarChar).Value = clientExtraInfo.RegionsOfOperation;
command.Parameters.Add("@ScopeOfServices", MySqlDbType.VarChar).Value = clientExtraInfo.ScopeOfServices;
command.Parameters.Add("@TimeTrading", MySqlDbType.VarChar).Value = clientExtraInfo.TimeTrading;
command.Parameters.Add("@UpdateDateTime", MySqlDbType.DateTime).Value = clientExtraInfo.UpdateDateTime;
command.Parameters.Add("@UserIdUpdatedBy", MySqlDbType.VarChar).Value = clientExtraInfo.UserIdUpdatedBy;
command.Parameters.Add("@IsVoid", MySqlDbType.VarChar).Value = clientExtraInfo.IsVoid;
command.Parameters.Add("@RecordVersion", MySqlDbType.Int32).Value = clientExtraInfo.RecordVersion;
if (action == MakConstant.SQLAction.CREATE)
{
command.Parameters.Add("@CreationDateTime", MySqlDbType.DateTime, 8).Value = clientExtraInfo.CreationDateTime;
command.Parameters.Add("@UserIdCreatedBy", MySqlDbType.VarChar).Value = clientExtraInfo.UserIdCreatedBy;
}
}
// -----------------------------------------------------
// Add new Client
// -----------------------------------------------------
public static ResponseStatus Insert(
HeaderInfo headerInfo,
ClientExtraInformation clientExtraInfo,
MySqlConnection connection)
{
ResponseStatus response = new ResponseStatus();
response.ReturnCode = 1;
response.ReasonCode = 1;
response.Message = "Client Added Successfully";
int _uid = 0;
int nextUID = GetLastUID() + 1; // 2010100000
clientExtraInfo.UID = nextUID;
DateTime _now = DateTime.Today;
clientExtraInfo.RecordVersion = 1;
clientExtraInfo.IsVoid = "N";
clientExtraInfo.CreationDateTime = headerInfo.CurrentDateTime;
clientExtraInfo.UpdateDateTime = headerInfo.CurrentDateTime;
clientExtraInfo.UserIdCreatedBy = headerInfo.UserID;
clientExtraInfo.UserIdUpdatedBy = headerInfo.UserID;
var commandString =
(
"INSERT INTO ClientExtraInformation " +
"(" +
ClientFieldString() +
")" +
" VALUES " +
"( @UID " +
", @FKClientUID " +
", @DateToEnterOnPolicies " +
", @ScopeOfServices " +
", @ActionPlanDate " +
", @CertificationTargetDate " +
", @TimeTrading " +
", @RegionsOfOperation " +
", @OperationalMeetingsFrequency " +
", @ProjectMeetingsFrequency " +
", @IsVoid " +
", @RecordVersion " +
", @UpdateDateTime " +
", @UserIdUpdatedBy " +
", @CreationDateTime " +
", @UserIdCreatedBy ) "
);
using (var command = new MySqlCommand(commandString, connection))
{
clientExtraInfo.RecordVersion = 1;
clientExtraInfo.IsVoid = "N";
AddSQLParameters(command, MakConstant.SQLAction.CREATE, clientExtraInfo);
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
command.ExecuteNonQuery();
}
response.Contents = _uid;
return(response);
}
/// <summary>
/// Update client details
/// </summary>
/// <returns></returns>
public static ResponseStatus Update(
HeaderInfo headerInfo,
ClientExtraInformation clientExtraInfo,
MySqlConnection connection)
{
var response = new ResponseStatus {
ReturnCode = 1, ReasonCode = 1, Message = "Client Updated Successfully."
};
// using (var connection = new MySqlConnection(ConnString.ConnectionString))
using (connection)
{
var commandString = (
"UPDATE ClientExtraInformation " +
" SET " +
FCMDBFieldName.ClientExtraInformation.ActionPlanDate + " = @" + FCMDBFieldName.ClientExtraInformation.ActionPlanDate + ", " +
FCMDBFieldName.ClientExtraInformation.CertificationTargetDate + " = @" + FCMDBFieldName.ClientExtraInformation.CertificationTargetDate + ", " +
FCMDBFieldName.ClientExtraInformation.DateToEnterOnPolicies + " = @" + FCMDBFieldName.ClientExtraInformation.DateToEnterOnPolicies + ", " +
FCMDBFieldName.ClientExtraInformation.OperationalMeetingsFrequency + " = @" + FCMDBFieldName.ClientExtraInformation.OperationalMeetingsFrequency + ", " +
FCMDBFieldName.ClientExtraInformation.ProjectMeetingsFrequency + " = @" + FCMDBFieldName.ClientExtraInformation.ProjectMeetingsFrequency + ", " +
FCMDBFieldName.ClientExtraInformation.RecordVersion + " = @" + FCMDBFieldName.ClientExtraInformation.RecordVersion + ", " +
FCMDBFieldName.ClientExtraInformation.RegionsOfOperation + " = @" + FCMDBFieldName.ClientExtraInformation.RegionsOfOperation + ", " +
FCMDBFieldName.ClientExtraInformation.ScopeOfServices + " = @" + FCMDBFieldName.ClientExtraInformation.ScopeOfServices + ", " +
FCMDBFieldName.ClientExtraInformation.TimeTrading + " = @" + FCMDBFieldName.ClientExtraInformation.TimeTrading + ", " +
FCMDBFieldName.ClientExtraInformation.IsVoid + " = @" + FCMDBFieldName.ClientExtraInformation.IsVoid + ", " +
FCMDBFieldName.ClientExtraInformation.UpdateDateTime + " = @" + FCMDBFieldName.ClientExtraInformation.UpdateDateTime + ", " +
FCMDBFieldName.ClientExtraInformation.UserIdUpdatedBy + " = @" + FCMDBFieldName.ClientExtraInformation.UserIdUpdatedBy +
" WHERE FKClientUID = @FKClientUID "
);
clientExtraInfo.RecordVersion++;
clientExtraInfo.UpdateDateTime = DateTime.Now;
clientExtraInfo.UserIdUpdatedBy = headerInfo.UserID;
clientExtraInfo.IsVoid = "N";
using (var command = new MySqlCommand(
cmdText: commandString,
connection: connection))
{
AddSQLParameters(command, MakConstant.SQLAction.UPDATE, clientExtraInfo);
try
{
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
command.ExecuteNonQuery();
}
catch (Exception ex)
{
LogFile.WriteToTodaysLogFile(ex.ToString(), headerInfo.UserID);
response.ReturnCode = -0020;
response.ReasonCode = 0001;
response.Message = "Error saving Client Extra Information. " + ex.ToString();
return(response);
}
}
}
return(response);
}
/// <summary>
/// Add client
/// </summary>
/// <param name="headerInfo"> </param>
/// <param name="eventClient"></param>
/// <param name="linkInitialSet"> </param>
/// <returns></returns>
public static ResponseStatus ClientAdd(HeaderInfo headerInfo,
Client.Client eventClient,
string linkInitialSet)
{
ResponseStatus response = new ResponseStatus();
//
// This is a new client.
//
if (string.IsNullOrEmpty(eventClient.Name))
{
response.ReturnCode = -0010;
response.ReasonCode = 0001;
response.Message = "Client Name is mandatory.";
response.Contents = 0;
return(response);
}
// --------------------------------------------------------------
// Check if user ID is already connected to a client
// --------------------------------------------------------------
#region Check if user is already connected to a client
if (!string.IsNullOrEmpty(eventClient.FKUserID))
{
var checkLinkedUser = new Client.Client {
FKUserID = eventClient.FKUserID
};
var responseLinked = checkLinkedUser.ReadLinkedUser();
if (!responseLinked.Successful)
{
return(responseLinked);
}
if (responseLinked.ReturnCode == 0001 && responseLinked.ReasonCode == 0001)
{
response.ReturnCode = -0010;
response.ReasonCode = 0002;
response.Message = "User ID is already linked to another client.";
response.Contents = 0;
return(response);
}
}
#endregion
var newClientUid = 0;
using (var connection = new SqlConnection(ConnString.ConnectionString))
{
using (var tr = new TransactionScope(TransactionScopeOption.Required))
{
connection.Open();
// -------------------------------
// Call method to add new client
// -------------------------------
var newClient = eventClient.Insert(headerInfo, connection);
// var newClientX = eventClient.MySQLInsert(headerInfo);
newClientUid = Convert.ToInt32(newClient.Contents);
// -------------------------------------------
// Call method to add client extra information
// -------------------------------------------
eventClient.clientExtraInformation.FKClientUID = eventClient.UID;
var cei = ClientExtraInformation.Insert(
HeaderInfo.Instance,
eventClient.clientExtraInformation,
connection);
if (cei.ReturnCode != 1)
{
// Rollback transaction
//
tr.Dispose();
return(cei);
}
// --------------------------------------------
// Add first document set
// --------------------------------------------
var cds = new ClientDocumentSet();
cds.FKClientUID = newClientUid;
// cds.FolderOnly = "CLIENT" + newClientUID.ToString().Trim().PadLeft(4, '0');
cds.FolderOnly = "CLIENT" + newClientUid.ToString(CultureInfo.InvariantCulture).Trim().PadLeft(4, '0');
// cds.Folder = FCMConstant.SYSFOLDER.CLIENTFOLDER + "\\CLIENT" + newClientUID.ToString().Trim().PadLeft(4, '0');
cds.Folder = FCMConstant.SYSFOLDER.CLIENTFOLDER + @"\" + cds.FolderOnly;
cds.SourceFolder = FCMConstant.SYSFOLDER.TEMPLATEFOLDER;
cds.Add(headerInfo, connection);
// --------------------------------------------
// Apply initial document set
// --------------------------------------------
if (linkInitialSet == "Y")
{
BUSClientDocument.AssociateDocumentsToClient(
clientDocumentSet: cds,
documentSetUID: eventClient.FKDocumentSetUID,
headerInfo: headerInfo);
// Fix Destination Folder Location
//
BUSClientDocumentGeneration.UpdateLocation(cds.FKClientUID, cds.ClientSetID);
}
// Commit transaction
//
tr.Complete();
}
}
ClientList(headerInfo);
// Return new client id
response.Contents = newClientUid;
return(response);
}
/// <summary>
/// Update client details
/// </summary>
/// <param name="headerInfo"> </param>
/// <param name="eventClient"> </param>
public static ResponseStatus ClientUpdate(HeaderInfo headerInfo, Client.Client eventClient)
{
var response = new ResponseStatus();
// --------------------------------------------------------------
// Check if user ID is already connected to a client
// --------------------------------------------------------------
var checkLinkedUser = new Client.Client
{
FKUserID = eventClient.FKUserID,
UID = eventClient.UID
};
if (!string.IsNullOrEmpty(eventClient.FKUserID))
{
var responseLinked = checkLinkedUser.ReadLinkedUser();
if (responseLinked.ReturnCode == 0001 && responseLinked.ReasonCode == 0001)
{
response.ReturnCode = 0001;
response.ReasonCode = 0002;
response.Message = "User ID is already linked to another client.";
response.Contents = 0;
return(response);
}
if (responseLinked.ReturnCode == 0001 && responseLinked.ReasonCode == 0003)
{
// All good. User ID is connected to Client Supplied.
}
if (responseLinked.ReturnCode == 0001 && responseLinked.ReasonCode == 0001)
{
response.ReturnCode = -0010;
response.ReasonCode = 0002;
response.Message = "User ID is already linked to another client.";
response.Contents = 0;
return(response);
}
}
using (var connection = new SqlConnection(ConnString.ConnectionString))
{
using (var tr = new TransactionScope(TransactionScopeOption.Required))
{
connection.Open();
var responseClientUpdate = eventClient.Update(headerInfo);
if (!responseClientUpdate.Successful)
{
// Rollback
tr.Dispose();
return(responseClientUpdate);
}
// -------------------------------------------
// Call method to add client extra information
// -------------------------------------------
var ceiResponse = ClientExtraInformation.Read(headerInfo, eventClient.UID);
if (ceiResponse.ReturnCode != 1)
{
// Rollback
tr.Dispose();
return(ceiResponse);
}
var ceiRead = (ClientExtraInformation)ceiResponse.Contents;
eventClient.clientExtraInformation.RecordVersion = ceiRead.RecordVersion;
var cei = ClientExtraInformation.Update(
headerInfo,
eventClient.clientExtraInformation);
if (!cei.Successful)
{
return(cei);
}
tr.Complete();
}
}
return(response);
}
