Добрый вечер!
Есть форма. на ней в гриде должна выводиться временная таблица. Заполнение временной таблицы выполняется в методе init данной таблицы в Data Sources
PHP код:
public void init()
{
str sqlStr;
ResultSet myResult;
Statement stmt;
Connection con;
container cont, cont2;
Map map = new Map (Types::String, Types::Container);
str getSQLCommand1()
{
DataAreaId dataAreaId;
QueryBuildDataSource qbds;
;
breakpoint;
dataAreaId = CompanyInfo::find().DataAreaId;
sqlStr=@"
DECLARE @da NVARCHAR(MAX) = '"+dataAreaId+@"'
DECLARE @STYPE INT = "+int2str(enum2int(SalesType::Sales))+@"
DECLARE @JTYPE INT = "+int2str(enum2int(InventJournalType::Transfer))+@"
DECLARE @Transformers INT = "+int2str(enum2int(TypeOfLinesales_ZTR::Transformers))+@"
DECLARE @TransformersEquipment INT = "+int2str(enum2int(TypeOfLinesales_ZTR::TransformersEquipment))+@"
DECLARE @RepairTransformers INT = "+int2str(enum2int(TypeOfLinesales_ZTR::RepairTransformers))+@"
DECLARE @SparePartsServiceCenter INT = "+int2str(enum2int(TypeOfLinesales_ZTR::SparePartsServiceCenter))+@"
DECLARE @DeliveryClaims INT = "+int2str(enum2int(TypeOfLinesales_ZTR::DeliveryClaims))+@"
DECLARE @ComplexProjects INT = "+int2str(enum2int(TypeOfLinesales_ZTR::ComplexProjects))+@"
DECLARE @SpareParts INT = "+int2str(enum2int(TypeOfLinesales_ZTR::SpareParts))+@"
IF OBJECT_ID('tempdb..#ijtrans') IS NOT NULL
DROP TABLE #ijtrans -- Дропнуть, если есть...
IF OBJECT_ID('tempdb..#ijtrans2') IS NOT NULL
DROP TABLE #ijtrans2 -- Дропнуть, если есть...
IF OBJECT_ID('tempdb..#sqline') IS NOT NULL
DROP TABLE #sqline -- Дропнуть, если есть...
;WITH ijtrans
AS (select distinct ijt.BUDGETCODE_ZTR, Sales.SalesId, ijt.ProdNum_ZTR, ijt.JournalId from dbo.INVENTJOURNALTRANS ijt
left join INVENTDIM id on ijt.DATAAREAID = id.DATAAREAID
and id.INVENTDIMID = ijt.TOINVENTDIMID
and id.INVENTLOCATIONID like 'C00647%'
join INVENTDIM id2 on ijt.DATAAREAID = id2.DATAAREAID
and id2.INVENTDIMID = ijt.INVENTDIMID
and id2.INVENTLOCATIONID not like 'C00647%'
left join (select SL.SalesId,SL.DataAreaId,SL.BUDGETCODE_ZTR,SL.PRODNUM_ZTR from salesline SL
join salestable ST on sl.SalesId = ST.SalesId
and ST.SalesType = @STYPE
join INVENTTRANS itt on SL.DATAAREAID = itt.DATAAREAID
and itt.INVENTTRANSID = SL.INVENTTRANSID
and itt.STORNO_ZTR = 0) Sales on ijt.DATAAREAID = Sales.DATAAREAID
-- and ijt.JOURNALID = sl.JOURNALID_ZTR
and ijt.BUDGETCODE_ZTR = Sales.BUDGETCODE_ZTR
and ijt.PRODNUM_ZTR = Sales.PRODNUM_ZTR
join INVENTTRANS it on ijt.DATAAREAID = it.DATAAREAID
and it.INVENTTRANSID = ijt.INVENTTRANSID
and it.STORNO_ZTR = 0
where ijt.DATAAREAID = 'ztr'--@da
and ijt.JournalType = @JTYPE
and ijt.BUDGETCODE_ZTR != ''
union
/*)
SELECT ijtrans.*
INTO #ijtrans
from ijtrans order by budgetcode_ZTR
SELECT * from #ijtrans
;WITH ijtrans2
AS (*/
select distinct sqln.BUDGETCODE_ZTR, sales.SALESID, ijt.ProdNum_ZTR, ijt.JournalId from dbo.INVENTJOURNALTRANS ijt
join INVENTDIM id on ijt.DATAAREAID = id.DATAAREAID
and id.INVENTDIMID = ijt.TOINVENTDIMID
and id.INVENTLOCATIONID like 'C00647%'
join INVENTDIM id2 on ijt.DATAAREAID = id2.DATAAREAID
and id2.INVENTDIMID = ijt.INVENTDIMID
and id2.INVENTLOCATIONID not like 'C00647%'
join PRODNUMTABLE_ZTR pn on pn.DATAAREAID = ijt.DATAAREAID
and pn.PRODNUM = ijt.PRODNUM_ZTR
and pn.BUDGET_CODE_ZTR != ''
join SALESQUOTATIONLINE sqln on sqln.DATAAREAID = ijt.DATAAREAID
and sqln.BUDGETCODE_ZTR = pn.BUDGET_CODE_ZTR
left join (select SL.SalesId,SL.DataAreaId,SL.BUDGETCODE_ZTR,SL.PRODNUM_ZTR from salesline SL
join salestable ST on sl.SalesId = ST.SalesId
and ST.SalesType = 3 --@STYPE
join INVENTTRANS itt on SL.DATAAREAID = itt.DATAAREAID
and itt.INVENTTRANSID = SL.INVENTTRANSID
and itt.STORNO_ZTR = 0) Sales on ijt.DATAAREAID = Sales.DATAAREAID
-- and ijt.BUDGETCODE_ZTR = Sales.BUDGETCODE_ZTR
and ijt.PRODNUM_ZTR = Sales.PRODNUM_ZTR
where ijt.DATAAREAID = 'ztr'--@da
and ijt.JournalType = @JTYPE
and ijt.BUDGETCODE_ZTR = '')
SELECT ijtrans.*
INTO #ijtrans
from ijtrans
;WITH sqline
AS (select
sqln.BudgetCode_ZTR,
sqln.ProductionDateActual_ZTR,
sqln.ShipmentDateActual_ZTR,
sqln.ProductName_ZTR,
#ijtrans.ProdNum_ZTR,
#ijtrans.JOURNALID,
#ijtrans.SALESID,
sqln.SalesQty,
sqln.ProductsCapacity_ZTR,
mst.NAME
from salesquotationline sqln
join #ijtrans on #ijtrans.BUDGETCODE_ZTR = sqln.BUDGETCODE_ZTR
join MultiLevelSegmentData_ZTR msd on msd.DATAAREAID = @da
and msd.RefRecId = sqln.RECID and msd.RefTableId = 1967
and msd.SegmentId = 'География'
join MultilevelSegmentTree_ZTR mst on mst.DATAAREAID = @da
and mst.TreeNodeId = msd.TreeNodeId
where (sqln.TYPEOFLINESALES_ZTR = @Transformers
or sqln.TYPEOFLINESALES_ZTR = @TransformersEquipment
or sqln.TYPEOFLINESALES_ZTR = @RepairTransformers
or sqln.TYPEOFLINESALES_ZTR = @SparePartsServiceCenter
or sqln.TYPEOFLINESALES_ZTR = @DeliveryClaims
or sqln.TYPEOFLINESALES_ZTR = @ComplexProjects
or sqln.TYPEOFLINESALES_ZTR = @SpareParts))
SELECT sqline.*
INTO #sqline
from sqline
--SELECT count(distinct budgetcode_ZTR) from #sqline
SELECT * from #sqline order by BudgetCode_ZTR
";
return sqlStr;
}
con = new Connection();
stmt = con.createStatement();
super();
sqlStr = getSQLCommand1();
myResult = callSqlOnServer_ZTR(stmt, sqlStr, false);
while (myResult.next())
{
Tmp_DeliverShipProducts_ZTR.SALESID = myResult.getString(1);
Tmp_DeliverShipProducts_ZTR.ProductionDateActual_ZTR = myResult.getDate(2);
Tmp_DeliverShipProducts_ZTR.ShipmentDateActual_ZTR = myResult.getDate(3);
Tmp_DeliverShipProducts_ZTR.ProductName_ZTR = myResult.getString(4);
Tmp_DeliverShipProducts_ZTR.ProdNum_ZTR = myResult.getString(5);
Tmp_DeliverShipProducts_ZTR.JOURNALID = myResult.getString(6);
Tmp_DeliverShipProducts_ZTR.SALESID = myResult.getString(7);
Tmp_DeliverShipProducts_ZTR.SalesQty = myResult.getReal(8);
Tmp_DeliverShipProducts_ZTR.ProductsCapacity_ZTR = myResult.getReal(9);
Tmp_DeliverShipProducts_ZTR.Name = myResult.getString(10);
}
}
Необходимо данные выбранные в SQL коде занести во временную таблицу Tmp_DeliverShipProducts_ZTR
В этом для меня и загвоздка, не могу понять как занести данные в таблицу
Я новичок, так что пожалуйста не судите строго
