public MemberDto GetAnalystByAnalystId(int analystId)
{
var cn = new SqlConnection(GetConnection());
var analyst = new MemberDto();
try
{
cn.Open();
string cmdText = "select m.*,mh.HorizonId,mps.ProductCategoryId,ms.StrategyId,msr.ResearchId from Members m left join MemberHorizon mh on mh.MemberId = m.MemberId left join MemberProductSegments mps on mps.MemberId = m.MemberId left join MemberStrategy ms on ms.MemberId=m.MemberId left join MemberStyleResearch msr on msr.MemberId = m.MemberId where m.MemberId =" + analystId;
var cmd = new SqlCommand(cmdText, cn);
var isEnter = false;
var dr = cmd.ExecuteReader();
var productCategoryIds = new List<int>();
var styleResearchIds = new List<int>();
var strategyIds = new List<int>();
var horizonIds = new List<int>();
while (dr.Read())
{
if (!isEnter)
{
analyst.FirstName = Convert.ToString(dr["FirstName"]);
analyst.LastName = Convert.ToString(dr["LastName"]);
analyst.Organization = Convert.ToString(dr["Organization"]);
analyst.Mobile = Convert.ToString(dr["Mobile"]);
analyst.LandLine = Convert.ToString(dr["LandLine"]);
analyst.ResearchExpreince = dr.IsDBNull(12) ? 0 : Convert.ToInt32(dr["ResearchExpreince"]);
analyst.HigherDegreeId = Convert.ToInt32(dr["HigherDegreeId"]);
isEnter = true;
}
var producttCatId = dr.IsDBNull(26) ? 0 : Convert.ToInt32(dr["ProductCategoryId"]);
var styleResearchId = dr.IsDBNull(28) ? 0 : Convert.ToInt32(dr["ResearchId"]);
var strategyId = dr.IsDBNull(27) ? 0 : Convert.ToInt32(dr["StrategyId"]);
var horizonId = dr.IsDBNull(25) ? 0 : Convert.ToInt32(dr["HorizonId"]);
if (producttCatId > 0 && !productCategoryIds.Contains(producttCatId))
productCategoryIds.Add(producttCatId);
if (styleResearchId > 0 && !styleResearchIds.Contains(styleResearchId))
styleResearchIds.Add(styleResearchId);
if (strategyId > 0 && !strategyIds.Contains(strategyId))
strategyIds.Add(strategyId);
if (horizonId > 0 && !horizonIds.Contains(horizonId))
horizonIds.Add(horizonId);
}
analyst.ProductCategoryIds = productCategoryIds;
analyst.StyleResearchIds = styleResearchIds;
analyst.StrategyIds = strategyIds;
analyst.HorizonIds = horizonIds;
cn.Close();
}
catch (Exception)
{
cn.Close();
}
return analyst;
}
public bool RegisterAnalyst(MemberDto memberDto)
{
var cn = new SqlConnection(GetConnection());
SqlTransaction trx = null;
var isInsert = false;
try
{
cn.Open();
trx = cn.BeginTransaction();
string cmdText = " insert into Members(FirstName,LastName,Organization,Mobile,LandLine,ResearchExpreince,HigherDegreeId,Type,IsApprove,CreateUserId,CreateTimeStamp,ModifiedUserId,ModifiedTimeStamp) values('" + memberDto.FirstName + "','" + memberDto.LastName + "','" + memberDto.Organization + "','" + memberDto.Mobile + "','" + memberDto.LandLine + "'," + memberDto.ResearchExpreince + "," + memberDto.HigherDegreeId + "," + memberDto.Type + ",'true'," + memberDto.CreateUserId + ",'" + DateTime.Now.ToString("yyyy-MM-dd") + "'," + memberDto.ModifiedUserId + ",'" + DateTime.Now.ToString("yyyy-MM-dd") + "') select Scope_Identity();";
var cmd = new SqlCommand(cmdText, cn) { Transaction = trx };
var memberId = Convert.ToInt32(cmd.ExecuteScalar());
foreach (var segment in memberDto.MemberProductSegmentsDtos)
(new SqlCommand("insert into MemberProductSegments(MemberId,ProductCategoryId) values( " + memberId + "," + segment.ProductCategoryId + ")", cn) { Transaction = trx }).ExecuteNonQuery();
foreach (var style in memberDto.MemberStyleResearches)
(new SqlCommand("insert into MemberStyleResearch(MemberId,ResearchId) values( " + memberId + "," + style.ResearchId + ")", cn) { Transaction = trx }).ExecuteNonQuery();
foreach (var strategy in memberDto.MemberStrategyDtos)
(new SqlCommand("insert into MemberStrategy(MemberId,StrategyId) values( " + memberId + "," + strategy.StrategyId + ")", cn) { Transaction = trx }).ExecuteNonQuery();
foreach (var horizon in memberDto.MemberHorizonDtos)
(new SqlCommand("insert into MemberHorizon(MemberId,HorizonId) values( " + memberId + "," + horizon.HorizonId + ")", cn) { Transaction = trx }).ExecuteNonQuery();
foreach (var documents in memberDto.MemberDocumentsDtos)
(new SqlCommand("insert into MemberDocuments(MemberId,DocumentName,FileName) values( " + memberId + ",'" + documents.DocumentName + "','" + documents.FileName + "')", cn) { Transaction = trx }).ExecuteNonQuery();
trx.Commit();
isInsert = true;
cn.Close();
}
catch (Exception)
{
if (trx != null) trx.Rollback();
cn.Close();
}
return isInsert;
}
public bool UpdateAnalyst(MemberDto memberDto)
{
var cn = new SqlConnection(GetConnection());
SqlTransaction trx = null;
var isUpdate = false;
try
{
cn.Open();
trx = cn.BeginTransaction();
string cmdText = " Update Members set FirstName = '" + memberDto.FirstName + "',LastName='" +
memberDto.LastName + "',Organization='" + memberDto.Organization + "',Mobile='" +
memberDto.Mobile + "',LandLine='" + memberDto.LandLine + "',ResearchExpreince=" +
memberDto.ResearchExpreince + ",HigherDegreeId=" + memberDto.HigherDegreeId +
",ModifiedUserId=" + memberDto.ModifiedUserId + ",ModifiedTimeStamp='" +
DateTime.Now.ToString("yyyy-MM-dd") + "' where MemberId= " + memberDto.MemberId;
var cmd = new SqlCommand(cmdText, cn) { Transaction = trx };
cmd.ExecuteNonQuery();
(new SqlCommand("delete from MemberProductSegments where MemberId=" + memberDto.MemberId, cn) { Transaction = trx }).ExecuteNonQuery();
foreach (var segment in memberDto.MemberProductSegmentsDtos)
(new SqlCommand("insert into MemberProductSegments(MemberId,ProductCategoryId) values( " + memberDto.MemberId + "," + segment.ProductCategoryId + ")", cn) { Transaction = trx }).ExecuteNonQuery();
(new SqlCommand("delete from MemberStyleResearch where MemberId=" + memberDto.MemberId, cn) { Transaction = trx }).ExecuteNonQuery();
foreach (var style in memberDto.MemberStyleResearches)
(new SqlCommand("insert into MemberStyleResearch(MemberId,ResearchId) values( " + memberDto.MemberId + "," + style.ResearchId + ")", cn) { Transaction = trx }).ExecuteNonQuery();
(new SqlCommand("delete from MemberStrategy where MemberId=" + memberDto.MemberId, cn) { Transaction = trx }).ExecuteNonQuery();
foreach (var strategy in memberDto.MemberStrategyDtos)
(new SqlCommand("insert into MemberStrategy(MemberId,StrategyId) values( " + memberDto.MemberId + "," + strategy.StrategyId + ")", cn) { Transaction = trx }).ExecuteNonQuery();
(new SqlCommand("delete from MemberHorizon where MemberId=" + memberDto.MemberId, cn) { Transaction = trx }).ExecuteNonQuery();
foreach (var horizon in memberDto.MemberHorizonDtos)
(new SqlCommand("insert into MemberHorizon(MemberId,HorizonId) values( " + memberDto.MemberId + "," + horizon.HorizonId + ")", cn) { Transaction = trx }).ExecuteNonQuery();
foreach (var documents in memberDto.MemberDocumentsDtos)
(new SqlCommand("insert into MemberDocuments(MemberId,DocumentName,FileName) values( " + memberDto.MemberId + ",'" + documents.DocumentName + "','" + documents.FileName + "')", cn) { Transaction = trx }).ExecuteNonQuery();
trx.Commit();
isUpdate = true;
cn.Close();
}
catch (Exception)
{
if (trx != null) trx.Rollback();
cn.Close();
}
return isUpdate;
}
public bool ResgisterBroker(MemberDto memberDto)
{
return _iUserHelper.RegisterBroker(memberDto);
}
public MemberDto GetAnalystDetail(int analystId)
{
var cn = new SqlConnection(GetConnection());
var analyst = new MemberDto();
try
{
cn.Open();
string cmdText = "select m.*,h.HorizonName,pc.CategoryName,s.StratagyName,rs.ResearchName,hd.HigherDegree from Members m left join MemberHorizon mh on mh.MemberId = m.MemberId left join Horizons h on h.HorizonId = mh.HorizonId left join MemberProductSegments mps on mps.MemberId = m.MemberId left join ProductCategorys pc on pc.CategoryId = mps.ProductCategoryId left join MemberStrategy ms on ms.MemberId=m.MemberId left join Strategy s on s.StrategiesId = ms.StrategyId left join MemberStyleResearch msr on msr.MemberId = m.MemberId left join ResearchStyle rs on rs.ResearchId = msr.ResearchId left join HigherDegrees hd on hd.HigherDegreeId= m.HigherDegreeId where m.MemberId =" + analystId;
var cmd = new SqlCommand(cmdText, cn);
var isEnter = false;
var dr = cmd.ExecuteReader();
var productCategoryNames = new List<string>();
var styleResearchName = new List<string>();
var strategyNames = new List<string>();
var horizonNames = new List<string>();
var researchDesseminationNames = new List<string>();
while (dr.Read())
{
if (!isEnter)
{
analyst.FirstName = Convert.ToString(dr["FirstName"]);
analyst.LastName = Convert.ToString(dr["LastName"]);
analyst.Organization = Convert.ToString(dr["Organization"]);
analyst.Mobile = Convert.ToString(dr["Mobile"]);
analyst.LandLine = Convert.ToString(dr["LandLine"]);
analyst.ResearchExpreince = dr.IsDBNull(12) ? 0 : Convert.ToInt32(dr["ResearchExpreince"]);
analyst.HigherDegree = Convert.ToString(dr["HigherDegree"]);
isEnter = true;
}
var producttCatName = Convert.ToString(dr["CategoryName"]);
var styleResearch = Convert.ToString(dr["ResearchName"]);
var strategy = Convert.ToString(dr["StratagyName"]);
var horizon = Convert.ToString(dr["HorizonName"]);
if (producttCatName != string.Empty && !productCategoryNames.Contains(producttCatName))
productCategoryNames.Add(producttCatName);
if (styleResearch != string.Empty && !styleResearchName.Contains(styleResearch))
styleResearchName.Add(styleResearch);
if (strategy != string.Empty && !strategyNames.Contains(strategy))
strategyNames.Add(strategy);
if (horizon != string.Empty && !horizonNames.Contains(horizon))
horizonNames.Add(horizon);
}
analyst.ProductCatgoryNames = productCategoryNames;
analyst.StyleResearchName = styleResearchName;
analyst.StrategyNames = strategyNames;
analyst.HorizonNames = horizonNames;
analyst.ResearchDesseminationNames = researchDesseminationNames;
cn.Close();
}
catch (Exception)
{
cn.Close();
}
return analyst;
}
public bool UpdateAnalyst(MemberDto memberDto)
{
return _iMemberRepository.UpdateAnalyst(memberDto);
}
public bool RegisterAnalyst(MemberDto memberDto)
{
return _iMemberRepository.RegisterAnalyst(memberDto);
}
public bool RegisterBroker(MemberDto memberDto)
{
return _iUserRepository.RegisterBroker(memberDto);
}
public bool UpdateAnalyst(MemberDto memberDto)
{
return _iMemberHelper.UpdateAnalyst(memberDto);
}
public bool ResgisterAnalyst(MemberDto memberDto)
{
return _iMemberHelper.RegisterAnalyst(memberDto);
}