public void InsertAddressBase()
{
using (var context = new UNSModel())
{
var y = new AddressBase()
{
ID = Guid.NewGuid(), GUID = Guid.NewGuid()
};
var y1 = new AddressBase()
{
ID = Guid.NewGuid(),
GUID = Guid.NewGuid(),
PREV = new List <AddressBase>()
{
y
},
Code = new AddressCode()
{
IFNSFL = "s", OKATO = "x", OKTMO = "z"
},
RootStatus = new AddressStatus()
{
LIVESTATUS = true, OPERSTATUS = 1, REGIONCODE = "77"
}
};
context.Set <AddressBase>().Add(y);
context.Set <AddressBase>().Add(y1);
context.SaveChanges();
context.Set <AddressBase>().Remove(y);
context.Set <AddressBase>().Remove(y1);
context.SaveChanges();
}
}
public void BTIWallType()
{
using (UNSModel context = new UNSModel())
{
EnableCalculations(false);
List <IntegraDUExcel> integraDUs = ReestrSheet();
integraDUs.Remove(integraDUs[0]);
var hasUNOMs =
(from integraDU in integraDUs
join stage in context.IntegraDU
on integraDU.UNIU equals stage.UNIU
select new { integraDU, stage }).ToList();
foreach (var destRow in hasUNOMs)
{
try
{
destRow.integraDU.BTIwallType = destRow.stage.BTIWallType;
destRow.integraDU.BTIdestination = destRow.stage.BTITarget;
}
catch (Exception)
{ }
}
EnableCalculations(true);
}
}
public void InsertInstallationPlaceDU()
{
using (var context = new UNSModel())
{
var ip = new InstallationPlaceBuilding()
{
TechProject = new TechProject()
{
},
Location = new LocationOneAddress()
{
Address = new AddressCached()
{
AddressID = Guid.NewGuid(),
AddressGUID = Guid.NewGuid(),
AddressPARENTGUID = Guid.NewGuid(),
STARTDATE = DateTime.Now,
ENDDATE = DateTime.Now,
FullAddress = "sssdf",
ItemAddress = "dhdfhdfgh",
ItemCategory = "sdfsdfsd",
ItemType = "sfdsfsdf"
},
AdministrativeArea = context.AdmAreas.FirstOrDefault(),
ClarificationOfLocation = "ddddddddddddddddddddddd",
},
InstallationPlaceType = "sfsfdsf",
RegistrationNumber = "23423423"
};
context.Set <InstallationPlaceBuilding>().Add(ip);
context.SaveChanges();
context.Set <InstallationPlaceBuilding>().Remove(ip);
context.SaveChanges();
}
}
internal void ReplaceOwnerToPrim()
{
using (UNSModel _context = new UNSModel())
{
EnableCalculations(false);
List <IntegraDUExcel> integraDUs = ReestrSheet();
integraDUs.Remove(integraDUs[0]);
List <IntegraDUExcel> hasUNOMs =
(from integraDU in integraDUs
where integraDU.IntoProductionDate == null &&
integraDU.CoordinationDate == null &&
integraDU.LetterOutNumber == null &&
integraDU.HouseOwner != null &&
integraDU.Contacts == null
select integraDU).ToList();
foreach (IntegraDUExcel destRow in hasUNOMs)
{
int _unom = int.Parse(destRow.UNOM.ToString());
if (destRow.Comment == null)
{
destRow.Comment = destRow.HouseOwner;
}
else
{
destRow.Comment = string.Join("; ", destRow.Comment, destRow.HouseOwner);
}
destRow.HouseOwner = null;
}
EnableCalculations(true);
}
}
public void DU_K_UD()
{
using (var context = new UNSModel())
{
/*var t = new DU_K_UD();
* context.Du_K_UD.Add(t);
* context.SaveChanges();*/
}
}
public void InsertAddressAOsAll()
{
using (var context = new UNSModel())
{
var mapper = new MapperConfiguration(cfg => cfg.AddProfile <Fias_MapProfile>()).CreateMapper();
var sel = context.AddressObjects.Take(100).ToList().ConvertAll(s => mapper.Map <AddressObject, AddressAO>(s));
context.AddressAOs.AddRange(sel);
context.SaveChanges();
}
}
internal void UpdateByDB()
{
using (UNSModel context = new UNSModel())
{
EnableCalculations(false);
List <IntegraDUExcel> integraDUs = ReestrSheet();
integraDUs.Remove(integraDUs[0]);
var hasUNOMs =
(from integraDU in integraDUs
join stage in context.IntegraDUStages
on integraDU.UNIU equals stage.UNIU
where integraDU.IntoProductionDate == null
//&& integraDU.CoordinationDate == null
//&& integraDU.LetterOutNumber == null
&& integraDU.HouseOwner == null
select new { integraDU, stage.UNOM }).ToList();
foreach (var destRow in hasUNOMs)
{
try
{
int unom = int.Parse(destRow.UNOM.ToString());
List <AddressOwnerFind_Result> AddressOwners = context.AddressOwnerFind(unom).ToList();
if (AddressOwners.Any())
{
AddressOwnerFind_Result AddressOwner = AddressOwners.Single();
if (AddressOwner.ShortName != null)
{
destRow.integraDU.HouseOwner = AddressOwner.ShortName;
}
if (AddressOwner.ChiefName != null)
{
destRow.integraDU.Director = AddressOwner.ChiefName;
}
if (AddressOwner.ChiefPosition != null)
{
destRow.integraDU.DirectorPosition = AddressOwner.ChiefPosition;
}
if (AddressOwner.Contacts != null)
{
destRow.integraDU.Contacts = AddressOwner.Contacts;
}
}
}
catch (FormatException)
{ }
catch (InvalidOperationException ie)
{ Logger.Logger.Error(ie.Message); }
catch (Exception exception)
{ Logger.Logger.Error(exception.Message); }
}
EnableCalculations(true);
}
}
public void InsertAddressRoom()
{
using (var context = new UNSModel())
{
var y = new AddressRoom()
{
ID = Guid.NewGuid(), GUID = Guid.NewGuid(),
Code = new AddressCode()
{
IFNSFL = "s", OKATO = "x", OKTMO = "z"
},
RootStatus = new AddressStatus()
{
LIVESTATUS = true, OPERSTATUS = 1, REGIONCODE = "77"
},
CADNUM = "sdfsdfs",
FLATNUMBER = "sfsdfsd",
ENDDATE = DateTime.Now,
STARTDATE = DateTime.Now,
FLATTYPE = 1
};
var y1 = new AddressRoom()
{
ID = Guid.NewGuid(),
GUID = Guid.NewGuid(),
PREV = new List <AddressBase>()
{
y
},
Code = new AddressCode()
{
IFNSFL = "s", OKATO = "x", OKTMO = "z"
},
RootStatus = new AddressStatus()
{
LIVESTATUS = true, OPERSTATUS = 1, REGIONCODE = "77"
},
CADNUM = "sdfsdfs",
FLATNUMBER = "sfsdfsd",
ENDDATE = DateTime.Now,
STARTDATE = DateTime.Now,
FLATTYPE = 1
};
context.Set <AddressRoom>().Add(y);
context.Set <AddressRoom>().Add(y1);
context.SaveChanges();
context.Set <AddressRoom>().Remove(y);
context.Set <AddressRoom>().Remove(y1);
context.SaveChanges();
}
}
public void InsertDocument()
{
using (var context = new UNSModel())
{
var y = new Document()
{
DocumentName = "sfsdfsdf"
};
context.Set <Document>().Add(y);
context.SaveChanges();
context.Set <Document>().Remove(y);
context.SaveChanges();
}
}
public void InsertSimpLetters()
{
using (var context = new UNSModel())
{
var y = new SimplifiedLetter()
{
DocumentName = "sfsdfsdf", OutgoingDate = DateTime.Now, Recipient = "ssdfsdf"
};
context.Set <SimplifiedLetter>().Add(y);
context.SaveChanges();
context.Set <SimplifiedLetter>().Remove(y);
context.SaveChanges();
}
}
public void InsertAddressCached()
{
using (var context = new UNSModel())
{
var y = new AddressCached()
{
ItemAddress = "sfsdfsdf", ItemCategory = "xgdfgdfgdF", STARTDATE = DateTime.Now, ENDDATE = DateTime.Now, FullAddress = "zdfzfa", ItemType = "asdads", AddressGUID = Guid.NewGuid(), AddressPARENTGUID = Guid.NewGuid(), AddressID = Guid.NewGuid()
};
context.Set <AddressCached>().Add(y);
context.SaveChanges();
context.Set <AddressCached>().Remove(y);
context.SaveChanges();
}
}
public void InsertAddressStatus()
{
using (var context = new UNSModel())
{
var y = new AddressStatus()
{
LIVESTATUS = true, OPERSTATUS = 1, REGIONCODE = "77"
};
context.Set <AddressStatus>().Add(y);
context.SaveChanges();
context.Set <AddressStatus>().Remove(y);
context.SaveChanges();
}
}
public void InsertAddressCode()
{
using (var context = new UNSModel())
{
var y = new AddressCode()
{
IFNSFL = "s", OKATO = "x", OKTMO = "z"
};
context.Set <AddressCode>().Add(y);
context.SaveChanges();
context.Set <AddressCode>().Remove(y);
context.SaveChanges();
}
}
public void InsertPersons()
{
using (var context = new UNSModel(@"data source=BUSHMAKIN;initial catalog=UNS;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework"))
{
var y = new Person()
{
Family = "dfgdfgd", Patronymic = "sdfsdfsdfsdf ", Name = "sfsdfsdf"
};
context.Persons.Add(y);
context.SaveChanges();
context.Persons.Remove(y);
context.SaveChanges();
}
}
public void CreateAllAkts()
{
using (var context = new UNSModel("data source=BUSHMAKIN;initial catalog=UNS;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework"))
{
context.Database.CommandTimeout = 180;
var integraDUExcelLayouts = context.DUTechnicalCertificates.Where(w => w.Stage == "18006").OrderBy(o => o.Number).ToList();
//integraDUExcelLayouts.Add(integraDUExcelLayoutsLocal.Where(w => w.DUType == "ДУ-К-Д").Take(10).OrderByDescending(o => o.Number).FirstOrDefault());
//integraDUExcelLayouts.Add(integraDUExcelLayoutsLocal.Where(w => w.DUType == "ДУ-К-УД").Take(10).OrderByDescending(o => o.Number).FirstOrDefault());
// integraDUExcelLayouts.Add(integraDUExcelLayoutsLocal.Where(w => w.DUType == "ДУ-К-С").Take(10).OrderByDescending(o => o.Number).FirstOrDefault());
//integraDUExcelLayouts.Add(integraDUExcelLayoutsLocal.Where(w => w.DUType == "ДУ-К-У").Take(10).OrderByDescending(o => o.Number).FirstOrDefault());
// integraDUExcelLayouts.Add(integraDUExcelLayoutsLocal.Where(w => w.DUType == "ДУ-М-УД").Take(10).OrderByDescending(o => o.Number).FirstOrDefault());
// integraDUExcelLayouts.Add(integraDUExcelLayoutsLocal.Where(w => w.DUType == "ДУ-М-У").Take(10).OrderByDescending(o => o.Number).FirstOrDefault());
(new Akt_Word_Operator()).Create(integraDUExcelLayouts);
}
}
public void InsertTechProject()
{
using (var context = new UNSModel())
{
var y = new TechProject()
{
ProjectCode = Guid.NewGuid().ToString(),
ProjectDate = DateTime.Parse("2015-01-01"),
DocumentName = "Проект домового указателя \"ДУ\"",
Content = new List <Document>()
};
context.Set <TechProject>().Add(y);
context.SaveChanges();
context.Set <TechProject>().Remove(y);
context.SaveChanges();
}
}
public void Print()
{
using (var context = new UNSModel())
{
context.Database.CommandTimeout = 180;
//var integraDUExcelLayouts = context.IntegraDU.Where(w => w.Stage == "18006").OrderBy(o => o.Number).Select(s=>(PassportContent)s).ToList();
var days = new List <DateTime>()
{
DateTime.Parse("2019-08-01"),
DateTime.Parse("2019-08-02"),
DateTime.Parse("2019-08-05"),
DateTime.Parse("2019-08-06"),
DateTime.Parse("2019-08-07"),
DateTime.Parse("2019-08-08"),
DateTime.Parse("2019-08-09")
};
//new Passport_Word_Operator().Create(integraDUExcelLayouts);
}
}
/// <summary>
/// Создание технических паспортов в основной папке
/// </summary>
internal void CreatePassports()
{
using (var context = new UNSModel())
{
var UNIUs = SelectByPattern();
var passportOperator = new Passport_Word_Operator();
var newPassportContents = new List <DUTechnicalCertificate>();
var idu = context.IntegraDUStages.Where(w => UNIUs.Contains(w.UNIU)).ToList().Select(s => Mapper.Map <IntegraDUStages, DUTechnicalCertificate>(s));
foreach (var UNIU in UNIUs)
{
var newPassportContent = idu.Where(w => w.UNIU == UNIU).FirstOrDefault();
newPassportContent.DateManufacture = DateTime.Today;
newPassportContent.OutDate = DateTime.Today;
newPassportContents.Add(newPassportContent);
}
passportOperator.Create(newPassportContents);
context.DUTechnicalCertificates.AddRange(newPassportContents);
context.SaveChanges();
}
}
public void InsertAddressBasePrevNext()
{
using (var context = new UNSModel())
{
AddressBase prev;
AddressBase next;
prev = new AddressBase
{
ID = Guid.NewGuid(),
GUID = Guid.NewGuid(), CADNUM = "PREV"
};
next = new AddressBase()
{
ID = Guid.NewGuid(),
GUID = Guid.NewGuid(),
PREV = new List <AddressBase>()
{
prev
},
CADNUM = "NEXT"
};
prev.NEXT = new List <AddressBase>()
{
next
};
context.AddressBases.Add(prev);
context.AddressBases.Add(next);
context.SaveChanges();
var t = prev.NEXT.Contains(next);
var t1 = next.PREV.Contains(prev);
if (!(t || t1))
{
throw new Exception("ssfsfsd");
}
context.AddressBases.Remove(prev);
context.AddressBases.Remove(next);
context.SaveChanges();
}
}
public void InsertDuLB_D()
{
using (var context = new UNSModel())
{
var ip = new DuLB_UD()
{
TechProject = new TechProject()
{
},
Location = new LocationOneAddress()
{
Address = new AddressCached()
{
AddressID = Guid.NewGuid(),
AddressGUID = Guid.NewGuid(),
AddressPARENTGUID = Guid.NewGuid(),
STARTDATE = DateTime.Now,
ENDDATE = DateTime.Now,
FullAddress = "sssdf",
ItemAddress = "dhdfhdfgh",
ItemCategory = "sdfsdfsd",
ItemType = "sfdsfsdf"
},
AdministrativeArea = context.AdmAreas.FirstOrDefault(),
ClarificationOfLocation = "ddddddddddddddddddddddd",
},
InstallationPlaceType = "sfsfdsf",
RegistrationNumber = "23423423",
ContentHouse = new LightBoxElement()
{
ContentText = "sdfsdfsdfsdf", Width = 100, Height = 666, MaxPower = 50
}
};
context.Set <DuLB_UD>().Add(ip);
context.SaveChanges();
context.Set <DuLB_UD>().Remove(ip);
context.SaveChanges();
}
}
public void UpdateStatusInstallation()
{
using (UNSModel context = new UNSModel())
{
EnableCalculations(false);
List <IntegraDUExcel> integraDUs = ReestrSheet();
integraDUs.Remove(integraDUs[0]);
var hasUNOMs =
(from integraDU in integraDUs
join stage in context.IntegraDU
on integraDU.UNIU equals stage.UNIU
select new { integraDU, stage }).ToList();
foreach (var destRow in hasUNOMs)
{
try
{
destRow.integraDU.InstallStatus = destRow.stage.InstallationStatus;
}
catch (Exception)
{ }
}
EnableCalculations(true);
}
}
public Data_Organization_9773_OwnerRawAddress_MapProfile(UNSModel context) : this()
{
Context = context;
}
internal void CreateLetters()
{
var currentBookName = Path.GetFileNameWithoutExtension(Globals.ThisAddIn.Application.ActiveWorkbook.Name);
try
{
Excel.Range firm = Globals.ThisAddIn.Application.Selection as Excel.Range;
using (var context = new UNSModel())
{
var fullselected = (from row in ReestrSheet()
join unsrow in context.IntegraDUStages
on row.UNIU equals unsrow.UNIU
where row.HouseOwner != null &&
row.HouseOwner.ToString() == firm.Value2 &&
row.LetterOutData == null &&
row.LetterOutNumber == null
orderby row.AddressObject, row.AddressHouse
select new { row, unsrow }).ToList();
var unsselected = fullselected.Select(s => s.unsrow);
var selected = fullselected.Select(s => s.row);
if (selected.Any())
{
var currentDateLetterCount = context.SimplifiedLetters.Where(w => w.OutgoingDate == DateTime.Today).Count();
currentDateLetterCount++;
var selectedFirst = selected.FirstOrDefault();
var newLeller = new SimplifiedLetter()
{
OutgoingNumber = string.Join("/", DateTime.Now.ToString("yyyyMMdd"), currentDateLetterCount.ToString()),
OutgoingDate = DateTime.Now.Date,
Recipient = selectedFirst.HouseOwner?.ToString(),
RecipientDirectorName = selectedFirst.Director?.ToString(),
RecipientDirectorPosition = selectedFirst.DirectorPosition?.ToString(),
IntegraDUStages = unsselected.ToList()
};
Excel.Workbook newWB = Globals.ThisAddIn.Application.Workbooks
.Add(LettersTemplate.FullName);
for (int n = 0; n <= selected.Count() - 1; n++)
{
Excel.Range destRow = newWB.Sheets[1].Cells.Rows[n + 2];
destRow.Cells[1, 1].Value = n + 1;
destRow.Cells[1, 2].Value = selected.ElementAt(n).AddressObject;
destRow.Cells[1, 3].Value = selected.ElementAt(n).AddressHouse;
destRow.Cells[1, 4].Value = selected.ElementAt(n).UNOM;
destRow.Cells[1, 5].Value = selected.ElementAt(n).UNIU;
if (selected.ElementAt(n).LetterOutNumber == null)
{
selected.ElementAt(n).LetterOutNumber = newLeller.OutgoingNumber;
}
if (selected.ElementAt(n).LetterOutData == null)
{
selected.ElementAt(n).LetterOutData = newLeller.OutgoingDate;
}
}
var newpath = Path.Combine(rootletters.FullName, currentBookName, "На отправку", firm.Value2.Replace(@"\\", ":").Replace(@"""", ""));//+ ".xlsx"
newWB.SaveAs(newpath, Excel.XlFileFormat.xlOpenXMLWorkbook);
newWB.Close();
var oper = new QueryToHouseOwner_Word_Operator();
oper.Create(newpath,
newLeller.Recipient,
newLeller.RecipientDirectorPosition,
newLeller.RecipientDirectorName,
newLeller.OutgoingNumber,
newLeller.OutgoingDate
);
context.SimplifiedLetters.Add(newLeller);
context.SaveChanges();
}
}
}
catch (Exception innere)
{ Logger.Logger.Error(innere.Message); }
finally
{
EnableCalculations(true);
}
}
public Operator(string сonnectionString) : this()
{
ConnectionString = сonnectionString;
ContextMain = new JSONContext(сonnectionString);
ContexUNS = new UNSModel();
}
public Organization_v1_1_Organization_MapProfile(UNS.Models.UNSModel context) : this()
{
ContexUNS = context;
}
public Data_Organization_28509_Organization_MapProfile(UNSModel context) : this()
{
Context = context;
}
/// <summary>
///
/// </summary>
public void UpdateOrganizationsByDomMosRu()
{
using (JSONContext jscontext = new JSONContext())
using (UNSModel unscontext = new UNSModel())
{
Regex reHref = new Regex("&#\\d+;");
List <UPRsite> inserted = (from upr in jscontext.UPRsites
select upr).ToList();
foreach (UPRsite uprsite in inserted)
{
uprsite.InfTableRows.ToList();
var ogrn = FirstVal(uprsite.InfTableRows.Where(p => p.Name.Trim() == "Основной государственный регистрационный номер (ОГРН):" ? true : false).Select(p => p.Value));
var inn = FirstVal(uprsite.InfTableRows.Where(p => p.Name != null && p.Name.Trim() == "Идентификационный номер налогоплательщика(ИНН):" ? true : false).Select(p => p.Value));
var shname = FirstVal(uprsite.InfTableRows.Where(p => p.Name != null && p.Name.Trim() == "Сокращенное наименование" ? true : false).Select(p => p.Value));
var uaddress = FirstVal(uprsite.InfTableRows.Where(p => p.Name != null && p.Name.Trim() == "Место государственной регистрации юридического лица(место нахождения юридического лица):" ? true : false).Select(p => p.Value));
var form = FirstVal(uprsite.InfTableRows.Where(p => p.Name != null && p.Name.Trim() == "Организационно-правовая форма" ? true : false).Select(p => p.Value));
var orgType = unscontext.OrganizationTypes.Where(ot => ot.FullTypeName.Trim() == form ? true : false).FirstOrDefault();
var fio = FirstVal(uprsite.InfTableRows.Where(p => p.Name.Trim() == "ФИО руководителя (председателя)" ? true : false).Select(p => p.Value));
Organization organization = new Organization()
{
Id = uprsite.ID,
FullName = uprsite.Name,
OGRN = ogrn,
INN = inn,
ShortName = shname,
UrAddress = uaddress,
OrganizationType = orgType
};
//organization.DirectorPosition.Add(dirpos);
if (organization.OGRN != null)
{
unscontext.Organizations.AddOrUpdate(p => new { p.OGRN }, organization);
}
else
{
unscontext.Organizations.AddOrUpdate(p => new { p.Id }, organization);
}
jscontext.SaveChanges();
if (fio.Any())
{
var dirpos = (new AccountantGeneralPosition()
{
PositionType = new PersonPositionType()
{
PositionType = ""
},
Person = new Person()
{
Family = fio, Id = Guid.NewGuid()
},
InstDocument = new Document()
{
DocumentName = ""
},
//Organization_Id = organization.Id
});
//unscontext.AccountantGeneralPositions.AddOrUpdate(p => p.Organization_Id, dirpos);
jscontext.SaveChanges();
}
}
}
}
