private List <RowManagerContrAgGr> GetRows(List <ShahmatcaModel> shahmatcaModels)
{
int numberRow = 0;
List <ClientInfo> clientInfos = _context.Set <ClientInfo>().ToList();
List <Data.Entities.OneCInfo.UserInfo> userInfos = _context.Set <Data.Entities.OneCInfo.UserInfo>().ToList();
List <RowManagerContrAgGr> rowManagerContrAgGrs = new List <RowManagerContrAgGr>();
foreach (ShahmatcaModel shahmatcaModel in shahmatcaModels)
{
ClientInfo clientInfo = clientInfos.FirstOrDefault(c => c.OneCId.ToString() == shahmatcaModel.Contragent_ID);
Data.Entities.OneCInfo.UserInfo userInfo = userInfos.FirstOrDefault(u => u.OneCId.ToString() == shahmatcaModel.Manager_ID);
if (clientInfo != null && userInfo != null)
{
RowManagerContrAgGr rowManagerContrAgGr = rowManagerContrAgGrs.FirstOrDefault(r => r.Contragent_ID == shahmatcaModel.Contragent_ID);
if (rowManagerContrAgGr == null)
{
rowManagerContrAgGrs.Add(new RowManagerContrAgGr()
{
Contragent = shahmatcaModel.Contragent,
Contragent_ID = shahmatcaModel.Contragent_ID,
GR_Contragent = shahmatcaModel.GR_Contragent,
Manager = shahmatcaModel.Manager,
Manager_ID = shahmatcaModel.Manager_ID,
NumberRow = numberRow
});
numberRow++;
}
}
}
return(rowManagerContrAgGrs);
}
private void CreateXsml(Dictionary <string, List <OhvatModel> > ohvats)
{
foreach (string id in ohvats.Keys)
{
if (!Directory.Exists("PDF/Manager"))
{
Directory.CreateDirectory("PDF/Manager");
}
Data.Entities.OneCInfo.UserInfo userInfo = _context.Set <Data.Entities.OneCInfo.UserInfo>().FirstOrDefault(c => c.OneCId.ToString() == id);
if (userInfo == null)
{
continue;
}
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.
Create($"PDF/Manager/Oxvat{userInfo.UserId}.xlsx", SpreadsheetDocumentType.Workbook);
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.
AppendChild <Sheets>(new Sheets());
WorkbookStylesPart stylePart = workbookpart.AddNewPart <WorkbookStylesPart>();
stylePart.Stylesheet = GenerateStylesheet();
stylePart.Stylesheet.Save();
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.
GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "mySheet"
};
sheets.Append(sheet);
int i = 0;
CreatHeadXSML(spreadsheetDocument, ref i, worksheetPart);
CreatBody(spreadsheetDocument, ref i, worksheetPart, ohvats.GetValueOrDefault(id), userInfo);
worksheetPart.Worksheet.Save();
spreadsheetDocument.Close();
}
}
private void CreatBody(SpreadsheetDocument spreadsheetDocument, ref int i, WorksheetPart worksheetPart, List <OhvatModel> ohvatModels, Data.Entities.OneCInfo.UserInfo userInfo)
{
SharedStringTablePart shareStringPart = GetSharedStringTablePart(spreadsheetDocument.WorkbookPart);
int j = 0;
foreach (OhvatModel ohvatModel in ohvatModels)
{
ClientInfo clientInfo = _context.Set <ClientInfo>()
.FirstOrDefault(c => c.OneCId.ToString() == ohvatModel.Contragent_ID);
Client client = _context.Set <Client>()
.FirstOrDefault(c => clientInfo != null && c.Id == clientInfo.ClientId);
if (client != null)
{
List <ClientPhone> clientPhones = _context.Set <ClientPhone>()
.Where(c => c.ClientId == client.Id).ToList();
string nameColummn = GetCharOfTabel(4);
InsertSharedStringItem(ohvatModel.Contragent, shareStringPart);
Cell cell = InsertCellInWorksheet(nameColummn, (uint)j + 9, worksheetPart, 4, 30);
cell.CellValue = new CellValue(i.ToString());
cell.DataType = new EnumValue <CellValues>(CellValues.SharedString);
worksheetPart.Worksheet.Save();
i++;
nameColummn = GetCharOfTabel(5);
InsertSharedStringItem(ohvatModel.GR_Contragent, shareStringPart);
cell = InsertCellInWorksheet(nameColummn, (uint)j + 9, worksheetPart, 4, 30);
cell.CellValue = new CellValue(i.ToString());
cell.DataType = new EnumValue <CellValues>(CellValues.SharedString);
worksheetPart.Worksheet.Save();
i++;
string clietType = client.ClientType == ClientTypes.WithoutType ? "Без типа" : client.ClientType == ClientTypes.Small ? "Небольшой" : client.ClientType == ClientTypes.Middle1 ? "Средний 1" :
client.ClientType == ClientTypes.Middle2 ? "Средний 2" : client.ClientType == ClientTypes.Large1 ? "Крупный 1" : client.ClientType == ClientTypes.Large2 ? "Крупный 2" :
client.ClientType == ClientTypes.Large3 ? "Крупный 3" : client.ClientType == ClientTypes.VeryLarge ? "Очень крупный" : "";
nameColummn = GetCharOfTabel(6);
InsertSharedStringItem(clietType, shareStringPart);
cell = InsertCellInWorksheet(nameColummn, (uint)j + 9, worksheetPart, 4, 30);
cell.CellValue = new CellValue(i.ToString());
cell.DataType = new EnumValue <CellValues>(CellValues.SharedString);
worksheetPart.Worksheet.Save();
i++;
string numberOfShipments = client.NumberOfShipments == NumberOfShipments.WithoutType ? "Не указан" : client.NumberOfShipments == NumberOfShipments.OnePerMonth ? "1 раз в месяц" : client.NumberOfShipments == NumberOfShipments.OnePerTwoWeek ? "1 раз в 2 недели" :
client.NumberOfShipments == NumberOfShipments.ThreePerMonth ? "1 раз в неделю" : client.NumberOfShipments == NumberOfShipments.FivePerMonth ? "5 раз в месяц" : client.NumberOfShipments == NumberOfShipments.SixPerMonth ? "6 раз в месяц" :
client.NumberOfShipments == NumberOfShipments.TwoPerWeek ? "2 раза в неделю" : "";
nameColummn = GetCharOfTabel(7);
InsertSharedStringItem(clietType, shareStringPart);
cell = InsertCellInWorksheet(numberOfShipments, (uint)j + 9, worksheetPart, 4, 30);
cell.CellValue = new CellValue(i.ToString());
cell.DataType = new EnumValue <CellValues>(CellValues.SharedString);
worksheetPart.Worksheet.Save();
i++;
nameColummn = GetCharOfTabel(8);
InsertSharedStringItem(ohvatModel.KolNom, shareStringPart);
cell = InsertCellInWorksheet(nameColummn, (uint)j + 9, worksheetPart, (uint)GetIdStyles(Convert.ToDouble(ohvatModel.KolNom), Convert.ToDouble(ohvatModel.KolNomPred)), 30);
cell.CellValue = new CellValue(i.ToString());
cell.DataType = new EnumValue <CellValues>(CellValues.SharedString);
worksheetPart.Worksheet.Save();
i++;
nameColummn = GetCharOfTabel(9);
InsertSharedStringItem(ohvatModel.KolNom_5, shareStringPart);
cell = InsertCellInWorksheet(nameColummn, (uint)j + 9, worksheetPart, (uint)GetIdStyles(Convert.ToDouble(ohvatModel.KolNom_5), Convert.ToDouble(ohvatModel.KolNom_5Pred)), 30);
cell.CellValue = new CellValue(i.ToString());
cell.DataType = new EnumValue <CellValues>(CellValues.SharedString);
worksheetPart.Worksheet.Save();
i++;
nameColummn = GetCharOfTabel(10);
InsertSharedStringItem(ohvatModel.KolNomPred, shareStringPart);
cell = InsertCellInWorksheet(nameColummn, (uint)j + 9, worksheetPart, 12, 30);
cell.CellValue = new CellValue(i.ToString());
cell.DataType = new EnumValue <CellValues>(CellValues.SharedString);
worksheetPart.Worksheet.Save();
i++;
nameColummn = GetCharOfTabel(11);
InsertSharedStringItem(ohvatModel.KolNom_5Pred, shareStringPart);
cell = InsertCellInWorksheet(nameColummn, (uint)j + 9, worksheetPart, 12, 30);
cell.CellValue = new CellValue(i.ToString());
cell.DataType = new EnumValue <CellValues>(CellValues.SharedString);
worksheetPart.Worksheet.Save();
i++;
nameColummn = GetCharOfTabel(12);
InsertSharedStringItem(ohvatModel.Summa, shareStringPart);
cell = InsertCellInWorksheet(nameColummn, (uint)j + 9, worksheetPart, (uint)GetIdStyles(Convert.ToDouble(ohvatModel.Summa.Replace('.', ',')), Convert.ToDouble(ohvatModel.SummaPred.Replace('.', ','))), 30);
cell.CellValue = new CellValue(i.ToString());
cell.DataType = new EnumValue <CellValues>(CellValues.SharedString);
worksheetPart.Worksheet.Save();
i++;
nameColummn = GetCharOfTabel(13);
InsertSharedStringItem(ohvatModel.Summa_5, shareStringPart);
cell = InsertCellInWorksheet(nameColummn, (uint)j + 9, worksheetPart, (uint)GetIdStyles(Convert.ToDouble(ohvatModel.Summa_5.Replace('.', ',')), Convert.ToDouble(ohvatModel.Summa_5Pred.Replace('.', ','))), 30);
cell.CellValue = new CellValue(i.ToString());
cell.DataType = new EnumValue <CellValues>(CellValues.SharedString);
worksheetPart.Worksheet.Save();
i++;
nameColummn = GetCharOfTabel(14);
InsertSharedStringItem(ohvatModel.SummaPred, shareStringPart);
cell = InsertCellInWorksheet(nameColummn, (uint)j + 9, worksheetPart, 12, 30);
cell.CellValue = new CellValue(i.ToString());
cell.DataType = new EnumValue <CellValues>(CellValues.SharedString);
worksheetPart.Worksheet.Save();
i++;
nameColummn = GetCharOfTabel(15);
InsertSharedStringItem(ohvatModel.Summa_5Pred, shareStringPart);
cell = InsertCellInWorksheet(nameColummn, (uint)j + 9, worksheetPart, 12, 30);
cell.CellValue = new CellValue(i.ToString());
cell.DataType = new EnumValue <CellValues>(CellValues.SharedString);
worksheetPart.Worksheet.Save();
i++;
j++;
}
}
}