public static void AllOperatin(string connectionString)
{
string sql = $@"EXEC all_operation";
DirectDataManager ddmAll = new DirectDataManager(sql, connectionString);
ddmAll.ExecuteQuery();
}
public List <ClassifierViewModel> GetInternalOrderModelList(long classifTypeId)
{
var u = new UnitOfWork();
//var classifType = u.ClassifierType.GetActive().FirstOrDefault(w => w.Id == classifTypeId);
var sql = $@"SELECT C.[Id]
, C.[Name]
, C.[Active]
, C.[Code]
, CT.Name AS Type
, CT.RusName AS TypeRus
, C.ClassifierType_Id AS TypeId
, L2.Name AS Level2
, L1.Name AS Level1
FROM [dbo].[Classifier] AS C
LEFT JOIN [ClassifierType] AS CT
ON C.ClassifierType_Id = CT.Id
LEFT JOIN [IOMapping] AS IO
ON C.Id = IO.InternalOrder_Id
LEFT JOIN [Classifier] AS L2
ON IO.Brand_Id = L2.Id
LEFT JOIN [Classifier] AS L1
ON IO.Client_Id = L1.Id
WHERE C.ClassifierType_Id = {classifTypeId} AND C.Deleted = 0
Order by L1.Name, L2.Name, C.[Name]" ;
var ddm = new DirectDataManager(sql);
var list = ddm.ToList <ClassifierViewModel>().ToList();
return(list);
}
/// <summary>
/// Удаление записей из Репорта, которых нет в основной таблицы
/// </summary>
/// <param name="conn"></param>
public static void DeleteEstimate(string connectionString)
{
string sql = $@"BEGIN TRANSACTION;
DELETE mSR
FROM SequoiaReport.dbo.Estimate AS mSR
WHERE mSR.SequoiaKaId NOT IN (
SELECT mSE.Id
FROM Sequoia.dbo.Estimate AS mSE)
COMMIT;";
DirectDataManager ddmCreate = new DirectDataManager(sql, connectionString);
ddmCreate.ExecuteQuery();
}
public static bool ExiststDataBase(string ConnectionString, string TableName)
{
System.Data.Common.DbConnectionStringBuilder builder = new System.Data.Common.DbConnectionStringBuilder();
builder.ConnectionString = ConfigurationManager.ConnectionStrings[ConnectionString].ConnectionString;
string database = builder["Initial Catalog"] as string;
string sql = $@"SELECT TOP 1 TABLE_NAME
FROM information_schema.TABLES
WHERE (TABLE_CATALOG = '{database}') AND (TABLE_NAME = '{TableName}')";
DirectDataManager ddm = new DirectDataManager(sql, ConnectionString);
var result = ddm.SingleObject("TABLE_NAME");
if (result != null)
{
return(true);
}
return(false);
}
public ClassifierViewModel GetInternalOrder(long classifId, string type = "")
{
var u = new UnitOfWork();
var classifType = u.ClassifierType.GetActive().FirstOrDefault(w => w.Name == type);
var sql = $@"SELECT C.[Id]
, C.[Name]
, C.[Active]
, C.[Code]
, CT.Name AS Type
, CT.RusName AS TypeRus
, C.ClassifierType_Id AS TypeId
, L2.Name AS Level2
, L1.Name AS Level1
, L2.Id AS Level2Id
, L1.Id AS Level1Id
FROM [dbo].[Classifier] AS C
LEFT JOIN [ClassifierType] AS CT
ON C.ClassifierType_Id = CT.Id
LEFT JOIN [IOMapping] AS IO
ON C.Id = IO.InternalOrder_Id
LEFT JOIN [Classifier] AS L2
ON IO.Brand_Id = L2.Id
LEFT JOIN [Classifier] AS L1
ON IO.Client_Id = L1.Id
WHERE C.Id = {classifId} AND C.Deleted = 0
Order by L1.Name, L2.Name, C.[Name]" ;
var ddm = new DirectDataManager(sql);
var list = ddm.ToList <ClassifierViewModel>().ToList();
if (list.Count == 0)
{
var newClassifier = new ClassifierViewModel();
newClassifier.Active = true;
newClassifier.TypeId = classifType.Id;
newClassifier.Type = classifType.Name;
newClassifier.TypeRus = classifType.RusName;
list.Add(newClassifier);
}
return(list.Count == 0 ? null : list[0]);
}
/// <summary>
/// Обновление записей в репорте, которые поменялись
/// </summary>
/// <param name="connectionString"></param>
public static void UpdateEstimate(string connectionString)
{
string sql = $@"BEGIN TRANSACTION;
UPDATE mSR
SET
mSR.EstimSum=mSE.EstimSum,
mSR.FactSum=mSE.FactSum,
mSR.TargetSum=mSE.TargetSum
FROM SequoiaReport.dbo.Estimate AS mSR,
Sequoia.dbo.Estimate AS mSE
WHERE mSR.SequoiaKaId=mSE.Id AND (
mSR.EstimSum != mSE.EstimSum OR
mSR.FactSum != mSE.FactSum OR
mSR.TargetSum != mSE.TargetSum)
COMMIT;";
DirectDataManager ddmCreate = new DirectDataManager(sql, connectionString);
ddmCreate.ExecuteQuery();
}
static void Main(string[] args)
{
string connectionStringSequoiaKA = "SequoiaKAContext";
string connectionStringSequioaReport = "SequoiaReportContext";
string sql = $@"SELECT
mEstim.Id,
mEstim.EstimSum,
mEstim.TargetSum,
mEstim.FactSum
--mEstim.Category_Id
FROM
dbo.Estimate AS mEstim
WHERE mEstim.Year_Id=67 AND mEstim.Client_Id=1 AND Category_Id=146";
DirectDataManager ddm = new DirectDataManager(sql, connectionStringSequoiaKA);
var Estimations = ddm.ToList <Estimate>().ToList();
bool ExistsTable = ExiststDataBase(connectionStringSequioaReport, "Estimate");
if (!ExistsTable)
{
string sqlCreateTable = $@" CREATE TABLE [dbo].[Estimate] (
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[SequoiaKaId] [bigint] NOT NULL,
[EstimSum] [decimal](18, 2) NOT NULL,
[TargetSum] [decimal](18, 2) NOT NULL,
[FactSum] [decimal](18, 2) NOT NULL);" ;
DirectDataManager ddmCreate = new DirectDataManager(sqlCreateTable, connectionStringSequioaReport);
ddmCreate.ExecuteQuery();
}
//DeleteEstimate(connectionStringSequioaReport);
//InsertIntoEstimate(connectionStringSequioaReport);
//UpdateEstimate(connectionStringSequioaReport);
AllOperatin(connectionStringSequioaReport);
//Console.ReadKey();
}
/// <summary>
/// Добавление записей, которых еще нет в репорте
/// </summary>
/// <param name="connectionString"></param>
public static void InsertIntoEstimate(string connectionString)
{
string sql = $@"BEGIN TRANSACTION;
INSERT INTO SequoiaReport.dbo.Estimate
([SequoiaKaId],
[EstimSum],
[TargetSum],
[FactSum])
SELECT mSE.Id,
mSE.EstimSum,
mSE.TargetSum,
mSE.FactSum
FROM Sequoia.dbo.Estimate AS mSE
WHERE NOT EXISTS (
SELECT 1
FROM SequoiaReport.dbo.Estimate AS mSR
WHERE mSE.Id=mSR.SequoiaKaId)
COMMIT;";
DirectDataManager ddmCreate = new DirectDataManager(sql, connectionString);
ddmCreate.ExecuteQuery();
}
public List <ClassifierViewModel> GetClassifierModelList(long classifTypeId)
{
var u = new UnitOfWork();
var classifType = u.ClassifierType.GetActive().FirstOrDefault(w => w.Id == classifTypeId);
var sql = $@"SELECT C.[Id]
, C.[Name]
, C.[Active]
, C.[Code]
, C.[Year]
, CT.Name AS Type
, CT.RusName AS TypeRus
, C.ClassifierType_Id AS TypeId
, L2.Name AS Level2
, CASE CT.Name
WHEN 'CurrencyCourse' THEN CAST(C.[Year] AS nvarchar(25))
ELSE L1.Name
END AS Level1
FROM [dbo].[Classifier] AS C
LEFT JOIN [ClassifierType] AS CT
ON C.ClassifierType_Id = CT.Id
LEFT JOIN [Classifier] AS L2
ON C.Parent_Id = L2.Id
LEFT JOIN [Classifier] AS L1
ON L2.Parent_Id = L1.Id
WHERE C.ClassifierType_Id = {classifTypeId} AND C.Deleted = 0
Order by L1.Name, L2.Name, C.[Name]" ;
var ddm = new DirectDataManager(sql);
var list = ddm.ToList <ClassifierViewModel>().ToList();
if (classifType.Name == "CurrencyCourse")
{
list = list.OrderByDescending(o => o.Year).ToList();
}
return(list);
}
public List <ActualReportView> GetActualReport(FilterReport fr)
{
StringBuilder sb = new StringBuilder();
sb.Append($@"WHERE AD.[Month]={fr.month}");
if (fr.spend != 0)
{
sb.Append($"AND Spend_1={fr.spend}");
}
List <ActualReportView> ListReport = null;
var sql = $@"SELECT
AD.Id,
AD.[Year],
AD.[Month],
AD.Client_Id,
AD.Brand_Id,
AD.Spend_Id,
GroupClients.Name As GroupClient,
Clients.Name As Client,
Product1.Name As Product_1,
Product2.Name As Product_2,
Product3.Name As Product_3,
Spend1.Name As Spend_1,
Spend2.Name As Spend_2,
Spend3.Name As Spend_3,
AD.EUser,
AD.EDo,
AD.EDocumentHeaderText,
AD.EPurchaseOrderText,
AD.EOperationName,
AD.EMaterial,
AD.EMaterialDescription,
AD.EPostingDate,
AD.CreatedOn,
AD.ETotalQuantity,
AD.EDocumentDate,
AD.EVendorName,
AD.ETransactionCurrency,
AD.ETransactionCurrencySum,
AD.EIOName,
AD.EIOCode,
AD.EGLName,
AD.EGLCode
FROM
dbo.ActualData AS AD
LEFT JOIN dbo.Classifier AS Clients
ON Clients.Id = AD.Client_Id
LEFT JOIN dbo.Classifier AS GroupClients
ON Clients.Parent_Id = GroupClients.Id
LEFT JOIN dbo.Classifier AS Product3
ON Product3.Id = AD.Brand_Id
LEFT JOIN dbo.Classifier AS Product2
ON Product2.Id = Product3.Parent_Id
LEFT JOIN dbo.Classifier AS Product1
ON Product1.Id = Product2.Parent_Id
LEFT JOIN dbo.Classifier AS Spend3
ON Spend3.Id = AD.Spend_Id
LEFT JOIN dbo.Classifier AS Spend2
ON Spend3.Parent_Id = Spend2.Id
LEFT JOIN dbo.Classifier As Spend1
ON Spend1.Id = Spend2.Parent_Id";
var ddm = new DirectDataManager(sql);
ListReport = ddm.ToList <ActualReportView>().ToList();
for (int i = 0; i < ListReport.Count; i++)
{
ListReport[i].Period = GetDatePerion(ListReport[i].Year, ListReport[i].Month);
}
return(ListReport);
}