public static bool ChangeTenderClaimClaimStatus(TenderClaim model)
{
var result = false;
using (var conn = new SqlConnection(_connectionString))
{
var cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "ChangeTenderClaimClaimStatus";
cmd.Parameters.AddWithValue("@id", model.Id);
cmd.Parameters.AddWithValue("@claimStatus", model.ClaimStatus);
conn.Open();
result = cmd.ExecuteNonQuery() > 0;
}
return result;
}
private static string GetClaimInfo(TenderClaim claim)
{
var db = new DbEngine();
var dealTypes = db.LoadDealTypes();
return "Заявка № " + claim.Id + ", Автор: " + GetUserById(claim.Author.Id).ShortName +
", Номер конкурса: " + claim.TenderNumber + ", Дата начала" +
claim.TenderStart.ToString("dd.MM.yyyy") + ", Срок сдачи: "
+ claim.ClaimDeadline.ToString("dd.MM.yyyy") + ", Менеджер: " +
GetUserById(claim.Manager.Id).ShortName + ", Подразделение менеджера: " +
claim.Manager.SubDivision +
", Заказчик: " + claim.Customer + " ИНН заказчика: " + claim.CustomerInn +
", Тип конкурса: " + dealTypes.First(x => x.Id == claim.DealType).Value + (claim.Sum > 0
? ", Сумма: " + claim.Sum.ToString("N2")
: string.Empty) + (!string.IsNullOrEmpty(claim.TenderUrl)
? ", Сcылка на конкурс: <a href='" + claim.TenderUrl + "'>[Ссылка]</a>]"
: string.Empty) + ".<br/>";
}
public JsonResult EditClaimDeadline(TenderClaim claim)
{
var isComplete = false;
try
{
bool dateValid = !string.IsNullOrEmpty(claim.ClaimDeadline.ToShortDateString());
if (dateValid)
{
var db = new DbEngine();
db.UpdateClaimDeadline(claim.Id, claim.ClaimDeadline);
isComplete = true;
}
}
catch (Exception)
{
isComplete = false;
}
return Json(new { IsComplete = isComplete });
}
public JsonResult UpdateClaimCurrency(TenderClaim model)
{
var isComplete = false;
try
{
var db = new DbEngine();
isComplete = db.UpdateClaimCurrency(model);
}
catch (Exception ex)
{
isComplete = false;
}
return Json(new { IsComplete = isComplete });
}
public JsonResult SaveClaim(TenderClaim model)
{
var isComplete = false;
ClaimStatusHistory statusHistory = null;
int? idClaim;
string errorText = String.Empty;
try
{
model.KPDeadline = DateTime.ParseExact(model.KPDeadlineString, "dd.MM.yyyy", CultureInfo.CurrentCulture);
model.ClaimDeadline = DateTime.ParseExact(model.ClaimDeadlineString, "dd.MM.yyyy", CultureInfo.CurrentCulture);
model.TenderStart = DateTime.ParseExact(model.TenderStartString, "dd.MM.yyyy", CultureInfo.CurrentCulture);
if (!string.IsNullOrEmpty(model.DeliveryDateString))
model.DeliveryDate = DateTime.ParseExact(model.DeliveryDateString, "dd.MM.yyyy", CultureInfo.CurrentCulture);
if (!string.IsNullOrEmpty(model.DeliveryDateEndString))
model.DeliveryDateEnd = DateTime.ParseExact(model.DeliveryDateEndString, "dd.MM.yyyy", CultureInfo.CurrentCulture);
if (!string.IsNullOrEmpty(model.AuctionDateString))
model.AuctionDate = DateTime.ParseExact(model.AuctionDateString, "dd.MM.yyyy", CultureInfo.CurrentCulture);
var modelValid = true;
if (string.IsNullOrEmpty(model.Customer) || model.Sum < 0) modelValid = false;
if (modelValid)
{
var user = GetUser();
var db = new DbEngine();
model.ClaimStatus = 1;
model.TenderStatus = 1;
model.Deleted = false;
model.RecordDate = DateTime.Now;
model.Author = UserHelper.GetUserById(user.Id);
isComplete = db.SaveTenderClaim(ref model);
if (model.DeliveryDateString == null) model.DeliveryDateString = string.Empty;
if (model.DeliveryDateEndString == null) model.DeliveryDateEndString = string.Empty;
if (model.AuctionDateString == null) model.AuctionDateString = string.Empty;
if (model.DeliveryPlace == null) model.DeliveryPlace = string.Empty;
if (isComplete)
{
//История изменения статуса
statusHistory = new ClaimStatusHistory()
{
Date = DateTime.Now,
IdClaim = model.Id,
IdUser = user.Id,
Status = new ClaimStatus() { Id = model.ClaimStatus },
Comment = "Автор: " + user.ShortName
};
db.SaveClaimStatusHistory(statusHistory);
statusHistory.DateString = statusHistory.Date.ToString("dd.MM.yyyy HH:mm");
//>>>>Уведомления
if (model.Author.Id != model.Manager.Id)
{
var manager = UserHelper.GetUserById(model.Manager.Id);
if (manager != null)
{
var host = ConfigurationManager.AppSettings["AppHost"];
var message = new StringBuilder();
message.Append("Добрый день!");
//message.Append(manager.Name);
message.Append("<br/>");
message.Append("Пользователь ");
message.Append(user.ShortName);
message.Append(" создал заявку где Вы назначены менеджером.");
message.Append("<br/><br />");
message.Append(GetClaimInfo(model));
message.Append("<br/>");
message.Append("Ссылка на заявку: ");
message.Append("<a href='" + host + "/Claim/Index?claimId=" + model.Id + "'>" + host +
"/Claim/Index?claimId=" + model.Id + "</a>");
//message.Append("<br/>Сообщение от системы Спец расчет");
Notification.SendNotification(new List<UserBase>() { manager }, message.ToString(),
String.Format("{0} - {1} - Новая заявка СпецРасчет", model.TenderNumber, model.Customer));
}
}
}
}
}
catch (Exception ex)
{
isComplete = false;
errorText = ex.Message;
}
return Json(new { IsComplete = isComplete, Model = model, StatusHistory = statusHistory, errorText = errorText });
}
public ActionResult NewClaim(TenderClaim model, string managerSid)
{
var manager = new EmployeeSm(managerSid);
model.Manager = new Manager()
{
Id = manager.AdSid,
ShortName = manager.DisplayName,
SubDivision = manager.DepartmentName
};
model.Author = GetUser();
model.RecordDate = DateTime.Now;
model.ClaimStatus = 1;
model.TenderStatus = 1;
var db = new DbEngine();
var success = db.SaveTenderClaim(ref model);
if (success)
{
string message = "";
if (Request.Files.Count > 0)
{
int idClaim = model.Id;
if (idClaim != null && idClaim > 0)
{
for (int i = 0; i < Request.Files.Count; i++)
{
var file = Request.Files[i];
var fileFormats = WebConfigurationManager.AppSettings["FileFormat4TenderClaimFile"].Split(',').Select(s => s.ToLower()).ToArray();
byte[] fileData = null;
if (Array.IndexOf(fileFormats, Path.GetExtension(file.FileName).ToLower()) > -1)
{
using (var br = new BinaryReader(file.InputStream))
{
fileData = br.ReadBytes(file.ContentLength);
}
var claimFile = new TenderClaimFile() { IdClaim = idClaim, File = fileData, FileName = file.FileName };
db.SaveTenderClaimFile(ref claimFile);
}
else if (file.ContentLength > 0) message += String.Format("Файл {0} имеет недопустимое расширение.", file.FileName);
}
//}
}
}
TempData["error"] = message;
if (success)
{
//История изменения статуса
var statusHistory = new ClaimStatusHistory()
{
Date = DateTime.Now,
IdClaim = model.Id,
IdUser = model.Author.Id,
Status = new ClaimStatus() { Id = model.ClaimStatus },
Comment = "Автор: " + model.Author.ShortName
};
db.SaveClaimStatusHistory(statusHistory);
statusHistory.DateString = statusHistory.Date.ToString("dd.MM.yyyy HH:mm");
//>>>>Уведомления
if (model.Author.Id != model.Manager.Id)
{
var host = ConfigurationManager.AppSettings["AppHost"];
var emessage = new StringBuilder();
emessage.Append("Добрый день!");
//message.Append(manager.Name);
emessage.Append("<br/>");
emessage.Append("Пользователь ");
emessage.Append(model.Author.ShortName);
emessage.Append(" создал заявку где Вы назначены менеджером.");
emessage.Append("<br/><br />");
emessage.Append(GetClaimInfo(model));
emessage.Append("<br/>");
emessage.Append("Ссылка на заявку: ");
emessage.Append("<a href='" + host + "/Claim/Index?claimId=" + model.Id + "'>" + host +
"/Claim/Index?claimId=" + model.Id + "</a>");
//message.Append("<br/>Сообщение от системы Спец расчет");
Notification.SendNotification(new List<UserBase>() { manager.GetUserBase(new List<Role>() { Role.Manager }) }, emessage.ToString(),
String.Format("{0} - {1} - Новая заявка СпецРасчет", model.TenderNumber, model.Customer));
}
}
return RedirectToAction("Index", "Claim", new { claimId = model.Id });
}
return RedirectToAction("NewClaim", "Claim", new { errorMessage = "при сохранении возникла ошибка" });
}
public TenderClaim LoadTenderClaimById(int id)
{
TenderClaim model = null;
using (var conn = new SqlConnection(_connectionString))
{
var cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "LoadTenderClaimById";
cmd.Parameters.AddWithValue("@id", id);
conn.Open();
var rd = cmd.ExecuteReader();
if (rd.HasRows)
{
rd.Read();
model = new TenderClaim()
{
Id = rd.GetInt32(0),
TenderNumber = rd.GetString(1),
TenderStart = rd.GetDateTime(2),
ClaimDeadline = rd.GetDateTime(3),
KPDeadline = rd.GetDateTime(4),
Comment = rd.GetString(5),
Customer = rd.GetString(6),
CustomerInn = rd[7] == DBNull.Value ? String.Empty : rd.GetString(7),
Sum = (double) rd.GetDecimal(8),
DealType = rd.GetInt32(9),
TenderUrl = rd.GetString(10),
TenderStatus = rd.GetInt32(11),
Manager = new Manager()
{
Id = rd.GetString(12),
SubDivision = rd.GetString(13)
},
ClaimStatus = rd.GetInt32(14),
RecordDate = rd.GetDateTime(15),
Author = new UserBase() {Id = rd.GetString(17)},
CurrencyUsd = (double) rd.GetDecimal(20),
CurrencyEur = (double) rd.GetDecimal(21),
DeliveryDate = rd[22] == DBNull.Value ? null : (DateTime?) rd.GetDateTime(22),
DeliveryPlace = rd.GetString(23),
AuctionDate = rd[24] == DBNull.Value ? null : (DateTime?) rd.GetDateTime(24),
ProductManagers = new List<ProductManager>()
,
SumCurrency = rd[25] == DBNull.Value ? 1 : rd.GetInt32(25),
DeliveryDateEnd = rd[26] == DBNull.Value ? null : (DateTime?)rd.GetDateTime(26)
};
if (model.Sum.Equals(-1)) model.Sum = 0;
if (model.CurrencyUsd.Equals(-1)) model.CurrencyUsd = 0;
if (model.CurrencyEur.Equals(-1)) model.CurrencyEur = 0;
model.KPDeadlineString = model.KPDeadline.ToString("dd.MM.yyyy");
model.TenderStartString = model.TenderStart.ToString("dd.MM.yyyy");
model.ClaimDeadlineString = model.ClaimDeadline.ToString("dd.MM.yyyy");
model.RecordDateString = model.RecordDate.ToString("dd.MM.yyyy HH:mm");
model.DeliveryDateString = model.DeliveryDate.HasValue
? model.DeliveryDate.Value.ToString("dd.MM.yyyy")
: string.Empty;
model.DeliveryDateEndString = model.DeliveryDateEnd.HasValue
? model.DeliveryDateEnd.Value.ToString("dd.MM.yyyy")
: string.Empty;
model.AuctionDateString = model.AuctionDate.HasValue
? model.AuctionDate.Value.ToString("dd.MM.yyyy")
: string.Empty;
model.StrSum = String.Format("{0:### ### ### ### ###.##}", model.Sum);
switch (model.SumCurrency)
{
case 1:
model.StrSum += " руб.";
break;
case 2:
model.StrSum += " USD";
break;
case 3:
model.StrSum += " EUR";
break;
}
}
rd.Dispose();
}
return model;
}
public List<TenderClaim> FilterTenderClaims(FilterTenderClaim filter)
{
var list = new List<TenderClaim>();
using (var conn = new SqlConnection(_connectionString))
{
var cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "FilterTenderClaims";
cmd.Parameters.AddWithValue("@rowCount", filter.RowCount);
if (filter.IdClaim != 0) cmd.Parameters.AddWithValue("@idClaim", filter.IdClaim);
if (!string.IsNullOrEmpty(filter.TenderNumber)) cmd.Parameters.AddWithValue("@tenderNumber", filter.TenderNumber);
if (!string.IsNullOrEmpty(filter.Customer)) cmd.Parameters.AddWithValue("@customer", filter.Customer);
if (filter.ClaimStatus != null && filter.ClaimStatus.Any()) cmd.Parameters.AddWithValue("@claimStatusIds", string.Join(",", filter.ClaimStatus));
if (filter.DealTypeId != 0) cmd.Parameters.AddWithValue("@dealTypeId", filter.DealTypeId);
if (!string.IsNullOrEmpty(filter.IdManager)) cmd.Parameters.AddWithValue("@manager", filter.IdManager);
if (!string.IsNullOrEmpty(filter.ManagerSubDivision)) cmd.Parameters.AddWithValue("@managerSubDivision", filter.ManagerSubDivision);
if (filter.Overdie.HasValue) cmd.Parameters.AddWithValue("@overdie", filter.Overdie);
if (!string.IsNullOrEmpty(filter.IdProductManager)) cmd.Parameters.AddWithValue("@idProductManager", filter.IdProductManager);
if (!string.IsNullOrEmpty(filter.Author)) cmd.Parameters.AddWithValue("@author", filter.Author);
if (!string.IsNullOrEmpty(filter.TenderStartFrom)) cmd.Parameters.AddWithValue("@tenderStartFrom", DateTime.ParseExact(filter.TenderStartFrom, "dd.MM.yyyy", CultureInfo.CurrentCulture));
if (!string.IsNullOrEmpty(filter.TenderStartTo)) cmd.Parameters.AddWithValue("@tenderStartTo", DateTime.ParseExact(filter.TenderStartTo, "dd.MM.yyyy", CultureInfo.CurrentCulture));
conn.Open();
var rd = cmd.ExecuteReader();
if (rd.HasRows)
{
while (rd.Read())
{
var model = new TenderClaim()
{
Id = rd.GetInt32(0),
TenderNumber = rd.GetString(1),
TenderStart = rd.GetDateTime(2),
ClaimDeadline = rd.GetDateTime(3),
KPDeadline = rd.GetDateTime(4),
Comment = rd.GetString(5),
Customer = rd.GetString(6),
Sum = (double) rd.GetDecimal(8),
DealType = rd.GetInt32(9),
TenderUrl = rd.GetString(10),
TenderStatus = rd.GetInt32(11),
Manager = new Manager()
{
Id = rd.GetString(12),
SubDivision = rd.GetString(13)
},
ClaimStatus = rd.GetInt32(14),
RecordDate = rd.GetDateTime(15),
Author = new UserBase() {Id = rd.GetString(17)},
CurrencyUsd = (double)rd.GetDecimal(20),
CurrencyEur = (double)rd.GetDecimal(21),
DeliveryDate = rd[22] == DBNull.Value ? null : (DateTime?)rd.GetDateTime(22),
DeliveryPlace = rd.GetString(23),
AuctionDate = rd[24] == DBNull.Value ? null : (DateTime?)rd.GetDateTime(24),
ProductManagers = new List<ProductManager>(),
StrSum = String.Format("{0:### ### ### ### ###.##}", (double)rd.GetDecimal(8))
};
if (!rd.IsDBNull(7))
{
model.CustomerInn = rd.GetString(7);
}
else
{
model.CustomerInn = String.Empty;}
if (model.Sum.Equals(-1)) model.Sum = 0;
if (model.CurrencyUsd.Equals(-1)) model.CurrencyUsd = 0;
if (model.CurrencyEur.Equals(-1)) model.CurrencyEur = 0;
model.KPDeadlineString = model.KPDeadline.ToString("dd.MM.yyyy");
model.TenderStartString = model.TenderStart.ToString("dd.MM.yyyy");
model.ClaimDeadlineString = model.ClaimDeadline.ToString("dd.MM.yyyy");
model.RecordDateString = model.RecordDate.ToString("dd.MM.yyyy HH:mm");
model.DeliveryDateString = model.DeliveryDate.HasValue
? model.DeliveryDate.Value.ToString("dd.MM.yyyy")
: string.Empty;
model.AuctionDateString = model.AuctionDate.HasValue
? model.AuctionDate.Value.ToString("dd.MM.yyyy")
: string.Empty;
list.Add(model);
}
}
rd.Dispose();
}
return list;
}
public bool UpdateClaimCurrency(TenderClaim model)
{
var result = false;
using (var conn = new SqlConnection(_connectionString))
{
var cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "UpdateTenderClaimCurrency";
cmd.Parameters.AddWithValue("@id", model.Id);
if (!model.CurrencyUsd.Equals(0)) cmd.Parameters.AddWithValue("@currencyUsd", model.CurrencyUsd);
if (!model.CurrencyEur.Equals(0)) cmd.Parameters.AddWithValue("@currencyEur", model.CurrencyEur);
conn.Open();
result = cmd.ExecuteNonQuery() > 0;
}
return result;
}
public bool SaveTenderClaim(ref TenderClaim model)
{
var result = false;
using (var conn = new SqlConnection(_connectionString))
{
var cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "SaveTenderClaim";
if (!string.IsNullOrEmpty(model.TenderNumber))
cmd.Parameters.AddWithValue("@tenderNumber", model.TenderNumber);
cmd.Parameters.AddWithValue("@tenderStart", model.TenderStart);
cmd.Parameters.AddWithValue("@claimDeadline", model.ClaimDeadline);
cmd.Parameters.AddWithValue("@kPDeadline", model.KPDeadline);
if (!string.IsNullOrEmpty(model.Comment))
cmd.Parameters.AddWithValue("@comment", model.Comment);
cmd.Parameters.AddWithValue("@customer", model.Customer);
if (!string.IsNullOrEmpty(model.CustomerInn))
cmd.Parameters.AddWithValue("@customerInn", model.CustomerInn);
if (model.Sum >= 0) cmd.Parameters.AddWithValue("@totalSum", model.Sum);
cmd.Parameters.AddWithValue("@dealType", model.DealType);
if (!string.IsNullOrEmpty(model.TenderUrl))
cmd.Parameters.AddWithValue("@tenderUrl", model.TenderUrl);
if (!model.CurrencyUsd.Equals(0)) cmd.Parameters.AddWithValue("@currencyUsd", model.CurrencyUsd);
if (!model.CurrencyEur.Equals(0)) cmd.Parameters.AddWithValue("@currencyEur", model.CurrencyEur);
if (!string.IsNullOrEmpty(model.DeliveryPlace))
cmd.Parameters.AddWithValue("@deliveryPlace", model.DeliveryPlace);
if (model.DeliveryDate.HasValue) cmd.Parameters.AddWithValue("@deliveryDate", model.DeliveryDate.Value);
if (model.DeliveryDateEnd.HasValue) cmd.Parameters.AddWithValue("@deliveryDateEnd", model.DeliveryDateEnd.Value);
if (model.AuctionDate.HasValue) cmd.Parameters.AddWithValue("@auctionDate", model.AuctionDate.Value);
cmd.Parameters.AddWithValue("@tenderStatus", model.TenderStatus);
cmd.Parameters.AddWithValue("@manager", model.Manager.Id);
cmd.Parameters.AddWithValue("@managerSubDivision", model.Manager.SubDivision);
cmd.Parameters.AddWithValue("@claimStatus", model.ClaimStatus);
cmd.Parameters.AddWithValue("@recordDate", model.RecordDate);
cmd.Parameters.AddWithValue("@deleted", model.Deleted);
cmd.Parameters.AddWithValue("@author", model.Author.Id);
if (model.SumCurrency > 0)
cmd.Parameters.AddWithValue("@idSumCurrency", model.SumCurrency);
conn.Open();
var rd = cmd.ExecuteReader();
if (rd.HasRows)
{
rd.Read();
var id = rd.GetInt32(0);
if (id != -1)
{
result = true;
model.Id = id;
}
}
rd.Dispose();
}
return result;
}
