public void TestOfficialXlsx()
{
ReportCardModel model = new ReportCardModel()
{
SettingParameters = new SettingParameters()
{
TabelNumber = "7751-00-548", Year = 2021, IdDepartment = 8,
Type = new Type()
{
IdType = 1, NameType = "Полный"
},
View = new View()
{
IdView = 0, NameView = "Первичный"
},
Mouth = new Mouth()
{
NumberMouthString = "09", NameMouth = "Сентябрь", NumberMouth = 9
}
}
};
SqlConnectionType sql = new SqlConnectionType();
SelectImns selectFrames = new SelectImns();
XmlReadOrWrite xml = new XmlReadOrWrite();
SelectSql select = new SelectSql();
select.SelectCardModelLeader(ref model);
var command = string.Format(selectFrames.UserReportCard, model.SettingParameters.LeaderD.NameDepartment, $"{model.SettingParameters.Year}-{model.SettingParameters.Mouth.NumberMouthString}-01");
var userReportCard = sql.XmlString("Data Source=i7751-app020;Initial Catalog=imns51;Integrated Security=True;MultipleActiveResultSets=True", command);
userReportCard = string.Concat("<SettingParameters>", userReportCard, "</SettingParameters>");
model.SettingParameters.UsersReportCard = ((SettingParameters)xml.ReadXmlText(userReportCard, typeof(SettingParameters))).UsersReportCard;
foreach (var usersReportCard in model.SettingParameters.UsersReportCard)
{
var commandVacation = string.Format(selectFrames.ItemVacationNew, usersReportCard.Tab_num, $"{model.SettingParameters.Year}", $"{model.SettingParameters.Year}");
var userVacation = sql.XmlString("Data Source=i7751-app020;Initial Catalog=imns51;Integrated Security=True;MultipleActiveResultSets=True", commandVacation);
userVacation = string.Concat("<UsersReportCard>", userVacation, "</UsersReportCard>");
usersReportCard.ItemVacation = ((UsersReportCard)xml.ReadXmlText(userVacation, typeof(UsersReportCard))).ItemVacation;
var commandDisability = string.Format(selectFrames.Disability, usersReportCard.Tab_num, $"{model.SettingParameters.Year}");
var userDisability = sql.XmlString("Data Source=i7751-app020;Initial Catalog=imns51;Integrated Security=True;MultipleActiveResultSets=True", commandDisability);
userDisability = string.Concat("<UsersReportCard>", userDisability, "</UsersReportCard>");
usersReportCard.Disability = ((UsersReportCard)xml.ReadXmlText(userDisability, typeof(UsersReportCard))).Disability;
var commandBusiness = string.Format(selectFrames.Business, usersReportCard.Tab_num, $"{model.SettingParameters.Year}");
var userBusiness = sql.XmlString("Data Source=i7751-app020;Initial Catalog=imns51;Integrated Security=True;MultipleActiveResultSets=True", commandBusiness);
userBusiness = string.Concat("<UsersReportCard>", userBusiness, "</UsersReportCard>");
usersReportCard.Business = ((UsersReportCard)xml.ReadXmlText(userBusiness, typeof(UsersReportCard))).Business;
}
ReportCard report = new ReportCard();
report.CreateDocument("D:\\Testing\\", model);
}
public void MemoGen()
{
MemoReport memo = new MemoReport();
// memo.CreateDocument("D:\\Testing\\",null,null);
ModelMemoReport memoReport = new ModelMemoReport()
{
SelectParameterDocument = new SelectParameterDocument()
{
IdUser = 33, NameDocument = "RR", NumberDocument = 4, TabelNumber = "7751-00-099", TypeDocument = 1
}
};
SelectSql select = new SelectSql();
SelectImns selectFrames = new SelectImns();
SqlConnectionType sql = new SqlConnectionType();
XmlReadOrWrite xml = new XmlReadOrWrite();
select.SelectMemoReport(ref memoReport);
var commandOrders = string.Format(selectFrames.LastOrder, memoReport.UserDepartment.SmallTabelNumber);
var userOrder = sql.XmlString("Data Source=i7751-app020;Initial Catalog=imns51;Integrated Security=True;MultipleActiveResultSets=True", commandOrders);
if (userOrder != null)
{
userOrder = string.Concat("<Orders>", userOrder, "</Orders>");
memoReport.UserDepartment.Orders = ((Orders)xml.ReadXmlText(userOrder, typeof(Orders)));
}
memo.CreateDocument("D:\\Testing\\", memoReport);
}
/// <summary>
/// Выполнение процедуры возвращающая xml файл для разбора на web UI
/// </summary>
/// <param name="model">Модель выборки</param>
/// <returns></returns>
public ModelSelect ResultSelectProcedureString <T>(ModelSelect model)
{
try
{
var xml = new XmlReadOrWrite();
SerializeJson serializeJson = new SerializeJson();
var result = Automation.Database.SqlQuery <string>(model.ParameterProcedureWeb.SelectUser,
new SqlParameter(model.ParameterProcedureWeb.ParameterProcedure.Split(',')[0], model.ParametrsSelect.IdCodeProcedure),
new SqlParameter(model.ParameterProcedureWeb.ParameterProcedure.Split(',')[1], string.IsNullOrWhiteSpace(model.ParametrsSelect.Inn) ? (object)DBNull.Value : model.ParametrsSelect.Inn),
new SqlParameter(model.ParameterProcedureWeb.ParameterProcedure.Split(',')[2], model.ParametrsSelect.RegNumber)).ToArray();
var resultServer = (T)xml.ReadXmlText(string.Join("", result), typeof(T));
model.ResultSelectProcedureWeb = serializeJson.JsonLibrary(resultServer);
return(model);
}
catch (Exception e)
{
Loggers.Log4NetLogger.Error(e);
return(null);
}
}
/// <summary>
/// Это для инвентаризации выборка
/// </summary>
/// <param name="logic">Логика</param>
/// <returns></returns>
public string SqlModelInventory <T>(LogicaSelect logic)
{
SerializeJson serializeJson = new SerializeJson();
object result;
string resultModel;
Inventory.Database.CommandTimeout = 120000;
if (logic.IsResultXml)
{
var xml = new XmlReadOrWrite();
result = Inventory.Database.SqlQuery <string>(logic.SelectUser).ToArray();
var resultXml = (T)xml.ReadXmlText(string.Join("", (string[])result), typeof(T));
resultModel = serializeJson.JsonLibrary(resultXml, "dd.MM.yyyy HH:mm");
}
else
{
result = Inventory.Database.SqlQuery <T>(logic.SelectUser).ToList();
dynamic expand = new ExpandoObject();
AddProperty(expand, typeof(T).Name, result);
resultModel = serializeJson.JsonLibrary(expand, "dd.MM.yyyy HH:mm");
}
return(resultModel);
}
/// <summary>
/// Выполнение запроса и конвертация его в JSON
/// </summary>
/// <param name="sqlSelect">Выборка пользовательская из таблицы LogicsSelectAutomation</param>
/// <returns></returns>
public string SqlModelAutomation <T>(SelectParametrSheme.LogicsSelectAutomation sqlSelect)
{
SerializeJson serializeJson = new SerializeJson();
object result;
string resultModel;
Automation.Database.CommandTimeout = 120000;
if (sqlSelect.IsResultXml)
{
var xml = new XmlReadOrWrite();
result = Automation.Database.SqlQuery <string>(sqlSelect.SelectUser).ToArray();
var resultXml = (T)xml.ReadXmlText(string.Join("", (string[])result), typeof(T));
resultModel = serializeJson.JsonLibrary(resultXml, "dd.MM.yyyy HH:mm");
}
else
{
result = Automation.Database.SqlQuery <T>(sqlSelect.SelectUser).ToList();
dynamic expand = new ExpandoObject();
AddProperty(expand, typeof(T).Name, result);
resultModel = serializeJson.JsonLibrary(expand, "dd.MM.yyyy HH:mm");
}
return(resultModel);
}
/// <summary>
/// Получение данных для клиента на парсинг значений
/// </summary>
/// <param name="idTemplate">Уникальные номера шаблонов</param>
/// <returns></returns>
public List <LibaryXMLAutoModelXmlSql.PreCheck.Ifns51.FromAis.SrvToLoad> LoadModelPreCheck(string idTemplate)
{
try
{
var xml = new XmlReadOrWrite();
var logicModel = Automation.LogicsSelectAutomations.FirstOrDefault(logic => logic.Id == 5);
if (logicModel != null)
{
var sqlSelectModel = logicModel.SelectUser.Replace(logicModel.SelectedParametr.Split(',')[0], idTemplate);
var result = Automation.Database.SqlQuery <string>(sqlSelectModel)?.ToArray();
if (result?[0] != null)
{
var resultServer = ((ArrayOfSrvToLoad)xml.ReadXmlText(string.Join("", result), typeof(ArrayOfSrvToLoad))).SrvToLoad.ToList();
return(resultServer);
}
}
}
catch (Exception ex)
{
Loggers.Log4NetLogger.Error(ex);
}
return(null);
}
/// <summary>
/// Собираем модель карточки (полная)
/// </summary>
/// <param name="inn">ИНН</param>
/// <param name="year">Год выгрузки</param>
/// <returns></returns>
public CardFaceUl CardUi(string inn, int year)
{
try
{
var xml = new XmlReadOrWrite();
var cardFace = new CardFaceUl()
{
Card = new Card()
};
Automation.Database.CommandTimeout = 120000;
var logicModel = Automation.LogicsSelectAutomations.FirstOrDefault(logic => logic.Id == 8);
if (logicModel != null)
{
cardFace.FaceUl = Automation.Database.SqlQuery <FaceUl>(logicModel.SelectUser,
new SqlParameter(logicModel.SelectedParametr.Split(',')[0], inn),
new SqlParameter(logicModel.SelectedParametr.Split(',')[1], 1),
new SqlParameter(logicModel.SelectedParametr.Split(',')[2], year)).FirstOrDefault();
cardFace.BranchUlFace = Automation.Database.SqlQuery <BranchUlFace>(logicModel.SelectUser,
new SqlParameter(logicModel.SelectedParametr.Split(',')[0], inn),
new SqlParameter(logicModel.SelectedParametr.Split(',')[1], 2),
new SqlParameter(logicModel.SelectedParametr.Split(',')[2], year)).ToArray();
cardFace.Card.List1Card = Automation.Database.SqlQuery <List1Card>(logicModel.SelectUser,
new SqlParameter(logicModel.SelectedParametr.Split(',')[0], inn),
new SqlParameter(logicModel.SelectedParametr.Split(',')[1], 3),
new SqlParameter(logicModel.SelectedParametr.Split(',')[2], year)).ToArray();
cardFace.HistoriUlFace = Automation.Database.SqlQuery <HistoriUlFace>(logicModel.SelectUser,
new SqlParameter(logicModel.SelectedParametr.Split(',')[0], inn),
new SqlParameter(logicModel.SelectedParametr.Split(',')[1], 4),
new SqlParameter(logicModel.SelectedParametr.Split(',')[2], year)).ToArray();
cardFace.CashUlFace = Automation.Database.SqlQuery <CashUlFace>(logicModel.SelectUser,
new SqlParameter(logicModel.SelectedParametr.Split(',')[0], inn),
new SqlParameter(logicModel.SelectedParametr.Split(',')[1], 5),
new SqlParameter(logicModel.SelectedParametr.Split(',')[2], year)).ToArray();
cardFace.CashBankSpr = Automation.Database.SqlQuery <CashBankSpr>(logicModel.SelectUser,
new SqlParameter(logicModel.SelectedParametr.Split(',')[0], inn),
new SqlParameter(logicModel.SelectedParametr.Split(',')[1], 6),
new SqlParameter(logicModel.SelectedParametr.Split(',')[2], year)).ToArray();
cardFace.Card.List2Card = Automation.Database.SqlQuery <List2Card>(logicModel.SelectUser,
new SqlParameter(logicModel.SelectedParametr.Split(',')[0], inn),
new SqlParameter(logicModel.SelectedParametr.Split(',')[1], 7),
new SqlParameter(logicModel.SelectedParametr.Split(',')[2], year)).ToArray();
var rukAndUcrh = Automation.Database.SqlQuery <string>(logicModel.SelectUser,
new SqlParameter(logicModel.SelectedParametr.Split(',')[0], inn),
new SqlParameter(logicModel.SelectedParametr.Split(',')[1], 8),
new SqlParameter(logicModel.SelectedParametr.Split(',')[2], year)).ToArray();
if (rukAndUcrh[0] != null)
{
var res = (CardFaceUl)xml.ReadXmlText(string.Join("", rukAndUcrh), typeof(CardFaceUl));
cardFace.RukAndUcrh = res.RukAndUcrh;
}
cardFace.ImZmTrUl = Automation.Database.SqlQuery <ImZmTrUl>(logicModel.SelectUser,
new SqlParameter(logicModel.SelectedParametr.Split(',')[0], inn),
new SqlParameter(logicModel.SelectedParametr.Split(',')[1], 9),
new SqlParameter(logicModel.SelectedParametr.Split(',')[2], year)).ToArray();
cardFace.Active = Automation.Database.SqlQuery <Active>(logicModel.SelectUser,
new SqlParameter(logicModel.SelectedParametr.Split(',')[0], inn),
new SqlParameter(logicModel.SelectedParametr.Split(',')[1], 10),
new SqlParameter(logicModel.SelectedParametr.Split(',')[2], year)).ToArray();
cardFace.Balans = Automation.Database.SqlQuery <Balans>(logicModel.SelectUser,
new SqlParameter(logicModel.SelectedParametr.Split(',')[0], inn),
new SqlParameter(logicModel.SelectedParametr.Split(',')[1], 11),
new SqlParameter(logicModel.SelectedParametr.Split(',')[2], year)).ToArray();
cardFace.Profit = Automation.Database.SqlQuery <Profit>(logicModel.SelectUser,
new SqlParameter(logicModel.SelectedParametr.Split(',')[0], inn),
new SqlParameter(logicModel.SelectedParametr.Split(',')[1], 12),
new SqlParameter(logicModel.SelectedParametr.Split(',')[2], year)).ToArray();
cardFace.Nds = Automation.Database.SqlQuery <Nds>(logicModel.SelectUser,
new SqlParameter(logicModel.SelectedParametr.Split(',')[0], inn),
new SqlParameter(logicModel.SelectedParametr.Split(',')[1], 13),
new SqlParameter(logicModel.SelectedParametr.Split(',')[2], year)).ToArray();
cardFace.FlRukUcrh = Automation.Database.SqlQuery <FlRukUcrh>(logicModel.SelectUser,
new SqlParameter(logicModel.SelectedParametr.Split(',')[0], inn),
new SqlParameter(logicModel.SelectedParametr.Split(',')[1], 14),
new SqlParameter(logicModel.SelectedParametr.Split(',')[2], year)).ToArray();
cardFace.SummaryBankSales = Automation.Database.SqlQuery <SummaryBankSales>(logicModel.SelectUser,
new SqlParameter(logicModel.SelectedParametr.Split(',')[0], inn),
new SqlParameter(logicModel.SelectedParametr.Split(',')[1], 15),
new SqlParameter(logicModel.SelectedParametr.Split(',')[2], year)).ToArray();
cardFace.SummaryBankPurchase = Automation.Database.SqlQuery <SummaryBankPurchase>(logicModel.SelectUser,
new SqlParameter(logicModel.SelectedParametr.Split(',')[0], inn),
new SqlParameter(logicModel.SelectedParametr.Split(',')[1], 16),
new SqlParameter(logicModel.SelectedParametr.Split(',')[2], year)).ToArray();
}
return(cardFace);
}
catch (Exception ex)
{
Loggers.Log4NetLogger.Error(ex);
}
return(null);
}