private void HandleModify(BOL_CLIENT_CERTIFICATE oCLIENT_CERTIFICATE, string OPERATION, DataRow row)
{
string strMsg = "";
using (TransactionScope dbTxn = new TransactionScope())
{
CLIENT_CERTIFICATE DAL_CLIENT_CERTIFICATE = new CLIENT_CERTIFICATE(con);
//check condition
if (!DAL_CLIENT_CERTIFICATE.IsAlreadyUpdated(oCLIENT_CERTIFICATE, out strMsg)) // IF updated_at is not already modified
{
//MODIFY the record
DAL_CLIENT_CERTIFICATE.Update(oCLIENT_CERTIFICATE, CURRENT_DATETIME, CURRENT_USER, out strMsg);
}
else
{
//already use in another process
ResponseUtility.ReturnFailMessage(row);
return;
}
//return message and MK value
if (String.IsNullOrEmpty(strMsg)) //success
{
dbTxn.Complete();
ResponseUtility.ReturnSuccessMessage(row, UPDATED_AT_DATETIME, CURRENT_DATETIME, CURRENT_USER);
}
else //failed
{
ResponseUtility.ReturnFailMessage(row);
}
}
}
private void HandleDelete(BOL_CLIENT_CERTIFICATE oCLIENT_CERTIFICATE, DataRow row)
{
string strMsg = "";
using (TransactionScope dbTxn = new TransactionScope())
{
CLIENT_CERTIFICATE DAL_CLIENT_CERTIFICATE = new CLIENT_CERTIFICATE(con);
if (!DAL_CLIENT_CERTIFICATE.IsAlreadyUpdated(oCLIENT_CERTIFICATE, out strMsg)) // IF updated_at is not already modified
{
//delete the record
DAL_CLIENT_CERTIFICATE.Delete(oCLIENT_CERTIFICATE, out strMsg);
}
else
{
//already use in another process
ResponseUtility.ReturnFailMessage(row);
return;
}
//return message and MK value
if (String.IsNullOrEmpty(strMsg)) //success
{
response.Status = 1;
dbTxn.Complete();
ResponseUtility.ReturnDeleteSuccessMessage(row);
}
else //failed
{
ResponseUtility.ReturnFailMessage(row);
}
}
}
public bool PKKeyCheck(BOL_CLIENT_CERTIFICATE oCLIENT_CERTIFICATE, out string strMsg)
{
ConnectionMaster oMaster = new ConnectionMaster(strConnectionString, strPKKeyCheck);
oMaster.crudCommand.Parameters.Add(new SqlParameter("@CLIENT_CERTIFICATE_NO", oCLIENT_CERTIFICATE.CLIENT_CERTIFICATE_NO));
oMaster.ExcuteQuery(4, out strMsg);
int count;
try
{
count = int.Parse(oMaster.dtExcuted.Rows[0][0].ToString());
}
catch (Exception)
{
count = 0;
}
if (count > 0) //check
{
return(true);
}
else
{
return(false);
}
}
private void HandleInsert(BOL_CLIENT_CERTIFICATE oCLIENT_CERTIFICATE, string OPERATION, DataRow row)
{
string strMsg = "";
CLIENT_CERTIFICATE DAL_CLIENT_CERTIFICATE = new CLIENT_CERTIFICATE(con);
using (TransactionScope dbTxn = new TransactionScope())
{
if (!DAL_CLIENT_CERTIFICATE.PKKeyCheck(oCLIENT_CERTIFICATE, out strMsg)) // IF updated_at is not already modified
{
//delete the record
DAL_CLIENT_CERTIFICATE.Insert(oCLIENT_CERTIFICATE, CURRENT_DATETIME, CURRENT_USER, out strMsg);
}
else
{
ResponseUtility.ReturnFailMessage(row, String.Format(Utility.Messages.Jimugo.E000ZZ028, oCLIENT_CERTIFICATE.CLIENT_CERTIFICATE_NO));
return;
}
//return message and MK value
if (String.IsNullOrEmpty(strMsg)) //success
{
dbTxn.Complete();
ResponseUtility.ReturnSuccessMessage(row, UPDATED_AT_DATETIME, CURRENT_DATETIME, CURRENT_USER);
}
else //failed
{
ResponseUtility.ReturnFailMessage(row);
}
}
}
public bool IsAlreadyUpdated(BOL_CLIENT_CERTIFICATE oCLIENT_CERTIFICATE, out string strMsg)
{
strSearchWithKeyAndUpdated_at = StringUtil.handleNullStringDate(strSearchWithKeyAndUpdated_at, "@UPDATED_AT", oCLIENT_CERTIFICATE.UPDATED_AT_RAW);
ConnectionMaster oMaster = new ConnectionMaster(strConnectionString, strSearchWithKeyAndUpdated_at);
oMaster.crudCommand.Parameters.Add(new SqlParameter("@CLIENT_CERTIFICATE_NO", oCLIENT_CERTIFICATE.CLIENT_CERTIFICATE_NO));
oMaster.crudCommand.Parameters.Add(new SqlParameter("@UPDATED_AT", oCLIENT_CERTIFICATE.UPDATED_AT_RAW != null ? oCLIENT_CERTIFICATE.UPDATED_AT_RAW : (object)DBNull.Value));
oMaster.ExcuteQuery(4, out strMsg);
int count;
try
{
count = int.Parse(oMaster.dtExcuted.Rows[0][0].ToString());
}
catch (Exception)
{
count = 0;
}
if (count <= 0) //check
{
return(true);
}
else
{
return(false);
}
}
public void Delete(BOL_CLIENT_CERTIFICATE oCLIENT_CERTIFICATE, out String strMsg)
{
strDelete = StringUtil.handleNullStringDate(strDelete, "@UPDATED_AT", oCLIENT_CERTIFICATE.UPDATED_AT_RAW);
ConnectionMaster oMaster = new ConnectionMaster(strConnectionString, strDelete);
oMaster.crudCommand.Parameters.Add(new SqlParameter("@CLIENT_CERTIFICATE_NO", oCLIENT_CERTIFICATE.CLIENT_CERTIFICATE_NO));
oMaster.crudCommand.Parameters.Add(new SqlParameter("@UPDATED_AT", oCLIENT_CERTIFICATE.UPDATED_AT_RAW != null ? oCLIENT_CERTIFICATE.UPDATED_AT_RAW : (object)DBNull.Value));
oMaster.ExcuteQuery(3, out strMsg);
}
public void EmailButtonUpdate(BOL_CLIENT_CERTIFICATE oCLIENT_CERTIFICATE, string UPDATED_AT_DATETIME, string CURRENT_DATETIME, string CURRENT_USER, out String strMsg)
{
strEmailButtonUpdate = StringUtil.handleNullStringDate(strEmailButtonUpdate, "@UPDATED_AT", oCLIENT_CERTIFICATE.UPDATED_AT_RAW);
ConnectionMaster oMaster = new ConnectionMaster(strConnectionString, strEmailButtonUpdate);
oMaster.crudCommand.Parameters.Add(new SqlParameter("@DISTRIBUTION_DATE", UPDATED_AT_DATETIME));
oMaster.crudCommand.Parameters.Add(new SqlParameter("@CURRENT_DATETIME", CURRENT_DATETIME));
oMaster.crudCommand.Parameters.Add(new SqlParameter("@CURRENT_USER", CURRENT_USER));
oMaster.crudCommand.Parameters.Add(new SqlParameter("@CLIENT_CERTIFICATE_NO", oCLIENT_CERTIFICATE.CLIENT_CERTIFICATE_NO));
oMaster.crudCommand.Parameters.Add(new SqlParameter("@UPDATED_AT", oCLIENT_CERTIFICATE.UPDATED_AT_RAW != null ? oCLIENT_CERTIFICATE.UPDATED_AT_RAW : (object)DBNull.Value));
oMaster.ExcuteQuery(2, out strMsg);
}
public void Insert(BOL_CLIENT_CERTIFICATE oCLIENT_CERTIFICATE, string CURRENT_DATETIME, string CURRENT_USER, out String strMsg)
{
ConnectionMaster oMaster = new ConnectionMaster(strConnectionString, strInsert);
oMaster.crudCommand.Parameters.Add(new SqlParameter("@FY", oCLIENT_CERTIFICATE.FY));
oMaster.crudCommand.Parameters.Add(new SqlParameter("@CLIENT_CERTIFICATE_NO", oCLIENT_CERTIFICATE.CLIENT_CERTIFICATE_NO));
oMaster.crudCommand.Parameters.Add(new SqlParameter("@PASSWORD", oCLIENT_CERTIFICATE.PASSWORD));
oMaster.crudCommand.Parameters.Add(new SqlParameter("@EXPIRATION_DATE", oCLIENT_CERTIFICATE.EXPIRATION_DATE));
oMaster.crudCommand.Parameters.Add(new SqlParameter("@COMPANY_NO_BOX", oCLIENT_CERTIFICATE.COMPANY_NO_BOX != null ? oCLIENT_CERTIFICATE.COMPANY_NO_BOX : (object)DBNull.Value));
oMaster.crudCommand.Parameters.Add(new SqlParameter("@DISTRIBUTION_DATE", oCLIENT_CERTIFICATE.DISTRIBUTION_DATE != null ? oCLIENT_CERTIFICATE.DISTRIBUTION_DATE : (object)DBNull.Value));
oMaster.crudCommand.Parameters.Add(new SqlParameter("@CURRENT_DATETIME", CURRENT_DATETIME));
oMaster.crudCommand.Parameters.Add(new SqlParameter("@CURRENT_USER", CURRENT_USER));
oMaster.ExcuteQuery(1, out strMsg);
}
public void Update(BOL_CLIENT_CERTIFICATE oCLIENT_CERTIFICATE, string CURRENT_DATETIME, string CURRENT_USER, out String strMsg)
{
strUpdate = StringUtil.handleNullStringDate(strUpdate, "@UPDATED_AT", oCLIENT_CERTIFICATE.UPDATED_AT_RAW);
ConnectionMaster oMaster = new ConnectionMaster(strConnectionString, strUpdate);
oMaster.crudCommand.Parameters.Add(new SqlParameter("@PASSWORD", oCLIENT_CERTIFICATE.PASSWORD));
oMaster.crudCommand.Parameters.Add(new SqlParameter("@EXPIRATION_DATE", oCLIENT_CERTIFICATE.EXPIRATION_DATE));
oMaster.crudCommand.Parameters.Add(new SqlParameter("@COMPANY_NO_BOX", oCLIENT_CERTIFICATE.COMPANY_NO_BOX != null ? oCLIENT_CERTIFICATE.COMPANY_NO_BOX : (object)DBNull.Value));
oMaster.crudCommand.Parameters.Add(new SqlParameter("@CURRENT_DATETIME", CURRENT_DATETIME));
oMaster.crudCommand.Parameters.Add(new SqlParameter("@CURRENT_USER", CURRENT_USER));
oMaster.crudCommand.Parameters.Add(new SqlParameter("@UPDATED_AT", oCLIENT_CERTIFICATE.UPDATED_AT_RAW != null ? oCLIENT_CERTIFICATE.UPDATED_AT_RAW : (object)DBNull.Value));
oMaster.crudCommand.Parameters.Add(new SqlParameter("@CLIENT_CERTIFICATE_NO", oCLIENT_CERTIFICATE.CLIENT_CERTIFICATE_NO));
oMaster.ExcuteQuery(2, out strMsg);
}
//private bool IsRecordModified(BOL_CLIENT_CERTIFICATE oCLIENT_CERTIFICATE, out string strMsg)
//{
// //search by key and check if is same
// return DAL_CLIENT_CERTIFICATE.IsAlreadyUpdated(oCLIENT_CERTIFICATE, out strMsg);
//}
#endregion
#region CastToBOL_CLIENT_CERTIFICATE
private BOL_CLIENT_CERTIFICATE Cast_CLIENT_CERTIFICATE(DataRow row)
{
BOL_CLIENT_CERTIFICATE oCLIENT_CERTIFICATE = new BOL_CLIENT_CERTIFICATE();
oCLIENT_CERTIFICATE.FY = Utility_Component.dtColumnToInt(row["FY"].ToString());
oCLIENT_CERTIFICATE.CLIENT_CERTIFICATE_NO = row["CLIENT_CERTIFICATE_NO"].ToString();
oCLIENT_CERTIFICATE.PASSWORD = row["PASSWORD"].ToString();
oCLIENT_CERTIFICATE.EXPIRATION_DATE = Utility_Component.dtColumnToDateTime(row["EXPIRATION_DATE"].ToString());
oCLIENT_CERTIFICATE.COMPANY_NO_BOX = row["COMPANY_NO_BOX"].ToString();
oCLIENT_CERTIFICATE.DISTRIBUTION_DATE = Utility_Component.dtColumnToDateTime(row["DISTRIBUTION_DATE"].ToString());
oCLIENT_CERTIFICATE.UPDATED_AT = row["UPDATED_AT"].ToString().Length >= 1 ? row["UPDATED_AT"].ToString() : null;
oCLIENT_CERTIFICATE.UPDATED_AT_RAW = row["UPDATED_AT_RAW"].ToString() == "" ? null : row["UPDATED_AT_RAW"].ToString();
oCLIENT_CERTIFICATE.UPDATED_BY = row["UPDATED_BY"].ToString().Length >= 1 ? row["UPDATED_BY"].ToString() : null;
return(oCLIENT_CERTIFICATE);
}
public MetaResponse UpdateClientCertificateList(string ClientCertificateList, string authHeader)
{
try
{
DataTable dgvList = Utility.Utility_Component.JsonToDt(ClientCertificateList);
for (int i = 0; i < dgvList.Rows.Count; i++)
{
BOL_CLIENT_CERTIFICATE oCLIENT_CERTIFICATE = new BOL_CLIENT_CERTIFICATE();
oCLIENT_CERTIFICATE = Cast_CLIENT_CERTIFICATE(dgvList.Rows[i]);
//assign login ID
switch (dgvList.Rows[i]["MK"].ToString())
{
case "I":
HandleInsert(oCLIENT_CERTIFICATE, "I", dgvList.Rows[i]);
break;
case "C":
HandleInsert(oCLIENT_CERTIFICATE, "C", dgvList.Rows[i]);
break;
case "M":
HandleModify(oCLIENT_CERTIFICATE, "M", dgvList.Rows[i]);
break;
case "D":
HandleDelete(oCLIENT_CERTIFICATE, dgvList.Rows[i]);
break;
default:
break;
}
}
response.Data = Utility.Utility_Component.DtToJSon(dgvList, "CompanyCodeList Update");
timer.Stop();
response.Meta.Duration = timer.Elapsed.TotalMilliseconds;
return(response);
}
catch (Exception ex)
{
return(ResponseUtility.GetUnexpectedResponse(response, timer, ex));
}
}
public MetaResponse SendMail(string ClientCertificateList)
{
try
{
string strMsg = "";
bool mailSuccess = false;
ArrayList company_no_boxs = new ArrayList();
DataTable dgvList = Utility.Utility_Component.JsonToDt(ClientCertificateList);
List <string> l_SentMail = new List <string>();
for (int i = 0; i < dgvList.Rows.Count; i++)
{
DataRow dr = dgvList.Rows[i];
string l_COMPANY_NO_BOX = dr["COMPANY_NO_BOX"].ToString();
var certificate_list = dgvList.AsEnumerable().Where(row => row.Field <string>("COMPANY_NO_BOX") == l_COMPANY_NO_BOX).Select(s => s.Field <string>("CLIENT_CERTIFICATE_NO")).ToArray();
BOL_CLIENT_CERTIFICATE oCLIENT_CERTIFICATE = new BOL_CLIENT_CERTIFICATE();
oCLIENT_CERTIFICATE = Cast_CLIENT_CERTIFICATE(dr); //check method
BOL_REQUEST_DETAIL oREQUEST_DETAIL = new BOL_REQUEST_DETAIL();
oREQUEST_DETAIL = Cast_REQUEST_DETAIL(dr);
CLIENT_CERTIFICATE DAL_CLIENT_CERTIFICATE = new CLIENT_CERTIFICATE(con);
if (l_SentMail.Where(x => x == l_COMPANY_NO_BOX).ToList().Count <= 0)
{
l_SentMail.Add(l_COMPANY_NO_BOX);
mailSuccess = PrepareAndSendMail(certificate_list, oREQUEST_DETAIL.COMPANY_NAME, oREQUEST_DETAIL.CLIENT_CERTIFICATE_SEND_EMAIL_ADDRESS, oCLIENT_CERTIFICATE.EXPIRATION_DATE, oCLIENT_CERTIFICATE.PASSWORD, oCLIENT_CERTIFICATE.FY);
if (mailSuccess)
{
//update email sent date
DAL_CLIENT_CERTIFICATE.EmailButtonUpdate(oCLIENT_CERTIFICATE, UPDATED_AT_DATETIME, CURRENT_DATETIME, CURRENT_USER, out strMsg);
if (String.IsNullOrEmpty(strMsg))
{
//success
dr["DISTRIBUTION_DATE"] = UPDATED_AT_DATETIME;
ResponseUtility.ReturnMailSuccessMessage(dr, UPDATED_AT_DATETIME, CURRENT_DATETIME, CURRENT_USER);
}
else
{
//already use in another process
ResponseUtility.ReturnFailMessage(dr);
}
}
else
{
//fail message
ResponseUtility.MailSendingFail(dr);
}
}
else
{
//update email sent date
DAL_CLIENT_CERTIFICATE.EmailButtonUpdate(oCLIENT_CERTIFICATE, UPDATED_AT_DATETIME, CURRENT_DATETIME, CURRENT_USER, out strMsg);
if (String.IsNullOrEmpty(strMsg))
{
//success
dr["DISTRIBUTION_DATE"] = UPDATED_AT_DATETIME;
ResponseUtility.ReturnMailSuccessMessage(dr, UPDATED_AT_DATETIME, CURRENT_DATETIME, CURRENT_USER);
}
else
{
//already use in another process
ResponseUtility.ReturnFailMessage(dr);
}
}
}
response.Data = Utility.Utility_Component.DtToJSon(dgvList, "Mail status");;
timer.Stop();
response.Meta.Duration = timer.Elapsed.TotalSeconds;
return(response);
}
catch (Exception ex)
{
return(ResponseUtility.GetUnexpectedResponse(response, timer, ex));
}
}