public List <Financing> GetAllFinancing()
{
List <Financing> financing = new List <Financing>();
using (var cn = new SqlConnection(Settings.GetConnectionString()))
{
SqlCommand cmd = new SqlCommand("FinancingSelectAll", cn);
cmd.CommandType = CommandType.StoredProcedure;
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
Financing currentRow = new Financing();
currentRow.FinancingId = (int)dr["FinancingId"];
currentRow.FinancingDescription = dr["FinancingDescription"].ToString();
financing.Add(currentRow);
}
}
}
return(financing);
}
public override int GetHashCode()
{
unchecked {
int hash = 17;
if (Country != default(string))
{
hash = hash * 23 + Country.GetHashCode();
}
if (City != default(string))
{
hash = hash * 23 + City.GetHashCode();
}
if (Bank != default(string))
{
hash = hash * 23 + Bank.GetHashCode();
}
if (PostalCode != default(string))
{
hash = hash * 23 + PostalCode.GetHashCode();
}
if (Financing != default(string))
{
hash = hash * 23 + Financing.GetHashCode();
}
if (LastDigits != default(string))
{
hash = hash * 23 + LastDigits.GetHashCode();
}
if (Brand != default(string))
{
hash = hash * 23 + Brand.GetHashCode();
}
if (ExpirationMonth != default(int))
{
hash = hash * 23 + ExpirationMonth.GetHashCode();
}
if (ExpirationYear != default(int))
{
hash = hash * 23 + ExpirationYear.GetHashCode();
}
if (Fingerprint != default(string))
{
hash = hash * 23 + Fingerprint.GetHashCode();
}
if (Name != default(string))
{
hash = hash * 23 + Name.GetHashCode();
}
if (SecurityCodeCheck != default(bool))
{
hash = hash * 23 + SecurityCodeCheck.GetHashCode();
}
return(hash);
}
}
public void AddFinancing(string financing)
{
Financing fin = new Financing()
{
Id = financings.Max(c => c.Id) + 1,
FinancingType = financing
};
financings.Add(fin);
}
/// <summary>
/// The Delete
/// </summary>
/// <param name="entity">The entity<see cref="Financing"/></param>
/// <returns>The <see cref="ServiceResult"/></returns>
public ServiceResult Delete(Financing entity)
{
try
{
_repository.Delete(entity);
return(new ServiceResult(true));
}
catch (Exception ex)
{
return(new ServiceResult(false)
{
Error = ex.ToString()
});
}
}
public override int GetHashCode()
{
unchecked {
int hash = 17;
hash = hash * 23 + Country.GetHashCode();
hash = hash * 23 + City.GetHashCode();
hash = hash * 23 + Bank.GetHashCode();
hash = hash * 23 + PostalCode.GetHashCode();
hash = hash * 23 + Financing.GetHashCode();
hash = hash * 23 + LastDigits.GetHashCode();
hash = hash * 23 + Brand.GetHashCode();
hash = hash * 23 + ExpirationMonth.GetHashCode();
hash = hash * 23 + ExpirationYear.GetHashCode();
hash = hash * 23 + Fingerprint.GetHashCode();
hash = hash * 23 + Name.GetHashCode();
hash = hash * 23 + SecurityCodeCheck.GetHashCode();
return(hash);
}
}
/// <summary>
/// The Save
/// </summary>
/// <param name="entity">The entity<see cref="Financing"/></param>
/// <returns>The <see cref="ServiceResult"/></returns>
public ServiceResult Save(Financing entity)
{
try
{
if (entity.Identifier == 0)
{
_repository.Add(entity);
}
else
{
_repository.Update(entity);
}
return(new ServiceResult(true));
}
catch (Exception ex)
{
return(new ServiceResult(false)
{
Error = ex.ToString()
});
}
}
public override IEnumerable <decimal> GetCostAt(DateTime at)
{
return(Financing.GetCostAt(at));
}
public override decimal GetSalePrice(decimal price, DateTime at)
{
return(0.00M
+ new HomeSaleStrategy(price, at).GetReturnedPrice()
- Financing.GetSalePrice(price, at));
}
public override decimal GetPurchasePrice(decimal price)
{
return(0.00M
+ new HomePurchaseStrategy(price, PurchasedAt).GetReturnedPrice()
+ Financing.GetPurchasePrice(price));
}
public void ParseCellOnce(ICell cell, string cellStrValue, CellRangeAddress cellRangeAddress, WorkbookParserContext context, out bool parseCellAgain, out bool skipRow)
{
parseCellAgain = false;
skipRow = false;
if (context.State == WorkbookParserState.Initial)
{
context.State = WorkbookParserState.Header;
parseCellAgain = true;
return;
}
if (context.State == WorkbookParserState.Header)
{
if (!cell.IsMergedCell)
{
if (cell.ColumnIndex == 0 && cellStrValue.Equals("№ п/п", StringComparison.CurrentCultureIgnoreCase))
{
context.State = WorkbookParserState.TableHeader;
return;
}
}
else if (cell.IsMergedCell && cellRangeAddress.NumberOfCells >= 10)
{
if (Regex.IsMatch(cellStrValue, "факультет|институт", RegexOptions.IgnoreCase))
{
var divisionTitle = cellStrValue.ToLower();
var division = divisions.FindOne(d => d.Title == divisionTitle);
if (division == null)
{
division = new Division {
Title = divisionTitle
};
var id = divisions.Insert(division);
db.Commit();
division.Id = id.AsInt32;
}
context.Division = division;
}
if (Regex.IsMatch(cellStrValue, "форма обучения", RegexOptions.IgnoreCase))
{
var eduForm = eduForms.FindOne(ef => ef.Title == cellStrValue);
if (eduForm == null)
{
eduForm = new EduForm {
Title = cellStrValue
};
var id = eduForms.Insert(eduForm);
db.Commit();
eduForm.Id = id.AsInt32;
}
context.EduForm = eduForm;
}
if (Regex.IsMatch(cellStrValue, "бюджет|договор", RegexOptions.IgnoreCase))
{
var financingTitle = cellStrValue.ToLower();
var financing = financings.FindOne(f => f.Title == financingTitle);
if (financing == null)
{
financing = new Financing {
Title = financingTitle
};
var id = financings.Insert(financing);
db.Commit();
financing.Id = id.AsInt32;
}
context.Financing = financing;
}
if (Regex.IsMatch(cellStrValue, "бакалавриат|специалитет|магистратур|подготовки кадров|основного общего|среднего общего", RegexOptions.IgnoreCase))
{
var eduLevelStrValue = GetEduLevelString(cellStrValue);
var eduLevel = eduLevels.FindOne(el => el.Title == eduLevelStrValue);
if (eduLevel == null)
{
eduLevel = new EduLevel {
Title = eduLevelStrValue
};
var id = eduLevels.Insert(eduLevel);
db.Commit();
eduLevel.Id = id.AsInt32;
}
context.EduLevel = eduLevel;
if (context.EduLevel.Title.StartsWith("специалитет СПО", StringComparison.CurrentCultureIgnoreCase))
{
context.IsCollegeList = true;
}
else
{
context.IsCollegeList = false;
}
var eduProgramTitle = Regex.Match(cellStrValue, @"«[^»]+»", RegexOptions.Singleline | RegexOptions.IgnoreCase).Value;
var eduProfileTitle = Regex.Match(cellStrValue, @"Профиль:(.*)", RegexOptions.Singleline | RegexOptions.IgnoreCase).Groups [1].Value;
if (string.IsNullOrEmpty(eduProfileTitle))
{
eduProfileTitle = Regex.Match(cellStrValue, @"на базе .* общего образования", RegexOptions.Singleline | RegexOptions.IgnoreCase).Value;
}
eduProgramTitle = FormatEduProgramTitle(eduProgramTitle);
eduProfileTitle = FormatEduProgramTitle(eduProfileTitle);
if (context.EduProgram == null)
{
context.EduProgram = new EduProgram();
}
context.EduProgram.Title = eduProgramTitle;
context.EduProgram.ProfileTitle = eduProfileTitle;
context.EduProgram.EduLevelId = context.EduLevel.Id;
context.EduProgram.DivisionId = context.Division.Id;
}
}
return;
}
if (context.State == WorkbookParserState.TableHeader && context.IsCollegeList)
{
if (cell.ColumnIndex == 5)
{
context.EduProgram.Exam1Title = cellStrValue;
}
else if (cell.ColumnIndex == 7)
{
context.EduProgram.Exam2Title = cellStrValue;
}
else if (cell.ColumnIndex > 7)
{
context.State = WorkbookParserState.List;
context.Order = 0;
skipRow = true;
InsertEduProgram(context);
}
return;
}
if (context.State == WorkbookParserState.TableHeader && !context.IsCollegeList)
{
if (cell.ColumnIndex == 4)
{
context.EduProgram.Exam1Title = cellStrValue;
}
else if (cell.ColumnIndex == 5)
{
context.EduProgram.Exam2Title = cellStrValue;
}
else if (cell.ColumnIndex == 6)
{
context.EduProgram.Exam3Title = cellStrValue;
}
else if (cell.ColumnIndex > 6)
{
context.State = WorkbookParserState.List;
context.Order = 0;
skipRow = true;
InsertEduProgram(context);
}
return;
}
if (context.State == WorkbookParserState.List)
{
if (cell.IsMergedCell && cellRangeAddress.NumberOfCells >= 10)
{
context.State = WorkbookParserState.Header;
parseCellAgain = true;
return;
}
if (cell.ColumnIndex == 0)
{
context.Applicant = new Applicant();
context.Applicant.Order = ++context.Order;
if (int.TryParse(cellStrValue, out int rankedOrder))
{
context.Applicant.RankedOrder = rankedOrder;
}
}
else if (cell.ColumnIndex == 1)
{
context.Applicant.Name = cellStrValue;
}
}
if (context.State == WorkbookParserState.List && context.IsCollegeList)
{
if (cell.ColumnIndex == 3)
{
context.Applicant.HasOriginal = cellStrValue.Equals("Оригинал", StringComparison.CurrentCultureIgnoreCase);
}
else if (cell.ColumnIndex == 5)
{
if (decimal.TryParse(cellStrValue, out decimal exam1Rate))
{
context.Applicant.Exam1Rate = exam1Rate;
}
}
else if (cell.ColumnIndex == 7)
{
context.Applicant.Exam2Mark = cellStrValue;
}
else if (cell.ColumnIndex == 9)
{
if (decimal.TryParse(cellStrValue, out decimal totalRate))
{
context.Applicant.TotalRate = totalRate;
}
}
else if (cell.ColumnIndex == 10)
{
context.Applicant.Status = cellStrValue;
}
else if (cell.ColumnIndex == 11)
{
context.Applicant.RejectReason = cellStrValue;
}
else if (cell.ColumnIndex > 11)
{
context.Applicant.EduProgramId = context.EduProgram.Id;
context.Applicant.EduFormId = context.EduForm.Id;
context.Applicant.FinancingId = context.Financing.Id;
applicants.Insert(context.Applicant);
skipRow = true;
}
return;
}
if (context.State == WorkbookParserState.List && !context.IsCollegeList)
{
if (cell.ColumnIndex == 2)
{
context.Applicant.HasOriginal = cellStrValue.Equals("Оригинал", StringComparison.CurrentCultureIgnoreCase);
}
else if (cell.ColumnIndex == 3)
{
context.Applicant.HasAgreement = cellStrValue.Equals("Да", StringComparison.CurrentCultureIgnoreCase);
}
if (cell.ColumnIndex == 4)
{
if (decimal.TryParse(cellStrValue, out decimal exam1Rate))
{
context.Applicant.Exam1Rate = exam1Rate;
}
}
else if (cell.ColumnIndex == 5)
{
if (decimal.TryParse(cellStrValue, out decimal exam2Rate))
{
context.Applicant.Exam2Rate = exam2Rate;
}
}
else if (cell.ColumnIndex == 6)
{
if (decimal.TryParse(cellStrValue, out decimal exam3Rate))
{
context.Applicant.Exam3Rate = exam3Rate;
}
}
else if (cell.ColumnIndex == 7)
{
if (decimal.TryParse(cellStrValue, out decimal achRate))
{
context.Applicant.AchRate = achRate;
}
}
else if (cell.ColumnIndex == 8)
{
if (decimal.TryParse(cellStrValue, out decimal totalRate))
{
context.Applicant.TotalRate = totalRate;
}
}
else if (cell.ColumnIndex == 9)
{
context.Applicant.Category = cellStrValue;
}
else if (cell.ColumnIndex == 10)
{
if (cellStrValue.Equals("Да", StringComparison.CurrentCultureIgnoreCase))
{
context.Applicant.HasPreemptiveRight = true;
}
}
else if (cell.ColumnIndex == 11)
{
context.Applicant.Status = cellStrValue;
}
else if (cell.ColumnIndex == 12)
{
context.Applicant.RejectReason = cellStrValue;
}
else if (cell.ColumnIndex > 12)
{
context.Applicant.EduProgramId = context.EduProgram.Id;
context.Applicant.EduFormId = context.EduForm.Id;
context.Applicant.FinancingId = context.Financing.Id;
applicants.Insert(context.Applicant);
skipRow = true;
}
return;
}
}
private void MI_Financing_FromExcel_Click(object sender, RoutedEventArgs e)
{
DBSolom.Db db = new Db(Func.GetConnectionString);
if (db.Lows.Include(i => i.Правовласник).FirstOrDefault(f => f.Видалено == false && f.Правовласник.Логін == Func.Login && f.Financing == true) is null)
{
MessageBox.Show("У вас відсутні права на виконання цієї операції!", "Maestro", MessageBoxButton.OK, MessageBoxImage.Stop);
}
else
{
if (MessageBox.Show("Увага!\nЦя операція є небезпечною та дуже затратною в часі, перевірте чи всі головні розпорядники є в базі, кекв та інші необхідні властивості.\nВи підтверджуєте виконання?", "Maestro", MessageBoxButton.YesNo, MessageBoxImage.Warning) == MessageBoxResult.Yes)
{
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.Filter = "Excel files (*.xlsx;*.xlsm;*.xls)|*.xlsx;*.xlsm;*.xls";
if (openFileDialog.ShowDialog() == true)
{
var Task = new Task(() =>
{
//Потому что Finally
Excel.Application application = null;
Excel.Workbook workbook = null;
Excel.Worksheet worksheet = null;
Excel.Range range = null;
User user = db.Users.First(f => f.Видалено == false && f.Логін == Func.Login);
try
{
#region "Variables"
application = new Excel.Application();
application.AskToUpdateLinks = false;
application.DisplayAlerts = false;
workbook = application.Workbooks.Open(openFileDialog.FileName);
worksheet = workbook.Worksheets["Maestro_Data"];
double sum = 0;
DateTime Проведено_Е = DateTime.Now;
string main_manager = null;
string microfond = null;
long kfk_code = 0;
long kekv_code = 0;
Main_manager Main_Manager;
KFK kFK;
KEKB kEKB;
MicroFoundation microFoundation;
List <Financing> localFinancings = new List <Financing>();
List <string> errors = new List <string>();
#endregion
for (int i = 2; i <= application.WorksheetFunction.CountA(worksheet.Columns[1]); i++)
{
#region "Variables"
range = worksheet.Cells[i, 1];
Проведено_Е = Convert.ToDateTime(Convert.ToString(range.Value2));
range = worksheet.Cells[i, 2];
microfond = (string)range.Value2;
microFoundation = db.MicroFoundations.Include(c => c.Фонд).First(f => f.Видалено == false && f.Повністю == microfond);
range = worksheet.Cells[i, 3];
main_manager = (string)range.Value2;
Main_Manager = db.Main_Managers.First(f => f.Видалено == false && f.Найменування == main_manager);
range = worksheet.Cells[i, 4];
kfk_code = Convert.ToInt64(Convert.ToString(range.Value2));
kFK = db.KFKs.First(f => f.Видалено == false && f.Код == kfk_code);
range = worksheet.Cells[i, 5];
kekv_code = Convert.ToInt64(Convert.ToString(range.Value2));
kEKB = db.KEKBs.First(f => f.Видалено == false && f.Код == kekv_code);
range = worksheet.Cells[i, 6];
sum = Convert.ToDouble(Convert.ToString(range.Value2 ?? 0));
#endregion
Financing financing = new Financing()
{
Створив = user,
Змінив = user,
Підписано = true,
Проведено = Проведено_Е,
Мікрофонд = microFoundation,
Головний_розпорядник = Main_Manager,
КФК = kFK,
КЕКВ = kEKB,
Сума = sum
};
localFinancings.Add(financing);
}
foreach (var item in localFinancings)
{
var x = Func.GetCurrentPlanAndRemainderFromDBPerMonth(db, item.Проведено.Year, item.КФК, item.Головний_розпорядник, item.КЕКВ, item.Мікрофонд.Фонд);
if ((x[TypeOfFinanceData.Remainders][item.Проведено.Month - 1] - item.Сума) < 0)
{
errors.Add($"[Дата: {item.Проведено.ToShortDateString()}] [Фонд: {item.Мікрофонд.Фонд.Код}]" +
$" [КПБ: {item.КФК.Код}] [Головний розпорядник: {item.Головний_розпорядник.Найменування}]" +
$" [КЕКВ: {item.КЕКВ.Код}] [Місяць: {Func.names_months[item.Проведено.Month - 1]}]" +
$" [Остаток:{x[TypeOfFinanceData.Remainders][item.Проведено.Month - 1]}]");
}
if (errors.Count == 0)
{
db.Financings.Local.Add(item);
}
else
{
break;
}
}
if (errors.Count == 0)
{
db.SaveChanges();
MessageBox.Show("Готово!", "Maestro", MessageBoxButton.OK, MessageBoxImage.Information);
}
else
{
Dispatcher.Invoke(() =>
{
Maestro.Sys.Errors er = new Maestro.Sys.Errors(errors);
er.Show();
});
}
}
catch (Exception Exp)
{
MessageBox.Show(Exp.Message);
}
finally
{
worksheet = null;
openFileDialog = null;
if (workbook != null)
{
workbook.Close(false);
workbook = null;
}
if (application != null)
{
application.Quit();
application = null;
}
}
});
Task.Start();
}
else
{
MessageBox.Show("Оберіть файл з листом Maestro_Data!", "Maestro", MessageBoxButton.OK, MessageBoxImage.Hand);
}
}
}
}
public static void SetFinancing(GameEntity product, Financing financing, int level)
{
product.financing.Financing[financing] = level;
}
