public void InsertData(string productId, string leadStatusCode, bool rightAdd, List<string> availableStatusList, string createdBy)
{
try
{
DateTime createdDate = DateTime.Now;
List<string> dbAvailableStatusList = null;
using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted }))
{
var config = slmdb.kkslm_ms_config_activity.Where(p => p.slm_Product_Id == productId && p.slm_LeadStatus == leadStatusCode && p.is_Deleted == false).FirstOrDefault();
if (config != null)
{
//ถ้ามีการเปลี่ยนแปลงสิทธิ์ ให้ลบข้อมูลเดิมที่อยู่ในเบส
if (config.slm_HaveRightAdd != rightAdd)
{
string del = "";
if (!string.IsNullOrEmpty(productId))
del = @"DELETE FROM " + SLMConstant.SLMDBName + ".dbo.kkslm_ms_config_activity WHERE slm_Product_Id = '" + productId + "' AND slm_LeadStatus = '" + leadStatusCode + "' ";
else
throw new Exception("Cannot find productId");
if (!string.IsNullOrEmpty(del))
slmdb.ExecuteStoreCommand(del);
}
}
if (rightAdd)
{
//หา AvailableStatus ที่มีอยู่ในเบส
if (!string.IsNullOrEmpty(productId))
dbAvailableStatusList = slmdb.kkslm_ms_config_activity.Where(p => p.slm_Product_Id == productId && p.slm_LeadStatus == leadStatusCode && p.is_Deleted == false).Select(p => p.slm_AvailableStatus).ToList();
else
throw new Exception("Cannot find productId");
//เอา branch ที่มีอยู่ในเบสแล้ว ออกจาก list ที่ต้องการ insert
if (dbAvailableStatusList.Count > 0)
availableStatusList = availableStatusList.Except<string>(dbAvailableStatusList).ToList();
if (availableStatusList.Count > 0)
{
foreach (string status in availableStatusList)
{
kkslm_ms_config_activity obj = new kkslm_ms_config_activity()
{
slm_Product_Id = string.IsNullOrEmpty(productId) ? null : productId,
slm_LeadStatus = leadStatusCode,
slm_HaveRightAdd = rightAdd,
slm_AvailableStatus = status,
slm_CreatedBy = createdBy,
slm_CreatedDate = createdDate,
slm_UpdatedBy = createdBy,
slm_UpdatedDate = createdDate,
is_Deleted = false
};
slmdb.kkslm_ms_config_activity.AddObject(obj);
}
slmdb.SaveChanges();
}
}
else
{
//ให้หาข้อเดิมในเบสก่อน ถ้ามีอยู่ไม่ต้องบันทึกซ้ำ
//เกิดจากการกด Insert แบบไม่มีสิทธิ์สองครั้ง
var config2 = slmdb.kkslm_ms_config_activity.Where(p => p.slm_Product_Id == productId && p.slm_LeadStatus == leadStatusCode && p.slm_HaveRightAdd == rightAdd && p.is_Deleted == false).FirstOrDefault();
if (config2 == null)
{
kkslm_ms_config_activity obj = new kkslm_ms_config_activity()
{
slm_Product_Id = string.IsNullOrEmpty(productId) ? null : productId,
slm_LeadStatus = leadStatusCode,
slm_HaveRightAdd = rightAdd,
slm_AvailableStatus = null,
slm_CreatedBy = createdBy,
slm_CreatedDate = createdDate,
slm_UpdatedBy = createdBy,
slm_UpdatedDate = createdDate,
is_Deleted = false
};
slmdb.kkslm_ms_config_activity.AddObject(obj);
slmdb.SaveChanges();
}
}
ts.Complete();
}
}
catch (Exception ex)
{
throw ex;
}
}
public void UpdateData(string productId, string leadStatusCode, bool rightAdd, List<string> availableStatusList, string createdBy)
{
try
{
DateTime createdDate = DateTime.Now;
using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted }))
{
string del = "";
if (!string.IsNullOrEmpty(productId))
del = @"DELETE FROM " + SLMConstant.SLMDBName + ".dbo.kkslm_ms_config_activity WHERE slm_Product_Id = '" + productId + "' AND slm_LeadStatus = '" + leadStatusCode + "' ";
else
throw new Exception("Cannot find productId");
if (!string.IsNullOrEmpty(del))
slmdb.ExecuteStoreCommand(del);
if (rightAdd)
{
foreach (string status in availableStatusList)
{
kkslm_ms_config_activity obj = new kkslm_ms_config_activity()
{
slm_Product_Id = string.IsNullOrEmpty(productId) ? null : productId,
slm_LeadStatus = leadStatusCode,
slm_HaveRightAdd = rightAdd,
slm_AvailableStatus = status,
slm_CreatedBy = createdBy,
slm_CreatedDate = createdDate,
slm_UpdatedBy = createdBy,
slm_UpdatedDate = createdDate,
is_Deleted = false
};
slmdb.kkslm_ms_config_activity.AddObject(obj);
}
}
else
{
kkslm_ms_config_activity obj = new kkslm_ms_config_activity()
{
slm_Product_Id = string.IsNullOrEmpty(productId) ? null : productId,
slm_LeadStatus = leadStatusCode,
slm_HaveRightAdd = rightAdd,
slm_AvailableStatus = null,
slm_CreatedBy = createdBy,
slm_CreatedDate = createdDate,
slm_UpdatedBy = createdBy,
slm_UpdatedDate = createdDate,
is_Deleted = false
};
slmdb.kkslm_ms_config_activity.AddObject(obj);
}
slmdb.SaveChanges();
ts.Complete();
}
}
catch (Exception ex)
{
throw ex;
}
}