public async Task <Select2Result> GetSalesPerson()
{
using (_unitOfWorkManager.Current.SetTenantId(_session.TenantId))
{
Select2Result sr = new Select2Result();
{
string SliderQuery = "SELECT * FROM [dbo].[View_SalesPersonSlider] where TenantId = " + _session.TenantId;
DataTable viewtable = new DataTable();
ConnectionAppService db = new ConnectionAppService();
SqlConnection con = new SqlConnection(db.ConnectionString());
con.Open();
SqlCommand cmd = new SqlCommand(SliderQuery, con);
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
sda.Fill(viewtable);
}
con.Close();
var Account = (from DataRow dr in viewtable.Rows
select new datadto
{
Id = Convert.ToInt32(dr["Id"]),
Name = Convert.ToString(dr["Name"])
});
sr.select2data = Account.ToArray();
}
return(sr);
}
}
public async Task <Array> GetActivepotential() // public async Task<PagedResultDto<SpResultActivepotential>> GetActivepotential()
{
long userid = (long)AbpSession.UserId;
int TenantId = (int)(AbpSession.TenantId);
List <SpResultActivepotential> Activepotentiallist = new List <SpResultActivepotential>();
ConnectionAppService db = new ConnectionAppService();
using (SqlConnection conn = new SqlConnection(db.ConnectionString()))
{
SqlCommand sqlComm = new SqlCommand("sp_activepotential", conn);
sqlComm.CommandType = CommandType.StoredProcedure;
sqlComm.Parameters.AddWithValue("@TenantId", TenantId);
sqlComm.Parameters.AddWithValue("@UserId", userid);
conn.Open();
using (var reader = sqlComm.ExecuteReader())
{
while (reader.Read())
{
SpResultActivepotential actpotential = new SpResultActivepotential();
actpotential.createmonth = Convert.ToString(reader["create_month"]);
actpotential.totalcreatedvalue = Convert.ToDecimal(reader["total_created_value"]);
actpotential.totalwonvalue = Convert.ToDecimal(reader["total_won_value"]);
actpotential.optimalactive = Convert.ToDecimal(reader["optimal_active"]);
Activepotentiallist.Add(actpotential);
}
}
conn.Close();
}
return(Activepotentiallist.ToArray());
//var ActivepotentialCount = Activepotentiallist.Count;
// return new PagedResultDto<SpResultActivepotential>(ActivepotentialCount, Activepotentiallist);
}
public async Task GetDeleteNewCustomerType(EntityDto input)
{
ConnectionAppService db = new ConnectionAppService();
DataTable ds = new DataTable();
using (SqlConnection conn = new SqlConnection(db.ConnectionString()))
{
SqlCommand sqlComm = new SqlCommand("Sp_FindMappedTable", conn);
sqlComm.Parameters.AddWithValue("@TableId", 9);
sqlComm.CommandType = CommandType.StoredProcedure;
using (SqlDataAdapter da = new SqlDataAdapter(sqlComm))
{
da.Fill(ds);
}
}
if (input.Id > 0)
{
var results = ds.Rows.Cast <DataRow>().Where(myRow => (int)myRow["Id"] == input.Id);
if (results.Count() > 0)
{
throw new UserFriendlyException("Ooops!", "Customer cannot be deleted '");
}
else
{
await _newCustomerTypeRepository.DeleteAsync(input.Id);
}
}
}
public bool GetMappedDepartment(EntityDto input)
{
bool ok = false;
ConnectionAppService db = new ConnectionAppService();
DataTable ds = new DataTable();
using (SqlConnection conn = new SqlConnection(db.ConnectionString()))
{
SqlCommand sqlComm = new SqlCommand("Sp_FindMappedTable", conn);
sqlComm.Parameters.AddWithValue("@TableId", 11);
sqlComm.CommandType = CommandType.StoredProcedure;
using (SqlDataAdapter da = new SqlDataAdapter(sqlComm))
{
da.Fill(ds);
}
}
if (input.Id > 0)
{
var results = ds.Rows.Cast <DataRow>().Where(myRow => (int)myRow["Id"] == input.Id);
if (results.Count() > 0)
{
ok = true;
}
}
return(ok);
}
public async Task <PagedResultDto <EnquiryQuotationKanbanList> > GetGlobalReport(EnquiryListInput input)
{
int TenantId = (int)(AbpSession.TenantId);
using (_unitOfWorkManager.Current.SetTenantId(TenantId))
{
string Query = "SELECT * FROM [dbo].[View_Kanban] WHERE TenantId = " + TenantId;
ConnectionAppService db = new ConnectionAppService();
DataTable dt = new DataTable();
SqlConnection con3 = new SqlConnection(db.ConnectionString());
con3.Open();
SqlCommand cmd3 = new SqlCommand(Query, con3);
DataTable dt3 = new DataTable();
using (SqlDataAdapter sda = new SqlDataAdapter(cmd3))
{
sda.Fill(dt);
}
var NormalTicket = (from DataRow dr in dt.Rows
select new EnquiryQuotationKanbanList
{
Id = Convert.ToInt32(dr["Id"]),
Title = Convert.ToString(dr["Title"]),
EnquiryNo = Convert.ToString(dr["EnquiryNo"]),
MileStoneId = Convert.ToInt32(dr["MileStoneId"]),
MileStoneStatusName = Convert.ToString(dr["StatusName"]),
CompanyName = Convert.ToString(dr["CompanyName"]),
ContactName = Convert.ToString(dr["ContactName"]),
QuotationId = Convert.ToInt32(dr["QuotationId"]),
QRefno = Convert.ToString(dr["QRefno"]),
CreationTime = Convert.ToDateTime(dr["CreationTime"]),
CloseDate = Convert.ToDateTime(dr["CloseDate"]),
Creator = Convert.ToString(dr["Creator"]),
CreatorImg = Convert.ToString(dr["CreatorImg"]),
Salesperson = Convert.ToString(dr["Salesperson"]),
SalespersonImg = Convert.ToString(dr["SalespersonImg"]),
Remarks = Convert.ToString(dr["Remarks"]),
Total = Convert.ToDecimal(dr["Total"]),
});
NormalTicket = NormalTicket.WhereIf(
!input.Filter.IsNullOrEmpty(),
p =>
p.Title.ToLower().Replace(" ", string.Empty).Contains(input.Filter.ToLower().Replace(" ", string.Empty)) ||
p.EnquiryNo.ToLower().Replace(" ", string.Empty).Contains(input.Filter.ToLower().Replace(" ", string.Empty)) ||
p.CompanyName.ToLower().Replace(" ", string.Empty).Contains(input.Filter.ToLower().Replace(" ", string.Empty)) ||
p.MileStoneStatusName.ToLower().Replace(" ", string.Empty).Contains(input.Filter.ToLower().Replace(" ", string.Empty)) ||
p.QRefno.ToLower().Replace(" ", string.Empty).Contains(input.Filter.ToLower().Replace(" ", string.Empty))
);
var NewStatussCount = NormalTicket.Count();
var NewStatussList = NormalTicket.OrderByDescending(p => p.CreationTime).Skip(input.SkipCount).Take(input.MaxResultCount).ToList();
var NewStatusdtos = NewStatussList.MapTo <List <EnquiryQuotationKanbanList> >();
return(new PagedResultDto <EnquiryQuotationKanbanList>(NewStatussCount, NewStatusdtos));
}
}
public async Task <Array> WonTargetDevelopment(NullableIdDto input)
{
List <QuotationYearList> results = new List <QuotationYearList>();
ConnectionAppService db = new ConnectionAppService();;
using (SqlConnection conn = new SqlConnection(db.ConnectionString()))
{
SqlCommand sqlComm = new SqlCommand("sp_WonTargetGraph", conn);
sqlComm.Parameters.AddWithValue("@TenantId", AbpSession.TenantId);
sqlComm.Parameters.AddWithValue("@UserId", input.Id);
sqlComm.CommandType = CommandType.StoredProcedure;
conn.Open();
using (SqlDataReader reader = sqlComm.ExecuteReader())
{
while (reader.Read())
{
QuotationYearList newItem = new QuotationYearList();
newItem.Year = Convert.ToInt32(reader["Wyear"]);
newItem.Month = Convert.ToInt32(reader["Wmonth"]);
newItem.MonthString = Convert.ToString(reader["WDate"]);
newItem.Value = Convert.ToDouble(reader["Wvalue"]);
results.Add(newItem);
}
}
conn.Close();
}
return(results.ToArray());
}
public async Task GetDeleteProduct(EntityDto input)
{
var product = _ProductRepository.GetAll().Where(c => c.Id == input.Id);
var p = (from c in product
join r in _QuotationProductRepository.GetAll() on c.Id equals r.ProductId
select r).FirstOrDefault();
if (p == null)
{
ConnectionAppService db = new ConnectionAppService();
DataTable ds = new DataTable();
using (SqlConnection conn = new SqlConnection(db.ConnectionString()))
{
SqlCommand sqlComm = new SqlCommand("Sp_DeleteAllDetail", conn);
sqlComm.Parameters.AddWithValue("@Id", input.Id);
sqlComm.Parameters.AddWithValue("@TableId", 2);
sqlComm.CommandType = CommandType.StoredProcedure;
conn.Open();
sqlComm.ExecuteNonQuery();
conn.Close();
}
}
else
{
throw new UserFriendlyException("Ooops!", "Product cannot be deleted");
}
}
public ListResultDto <TenantTargetListDto> GetTenantTarget(GetTenantTargetInput input)
{
long userid = (long)AbpSession.UserId;
int TenantId = (int)(AbpSession.TenantId);
List <string> UserRoleList = new List <string>();
ConnectionAppService db = new ConnectionAppService();
using (SqlConnection conn = new SqlConnection(db.ConnectionString()))
{
SqlCommand sqlComm = new SqlCommand("sp_GetUserRoles", conn);
sqlComm.CommandType = CommandType.StoredProcedure;
sqlComm.Parameters.AddWithValue("@TenantId", TenantId);
sqlComm.Parameters.AddWithValue("@UserId", userid);
conn.Open();
using (var reader = sqlComm.ExecuteReader())
{
while (reader.Read())
{
UserRoleList.Add((string)reader["DisplayName"]);
}
}
conn.Close();
}
using (_unitOfWorkManager.Current.SetTenantId(TenantId))
{
var TenantTarget = _TenantTargetRepository.GetAll().Where(p => p.Id == 0);
if (UserRoleList.Contains("Admin"))
{
TenantTarget = _TenantTargetRepository.GetAll()
.WhereIf(!input.Filter.IsNullOrEmpty(),
p => p.Value.ToString().Contains(input.Filter) ||
p.TenantId.ToString().Contains(input.Filter));
}
else if (UserRoleList.Contains("Sales Manager"))
{
TenantTarget = _TenantTargetRepository.GetAll().Where(p => p.CreatorUserId == userid)
.WhereIf(!input.Filter.IsNullOrEmpty(),
p => p.Value.ToString().Contains(input.Filter) ||
p.TenantId.ToString().Contains(input.Filter));
}
var query = from s in TenantTarget
select new TenantTargetListDto
{
TenantId = s.TenantId,
Value = s.Value,
TargetDate = s.TargetDate,
Id = s.Id
};
var list = query.ToList();
return(new ListResultDto <TenantTargetListDto>(list.MapTo <List <TenantTargetListDto> >()));
}
}
public async Task <FileDto> GetGlobalReportToExcel()
{
int TenantId = (int)(AbpSession.TenantId);
using (_unitOfWorkManager.Current.SetTenantId(TenantId))
{
string Query = "SELECT * FROM [dbo].[View_Kanban] WHERE TenantId = " + TenantId;
ConnectionAppService db = new ConnectionAppService();
DataTable dt = new DataTable();
SqlConnection con3 = new SqlConnection(db.ConnectionString());
con3.Open();
SqlCommand cmd3 = new SqlCommand(Query, con3);
DataTable dt3 = new DataTable();
using (SqlDataAdapter sda = new SqlDataAdapter(cmd3))
{
sda.Fill(dt);
}
var NormalTicket = (from DataRow dr in dt.Rows
select new EnquiryQuotationKanbanList
{
Id = Convert.ToInt32(dr["Id"]),
Title = Convert.ToString(dr["Title"]),
EnquiryNo = Convert.ToString(dr["EnquiryNo"]),
MileStoneId = Convert.ToInt32(dr["MileStoneId"]),
MileStoneStatusName = Convert.ToString(dr["StatusName"]),
CompanyName = Convert.ToString(dr["CompanyName"]),
ContactName = Convert.ToString(dr["ContactName"]),
QuotationId = Convert.ToInt32(dr["QuotationId"]),
QRefno = Convert.ToString(dr["QRefno"]),
CreationTime = Convert.ToDateTime(dr["CreationTime"]),
CloseDate = Convert.ToDateTime(dr["CloseDate"]),
Creator = Convert.ToString(dr["Creator"]),
CreatorImg = Convert.ToString(dr["CreatorImg"]),
Salesperson = Convert.ToString(dr["Salesperson"]),
SalespersonImg = Convert.ToString(dr["SalespersonImg"]),
Remarks = Convert.ToString(dr["Remarks"]),
Total = Convert.ToDecimal(dr["Total"]),
});
var NewStatussCount = NormalTicket.Count();
var NewStatussList = NormalTicket.OrderByDescending(p => p.CreationTime).ToList();
var NewStatusdtos = NewStatussList.MapTo <List <EnquiryQuotationKanbanList> >();
return(_GlobalReportExcelExporter.ExportToFile(NewStatusdtos));
}
}
public void GetUpdateQuotation(UpdateQuotationInput input)
{
ConnectionAppService db = new ConnectionAppService();
DataTable ds = new DataTable();
using (SqlConnection conn = new SqlConnection(db.ConnectionString()))
{
SqlCommand sqlComm = new SqlCommand("Sp_UpdateQuotationMileStone", conn);
sqlComm.Parameters.AddWithValue("@InquiryId", input.EnquiryId);
sqlComm.Parameters.AddWithValue("@MileStoneId", input.MilestoneId);
sqlComm.CommandType = CommandType.StoredProcedure;
conn.Open();
sqlComm.ExecuteNonQuery();
conn.Close();
}
}
public async Task GetDeleteEnquiry(EntityDto input)
{
ConnectionAppService db = new ConnectionAppService();
DataTable ds = new DataTable();
using (SqlConnection conn = new SqlConnection(db.ConnectionString()))
{
SqlCommand sqlComm = new SqlCommand("Sp_DeleteAllDetail", conn);
sqlComm.Parameters.AddWithValue("@Id", input.Id);
sqlComm.Parameters.AddWithValue("@TableId", 3);
sqlComm.CommandType = CommandType.StoredProcedure;
conn.Open();
sqlComm.ExecuteNonQuery();
conn.Close();
}
}
public async Task LinkProductToQuotation(ProductLinkInput input)
{
ConnectionAppService db = new ConnectionAppService();
using (SqlConnection con = new SqlConnection(db.ConnectionString()))
{
using (SqlCommand cmd = new SqlCommand("Sp_LinkQuotationProduct", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@TempProductId", SqlDbType.VarChar).Value = input.TempProductId;
cmd.Parameters.Add("@ProductId", SqlDbType.VarChar).Value = input.ProductId;
con.Open();
cmd.ExecuteNonQuery();
}
}
}
public async Task ConfirmDeleteProductSpecification(EntityDto input)
{
ConnectionAppService db = new ConnectionAppService();
DataTable ds = new DataTable();
using (SqlConnection conn = new SqlConnection(db.ConnectionString()))
{
SqlCommand sqlComm = new SqlCommand("Sp_SpecProductDelete", conn);
sqlComm.Parameters.AddWithValue("@SpecId", input.Id);
sqlComm.CommandType = CommandType.StoredProcedure;
using (SqlDataAdapter da = new SqlDataAdapter(sqlComm))
{
da.Fill(ds);
}
}
}
public void InquiryClosed(int Id)
{
ConnectionAppService db = new ConnectionAppService();
using (SqlConnection con = new SqlConnection(db.ConnectionString()))
{
using (SqlCommand cmd = new SqlCommand("Sp_ClosedQuotation", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@InquiryId", SqlDbType.VarChar).Value = Id;
cmd.Parameters.Add("@Type", SqlDbType.VarChar).Value = 1;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
public virtual async Task ProductGroupDetailChange(ProductGroupDetailChangeInput input)
{
ConnectionAppService db = new ConnectionAppService();
using (SqlConnection conn = new SqlConnection(db.ConnectionString()))
{
var Source = new SqlParameter
{
ParameterName = "Source",
Value = input.Source
};
var Destination = new SqlParameter
{
ParameterName = "Destination",
Value = input.Destination
};
var ProductGroupId = new SqlParameter
{
ParameterName = "ProductGroupId",
Value = input.ProductGroupId
};
var RowId = new SqlParameter
{
ParameterName = "RowId",
Value = input.RowId
};
SqlCommand sqlComm = new SqlCommand("Sp_ProductGroupDetailOrder", conn);
sqlComm.Parameters.Add(Source);
sqlComm.Parameters.Add(Destination);
sqlComm.Parameters.Add(ProductGroupId);
sqlComm.Parameters.Add(RowId);
sqlComm.CommandType = CommandType.StoredProcedure;
conn.Open();
try
{
sqlComm.ExecuteNonQuery();
}
catch (Exception ex)
{
}
conn.Close();
}
}
public async Task GetDeleteProductGroup(EntityDto input)
{
ConnectionAppService db = new ConnectionAppService();
DataTable ds = new DataTable();
using (SqlConnection conn = new SqlConnection(db.ConnectionString()))
{
SqlCommand sqlComm = new SqlCommand("Sp_FindMappedTable", conn);
sqlComm.Parameters.AddWithValue("@TableId", 14);
sqlComm.CommandType = CommandType.StoredProcedure;
using (SqlDataAdapter da = new SqlDataAdapter(sqlComm))
{
da.Fill(ds);
}
}
if (input.Id > 0)
{
var results = ds.Rows.Cast <DataRow>().Where(myRow => (int)myRow["Id"] == input.Id);
if (results.Count() > 0)
{
throw new UserFriendlyException("Ooops!", "Product Group cannot be deleted '");
}
else
{
using (SqlConnection conn = new SqlConnection(db.ConnectionString()))
{
SqlCommand sqlComm = new SqlCommand("Sp_DeleteAllDetails", conn);
sqlComm.Parameters.AddWithValue("@TableId", 3);
sqlComm.Parameters.AddWithValue("@Id", input.Id);
sqlComm.CommandType = CommandType.StoredProcedure;
conn.Open();
sqlComm.ExecuteNonQuery();
conn.Close();
}
}
}
}
public async Task <Array> GetUserTarget(EntityDto input) //public async Task<PagedResultDto<SpResultUserTarget>> GetUserTarget()
{
int TenantId = (int)(AbpSession.TenantId);
List <SpResultUserTarget> UserTargetlist = new List <SpResultUserTarget>();
ConnectionAppService db = new ConnectionAppService(); //var db = _scDbContext.Database.GetDbConnection().ConnectionString;
using (SqlConnection conn = new SqlConnection(db.ConnectionString()))
{
SqlCommand sqlComm = new SqlCommand("sp_GetUserTarget_Updated", conn);
sqlComm.CommandType = CommandType.StoredProcedure;
sqlComm.Parameters.AddWithValue("@TenantId", TenantId);
sqlComm.Parameters.AddWithValue("@UserId", input.Id);
conn.Open();
using (var reader = sqlComm.ExecuteReader())
{
while (reader.Read())
{
SpResultUserTarget userTarget = new SpResultUserTarget();
userTarget.Yeartarget = Convert.ToDecimal(reader["TargetYear"]);
userTarget.YearAchived = Convert.ToDecimal(reader["AchivedYear"]);
userTarget.MonthTarget = Convert.ToDecimal(reader["Targetmonth"]);
userTarget.MonthAchived = Convert.ToDecimal(reader["AchivedMonth"]);
userTarget._Month = Convert.ToDecimal(reader["_Month"]);
userTarget._Year = Convert.ToDecimal(reader["_Year"]);
UserTargetlist.Add(userTarget);
}
}
conn.Close();
}
var datalist = (from r in UserTargetlist select r).ToArray();
return(datalist.ToArray());
//var UserTargetCount = UserTargetlist.Count;
//return new PagedResultDto<SpResultUserTarget>(UserTargetCount, UserTargetlist);
}
public IEnumerable <DataTable> GetGridDatas(EntityDto input)
{
List <DataTable> list = new List <DataTable>();
var query = (from r in _viewRepository.GetAll().Where(p => p.Id == input.Id) select r.Query).FirstOrDefault();
ConnectionAppService db = new ConnectionAppService();
DataTable ds = new DataTable();
using (SqlConnection conn = new SqlConnection(db.ConnectionString()))
{
SqlCommand sqlComm = new SqlCommand("", conn)
{
CommandText = query,
CommandType = CommandType.Text
};
using (SqlDataAdapter da = new SqlDataAdapter(sqlComm))
{
da.Fill(ds);
}
list.Add(ds.Rows[0].Table);
}
return(list);
}
public async Task <Array> GetInquiryKanban(EnquiryKanbanInput input)
{
using (_unitOfWorkManager.Current.SetTenantId((int)AbpSession.TenantId))
{
string Query = "SELECT * FROM [dbo].[View_Kanban] WHERE TenantId = " + AbpSession.TenantId;
var SupportMileStones = (from r in _MilestoneRepository.GetAll() where r.IsQuotation == false select r).ToArray();
var QSupportMileStones = (from r in _MilestoneRepository.GetAll() where r.IsQuotation == true select r).ToArray();
var SubListout = new List <EnquiryQuotationKanbanArray>();
ConnectionAppService db = new ConnectionAppService();
DataTable dt = new DataTable();
SqlConnection con3 = new SqlConnection(db.ConnectionString());
con3.Open();
SqlCommand cmd3 = new SqlCommand(Query, con3);
DataTable dt3 = new DataTable();
using (SqlDataAdapter sda = new SqlDataAdapter(cmd3))
{
sda.Fill(dt);
}
var NormalTicket = (from DataRow dr in dt.Rows
select new EnquiryQuotationKanbanList
{
Id = Convert.ToInt32(dr["Id"]),
Title = Convert.ToString(dr["Title"]),
EnquiryNo = Convert.ToString(dr["EnquiryNo"]),
MileStoneId = Convert.ToInt32(dr["MileStoneId"]),
MileStoneStatusName = Convert.ToString(dr["StatusName"]),
CompanyName = Convert.ToString(dr["CompanyName"]),
ContactName = Convert.ToString(dr["ContactName"]),
QuotationId = Convert.ToInt32(dr["QuotationId"]),
QRefno = Convert.ToString(dr["QRefno"]),
CreationTime = Convert.ToDateTime(dr["CreationTime"]),
CloseDate = Convert.ToDateTime(dr["CloseDate"]),
Creator = Convert.ToString(dr["Creator"]),
CreatorImg = Convert.ToString(dr["CreatorImg"]),
Salesperson = Convert.ToString(dr["Salesperson"]),
SalespersonImg = Convert.ToString(dr["SalespersonImg"]),
Remarks = Convert.ToString(dr["Remarks"]),
Total = Convert.ToDecimal(dr["Total"]),
EnqQuotation = Convert.ToString(dr["QuotationInfo"]),
});
var NewStatuss = NormalTicket;
NewStatuss = NewStatuss.WhereIf(
!input.Filter.IsNullOrEmpty(),
p =>
p.Title.ToLower().Replace(" ", string.Empty).Contains(input.Filter.ToLower().Replace(" ", string.Empty)) ||
p.EnquiryNo.ToLower().Replace(" ", string.Empty).Contains(input.Filter.ToLower().Replace(" ", string.Empty)) ||
p.CompanyName.ToLower().Replace(" ", string.Empty).Contains(input.Filter.ToLower().Replace(" ", string.Empty))
);
var NewStatusdtos = NewStatuss.MapTo <List <EnquiryQuotationKanbanList> >();
foreach (var newsts in SupportMileStones)
{
SubListout.Add(new EnquiryQuotationKanbanArray
{
Flag = false,
MilestoneName = newsts.Name,
EndQuotation = false,
Total = (from r in NewStatuss where r.MileStoneId == newsts.Id && r.QuotationId > 0 select r.Total).Sum(),
EnquiryQuotationKanban = (from r in NewStatuss where r.MileStoneId == newsts.Id && r.QuotationId == 0 select r).OrderByDescending(p => p.CreationTime).ToArray()
});
}
foreach (var Qnewsts in QSupportMileStones)
{
SubListout.Add(new EnquiryQuotationKanbanArray
{
Flag = true,
EndQuotation = Qnewsts.EndOfQuotation,
MilestoneName = Qnewsts.Name,
Total = (from r in NewStatuss where r.MileStoneId == Qnewsts.Id && r.QuotationId > 0 select r.Total).Sum(),
EnquiryQuotationKanban = (from r in NewStatuss where r.MileStoneId == Qnewsts.Id && r.QuotationId > 0 select r).OrderByDescending(p => p.CreationTime).ToArray()
});
}
return(SubListout.ToArray());
}
}
public void SendResponceEmail(int EnquiryId, int TypeId)
{
string Body = string.Empty;
var builder = new ConfigurationBuilder()
.SetBasePath(Directory.GetCurrentDirectory());
var headerfinder = builder.GetFileProvider().GetFileInfo("Designerresponce.html");
string headerpath = headerfinder.PhysicalPath;
using (StreamReader reader = new StreamReader(headerpath, System.Text.Encoding.UTF8))
{
Body = reader.ReadToEnd();
}
string viewquery = "SELECT Top(1) * FROM [dbo].[View_Enquiry_UserDetails] where Id =" + EnquiryId;
DataTable ds = new DataTable();
ConnectionAppService db = new ConnectionAppService();
try
{
SqlConnection con = new SqlConnection(db.ConnectionString());
con.Open();
SqlCommand cmd = new SqlCommand(viewquery, con);
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
sda.Fill(ds);
}
con.Close();
}
catch (Exception ex)
{
}
var ManagerEmail = ds.Rows[0]["SEmail"].ToString();
var ManagerPwd = ds.Rows[0]["SPWD"].ToString();
var DesignerEmail = ds.Rows[0]["DEmail"].ToString();
var DesignerPwd = ds.Rows[0]["DPWD"].ToString();
Body = Body.Replace("{EnquiryRefNo}", ds.Rows[0]["SubMmissionId"].ToString());
Body = Body.Replace("{Company}", ds.Rows[0]["CompanyName"].ToString());
Body = Body.Replace("{Designer}", ds.Rows[0]["Designer"].ToString());
Body = Body.Replace("{Sales}", ds.Rows[0]["Sales"].ToString());
Body = Body.Replace("{Remarks}", ds.Rows[0]["Remarks"].ToString());
Body = Body.Replace("{Title}", ds.Rows[0]["Name"].ToString());
Body = Body.Replace("{root}", ds.Rows[0]["Id"].ToString());
Body = Body.Replace("{Manager}", ds.Rows[0]["Manager"].ToString());
if (TypeId == 1)
{
Body = Body.Replace("{Subject}", "Accepted");
}
if (TypeId == 2)
{
Body = Body.Replace("{Subject}", "Rejected");
}
string sendFromEmail = "*****@*****.**";
//string sendFromPassword = "******";
using (SmtpClient client = new SmtpClient("smtp.office365.com", 587))
{
client.Credentials = new NetworkCredential(ManagerEmail, ManagerPwd);
client.EnableSsl = true;
client.TargetName = "STARTTLS/smtp.office365.com";
MailMessage mail = new MailMessage();
mail.From = new MailAddress(ManagerEmail);
mail.To.Add(DesignerEmail);
mail.CC.Add(sendFromEmail);
mail.Subject = "Response for Design Job";
mail.Body = Body;
mail.IsBodyHtml = true;
try
{
client.Send(mail);
}
catch (Exception ex)
{
}
}
}
public async Task <List <SliderDataList> > GetSalesExecutive()
{
long userid = (long)AbpSession.UserId;
var user = await UserManager.GetUserByIdAsync(userid);
var grantedPermissions = await UserManager.GetGrantedPermissionsAsync(user);
var permissionresult = (from r in grantedPermissions where r.Name == "Pages.Tenant.Managemant.Leads" select r).FirstOrDefault();
var userrole = (from c in UserManager.Users
join urole in _userRoleRepository.GetAll() on c.Id equals urole.UserId
join role in _roleManager.Roles on urole.RoleId equals role.Id
where urole.UserId == userid
select role).FirstOrDefault();
using (_unitOfWorkManager.Current.SetTenantId(AbpSession.TenantId))
{
string SliderQuery = "SELECT * FROM [dbo].[View_SalesPersonSlider] where TenantId = " + AbpSession.TenantId;
DataTable viewtable = new DataTable();
ConnectionAppService db = new ConnectionAppService();
SqlConnection con = new SqlConnection(db.ConnectionString());
con.Open();
SqlCommand cmd = new SqlCommand(SliderQuery, con);
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
sda.Fill(viewtable);
}
con.Close();
var data = (from DataRow dr in viewtable.Rows
select new SliderDataList
{
Id = Convert.ToInt32(dr["Id"]),
Name = Convert.ToString(dr["Name"]),
ProfilePicture = _webUrlService.GetServerRootAddress().EnsureEndsWith('/') + Convert.ToString(dr["ProfileImage"]),
Email = Convert.ToString(dr["EmailAddress"])
}).ToList();
//if(userrole.DisplayName != "Admin")
//{
// data = (from r in data where r.Id == userid select r).ToList();
//}
if (permissionresult != null && userrole.DisplayName != "Admin")
{
data = (from r in data where r.Id == userid select r).ToList();
}
else if (permissionresult == null && userrole.DisplayName != "Admin")
{
var qchk = _QuotationRepository.GetAll().Where(p => p.SalesmanId == userid).FirstOrDefault();
if (qchk != null)
{
data = (from r in data where r.Id == userid select r).ToList();
}
else
{
data = (from r in data where r.Id < 1 select r).ToList();
}
}
if (data.Count > 1)
{
data.Add(new SliderDataList {
Id = 0, Name = "All", ProfilePicture = _webUrlService.GetServerRootAddress().EnsureEndsWith('/') + "Common/Profile/default-profile-picture.png", Email = "*****@*****.**"
});
}
var SliderData = data.OrderBy(p => p.Id).MapTo <List <SliderDataList> >();
return(SliderData);
}
}
public void SendWonMail(int QuotationId)
{
string Body = string.Empty;
var builder = new ConfigurationBuilder()
.SetBasePath(Directory.GetCurrentDirectory());
var headerfinder = builder.GetFileProvider().GetFileInfo("wonemtemp.html");
string headerpath = headerfinder.PhysicalPath;
using (StreamReader reader = new StreamReader(headerpath, System.Text.Encoding.UTF8))
{
Body = reader.ReadToEnd();
}
string viewquery = "SELECT Top(1) * FROM [dbo].[View_Quotation_UserDetails] where QId =" + QuotationId;
DataTable ds = new DataTable();
ConnectionAppService db = new ConnectionAppService();
try
{
SqlConnection con = new SqlConnection(db.ConnectionString());
con.Open();
SqlCommand cmd = new SqlCommand(viewquery, con);
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
sda.Fill(ds);
}
con.Close();
}
catch (Exception ex)
{
}
var ManagerEmail = ds.Rows[0]["MEmail"].ToString();
var DesignerEmail = ds.Rows[0]["DEmail"].ToString();
var CoordinatorEmail = ds.Rows[0]["CoEmail"].ToString();
var SalesEmail = ds.Rows[0]["SEmail"].ToString();
Body = Body.Replace("{EnquiryRefNo}", ds.Rows[0]["SubMmissionId"].ToString());
Body = Body.Replace("{Company}", ds.Rows[0]["CompanyName"].ToString());
Body = Body.Replace("{Designer}", ds.Rows[0]["Designer"].ToString());
Body = Body.Replace("{Sales}", ds.Rows[0]["Sales"].ToString());
Body = Body.Replace("{QuotationRefNo}", ds.Rows[0]["RefNo"].ToString());
Body = Body.Replace("{Title}", ds.Rows[0]["Name"].ToString());
Body = Body.Replace("{QId}", ds.Rows[0]["QId"].ToString());
Body = Body.Replace("{Id}", ds.Rows[0]["Id"].ToString());
Body = Body.Replace("{Remarks}", ds.Rows[0]["Remarks"].ToString());
Body = Body.Replace("{Total}", ds.Rows[0]["Total"].ToString());
string sendFromEmail = "*****@*****.**";
string sendFromPassword = "******";
using (SmtpClient client = new SmtpClient("smtp.office365.com", 587))
{
client.Credentials = new NetworkCredential(sendFromEmail, sendFromPassword);
client.EnableSsl = true;
client.TargetName = "STARTTLS/smtp.office365.com";
MailMessage mail = new MailMessage();
mail.From = new MailAddress(sendFromEmail);
mail.To.Add(ManagerEmail);
mail.To.Add(SalesEmail);
if (DesignerEmail != "")
{
mail.To.Add(DesignerEmail);
}
if (CoordinatorEmail != "")
{
mail.To.Add(CoordinatorEmail);
}
mail.CC.Add(sendFromEmail);
mail.Subject = "Won Email for Opportunity : " + ds.Rows[0]["SubMmissionId"].ToString();
mail.Body = Body;
mail.IsBodyHtml = true;
try
{
client.Send(mail);
}
catch (Exception ex)
{
}
}
}
public async Task RegenerateProduct(EntityDto input)
{
List <IEnumerable <string> > result = new List <IEnumerable <string> >();
var ProductSpecification = _ProductSpecificationRepository
.GetAll().Where(p => p.Id == input.Id).FirstOrDefault();
var datass = (from r in _ProdutSpecLinkRepository.GetAll()
where r.ProductSpecificationId == input.Id
group r by r.AttributeGroupId into g
select new { GroupId = g.Key });
var idd = (int)ProductSpecification.ProductGroupId;
var ProdutSpecs = (from r in datass
join s in _ProductGroupDetailRepository.GetAll() on r.GroupId equals s.AttributeGroupId
where s.ProductGroupId == idd
select new { GroupId = r.GroupId, Orderby = s.OrderBy }).ToArray();
var ProdutSpecss = ProdutSpecs.OrderBy(c => c.Orderby).ToList();
var FamilyCode = (from r in _ProdutSpecLinkRepository.GetAll()
where r.ProductSpecificationId == input.Id
group r by r.ProductGroups.prodFamily.ProductFamilyCode into g
select g.Key).ToArray();
result.Add(FamilyCode);
if (ProdutSpecss.Count() > 0)
{
foreach (var d in ProdutSpecss)
{
var datas = (from r in _AttributeGroupDetailRepository.GetAll()
join p in _ProdutSpecLinkRepository.GetAll() on r.AttributeId equals p.AttributeId
where r.AttributeGroupId == d.GroupId && p.ProductSpecificationId == input.Id
select r.Attributes.AttributeCode).ToArray();
result.Add(datas);
}
}
Func <IEnumerable <IEnumerable <string> >, IEnumerable <IEnumerable <string> > > f0 = null;
f0 = xss =>
{
if (!xss.Any())
{
return(new[] { Enumerable.Empty <string>() });
}
else
{
var query =
from x in xss.First()
from y in f0(xss.Skip(1))
select new[] { x }.Concat(y);
return(query);
}
};
Func <IEnumerable <IEnumerable <string> >, IEnumerable <string> > f = xss => f0(xss).Select(xs => String.Join("-", xs));
var results = f(result);
var resultsList = results.ToList();
var fruitSentence = resultsList.Aggregate((current, next) => $"{current}//{next}");
ConnectionAppService db = new ConnectionAppService();
using (SqlConnection con = new SqlConnection(db.ConnectionString()))
{
using (SqlCommand cmd = new SqlCommand("Sp_ResetProductGeneration", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Id", SqlDbType.Int).Value = input.Id;
cmd.Parameters.Add("@Code", SqlDbType.VarChar).Value = fruitSentence;
con.Open();
cmd.ExecuteNonQuery();
}
}
}
