public void Update(BOL_USAGE_CHARGE_MASTER oUSAGE_FEE_MASTER, string CURRENT_DATETIME, string CURRENT_USER, out String strMsg)
{
strUpdate = StringUtil.handleNullStringDate(strUpdate, "@UPDATED_AT", oUSAGE_FEE_MASTER.UPDATED_AT_RAW.ToString().Trim());
ConnectionMaster oMaster = new ConnectionMaster(strConnectionString, strUpdate);
oMaster.crudCommand.Parameters.Add(new SqlParameter("@FEE_STRUCTURE", oUSAGE_FEE_MASTER.FEE_STRUCTURE != null ? oUSAGE_FEE_MASTER.FEE_STRUCTURE : (object)DBNull.Value));
oMaster.crudCommand.Parameters.Add(new SqlParameter("@CONTRACT_NAME", oUSAGE_FEE_MASTER.CONTRACT_NAME != null ? oUSAGE_FEE_MASTER.CONTRACT_NAME : (object)DBNull.Value));
oMaster.crudCommand.Parameters.Add(new SqlParameter("@CONTRACT_QTY", oUSAGE_FEE_MASTER.CONTRACT_QTY != null ? oUSAGE_FEE_MASTER.CONTRACT_QTY : (object)DBNull.Value));
oMaster.crudCommand.Parameters.Add(new SqlParameter("@CONTRACT_UNIT", oUSAGE_FEE_MASTER.CONTRACT_UNIT != null ? oUSAGE_FEE_MASTER.CONTRACT_UNIT : (object)DBNull.Value));
oMaster.crudCommand.Parameters.Add(new SqlParameter("@INITIAL_COST", oUSAGE_FEE_MASTER.INITIAL_COST != null ? oUSAGE_FEE_MASTER.INITIAL_COST : (object)DBNull.Value));
oMaster.crudCommand.Parameters.Add(new SqlParameter("@MONTHLY_COST", oUSAGE_FEE_MASTER.MONTHLY_COST != null ? oUSAGE_FEE_MASTER.MONTHLY_COST : (object)DBNull.Value));
oMaster.crudCommand.Parameters.Add(new SqlParameter("@INPUT_TYPE", oUSAGE_FEE_MASTER.INPUT_TYPE != null ? oUSAGE_FEE_MASTER.INPUT_TYPE : (object)DBNull.Value));
oMaster.crudCommand.Parameters.Add(new SqlParameter("@NUMBER_DEFAULT", oUSAGE_FEE_MASTER.NUMBER_DEFAULT != null ? oUSAGE_FEE_MASTER.NUMBER_DEFAULT : (object)DBNull.Value));
oMaster.crudCommand.Parameters.Add(new SqlParameter("@MEMO", oUSAGE_FEE_MASTER.MEMO != null ? oUSAGE_FEE_MASTER.MEMO : (object)DBNull.Value));
oMaster.crudCommand.Parameters.Add(new SqlParameter("@DISPLAY_ORDER", oUSAGE_FEE_MASTER.DISPLAY_ORDER != null ? oUSAGE_FEE_MASTER.DISPLAY_ORDER : (object)DBNull.Value));
oMaster.crudCommand.Parameters.Add(new SqlParameter("@CONTRACT_CODE", oUSAGE_FEE_MASTER.CONTRACT_CODE.Trim()));
oMaster.crudCommand.Parameters.Add(new SqlParameter("@ADOPTION_DATE", oUSAGE_FEE_MASTER.ADOPTION_DATE));
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", oUSAGE_FEE_MASTER.UPDATED_AT_RAW != null ? oUSAGE_FEE_MASTER.UPDATED_AT_RAW : (object)DBNull.Value));
oMaster.ExcuteQuery(2, out strMsg);
}
public bool IsAlreadyUpdated(BOL_USAGE_CHARGE_MASTER oUSAGE_FEE_MASTER, out string strMsg)
{
strSearchWithKeyAndUpdated_at = StringUtil.handleNullStringDate(strSearchWithKeyAndUpdated_at, "@UPDATED_AT", oUSAGE_FEE_MASTER.UPDATED_AT_RAW);
ConnectionMaster oMaster = new ConnectionMaster(strConnectionString, strSearchWithKeyAndUpdated_at);
oMaster.crudCommand.Parameters.Add(new SqlParameter("@CONTRACT_CODE", oUSAGE_FEE_MASTER.CONTRACT_CODE));
oMaster.crudCommand.Parameters.Add(new SqlParameter("@ADOPTION_DATE", oUSAGE_FEE_MASTER.ADOPTION_DATE));
oMaster.crudCommand.Parameters.Add(new SqlParameter("@UPDATED_AT", oUSAGE_FEE_MASTER.UPDATED_AT_RAW != null ? oUSAGE_FEE_MASTER.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 bool PKKeyCheck(BOL_USAGE_CHARGE_MASTER oUSAGE_FEE_MASTER, out string strMsg)
{
ConnectionMaster oMaster = new ConnectionMaster(strConnectionString, strPKKeyCheck);
oMaster.crudCommand.Parameters.Add(new SqlParameter("@CONTRACT_CODE", oUSAGE_FEE_MASTER.CONTRACT_CODE));
oMaster.crudCommand.Parameters.Add(new SqlParameter("@ADOPTION_DATE", oUSAGE_FEE_MASTER.ADOPTION_DATE));
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 HandleDelete(BOL_USAGE_CHARGE_MASTER oUSAGE_CHARGE_MASTER, DataRow row)
{
string strMsg = "";
using (TransactionScope dbTxn = new TransactionScope())
{
USAGE_CHARGE_MASTER DAL_USAGE_CHARGE_MASTER = new USAGE_CHARGE_MASTER(con);
if (!DAL_USAGE_CHARGE_MASTER.IsAlreadyUpdated(oUSAGE_CHARGE_MASTER, out strMsg)) // IF updated_at is not already modified
{
//delete the record
DAL_USAGE_CHARGE_MASTER.Delete(oUSAGE_CHARGE_MASTER, out strMsg);
}
else
{
dbTxn.Dispose();
ResponseUtility.ReturnFailMessage(row);
return;
}
//return message and MK value
if (String.IsNullOrEmpty(strMsg)) //success
{
dbTxn.Complete();
ResponseUtility.ReturnDeleteSuccessMessage(row);
}
else //failed
{
ResponseUtility.ReturnFailMessage(row);
}
}
}
private void HandleModify(BOL_USAGE_CHARGE_MASTER oUSAGE_CHARGE_MASTER, string OPERATION, DataRow row)
{
string strMsg = "";
using (TransactionScope dbTxn = new TransactionScope())
{
USAGE_CHARGE_MASTER DAL_USAGE_CHARGE_MASTER = new USAGE_CHARGE_MASTER(con);
//check condition
if (!DAL_USAGE_CHARGE_MASTER.IsAlreadyUpdated(oUSAGE_CHARGE_MASTER, out strMsg)) // IF updated_at is not already modified
{
DAL_USAGE_CHARGE_MASTER.Update(oUSAGE_CHARGE_MASTER, 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 HandleInsert(BOL_USAGE_CHARGE_MASTER oUSAGE_CHARGE_MASTER, string OPERATION, DataRow row)
{
string strMsg = "";
USAGE_CHARGE_MASTER DAL_USAGE_CHARGE_MASTER = new USAGE_CHARGE_MASTER(con);
using (TransactionScope dbTxn = new TransactionScope())
{
if (!DAL_USAGE_CHARGE_MASTER.PKKeyCheck(oUSAGE_CHARGE_MASTER, out strMsg)) // IF updated_at is not already modified
{
//delete the record
DAL_USAGE_CHARGE_MASTER.Insert(oUSAGE_CHARGE_MASTER, CURRENT_DATETIME, CURRENT_USER, out strMsg);
}
else
{
ResponseUtility.ReturnFailMessage(row, String.Format(Utility.Messages.Jimugo.E000ZZ028, "契約コードと採用日の組み合わせ"));
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 BOL_USAGE_CHARGE_MASTER Cast_USAGE_CHARGE_MASTER(DataRow row)
{
BOL_USAGE_CHARGE_MASTER oUSAGE_FEE_MASTER = new BOL_USAGE_CHARGE_MASTER();
string fee_master = row["FEE_STRUCTURE"].ToString().Trim();
switch (fee_master)
{
case "基本契約":
oUSAGE_FEE_MASTER.FEE_STRUCTURE = "BASIC";
break;
case "オプション":
oUSAGE_FEE_MASTER.FEE_STRUCTURE = "OPTION";
break;
case "サービスデスク":
oUSAGE_FEE_MASTER.FEE_STRUCTURE = "SD";
break;
default:
oUSAGE_FEE_MASTER.FEE_STRUCTURE = null;
break;
}
//oUSAGE_FEE_MASTER.FEE_STRUCTURE = row["FEE_STRUCTURE"].ToString();
oUSAGE_FEE_MASTER.CONTRACT_CODE = row["CONTRACT_CODE"].ToString().Trim();
oUSAGE_FEE_MASTER.CONTRACT_NAME = row["CONTRACT_NAME"].ToString().Trim();
oUSAGE_FEE_MASTER.CONTRACT_QTY = Utility_Component.dtColumnToInt(row["CONTRACT_QTY"].ToString().Trim());
oUSAGE_FEE_MASTER.CONTRACT_UNIT = row["CONTRACT_UNIT"].ToString().Trim();
oUSAGE_FEE_MASTER.ADOPTION_DATE = Utility_Component.dtColumnToDateTime(row["ADOPTION_DATE"].ToString().Trim());
oUSAGE_FEE_MASTER.INITIAL_COST = Utility_Component.dtColumnToDecimal(row["INITIAL_COST"].ToString().Trim());
oUSAGE_FEE_MASTER.MONTHLY_COST = Utility_Component.dtColumnToDecimal(row["MONTHLY_COST"].ToString().Trim());
string input_type = row["INPUT_TYPE"].ToString();
switch (input_type)
{
case "選択":
oUSAGE_FEE_MASTER.INPUT_TYPE = 1;
break;
case "数量":
oUSAGE_FEE_MASTER.INPUT_TYPE = 2;
break;
default:
oUSAGE_FEE_MASTER.INPUT_TYPE = null;
break;
}
//oUSAGE_FEE_MASTER.INPUT_TYPE = Utility_Component.dtColumnToInt(row["INPUT_TYPE"].ToString());
oUSAGE_FEE_MASTER.NUMBER_DEFAULT = Utility_Component.dtColumnToInt(row["NUMBER_DEFAULT"].ToString());
oUSAGE_FEE_MASTER.MEMO = row["MEMO"].ToString();
oUSAGE_FEE_MASTER.DISPLAY_ORDER = Utility_Component.dtColumnToInt(row["DISPLAY_ORDER"].ToString());
oUSAGE_FEE_MASTER.UPDATED_AT = row["UPDATED_AT"].ToString().Length >= 1 ? row["UPDATED_AT"].ToString() : null;
oUSAGE_FEE_MASTER.UPDATED_AT_RAW = row["UPDATED_AT_RAW"].ToString() == "" ? null : row["UPDATED_AT_RAW"].ToString();
oUSAGE_FEE_MASTER.UPDATED_BY = row["UPDATED_BY"].ToString().Length >= 1 ? row["UPDATED_BY"].ToString() : null;
return(oUSAGE_FEE_MASTER);
}
public void Delete(BOL_USAGE_CHARGE_MASTER oUSAGE_FEE_MASTER, out String strMsg)
{
if (oUSAGE_FEE_MASTER.UPDATED_AT_RAW != null)
{
strDelete = strDelete.Replace("@UPDATED_AT", oUSAGE_FEE_MASTER.UPDATED_AT_RAW.ToString().Trim());
}
else
{
strDelete = strDelete.Replace("=@UPDATED_AT", "IS NULL");
}
ConnectionMaster oMaster = new ConnectionMaster(strConnectionString, strDelete);
oMaster.crudCommand.Parameters.Add(new SqlParameter("@CONTRACT_CODE", oUSAGE_FEE_MASTER.CONTRACT_CODE.Trim()));
oMaster.crudCommand.Parameters.Add(new SqlParameter("@ADOPTION_DATE", oUSAGE_FEE_MASTER.ADOPTION_DATE));
oMaster.ExcuteQuery(3, out strMsg);
}
public MetaResponse UpdateUsageFeeMasterList(string ClientCertificateList)
{
DataTable dgvList = Utility.Utility_Component.JsonToDt(ClientCertificateList);
for (int i = 0; i < dgvList.Rows.Count; i++)
{
BOL_USAGE_CHARGE_MASTER oUSAGE_CHARGE_MASTER = new BOL_USAGE_CHARGE_MASTER();
oUSAGE_CHARGE_MASTER = Cast_USAGE_CHARGE_MASTER(dgvList.Rows[i]);
switch (dgvList.Rows[i]["MK"].ToString())
{
case "I":
HandleInsert(oUSAGE_CHARGE_MASTER, "I", dgvList.Rows[i]);
break;
case "C":
HandleInsert(oUSAGE_CHARGE_MASTER, "C", dgvList.Rows[i]);
break;
case "M":
HandleModify(oUSAGE_CHARGE_MASTER, "M", dgvList.Rows[i]);
break;
case "D":
HandleDelete(oUSAGE_CHARGE_MASTER, dgvList.Rows[i]);
break;
default:
break;
}
}
response.Data = Utility.Utility_Component.DtToJSon(dgvList, "UsageChargeMaster Update");
timer.Stop();
response.Meta.Duration = timer.Elapsed.TotalMilliseconds;
return(response);
}