private void CreateHarmony(Recipient recipient)
{
string name = recipient.internalName;
var cellData = new List <object[]>();
int num = 0;
int colC = 0;
string date = payments[0].paymentDate;
string[] dateArr = date.Split(' ');
date = dateArr[0];
string checkName = "";
List <Payment> harmony = new List <Payment>();
for (int j = 0; j < payments.Count; j++)
{
if (payments[j].name.Contains("Гармония"))
{
harmony.Add(payments[j]);
}
}
var sortedpayments = from u in harmony
orderby u.name
select u;
if (sortedpayments.Count() != 0)
{
checkName = sortedpayments.ElementAt(0).name;
object[] objHeader = { "РНКО РИБ: принятые платежи в пользу " + sortedpayments.ElementAt(0).descriptionName };
cellData.Add(objHeader);
object[] objDate = { "За " + date };
cellData.Add(objDate);
object[] objDescription = { sortedpayments.ElementAt(0).name };
cellData.Add(objDescription);
object[] objColumnHeaders = sortedpayments.ElementAt(0).headersColumns;
cellData.Add(objColumnHeaders);
}
for (int i = 0; i < sortedpayments.Count(); i++)
{
recipient.exluse = sortedpayments.ElementAt(i).exluse;
++num;
if (checkName != sortedpayments.ElementAt(i).name)
{
num--;
using (ExcelPackage excel = new ExcelPackage())
{
excel.Workbook.Worksheets.Add(name + " " + date);
var worksheet = excel.Workbook.Worksheets[name + " " + date];
object[] objItog = new object[] { "Доп.Коммиссия:" };
cellData.Add(objItog);
object[] objDopComm = new object[] { "Итого:" };
cellData.Add(objDopComm);
//Оформление шапки, загрузки данных
worksheet.Cells["A1:J1"].Merge = true;
worksheet.Cells["A2:J2"].Merge = true;
worksheet.Cells["A3:J3"].Merge = true;
worksheet.Cells[1, 1].LoadFromArrays(cellData);
worksheet.Cells.AutoFitColumns();
worksheet.Cells[1, 1].Style.Font.Bold = true;
worksheet.Cells[3, 1].Style.Font.Bold = true;
worksheet.Cells["A1:A3"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Cells[1, 1].Style.Font.Size = 14;
worksheet.Cells[2, 1].Style.Font.Size = 12;
worksheet.Cells[3, 1].Style.Font.Size = 12;
//Оформление строк Итого, Доп.Комиссия
worksheet.Cells["A" + (num + 5) + ":" + Char.ConvertFromUtf32((colC + 3) + 64) + (num + 5)].Merge = true;
worksheet.Cells["A" + (num + 6) + ":" + Char.ConvertFromUtf32((colC + 2) + 64) + (num + 6)].Merge = true;
worksheet.Cells["A" + (num + 5) + ":" + Char.ConvertFromUtf32((colC + 3) + 64) + (num + 5)].Style.Font.Bold = true;
worksheet.Cells["A" + (num + 6) + ":" + Char.ConvertFromUtf32((colC + 2) + 64) + (num + 6)].Style.Font.Bold = true;
//Рамка
worksheet.Cells["A4:" + Char.ConvertFromUtf32((colC + 6) + 64) + (num + 6)].Style.Border.Top.Style = ExcelBorderStyle.Thin;
worksheet.Cells["A4:" + Char.ConvertFromUtf32((colC + 6) + 64) + (num + 6)].Style.Border.Right.Style = ExcelBorderStyle.Thin;
worksheet.Cells["A4:" + Char.ConvertFromUtf32((colC + 6) + 64) + (num + 6)].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
worksheet.Cells["A4:" + Char.ConvertFromUtf32((colC + 6) + 64) + (num + 6)].Style.Border.Left.Style = ExcelBorderStyle.Thin;
//Формулы для строки Итого
worksheet.Cells[Char.ConvertFromUtf32((colC + 3) + 64) + (num + 6)].Formula = "=SUM(" + Char.ConvertFromUtf32((colC + 3) + 64) + 5 + ":"
+ Char.ConvertFromUtf32((colC + 3) + 64) + (num + 4) + ")";
worksheet.Cells[Char.ConvertFromUtf32((colC + 4) + 64) + (num + 6)].Formula = "=SUM(" + Char.ConvertFromUtf32((colC + 4) + 64) + 5 + ":"
+ Char.ConvertFromUtf32((colC + 4) + 64) + (num + 4) + ")";
worksheet.Cells[Char.ConvertFromUtf32((colC + 6) + 64) + (num + 6)].Formula = "=SUM(" + Char.ConvertFromUtf32((colC + 6) + 64) + 5 + ":"
+ Char.ConvertFromUtf32((colC + 6) + 64) + (num + 4) + ")";
//Формула для строки Доп.Комиссии
if (!recipient.exluse)
{
worksheet.Cells[Char.ConvertFromUtf32((colC + 4) + 64) + (num + 5)].Formula = "=IF(" + Char.ConvertFromUtf32((colC + 5) + 64) + (num + 5) + "<10000,20,0)";
worksheet.Cells[Char.ConvertFromUtf32((colC + 4) + 64) + (num + 6)].Formula = "=SUM(" + Char.ConvertFromUtf32((colC + 4) + 64) + 5 + ":"
+ Char.ConvertFromUtf32((colC + 4) + 64) + (num + 4) + ")+20";
}
//Формула для строки Итого
worksheet.Cells[Char.ConvertFromUtf32((colC + 5) + 64) + (num + 6)].Formula = Char.ConvertFromUtf32((colC + 3) + 64) + (num + 6) + "-"
+ Char.ConvertFromUtf32((colC + 4) + 64) + (num + 6);
DirectoryInfo dirInfo = new DirectoryInfo(strPath);
if (!dirInfo.Exists)
{
dirInfo.Create();
}
dirInfo.CreateSubdirectory("Registers");
if (checkName.Contains("Юность"))
{
FileInfo excelFile = new FileInfo(strPath + @"\Registers\Register " + name + "_unost " + date + ".xlsx");
excel.SaveAs(excelFile);
Logger.Log.Info("Создан реестр " + name + "_unost ");
SendMail(recipient.email, name + "_unost " + date);
}
else if (checkName.Contains("Песч"))
{
FileInfo excelFile = new FileInfo(strPath + @"\Registers\Register " + name + "_pesch " + date + ".xlsx");
excel.SaveAs(excelFile);
Logger.Log.Info("Создан реестр " + name + "_pesch ");
SendMail(recipient.email, name + "_pesch " + date);
}
else if (checkName.Contains("Коптево"))
{
FileInfo excelFile = new FileInfo(strPath + @"\Registers\Register " + name + "_koptevo " + date + ".xlsx");
excel.SaveAs(excelFile);
Logger.Log.Info("Создан реестр " + name + "_koptevo ");
SendMail(recipient.email, name + "_koptevo " + date);
}
else if (checkName.Contains("Ленин"))
{
FileInfo excelFile = new FileInfo(strPath + @"\Registers\Register " + name + "_lenin " + date + ".xlsx");
excel.SaveAs(excelFile);
Logger.Log.Info("Создан реестр " + name + "_lenin ");
SendMail(recipient.email, name + "_lenin " + date);
}
else if (checkName.Contains("Дмитр"))
{
FileInfo excelFile = new FileInfo(strPath + @"\Registers\Register " + name + "_dmitr " + date + ".xlsx");
excel.SaveAs(excelFile);
Logger.Log.Info("Создан реестр " + name + "_dmitr ");
SendMail(recipient.email, name + "_dmitr " + date);
}
if (checkName.Contains("Мероп"))
{
FileInfo excelFile = new FileInfo(strPath + @"\Registers\Register " + name + " " + date + ".xlsx");
excel.SaveAs(excelFile);
Logger.Log.Info("Создан реестр " + name);
SendMail(recipient.email, name + " " + date);
}
}
//SendMail(recipients[i].email, name + " " + date);
checkName = sortedpayments.ElementAt(i).name;
cellData.Clear();
num = 1;
object[] objHeader = { "РНКО РИБ: принятые платежи в пользу " + sortedpayments.ElementAt(i).descriptionName };
cellData.Add(objHeader);
object[] objDate = { "За " + date };
cellData.Add(objDate);
object[] objDescription = { sortedpayments.ElementAt(i).name };
cellData.Add(objDescription);
object[] objColumnHeaders = sortedpayments.ElementAt(i).headersColumns;
cellData.Add(objColumnHeaders);
}
object[] obj = new object[sortedpayments.ElementAt(i).columns.Count + 6];
colC = sortedpayments.ElementAt(i).columns.Count;
obj[0] = num;
obj[1] = sortedpayments.ElementAt(i).paymentDate;
int index = 2;
foreach (var pair in sortedpayments.ElementAt(i).columns)
{
obj[index] = pair.Value;
index++;
}
obj[index] = sortedpayments.ElementAt(i).amount;
index++;
obj[index] = sortedpayments.ElementAt(i).comission;
index++;
obj[index] = sortedpayments.ElementAt(i).amount - sortedpayments.ElementAt(i).comission;
index++;
obj[index] = sortedpayments.ElementAt(i).fee;
cellData.Add(obj);
}
using (ExcelPackage excel = new ExcelPackage())
{
excel.Workbook.Worksheets.Add(name + " " + date);
var worksheet = excel.Workbook.Worksheets[name + " " + date];
object[] objItog = new object[] { "Доп.Коммиссия:" };
cellData.Add(objItog);
object[] objDopComm = new object[] { "Итого:" };
cellData.Add(objDopComm);
//Оформление шапки, загрузки данных
worksheet.Cells["A1:J1"].Merge = true;
worksheet.Cells["A2:J2"].Merge = true;
worksheet.Cells["A3:J3"].Merge = true;
worksheet.Cells[1, 1].LoadFromArrays(cellData);
worksheet.Cells.AutoFitColumns();
worksheet.Cells[1, 1].Style.Font.Bold = true;
worksheet.Cells[3, 1].Style.Font.Bold = true;
worksheet.Cells["A1:A3"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Cells[1, 1].Style.Font.Size = 14;
worksheet.Cells[2, 1].Style.Font.Size = 12;
worksheet.Cells[3, 1].Style.Font.Size = 12;
//Оформление строк Итого, Доп.Комиссия
worksheet.Cells["A" + (num + 5) + ":" + Char.ConvertFromUtf32((colC + 3) + 64) + (num + 5)].Merge = true;
worksheet.Cells["A" + (num + 6) + ":" + Char.ConvertFromUtf32((colC + 2) + 64) + (num + 6)].Merge = true;
worksheet.Cells["A" + (num + 5) + ":" + Char.ConvertFromUtf32((colC + 3) + 64) + (num + 5)].Style.Font.Bold = true;
worksheet.Cells["A" + (num + 6) + ":" + Char.ConvertFromUtf32((colC + 2) + 64) + (num + 6)].Style.Font.Bold = true;
//Рамка
worksheet.Cells["A4:" + Char.ConvertFromUtf32((colC + 6) + 64) + (num + 6)].Style.Border.Top.Style = ExcelBorderStyle.Thin;
worksheet.Cells["A4:" + Char.ConvertFromUtf32((colC + 6) + 64) + (num + 6)].Style.Border.Right.Style = ExcelBorderStyle.Thin;
worksheet.Cells["A4:" + Char.ConvertFromUtf32((colC + 6) + 64) + (num + 6)].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
worksheet.Cells["A4:" + Char.ConvertFromUtf32((colC + 6) + 64) + (num + 6)].Style.Border.Left.Style = ExcelBorderStyle.Thin;
//Формулы для строки Итого
worksheet.Cells[Char.ConvertFromUtf32((colC + 3) + 64) + (num + 6)].Formula = "=SUM(" + Char.ConvertFromUtf32((colC + 3) + 64) + 5 + ":"
+ Char.ConvertFromUtf32((colC + 3) + 64) + (num + 4) + ")";
worksheet.Cells[Char.ConvertFromUtf32((colC + 4) + 64) + (num + 6)].Formula = "=SUM(" + Char.ConvertFromUtf32((colC + 4) + 64) + 5 + ":"
+ Char.ConvertFromUtf32((colC + 4) + 64) + (num + 4) + ")";
worksheet.Cells[Char.ConvertFromUtf32((colC + 6) + 64) + (num + 6)].Formula = "=SUM(" + Char.ConvertFromUtf32((colC + 6) + 64) + 5 + ":"
+ Char.ConvertFromUtf32((colC + 6) + 64) + (num + 4) + ")";
//Формула для строки Доп.Комиссии
if (!recipient.exluse)
{
worksheet.Cells[Char.ConvertFromUtf32((colC + 4) + 64) + (num + 5)].Formula = "=IF(" + Char.ConvertFromUtf32((colC + 5) + 64) + (num + 5) + "<10000,20,0)";
worksheet.Cells[Char.ConvertFromUtf32((colC + 4) + 64) + (num + 6)].Formula = "=SUM(" + Char.ConvertFromUtf32((colC + 4) + 64) + 5 + ":"
+ Char.ConvertFromUtf32((colC + 4) + 64) + (num + 4) + ")+20";
}
//Формула для строки Итого
worksheet.Cells[Char.ConvertFromUtf32((colC + 5) + 64) + (num + 6)].Formula = Char.ConvertFromUtf32((colC + 3) + 64) + (num + 6) + "-"
+ Char.ConvertFromUtf32((colC + 4) + 64) + (num + 6);
DirectoryInfo dirInfo = new DirectoryInfo(strPath);
if (!dirInfo.Exists)
{
dirInfo.Create();
}
dirInfo.CreateSubdirectory("Registers");
if (checkName.Contains("Юность"))
{
FileInfo excelFile = new FileInfo(strPath + @"\Registers\Register " + name + "_unost " + date + ".xlsx");
excel.SaveAs(excelFile);
Logger.Log.Info("Создан реестр " + name + "_unost ");
SendMail(recipient.email, name + "_unost " + date);
}
else if (checkName.Contains("Песч"))
{
FileInfo excelFile = new FileInfo(strPath + @"\Registers\Register " + name + "_pesch " + date + ".xlsx");
excel.SaveAs(excelFile);
Logger.Log.Info("Создан реестр " + name + "_pesch ");
SendMail(recipient.email, name + "_pesch " + date);
}
else if (checkName.Contains("Коптево"))
{
FileInfo excelFile = new FileInfo(strPath + @"\Registers\Register " + name + "_koptevo " + date + ".xlsx");
excel.SaveAs(excelFile);
Logger.Log.Info("Создан реестр " + name + "_koptevo ");
SendMail(recipient.email, name + "_koptevo " + date);
}
else if (checkName.Contains("Ленин"))
{
FileInfo excelFile = new FileInfo(strPath + @"\Registers\Register " + name + "_lenin " + date + ".xlsx");
excel.SaveAs(excelFile);
Logger.Log.Info("Создан реестр " + name + "_lenin ");
SendMail(recipient.email, name + "_lenin " + date);
}
else if (checkName.Contains("Дмитр"))
{
FileInfo excelFile = new FileInfo(strPath + @"\Registers\Register " + name + "_dmitr " + date + ".xlsx");
excel.SaveAs(excelFile);
Logger.Log.Info("Создан реестр " + name + "_dmitr ");
SendMail(recipient.email, name + "_dmitr " + date);
}
if (checkName.Contains("Мероп"))
{
FileInfo excelFile = new FileInfo(strPath + @"\Registers\Register " + name + " " + date + ".xlsx");
excel.SaveAs(excelFile);
Logger.Log.Info("Создан реестр " + name);
SendMail(recipient.email, name + " " + date);
}
}
}
//Метод получения информации из БД
private void PaymentQuery(SqlConnection connDDS, SqlConnection connEventWatch, SqlConnection connBazaWRK)
{
string date1 = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd 00:00:00");
string date2 = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd 23:59:59");
//string date1 = "2020-09-23 00:00:00";
//string date2 = "2020-09-23 23:59:59";
List <CheckRecipient> checkRecipients = new List <CheckRecipient>();
SqlCommand cmdBazaWRK = connBazaWRK.CreateCommand();
cmdBazaWRK.CommandTimeout = 0;
SqlCommand cmdWatch = connEventWatch.CreateCommand();
cmdWatch.CommandTimeout = 0;
SqlCommand cmd = connDDS.CreateCommand();
cmd.CommandTimeout = 0;
//Выгружается информация о контрагентах из БД DDS, у которых были платежные операции за выбранный период и записываются в список recipients
cmd.CommandText = "SELECT DISTINCT t0.RecipientID, t1.InternalName, t1.Description FROM Recipient as t1,Payment as t0 WHERE t0.RecipientID = t1.RecipientID " +
"and ( PaymentDate >= '" + date1 + "' and PaymentDate <= '" + date2 + "' ) ";
using (DbDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
Recipient rec = new Recipient(reader.GetValue(0).ToString(), reader.GetValue(1).ToString());
rec.description = reader.GetValue(2).ToString();
recipients.Add(rec);
}
}
}
for (int i = 0; i < recipients.Count; i++)
{
List <string> check = new List <string>();
//Выгружается информация какие поля(пользовательские кнопки) имеются в таблице PaymentInformation контрагента
cmd.CommandText = "SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_NAME = '" + recipients[i].internalName + "PaymentInformation'";
using (DbDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
check.Add(reader.GetValue(0).ToString());
}
}
//Выгружается информация какие поля(пользовательские кнопки) контрагента имеются в таблице RecipientField
//Далее записываются только те поля, которые присутствуют в обоих таблицах
cmd.CommandText = "SELECT InternalName, Name FROM RecipientField WHERE recipientID = " + recipients[i].recipientID;
using (DbDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
bool kostyl = true;
string strHeaders = "№,Дата платежа";
while (reader.Read())
{
if (!reader.GetValue(0).ToString().Contains("summa"))
{
foreach (var item in check)
{
if (item == reader.GetValue(0).ToString())
{
if (kostyl)
{
recipients[i].queryColumns += reader.GetValue(0).ToString();
kostyl = false;
}
else
{
recipients[i].queryColumns += ", " + reader.GetValue(0).ToString();
}
strHeaders += "," + reader.GetValue(1).ToString();
}
}
}
}
strHeaders += ",Сумма внесенная,Сумма комиссии,Сумма к зачислению,Вознаграждение";
recipients[i].headersColumns = strHeaders.Split(',');
}
}
//Выгружается информация электронных адресов контрагентов
cmdBazaWRK.CommandText = "SELECT Email FROM Rcpts WHERE recipientID = " + recipients[i].recipientID;
using (DbDataReader readerWRK = cmdBazaWRK.ExecuteReader())
{
if (readerWRK.HasRows)
{
bool kostyl = true;
while (readerWRK.Read())
{
if (kostyl)
{
recipients[i].email += readerWRK.GetValue(0).ToString();
kostyl = false;
}
else
{
recipients[i].email += "," + readerWRK.GetValue(0).ToString();
}
}
}
}
}
for (int i = 0; i < recipients.Count; i++)
{
cmd.CommandText = "SELECT SessionID, PaymentInformationID FROM Payment WHERE RecipientID = " + recipients[i].recipientID +
" and (PaymentDate >= '" + date1 + "' and PaymentDate <= '" + date2 + "')";
using (DbDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
Payment payment = new Payment(recipients[i].recipientID, recipients[i].internalName, recipients[i].queryColumns);
payment.headersColumns = recipients[i].headersColumns;
payment.description = recipients[i].description;
payment.session = reader.GetValue(0).ToString();
payment.paymentInformationID = reader.GetValue(1).ToString();
payments.Add(payment);
}
}
}
}
for (int i = 0; i < payments.Count; i++)
{
cmd.CommandText = "SELECT " + payments[i].queryColumns + " FROM " + payments[i].internalName
+ "PaymentInformation WHERE PaymentInformationID = " + payments[i].paymentInformationID;
using (DbDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
string str = payments[i].queryColumns.Replace(" ", "");
string[] strArr = payments[i].queryColumns.Split(',');
for (int j = 0; j < strArr.Length; j++)
{
payments[i].columns.Add(strArr[j], reader.GetValue(j).ToString());
}
}
}
}
cmdWatch.CommandText = "SELECT Occured, Amount, Comission, Fee, RecipientName FROM PaymentAcceptedEvent WHERE Session = '" +
payments[i].session + "'";
using (DbDataReader readerWatch = cmdWatch.ExecuteReader())
{
if (readerWatch.HasRows)
{
while (readerWatch.Read())
{
payments[i].paymentDate = readerWatch.GetValue(0).ToString();
payments[i].amount = Convert.ToDouble(readerWatch.GetValue(1).ToString());
payments[i].comission = Convert.ToDouble(readerWatch.GetValue(2).ToString());
payments[i].fee = Convert.ToDouble(readerWatch.GetValue(3).ToString());
payments[i].name = readerWatch.GetValue(4).ToString();
}
}
}
}
cmdBazaWRK.CommandText = "SELECT Recipient, Receiver, Exluse FROM Requisites";
//Выгружаются данные всех имеющихся контрагентов
using (DbDataReader readerCheckRecipient = cmdBazaWRK.ExecuteReader())
{
if (readerCheckRecipient.HasRows)
{
while (readerCheckRecipient.Read())
{
CheckRecipient cRec = new CheckRecipient(readerCheckRecipient.GetValue(0).ToString(), readerCheckRecipient.GetValue(1).ToString(),
Boolean.Parse(readerCheckRecipient.GetValue(2).ToString()));
checkRecipients.Add(cRec);
}
}
}
for (int i = 0; i < payments.Count; i++)
{
for (int j = 0; j < checkRecipients.Count; j++)
{
if (payments[i].name.Contains(checkRecipients[j].name))
{
payments[i].descriptionName = checkRecipients[j].descr;
payments[i].exluse = checkRecipients[j].exluse;
}
}
}
CreateExel();
}
