Галактика в вопросах и ответах

Код: Выделить всё

//=============================================================
interface Test_DSQL 'Test_DSQL';
//-------------------------------------------------------------
table struct mt
(
 dsaldo   : date,
 cmc      : comp,
 cpodr    : comp,
 cparty   : comp
)
;
//--------------------------------------------------------------
create view
 as select
  *
from
  mt
;
//--------------------------------------------------------------
browse brMT;
  show at(,1,,10);
  table mt;
fields
 dsaldo   : [10], protect, noDel;
 cmc      : [17], protect, noDel;
 cpodr    : [17], protect, noDel;
 cparty   : [17], protect, noDel;
end;
handleEvent
cmInit:
{
  var stmt : longInt;
  stmt := sqlAllocStmt;
  sqlBindCol(stmt, 1, dsaldo);
  sqlBindCol(stmt, 2, cmc);
  sqlBindCol(stmt, 3, cpodr);
  sqlBindCol(stmt, 4, cparty);
  sqlExecStmt(stmt, 'SELECT max(saldomc.dsaldo), saldomc.cmc, saldomc.cpodr, saldomc.cparty FROM saldomc where kol < 60000 group by saldomc.cmc, saldomc.cpodr, saldomc.cparty order by saldomc.cmc, saldomc.cpodr, saldomc.cparty');
  sqlFetchInto(stmt, tnmt);
}
//-------------------------------------------------------------
end;
end.
 

Код: Выделить всё

  var strHandle : longInt;

  SQLAddStr(strHandle, 'SELECT')
  SQLAddStr(strHandle, '        MAX(SaldoMc.dSaldo)')
  SQLAddStr(strHandle, '      , KatMc.Name')
  SQLAddStr(strHandle, '      , COALESCE(KatPodr.Name, '''')')
  SQLAddStr(strHandle, '  FROM')
  SQLAddStr(strHandle, '      SaldoMc')
  SQLAddStr(strHandle, '  LEFT JOIN KatMc ON')
  SQLAddStr(strHandle, '    (')
  SQLAddStr(strHandle, '        SaldoMc.cMc = KatMc.Nrec')
  SQLAddStr(strHandle, '    )')
  SQLAddStr(strHandle, '  LEFT JOIN KatPodr ON')
  SQLAddStr(strHandle, '    (')
  SQLAddStr(strHandle, '        SaldoMc.cPodr = KatPodr.Nrec')
  SQLAddStr(strHandle, '    )')
  SQLAddStr(strHandle, '  WHERE')
  SQLAddStr(strHandle, '        SaldoMc.cMc IN (SELECT Pick.cRec FROM Pick WHERE Pick.wList = 1)')
  SQLAddStr(strHandle, '    AND SaldoMc.cParty <> #comp(0)')
  SQLAddStr(strHandle, '  GROUP BY')
  SQLAddStr(strHandle, '    KatMc.Name')
  SQLAddStr(strHandle, '  , KatPodr.Name')
  SQLAddStr(strHandle, '  ORDER BY')
  SQLAddStr(strHandle, '    KatMc.Name')
  SQLAddStr(strHandle, '  , KatPodr.Name')

  sqlPrepare(stmt, strHandle)
  sqlFetchInto(stmt, tnMt);

Код: Выделить всё

 SQL SELECT
        MAX(SaldoMc.dSaldo)
      , KatMc.Name
      , COALESCE(KatPodr.Name, '')
  FROM
      SaldoMc
  LEFT JOIN KatMc on
    (
        SaldoMc.cMc = KatMc.Nrec
    )
  LEFT JOIN KatMc on
    (
        SaldoMc.cPodr = KatPodr.Nrec
    )
  WHERE
       SaldoMc.cParty <> Comp(0)
  GROUP BY
    KatMc.Name
  , KatPodr.Name
  ORDER BY
    KatMc.Name
  , KatPodr.Name
 INTO Mt

Код: Выделить всё

      .appendSQL(',(CASE KatMarsh.ParGroup')
      .appendSQL('    WHEN')
      .appendSQL('      #COMP(0) THEN 0')
      .appendSQL('    ELSE')
      .appendSQL('      COALESCE')
      .appendSQL('      (')
      .appendSQL('     	  GETPAROPER')
      .appendSQL('          (')
      .appendSQL('      		   ' + coMarsh_Sp)
      .appendSQL('        		,Marsh_Sp.Nrec')
      .appendSQL('        		,' + coMnfOper)
      .appendSQL('        		,Marsh_Sp.Nope')
      .appendSQL('        		,p2.Block, p2.Ind')
      .appendSQL('        		,p1.Block, p1.Ind')
      .appendSQL('          ) ')
      .appendSQL('        ,0')
      .appendSQL('       ) / 6000')
      .appendSQL('      + RsvOPer.Kol *')
      .appendSQL('      COALESCE')
      .appendSQL('      (')
      .appendSQL('     	  GETPAROPER')
      .appendSQL('          (')
      .appendSQL('      		   ' + coMarsh_Sp)
      .appendSQL('        		,Marsh_Sp.Nrec')
      .appendSQL('        		,' + coMnfOper)
      .appendSQL('        		,Marsh_Sp.Nope')
      .appendSQL('        		,p3.Block, p3.Ind')
      .appendSQL('        		,p1.Block, p1.Ind')
      .appendSQL('          ) ')
      .appendSQL('        ,0')
      .appendSQL('       ) / 6000')
      .appendSQL('   END')
      .appendSQL(' ) AS CapacityPlan')
      .appendSQL(',ROUND(INT64(RsvOper.cRoleAn3)/6000,2) AS CapacityFact')

Код: Выделить всё

interface isqltable 'DSQL';
table struct plans
(
  nrec:comp,
  name: string,
  mckod: string,
  ed:string,
  podr:string,
  m_plan:longInt,
  mb_plan:longInt,
  mf_plan:longint,
  diff:longInt
)
with index
(
  plan01 = nrec (unique, surrogate),
  plan02 = name,
  plan03 = mckod,
  plan04 = ed,
  plan05 = podr,
  plan06 = m_plan,
  plan07 = mb_plan,
  plan08 = mf_plan,
  plan09 = diff
);

create view
var tek_date:date;
as select
  *
from
plans
;


screen sPlan;
  show at(,1,,10);
fields
	tek_date;
buttons
	cmReport ,,,'Плановый отчёт',,;
<<
Введите дату: .@@@@@@@@@@

 <.Сформировать отчёт.>

>>
end;
browse bPlan;
  show at(,11,,);
  table plans;

fields
  name    'Мц' : [15], protect, noDel;
  mckod   'код МЦ' : [5], protect, noDel;
  ed 	  'Единицы изм.':[3],protect,noDel;
  podr	  'Подразделение':[15],protect,noDel;
  m_plan  'Месячный план':[5],protect,noDel;
  mb_plan 'План на тек.дату':[5],protect,noDel;
  mf_plan 'Факт на тек.дату':[5],protect,noDel;
  diff    'Разность факта и плана':[5],protect,noDel;
end;

//-------------------------------------------------------------
handleEvent
cmInit:
{
  var stmt : longInt;
  var code : word;
  var str  : string;
  var n    : longInt;
  var stmt_str : LongInt;


  BeginTransaction(0);
  stmt := SqlAllocStmt;
  SqlBindParam(stmt, 1, tek_date);
  SqlExecStmt(stmt, 'COMPARE_REPORT_GROUPMC(?)');
  Message('Код sqlExecStmt: ' + string(sqlErrorCode(stmt)));
  SqlFreeStmt(stmt);
  EndTransaction;

  BeginTransaction(0);
  stmt := sqlAllocStmt;
  sqlExecStmt(stmt, 'SELECT name, mckod, ed,ware, month_plan, plan_from_month_begin, fact_from_month_begin, difference from biblises.pp_tmp_table');
  Message('Код sqlExecStmt: ' + string(sqlErrorCode(stmt)));
  sqlFetchInto(stmt, tnplans);
  sqlFreeStmt(stmt);
    EndTransaction;
/*	_loop plans
	{
		message(plans.name);
	}*/
}
end; // handleEvent

end.