public ActionResult ExportForm(int?id)
{
if (id != null)
{
RateUploadModel model = new RateUploadModel()
{
PaymentsGroupId = id
};
return(View(model));
}
else
{
ModelState.AddModelError("", "Ошибка, не задан счет ");
return(RedirectToAction("Index"));
}
}
public ActionResult UploadForm()
{
RateUploadModel model = new RateUploadModel()
{
SalaryRate = 30
};
DateTime d_now = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
List <RegularPayment> list = new List <RegularPayment>();
foreach (var rp in db.RegularPayments)
{
DateTime d_from = rp.PayoutFrom == null ? new DateTime(DateTime.Now.Year - 10, DateTime.Now.Month, 1) :
new DateTime(rp.PayoutFrom.Value.Year, rp.PayoutFrom.Value.Month, 1);
DateTime d_to = rp.PayoutTo == null ? new DateTime(DateTime.Now.Year + 10, DateTime.Now.Month, 1) :
new DateTime(rp.PayoutTo.Value.Year, rp.PayoutTo.Value.Month, 1);
if (d_now >= d_from && d_now <= d_to)
{
list.Add(rp);
}
}
model.regularPayments = list.ToArray();
if (model.regularPayments != null)
{
model.regularPaymentIds = (from p in model.regularPayments select p.RegularPaymentId).ToArray();
model.accrueRegularPayments = Enumerable.Repeat <bool>(true, model.regularPayments.Length).ToArray();
}
foreach (string s in UploadFormErros)
{
ModelState.AddModelError("", s);
}
UploadFormErros.Clear();
return(View(model));
}
public ActionResult UploadForm(RateUploadModel input)
{
Dictionary <string, double> stavki = new Dictionary <string, double>();
if (ModelState.IsValid)
{
if (input.UploadFile != null)
{
using (var package = new ExcelPackage(input.UploadFile.InputStream))
{
var Sheet = package.Workbook.Worksheets[1];
int lastRow = Sheet.Dimension.End.Row;
int lastCol = Sheet.Dimension.End.Column;
int i = 5;
do
{
if (Sheet.Cells[i, 1].Value != null && Sheet.Cells[i, 5].Value != null)
{
if (!String.IsNullOrEmpty(Sheet.Cells[i, 1].Value.ToString()))
{
if (!String.IsNullOrEmpty(Sheet.Cells[i, 5].Value.ToString()))
{
double v;
if (double.TryParse(Sheet.Cells[i, 5].Value.ToString(), out v))
{
stavki.Add(Sheet.Cells[i, 1].Value.ToString().Trim().Replace(". ", ".").Replace(". ", ".").ToUpper(), v);
}
}
}
}
i++;
} while (i <= lastRow);
PaymentsGroup pg = new PaymentsGroup()
{
State = PaymentsGroupState.InProcess, WhenCreated = DateTime.Now, WhenPaidOut = DateTime.Now
};
pg = db.PaymentsGroups.Add(pg);
foreach (KeyValuePair <string, double> s in stavki)
{
ApplicationUser user = db.Users.FirstOrDefault(x => x.ShortName.Replace(". ", ".").ToUpper() == s.Key.Replace(". ", ".").ToUpper());
if (user != null)
{
double limit = input.SalaryRate * s.Value / 100;
double c_sum = 0;
foreach (var b in user.UserBalances.OrderBy(x => x.Sum).ToList())
{
if (b.Sum == 0)
{
continue;
}
UserPayment p = null;
if (b.Sum > (limit - c_sum))
{
double rsum = PaymentRequest.TruncSum(limit - c_sum);
if (((uint)rsum) == 0)
{
continue;
}
p = new UserPayment()
{
PaymentGroup = pg, Project = b.Project, User = user, Sum = rsum
};
p = db.UserPayments.Add(p);
break;
}
else
{
double rsum = PaymentRequest.TruncSum(b.Sum);
if (((uint)rsum) == 0)
{
continue;
}
p = new UserPayment()
{
PaymentGroup = pg, Project = b.Project, User = user, Sum = rsum
};
c_sum += p.Sum;
p = db.UserPayments.Add(p);
}
}
}
else
{
ModelState.AddModelError("", "в БД не найден " + s.Key);
}
}
db.SaveChanges();
if (input.regularPaymentIds != null)
{
// обработка регулярных платежей
for (int l = 0; l < input.regularPaymentIds.Length; l++)
{
if (input.accrueRegularPayments[l])
{
RegularPayment rp = db.RegularPayments.Find(input.regularPaymentIds[l]);
if (rp != null)
{
// проверяем дату
DateTime d_from = rp.PayoutFrom == null ? new DateTime(DateTime.Now.Year - 10, DateTime.Now.Month, 1) :
new DateTime(rp.PayoutFrom.Value.Year, rp.PayoutFrom.Value.Month, 1);
DateTime d_to = rp.PayoutTo == null ? new DateTime(DateTime.Now.Year + 10, DateTime.Now.Month, 1) :
new DateTime(rp.PayoutTo.Value.Year, rp.PayoutTo.Value.Month, 1);
DateTime d_now = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
if (d_now >= d_from && d_now <= d_to)
{
UserPayment p = pg.UsersPayments.FirstOrDefault(x => x.Project.ProjectId == rp.Project.ProjectId && x.User.Id == rp.RecipientUser.Id);
if (p == null)
{
p = db.UserPayments.Add(new UserPayment {
PaymentGroup = pg, Project = rp.Project, User = rp.RecipientUser, Sum = 0
});
}
p.Sum += rp.Sum;
p.RegularPaymentSum = rp.Sum;
db.SaveChanges();
}
}
}
}
}
return(RedirectToAction("CreateConfirm/" + pg.PaymentsGroupId));
}
}
else
{
UploadFormErros.Add("Не выбран файл ставок");
}
}
return(RedirectToAction("UploadForm"));
}
public ActionResult ExportForm(RateUploadModel input)
{
if (input.PaymentsGroupId != null)
{
if (input.UploadFile != null)
{
PaymentsGroupViewModel payments = getPaymentsGroupViewModel(input.PaymentsGroupId);
if (payments != null)
{
MemoryStream ms = new MemoryStream();
using (var package = new ExcelPackage(input.UploadFile.InputStream))
{
var Sheet = package.Workbook.Worksheets[1];
int lastRow = Sheet.Dimension.End.Row;
int lastCol = Sheet.Dimension.End.Column;
if (Sheet.AutoFilterAddress != null)
{
Sheet.Cells[Sheet.AutoFilterAddress.ToString()].AutoFilter = false;
}
try
{
Sheet.Cells["F:" + Sheet.Dimension.End.Address].Clear();
}
catch (Exception) { }
for (int j = 0; j < payments.ProjectsNames.Length; j++)
{
Sheet.Cells[4, 6 + j].Value = payments.ProjectsNames[j];
Sheet.Cells[4, 6 + j].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
}
Sheet.Cells[4, 6 + payments.ProjectsNames.Length].Value = "ВСЕГО";
var f = Sheet.Cells[4, 6 + payments.ProjectsNames.Length].Style.Font;
f.Bold = true;
Sheet.Cells[4, 6 + payments.ProjectsNames.Length].Style.Font = f;
Sheet.Cells[4, 6 + payments.ProjectsNames.Length].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
int i = 5;
int count_sum_lines = 0;
do
{
if (Sheet.Cells[i, 1].Value != null)
{
if (Sheet.Cells[i, 1].Value.ToString().Trim() != "ИТОГО")
{
int index = payments.PaymentsIndexByShortName(Sheet.Cells[i, 1].Value.ToString());
if (index != -1)
{
for (int j = 0; j < payments.Header.Payments[index].Length; j++)
{
if (payments.Header.Payments[index][j] != -1)
{
Sheet.Cells[i, 6 + j].Value = payments.Header.Payments[index][j];
}
}
}
Sheet.Cells[i, 6 + payments.ProjectsNames.Length].FormulaR1C1 = "SUM(RC[-" + (payments.ProjectsNames.Length + 1) + "]:RC[-1])";
count_sum_lines++;
}
for (int j = 0; j <= payments.ProjectsNames.Length; j++)
{
Sheet.Cells[i, 6 + j].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
}
}
i++;
} while (i <= lastRow);
Sheet.Cells[i - 1, 6 + payments.ProjectsNames.Length].FormulaR1C1 = "SUM(R[-1]C[0]:R[-" + count_sum_lines + "]C[0])";
var range = Sheet.Cells["A4:" + Sheet.Cells[i, 6 + payments.ProjectsNames.Length].Address];
range.AutoFilter = true;
package.SaveAs(ms);
}
ms.Position = 0;
return(new FileStreamResult(ms, "application/xlsx")
{
FileDownloadName = "Виртуальный счет КБ Харьков от " + payments.Header.WhenCreated.ToString("dd.MM.yyyy") + ".xlsx"
});
}
else
{
ModelState.AddModelError("", "Ошибка, не найден счет ");
}
}
else
{
ModelState.AddModelError("", "Ошибка загрузки файла ");
}
}
else
{
ModelState.AddModelError("", "Ошибка, не задан счет ");
}
return(View());
}