|
Помогите пожалуйста оптимизировать запрос
|
|||
---|---|---|---|
#18+
В общем стоит супермаг с ораклом. Отчеты обрабатывает очень долго. Причину установить не смог авторusing System; using System.Collections; using System.Collections.Generic; using System.ComponentModel; using System.Windows.Forms; using System.Drawing; using System.Data; using System.Reflection; using FastReport; using FastReport.Data; using FastReport.Dialog; using FastReport.Table; using FastReport.Utils; using FastReport.Format; using Sm.FastReports; using Sm.FastReports.Link; using Sm.Interfaces; using Sm.Objects; using Sm.Core; namespace FastReport { public class ReportScript { string qMainSQL=" SELECT article, cardname, ctg_code, subgrp_name, ctg_name, status_text, last_income_dt, last_income_cl, accprice, sales, remains_on_end, cashprice, extracharge, extracharge_art/100, step, minprice, sales_to, profit, CASE sales_to WHEN 0 THEN 0 ELSE profit/sales_to*100 END margin, inAuction, (SELECT rate FROM supermag.smaltunits a WHERE a.article=s.article AND a.altunit=2) volume FROM ( SELECT s.*, CASE status WHEN 1 THEN 'Активна' WHEN 2 THEN 'Неактивна' ELSE 'Не определено' END status_text, CASE accprice WHEN 0 THEN 0 ELSE cashprice/accprice-1 END extracharge, CASE status WHEN 1 THEN sales*cashprice WHEN 2 THEN remains_on_end*cashprice ELSE 0 END sales_to, (cashprice-accprice)* CASE status WHEN 1 THEN sales WHEN 2 THEN remains_on_end ELSE 0 END profit, supermag.classifier.NORMALIZECLASSIF(ctg_code) ctg, TO_CHAR(d.createdat,'dd.mm.yyyy') last_income_dt, (SELECT name FROM supermag.smclientinfo c WHERE c.id=d.clientindex) last_income_cl FROM supermag.rep$pricecut s LEFT OUTER JOIN supermag.smdocuments d ON d.doctype='WI' AND d.id=s.last_income ORDER BY ctg ASC, article ASC) s "; string sLocName; string sGroupName; string sUserName; string sDateName; RepHost host; private void _StartReport(object sender, EventArgs e) { // Хост отчёта host = new RepHost(Report); // контрагент "От имени" sUserName = RepClientInfo.GetClientName(host.Connection, Report.GetParameterValue("P_USER_ID")); //Проверка необходимости отображения последней колонки с объемом if(ToInt32(Report.GetParameterValue("P_FLAG1"))==0){ Page1.PaperWidth=615; Cell76.Visible=false; Cell77.Visible=false; Cell78.Visible=false; Cell79.Visible=false; } // подзаголовок отчета: период отчета string sFirstDate = ToString(Report.GetParameterValue("P_FIRSTDATE")); string sLastDate = ToString(Report.GetParameterValue("P_LASTDATE")); int iCountDay = (RepConvert.ToSmDate(sLastDate)-RepConvert.ToSmDate(sFirstDate)).Days+1; sDateName = "с "+sFirstDate+" по "+sLastDate+" ( "+Safe.ToSqlNumber( iCountDay )+" дн. )"; // фильтр по местам хранения RepOracleRunner.ExecuteProcedure(host.Connection,"Supermag.RepTools.LoadLoc", Report.GetParameterValue("P_STORED_ALL"), Report.GetParameterValue("P_STORED_NAME"), null, null); // подзаголовок отчета: места хранения sLocName = RepOracleRunner.ExecuteScalar<string>(host.Connection, "select supermag.RepTools.LocString(10) from dual"); // фильтр по группам товаров RepOracleRunner.ExecuteProcedure(host.Connection,"Supermag.RepTools.LoadClass", Report.GetParameterValue("P_GROUP_ALL"), Report.GetParameterValue("P_BGROUP"), Report.GetParameterValue("P_GROUP_TREE")); // подзаголовок отчета: группы товаров sGroupName = RepOracleRunner.ExecuteScalar<string>(host.Connection, "select supermag.RepTools.ClassString(10) from dual"); RepOracleRunner.ExecuteProcedure(host.Connection,"supermag.rep_formtemptabletoo.fill_pricecut", sFirstDate, sLastDate); host.TableDataSourcePreInit("MainSQL", qMainSQL); } private void ReportSummary1_BeforePrint(object sender, EventArgs e) { if(Report.GetTotalValueNullable("TotalSales_TO").ToString()==null){ Cell75.Text=""; } } } } может быть дело в запросе? авторstring qMainSQL=" SELECT article, cardname, ctg_code, subgrp_name, ctg_name, status_text, last_income_dt, last_income_cl, accprice, sales, remains_on_end, cashprice, extracharge, extracharge_art/100, step, minprice, sales_to, profit, CASE sales_to WHEN 0 THEN 0 ELSE profit/sales_to*100 END margin, inAuction, (SELECT rate FROM supermag.smaltunits a WHERE a.article=s.article AND a.altunit=2) volume FROM ( SELECT s.*, CASE status WHEN 1 THEN 'Активна' WHEN 2 THEN 'Неактивна' ELSE 'Не определено' END status_text, CASE accprice WHEN 0 THEN 0 ELSE cashprice/accprice-1 END extracharge, CASE status WHEN 1 THEN sales*cashprice WHEN 2 THEN remains_on_end*cashprice ELSE 0 END sales_to, (cashprice-accprice)* CASE status WHEN 1 THEN sales WHEN 2 THEN remains_on_end ELSE 0 END profit, supermag.classifier.NORMALIZECLASSIF(ctg_code) ctg, TO_CHAR(d.createdat,'dd.mm.yyyy') last_income_dt, (SELECT name FROM supermag.smclientinfo c WHERE c.id=d.clientindex) last_income_cl FROM supermag.rep$pricecut s LEFT OUTER JOIN supermag.smdocuments d ON d.doctype='WI' AND d.id=s.last_income ORDER BY ctg ASC, article ASC) s "; ... |
|||
:
Нравится:
Не нравится:
|
|||
29.01.2021, 05:18 |
|
Помогите пожалуйста оптимизировать запрос
|
|||
---|---|---|---|
#18+
Здесь запрос идет из таблицы-результата rep$pricecut, вам придется отлаживать код, который ее заполняет, как уже советовали на olegon. ... |
|||
:
Нравится:
Не нравится:
|
|||
29.01.2021, 07:45 |
|
Помогите пожалуйста оптимизировать запрос
|
|||
---|---|---|---|
#18+
вы имеете в виду это? автор<Dictionary> <SupermagFastReportConnection Name="Connection1"> <TableDataSource Name="Table" Alias="MainSQL" DataType="System.Int32" Enabled="true" SelectCommand=" SELECT NULL article, NULL cardname, NULL ctg_code, NULL subgrp_name, NULL ctg_name, NULL status_text, NULL last_income_dt, NULL last_income_cl, 0 accprice, 0 sales, 0 remains_on_end, 0 cashprice, 0 extracharge, 0 extracharge_art, 0 step, 0 minprice, 0 sales_to, 0 profit, 0 margin, NULL inAuction, 0 volume FROM dual"> <Column Name="ARTICLE" DataType="System.String"/> <Column Name="CARDNAME" DataType="System.String"/> <Column Name="CTG_CODE" DataType="System.String"/> <Column Name="SUBGRP_NAME" DataType="System.String"/> <Column Name="CTG_NAME" DataType="System.String"/> <Column Name="STATUS_TEXT" DataType="System.String"/> <Column Name="LAST_INCOME_DT" DataType="System.String"/> <Column Name="LAST_INCOME_CL" DataType="System.String"/> <Column Name="ACCPRICE" DataType="System.Decimal"/> <Column Name="SALES" DataType="System.Decimal"/> <Column Name="REMAINS_ON_END" DataType="System.Decimal"/> <Column Name="CASHPRICE" DataType="System.Decimal"/> <Column Name="EXTRACHARGE" DataType="System.Decimal"/> <Column Name="EXTRACHARGE_ART" DataType="System.Decimal"/> <Column Name="STEP" DataType="System.Decimal"/> <Column Name="MINPRICE" DataType="System.Decimal"/> <Column Name="SALES_TO" DataType="System.Decimal"/> <Column Name="PROFIT" DataType="System.Decimal"/> <Column Name="MARGIN" DataType="System.Decimal"/> <Column Name="INAUCTION" DataType="System.String"/> <Column Name="VOLUME" DataType="System.Decimal"/> </TableDataSource> </SupermagFastReportConnection> <Parameter Name="P_FIRSTDATE" DataType="System.String"/> <Parameter Name="P_LASTDATE" DataType="System.String"/> <Parameter Name="P_FLAG1" DataType="System.Int32"/> <Parameter Name="P_FLAG2" DataType="System.Int32"/> <Parameter Name="P_STORED_ALL" DataType="System.Int32"/> <Parameter Name="P_STORED_NAME" DataType="System.String"/> <Parameter Name="P_SURNAME" DataType="System.String"/> <Parameter Name="P_USER_ID" DataType="System.Int32"/> <Parameter Name="P_BGROUP" DataType="System.Int32"/> <Parameter Name="P_GROUP_ALL" DataType="System.Int32"/> <Parameter Name="P_GROUP_TREE" DataType="System.String"/> <Total Name="SalesSum" Expression="[MainSQL.SALES]" Evaluator="DataSpec" PrintOn="GroupFooter1"/> <Total Name="Sales_TOSum" Expression="[MainSQL.SALES_TO]" Evaluator="DataSpec" PrintOn="GroupFooter1"/> <Total Name="ProfitSum" Expression="[MainSQL.PROFIT]" Evaluator="DataSpec" PrintOn="GroupFooter1"/> <Total Name="TotalSales_TO" Expression="[MainSQL.SALES_TO]" Evaluator="DataSpec" PrintOn="ReportSummary1"/> <Total Name="TotalProfit" Expression="[MainSQL.PROFIT]" Evaluator="DataSpec" PrintOn="ReportSummary1"/> <Total Name="VolumeSum" Expression="[MainSQL.VOLUME]" Evaluator="DataSpec" PrintOn="GroupFooter1"/> </Dictionary> <ReportPage Name="Page1" Landscape="true" PaperWidth="635" PaperHeight="210" Fill.Color="White" TitleBeforeHeader="false"> <PageHeaderBand Name="PageHeader1" Width="2324.7" Height="15.12" PrintOn="FirstPage"> <TextObject Name="txtSurname" Left="1984.5" Width="343.98" Height="15.12" Anchor="Top, Right" CanGrow="true" CanBreak="false" Text="[P_SURNAME]" HorzAlign="Right" Font="Arial, 8pt"/> <TextObject Name="txtPrintDate" Left="340.2" Width="1644.3" Height="15.12" Anchor="Top, Left, Right" Text="Дата печати: [Format("{0:dd.MM.yy HH:mm}", [Date])]" Padding="0, 0, 2, 0" HorzAlign="Center" Font="Arial, 8pt"/> <TextObject Name="txtUserName" Width="330.75" Height="15.12" CanGrow="true" CanBreak="false" Text="[sUserName]" Font="Arial, 8pt"/> </PageHeaderBand> <ReportTitleBand Name="ReportTitle1" Top="19.12" Width="2324.7" Height="113.4" CanGrow="true"> <TextObject Name="Text1" Top="9.45" Width="2322.81" Height="18.9" Anchor="Top, Left, Right" Text="Ценовой срез" HorzAlign="Center" Font="Arial, 11pt, style=Bold"/> <TableObject Name="Table1" Left="9.45" Top="37.8" Width="463.05" Height="56.7"> <TableColumn Name="Column1" Width="463.05"/> <TableRow Name="Row1"> <TableCell Name="Cell1" Text="Период [sDateName]" VertAlign="Center" Font="Arial, 9pt"/> </TableRow> <TableRow Name="Row2"> <TableCell Name="Cell6" Text="[sLocName]" VertAlign="Center" Font="Arial, 9pt"/> </TableRow> <TableRow Name="Row3"> <TableCell Name="Cell11" Text="[sGroupName]" VertAlign="Center" Font="Arial, 9pt"/> </TableRow> </TableObject> </ReportTitleBand> <ColumnHeaderBand Name="ColumnHeader" Top="136.52" Width="2324.7" Height="37.8" CanGrow="true" CanShrink="true" PrintOn="FirstPage"> <TableObject Name="Table2" Width="2324.7" Height="37.8"> <TableColumn Name="Column2" Width="94.5"/> <TableColumn Name="Column3" Width="311.85"/> <TableColumn Name="Column4" Width="75.6"/> <TableColumn Name="Column5" Width="236.25"/> <TableColumn Name="Column6" Width="302.4"/> <TableColumn Name="Column7" Width="113.4"/> <TableColumn Name="Column78" Width="75.6"/> <TableColumn Name="Column90" Width="226.8"/> <TableColumn Name="Column8" Width="75.6"/> <TableColumn Name="Column9" Width="75.6"/> <TableColumn Name="Column10"/> <TableColumn Name="Column11" Width="0"/> <TableColumn Name="Column12" Width="0"/> <TableColumn Name="Column13"/> <TableColumn Name="Column14"/> <TableColumn Name="Column71"/> <TableColumn Name="Column82"/> <TableColumn Name="Column86"/> <TableColumn Name="Column70"/> <TableColumn Name="Column15"/> <TableColumn Name="Column16"/> <TableColumn Name="Column17"/> <TableColumn Name="Column66" Width="75.6"/> <TableRow Name="Row4" Height="37.8"> <TableCell Name="Cell12" Border.Lines="All" Border.LeftLine.Width="1.5" Border.TopLine.Width="1.5" Border.BottomLine.Width="1.5" Fill.Color="Olive" Text="Артикул" HorzAlign="Center" VertAlign="Center" Font="Arial, 8pt, style=Bold" TextFill.Color="White"/> <TableCell Name="Cell13" Border.Lines="All" Border.TopLine.Width="1.5" Border.BottomLine.Width="1.5" Fill.Color="Olive" Text="Товар" HorzAlign="Center" VertAlign="Center" Font="Arial, 8pt, style=Bold" TextFill.Color="White"/> <TableCell Name="Cell14" Border.Lines="All" Border.TopLine.Width="1.5" Border.BottomLine.Width="1.5" Fill.Color="Olive" Text="Категория" HorzAlign="Center" VertAlign="Center" Font="Arial, 8pt, style=Bold" TextFill.Color="White"/> <TableCell Name="Cell15" Border.Lines="All" Border.TopLine.Width="1.5" Border.BottomLine.Width="1.5" Fill.Color="Olive" Text="Наименование подгруппы" HorzAlign="Center" VertAlign="Center" Font="Arial, 8pt, style=Bold" TextFill.Color="White"/> <TableCell Name="Cell16" Border.Lines="All" Border.TopLine.Width="1.5" Border.BottomLine.Width="1.5" Fill.Color="Olive" Text="Наименование категории" HorzAlign="Center" VertAlign="Center" Font="Arial, 8pt, style=Bold" TextFill.Color="White"/> <TableCell Name="Cell17" Border.Lines="All" Border.TopLine.Width="1.5" Border.BottomLine.Width="1.5" Fill.Color="Olive" Text="Статус карточки" HorzAlign="Center" VertAlign="Center" Font="Arial, 8pt, style=Bold" TextFill.Color="White"/> <TableCell Name="Cell88" Border.Lines="Top, Bottom" Border.TopLine.Width="1.5" Border.BottomLine.Width="1.5" Fill.Color="Olive" Text="Дата последнего прихода" HorzAlign="Center" VertAlign="Center" Font="Arial, 8pt, style=Bold" TextFill.Color="White"/> <TableCell Name="Cell100" Border.Lines="All" Border.TopLine.Width="1.5" Border.BottomLine.Width="1.5" Fill.Color="Olive" Text="Последний поставщик" HorzAlign="Center" VertAlign="Center" Font="Arial, 8pt, style=Bold" TextFill.Color="White"/> <TableCell Name="Cell18" Border.Lines="All" Border.TopLine.Width="1.5" Border.BottomLine.Width="1.5" Fill.Color="Olive" Text="Учетная цена" HorzAlign="Center" VertAlign="Center" Font="Arial, 8pt, style=Bold" TextFill.Color="White"/> <TableCell Name="Cell19" Border.Lines="All" Border.TopLine.Width="1.5" Border.BottomLine.Width="1.5" Fill.Color="Olive" Text="Продажи, кол-во" HorzAlign="Center" VertAlign="Center" Font="Arial, 8pt, style=Bold" TextFill.Color="White"/> <TableCell Name="Cell20" Border.Lines="All" Border.TopLine.Width="1.5" Border.BottomLine.Width="1.5" Fill.Color="Olive" Text="Остатки, кол-во" HorzAlign="Center" VertAlign="Center" Font="Arial, 8pt, style=Bold" TextFill.Color="White"/> <TableCell Name="Cell21" Border.Lines="All" Border.TopLine.Width="1.5" Border.BottomLine.Width="1.5" Fill.Color="Olive" Text="Доля продаж" HorzAlign="Center" VertAlign="Center" Font="Arial, 8pt, style=Bold" TextFill.Color="White"/> <TableCell Name="Cell22" Border.Lines="All" Border.TopLine.Width="1.5" Border.BottomLine.Width="1.5" Fill.Color="Olive" Text="Группа по продажам" HorzAlign="Center" VertAlign="Center" Font="Arial, 8pt, style=Bold" TextFill.Color="White"/> <TableCell Name="Cell23" Border.Lines="All" Border.TopLine.Width="1.5" Border.BottomLine.Width="1.5" Fill.Color="Olive" Text="Цена для кассы" HorzAlign="Center" VertAlign="Center" Font="Arial, 8pt, style=Bold" TextFill.Color="White"/> <TableCell Name="Cell24" Border.Lines="All" Border.TopLine.Width="1.5" Border.BottomLine.Width="1.5" Fill.Color="Olive" Text="Наценка" HorzAlign="Center" VertAlign="Center" Font="Arial, 8pt, style=Bold" TextFill.Color="White"/> <TableCell Name="Cell81" Border.Lines="All" Border.TopLine.Width="1.5" Border.BottomLine.Width="1.5" Fill.Color="Olive" Text="Наценка (из карточки)" HorzAlign="Center" VertAlign="Center" Font="Arial, 8pt, style=Bold" TextFill.Color="White"/> <TableCell Name="Cell92" Border.Lines="All" Border.TopLine.Width="1.5" Border.BottomLine.Width="1.5" Fill.Color="Olive" Text="Шаг цены" HorzAlign="Center" VertAlign="Center" Font="Arial, 8pt, style=Bold" TextFill.Color="White"/> <TableCell Name="Cell96" Border.Lines="All" Border.TopLine.Width="1.5" Border.BottomLine.Width="1.5" Fill.Color="Olive" Text="Акция" HorzAlign="Center" VertAlign="Center" Font="Arial, 8pt, style=Bold" TextFill.Color="White"/> <TableCell Name="Cell80" Border.Lines="All" Border.TopLine.Width="1.5" Border.BottomLine.Width="1.5" Fill.Color="Olive" Text="МРЦ" HorzAlign="Center" VertAlign="Center" Font="Arial, 8pt, style=Bold" TextFill.Color="White"/> <TableCell Name="Cell25" Border.Lines="All" Border.TopLine.Width="1.5" Border.BottomLine.Width="1.5" Fill.Color="Olive" Text="ТО текущий, руб." HorzAlign="Center" VertAlign="Center" Font="Arial, 8pt, style=Bold" TextFill.Color="White"/> <TableCell Name="Cell26" Border.Lines="All" Border.TopLine.Width="1.5" Border.BottomLine.Width="1.5" Fill.Color="Olive" Text="Доход текущий, руб." HorzAlign="Center" VertAlign="Center" Font="Arial, 8pt, style=Bold" TextFill.Color="White"/> <TableCell Name="Cell27" Border.Lines="All" Border.TopLine.Width="1.5" Border.RightLine.Width="1.5" Border.BottomLine.Width="1.5" Fill.Color="Olive" Text="Маржа" HorzAlign="Center" VertAlign="Center" Font="Arial, 8pt, style=Bold" TextFill.Color="White"/> <TableCell Name="Cell76" Border.Lines="All" Fill.Color="Olive" Text="Объем одной шт., л" HorzAlign="Center" VertAlign="Center" Font="Arial, 8pt, style=Bold" TextFill.Color="White"/> </TableRow> </TableObject> </ColumnHeaderBand> <GroupHeaderBand Name="GroupHeader1" Top="178.32" Width="2324.7" CanGrow="true" CanShrink="true" CanBreak="true" Condition="[MainSQL.CTG_NAME]" SortOrder="None"> <DataBand Name="DataSpec" Top="182.32" Width="2324.7" Height="18.9" Guides="0,0,0" DataSource="Table"> <TableObject Name="Table1" Width="2324.7" Height="18.9"> <TableColumn Name="Column18" Width="94.5"/> <TableColumn Name="Column19" Width="311.85"/> <TableColumn Name="Column20" Width="75.6"/> <TableColumn Name="Column21" Width="236.25"/> <TableColumn Name="Column22" Width="302.4"/> <TableColumn Name="Column23" Width="113.4"/> <TableColumn Name="Column79" Width="75.6"/> <TableColumn Name="Column91" Width="226.8"/> <TableColumn Name="Column24" Width="75.6"/> <TableColumn Name="Column25" Width="75.6"/> <TableColumn Name="Column26"/> <TableColumn Name="Column27" Width="0"/> <TableColumn Name="Column28" Width="0"/> <TableColumn Name="Column29"/> <TableColumn Name="Column30"/> <TableColumn Name="Column73"/> <TableColumn Name="Column83"/> <TableColumn Name="Column87"/> <TableColumn Name="Column72"/> <TableColumn Name="Column31"/> <TableColumn Name="Column32"/> <TableColumn Name="Column33"/> <TableColumn Name="Column67" Width="75.6"/> <TableRow Name="Row5"> <TableCell Name="Cell28" Border.Lines="All" Text="[MainSQL.ARTICLE]" VertAlign="Center" Font="Arial, 8pt"/> <TableCell Name="Cell29" Border.Lines="All" Text="[MainSQL.CARDNAME]" VertAlign="Center" Font="Arial, 8pt"/> <TableCell Name="Cell30" Border.Lines="All" Text="[MainSQL.CTG_CODE]" VertAlign="Center" Font="Arial, 8pt"/> <TableCell Name="Cell31" Border.Lines="All" Text="[MainSQL.SUBGRP_NAME]" VertAlign="Center" Font="Arial, 8pt"/> <TableCell Name="Cell32" Border.Lines="All" Text="[MainSQL.CTG_NAME]" VertAlign="Center" Font="Arial, 8pt"/> <TableCell Name="Cell33" Border.Lines="All" Text="[MainSQL.STATUS_TEXT]" VertAlign="Center" Font="Arial, 8pt"/> <TableCell Name="Cell89" Border.Lines="All" Text="[MainSQL.LAST_INCOME_DT]" HorzAlign="Center" VertAlign="Bottom" Font="Arial, 8pt"/> <TableCell Name="Cell101" Border.Lines="All" Text="[MainSQL.LAST_INCOME_CL]" VertAlign="Center" Font="Arial, 8pt"/> <TableCell Name="Cell34" Border.Lines="All" Text="[MainSQL.ACCPRICE]" HorzAlign="Right" VertAlign="Center" Font="Arial, 8pt"/> <TableCell Name="Cell35" Border.Lines="All" Text="[MainSQL.SALES]" HorzAlign="Right" VertAlign="Center" Font="Arial, 8pt"/> <TableCell Name="Cell36" Border.Lines="All" Text="[MainSQL.REMAINS_ON_END]" HorzAlign="Right" VertAlign="Center" Font="Arial, 8pt"/> <TableCell Name="Cell37" Border.Lines="All" Format="Percent" Format.UseLocale="true" HorzAlign="Right" VertAlign="Center" Font="Arial, 8pt"/> <TableCell Name="Cell38" Border.Lines="All" HorzAlign="Center" VertAlign="Center" Font="Arial, 8pt"> <Highlight> <Condition Expression="Value == "C"" Fill.Color="210, 210, 255" ApplyFill="true" ApplyTextFill="false"/> <Condition Expression="Value == "A"" Fill.Color="OliveDrab" ApplyFill="true" ApplyTextFill="false"/> <Condition Expression="Value == "B"" Fill.Color="255, 192, 192" ApplyFill="true" ApplyTextFill="false"/> </Highlight> </TableCell> <TableCell Name="Cell39" Border.Lines="All" Text="[MainSQL.CASHPRICE]" HorzAlign="Right" VertAlign="Center" Font="Arial, 8pt"/> <TableCell Name="Cell40" Border.Lines="All" Text="[MainSQL.EXTRACHARGE]" Format="Percent" Format.UseLocale="true" HorzAlign="Right" VertAlign="Center" Font="Arial, 8pt"/> <TableCell Name="Cell83" Border.Lines="All" Text="[MainSQL.EXTRACHARGE_ART]" Format="Percent" Format.UseLocale="true" HorzAlign="Right" VertAlign="Center" Font="Arial, 8pt"/> <TableCell Name="Cell93" Border.Lines="All" Text="[MainSQL.STEP]" Format="Number" Format.UseLocale="true" HorzAlign="Right" VertAlign="Center" Font="Arial, 8pt"/> <TableCell Name="Cell97" Border.Lines="All" Text="[MainSQL.INAUCTION]" HorzAlign="Center" VertAlign="Center" Font="Arial, 8pt"/> <TableCell Name="Cell82" Border.Lines="All" Text="[MainSQL.MINPRICE]" Format="Number" Format.UseLocale="true" HorzAlign="Right" VertAlign="Center" Font="Arial, 8pt"/> <TableCell Name="Cell41" Border.Lines="All" Text="[MainSQL.SALES_TO]" HorzAlign="Right" VertAlign="Center" Font="Arial, 8pt"/> <TableCell Name="Cell42" Border.Lines="All" Text="[MainSQL.PROFIT]" HorzAlign="Right" VertAlign="Center" Font="Arial, 8pt"/> <TableCell Name="Cell43" Border.Lines="All" Text="[MainSQL.MARGIN]" Format="Number" Format.UseLocale="true" HorzAlign="Right" VertAlign="Center" Font="Arial, 8pt"/> <TableCell Name="Cell77" Border.Lines="All" Text="[MainSQL.VOLUME]" Format="Number" Format.UseLocale="true" HorzAlign="Right" VertAlign="Center" Font="Arial, 8pt"/> </TableRow> </TableObject> </DataBand> <GroupFooterBand Name="GroupFooter1" Top="205.22" Width="2324.7" Height="18.9"> <TableObject Name="Table1" Width="2324.7" Height="18.9"> <TableColumn Name="Column34" Width="94.5"/> <TableColumn Name="Column35" Width="311.85"/> <TableColumn Name="Column36" Width="75.6"/> <TableColumn Name="Column37" Width="236.25"/> <TableColumn Name="Column38" Width="302.4"/> <TableColumn Name="Column39" Width="113.4"/> <TableColumn Name="Column80" Width="75.6"/> <TableColumn Name="Column92" Width="226.8"/> <TableColumn Name="Column40" Width="75.6"/> <TableColumn Name="Column41" Width="75.6"/> <TableColumn Name="Column42"/> <TableColumn Name="Column43" Width="0"/> <TableColumn Name="Column44" Width="0"/> <TableColumn Name="Column45"/> <TableColumn Name="Column46"/> <TableColumn Name="Column75"/> <TableColumn Name="Column84"/> <TableColumn Name="Column88"/> <TableColumn Name="Column74"/> <TableColumn Name="Column47"/> <TableColumn Name="Column48"/> <TableColumn Name="Column49"/> <TableColumn Name="Column68" Width="75.6"/> <TableRow Name="Row6"> <TableCell Name="Cell44" Border.Lines="All" Fill.Color="210, 210, 255" VertAlign="Center" Font="Arial, 8pt, style=Bold"/> <TableCell Name="Cell45" Border.Lines="All" Fill.Color="210, 210, 255" Text="[MainSQL.CTG_NAME]" VertAlign="Center" Font="Arial, 8pt, style=Bold"/> <TableCell Name="Cell46" Border.Lines="All" Fill.Color="210, 210, 255" VertAlign="Center" Font="Arial, 8pt"/> <TableCell Name="Cell47" Border.Lines="All" Fill.Color="210, 210, 255" VertAlign="Center" Font="Arial, 8pt"/> <TableCell Name="Cell48" Border.Lines="All" Fill.Color="210, 210, 255" VertAlign="Center" Font="Arial, 8pt"/> <TableCell Name="Cell49" Border.Lines="All" Fill.Color="210, 210, 255" VertAlign="Center" Font="Arial, 8pt"/> <TableCell Name="Cell90" Border.Lines="All" Fill.Color="210, 210, 255"/> <TableCell Name="Cell102" Border.Lines="All" Fill.Color="210, 210, 255"/> <TableCell Name="Cell50" Border.Lines="All" Fill.Color="210, 210, 255" HorzAlign="Right" VertAlign="Center" Font="Arial, 8pt"/> <TableCell Name="Cell51" Border.Lines="All" Fill.Color="210, 210, 255" Text="[SalesSum]" Format="Number" Format.UseLocale="true" HorzAlign="Right" VertAlign="Center" Font="Arial, 8pt, style=Bold"/> <TableCell Name="Cell52" Border.Lines="All" Fill.Color="210, 210, 255" Format="Number" Format.UseLocale="true" HorzAlign="Right" VertAlign="Center" Font="Arial, 8pt"/> <TableCell Name="Cell53" Border.Lines="All" Fill.Color="210, 210, 255" Format="Number" Format.UseLocale="true" HorzAlign="Right" VertAlign="Center" Font="Arial, 8pt"/> <TableCell Name="Cell54" Border.Lines="All" Fill.Color="210, 210, 255" Format="Number" Format.UseLocale="true" HorzAlign="Center" VertAlign="Center" Font="Arial, 8pt"> <Highlight> <Condition Expression="Value == "C"" Fill.Color="210, 210, 255" ApplyFill="true" ApplyTextFill="false"/> <Condition Expression="Value == "A"" Fill.Color="OliveDrab" ApplyFill="true" ApplyTextFill="false"/> <Condition Expression="Value == "B"" Fill.Color="255, 192, 192" ApplyFill="true" ApplyTextFill="false"/> </Highlight> </TableCell> <TableCell Name="Cell55" Border.Lines="All" Fill.Color="210, 210, 255" Format="Number" Format.UseLocale="true" HorzAlign="Right" VertAlign="Center" Font="Arial, 8pt"/> <TableCell Name="Cell56" Border.Lines="All" Fill.Color="210, 210, 255" Format="Number" Format.UseLocale="true" HorzAlign="Right" VertAlign="Center" Font="Arial, 8pt"/> <TableCell Name="Cell85" Border.Lines="All" Fill.Color="210, 210, 255" Font="Arial, 8pt"/> <TableCell Name="Cell94" Border.Lines="All" Fill.Color="210, 210, 255"/> <TableCell Name="Cell98" Border.Lines="All" Fill.Color="210, 210, 255"/> <TableCell Name="Cell84" Border.Lines="All" Fill.Color="210, 210, 255" Font="Arial, 8pt"/> <TableCell Name="Cell57" Border.Lines="All" Fill.Color="210, 210, 255" Text="[Sales_TOSum]" Format="Number" Format.UseLocale="true" HorzAlign="Right" VertAlign="Center" Font="Arial, 8pt, style=Bold"/> <TableCell Name="Cell58" Border.Lines="All" Fill.Color="210, 210, 255" Text="[ProfitSum]" Format="Number" Format.UseLocale="true" HorzAlign="Right" VertAlign="Center" Font="Arial, 8pt, style=Bold"/> <TableCell Name="Cell59" Border.Lines="All" Fill.Color="210, 210, 255" Text="[[ProfitSum]/IIf([Sales_TOSum]<=0,1,[Sales_TOSum])*100]" Format="Number" Format.UseLocale="true" HorzAlign="Right" VertAlign="Center" Font="Arial, 8pt, style=Bold"/> <TableCell Name="Cell78" Border.Lines="All" Fill.Color="210, 210, 255" Format="Number" Format.UseLocale="true" HorzAlign="Right" VertAlign="Center" Font="Arial, 8pt, style=Bold"/> </TableRow> </TableObject> </GroupFooterBand> </GroupHeaderBand> <ReportSummaryBand Name="ReportSummary1" Top="228.12" Width="2324.7" Height="18.9" BeforePrintEvent="ReportSummary1_BeforePrint"> <TableObject Name="Table1" Width="2324.7" Height="18.9"> <TableColumn Name="Column50" Width="94.5"/> <TableColumn Name="Column51" Width="311.85"/> <TableColumn Name="Column52" Width="75.6"/> <TableColumn Name="Column53" Width="236.25"/> <TableColumn Name="Column54" Width="302.4"/> <TableColumn Name="Column55" Width="113.4"/> <TableColumn Name="Column81" Width="75.6"/> <TableColumn Name="Column93" Width="226.8"/> <TableColumn Name="Column56" Width="75.6"/> <TableColumn Name="Column57" Width="75.6"/> <TableColumn Name="Column58"/> <TableColumn Name="Column59" Width="0"/> <TableColumn Name="Column60" Width="0"/> <TableColumn Name="Column61"/> <TableColumn Name="Column62"/> <TableColumn Name="Column77"/> <TableColumn Name="Column85"/> <TableColumn Name="Column89"/> <TableColumn Name="Column76"/> <TableColumn Name="Column63"/> <TableColumn Name="Column64"/> <TableColumn Name="Column65"/> <TableColumn Name="Column69" Width="75.6"/> <TableRow Name="Row7" AutoSize="true"> <TableCell Name="Cell60" Border.Lines="All" Fill.Color="192, 255, 192" VertAlign="Center" Font="Arial, 8pt, style=Bold"/> <TableCell Name="Cell61" Border.Lines="All" Fill.Color="192, 255, 192" Text="Итог" VertAlign="Center" Font="Arial, 8pt, style=Bold"/> <TableCell Name="Cell62" Border.Lines="All" Fill.Color="192, 255, 192" VertAlign="Center" Font="Arial, 8pt"/> <TableCell Name="Cell63" Border.Lines="All" Fill.Color="192, 255, 192" VertAlign="Center" Font="Arial, 8pt"/> <TableCell Name="Cell64" Border.Lines="All" Fill.Color="192, 255, 192" VertAlign="Center" Font="Arial, 8pt"/> <TableCell Name="Cell65" Border.Lines="All" Fill.Color="192, 255, 192" VertAlign="Center" Font="Arial, 8pt"/> <TableCell Name="Cell91" Border.Lines="All" Fill.Color="192, 255, 192"/> <TableCell Name="Cell103" Border.Lines="All" Fill.Color="192, 255, 192"/> <TableCell Name="Cell66" Border.Lines="All" Fill.Color="192, 255, 192" HorzAlign="Right" VertAlign="Center" Font="Arial, 8pt"/> <TableCell Name="Cell67" Border.Lines="All" Fill.Color="192, 255, 192" Format="Number" Format.UseLocale="true" ProcessAt="GroupFinished" HorzAlign="Right" VertAlign="Center" Font="Arial, 8pt, style=Bold"/> <TableCell Name="Cell68" Border.Lines="All" Fill.Color="192, 255, 192" Format="Number" Format.UseLocale="true" HorzAlign="Right" VertAlign="Center" Font="Arial, 8pt"/> <TableCell Name="Cell69" Border.Lines="All" Fill.Color="192, 255, 192" Format="Number" Format.UseLocale="true" HorzAlign="Right" VertAlign="Center" Font="Arial, 8pt"/> <TableCell Name="Cell70" Border.Lines="All" Fill.Color="192, 255, 192" Format="Number" Format.UseLocale="true" HorzAlign="Center" VertAlign="Center" Font="Arial, 8pt"> <Highlight> <Condition Expression="Value == "C"" Fill.Color="210, 210, 255" ApplyFill="true" ApplyTextFill="false"/> <Condition Expression="Value == "A"" Fill.Color="OliveDrab" ApplyFill="true" ApplyTextFill="false"/> <Condition Expression="Value == "B"" Fill.Color="255, 192, 192" ApplyFill="true" ApplyTextFill="false"/> </Highlight> </TableCell> <TableCell Name="Cell71" Border.Lines="All" Fill.Color="192, 255, 192" Format="Number" Format.UseLocale="true" HorzAlign="Right" VertAlign="Center" Font="Arial, 8pt"/> <TableCell Name="Cell72" Border.Lines="All" Fill.Color="192, 255, 192" Format="Number" Format.UseLocale="true" HorzAlign="Right" VertAlign="Center" Font="Arial, 8pt"/> <TableCell Name="Cell87" Border.Lines="All" Fill.Color="192, 255, 192" Font="Arial, 8pt"/> <TableCell Name="Cell95" Border.Lines="All" Fill.Color="192, 255, 192"/> <TableCell Name="Cell99" Border.Lines="All" Fill.Color="192, 255, 192"/> <TableCell Name="Cell86" Border.Lines="All" Fill.Color="192, 255, 192" Font="Arial, 8pt"/> <TableCell Name="Cell73" Border.Lines="All" Fill.Color="192, 255, 192" Text="[TotalSales_TO]" Format="Number" Format.UseLocale="true" ProcessAt="GroupFinished" HorzAlign="Right" VertAlign="Center" Font="Arial, 8pt, style=Bold"/> <TableCell Name="Cell74" Border.Lines="All" Fill.Color="192, 255, 192" Text="[TotalProfit]" Format="Number" Format.UseLocale="true" ProcessAt="GroupFinished" HorzAlign="Right" VertAlign="Center" Font="Arial, 8pt, style=Bold"/> <TableCell Name="Cell75" Border.Lines="All" Fill.Color="192, 255, 192" Text="[[TotalProfit]/IIf([TotalSales_TO]<=0,1,[TotalSales_TO])*100]" Format="Number" Format.UseLocale="true" ProcessAt="GroupFinished" HorzAlign="Right" VertAlign="Center" Font="Arial, 8pt, style=Bold"/> <TableCell Name="Cell79" Border.Lines="All" Fill.Color="192, 255, 192" Format="Number" Format.UseLocale="true" HorzAlign="Right" VertAlign="Center" Font="Arial, 8pt, style=Bold"/> </TableRow> </TableObject> </ReportSummaryBand> </ReportPage> ... |
|||
:
Нравится:
Не нравится:
|
|||
29.01.2021, 08:32 |
|
Помогите пожалуйста оптимизировать запрос
|
|||
---|---|---|---|
#18+
Если вы собираетесь работать в поддержке Супермага, вам следует для начала отделить компоненты ПО. FarstReports - отчетный движок. Его нужно будет изучить. Здесь, на sql.ru, задавайте вопросы по конкретным запросам в СУБД. FastReport делает вызов процедуры Код: plsql 1.
Вот ее вам нужно открыть и отлаживать. ... |
|||
:
Нравится:
Не нравится:
|
|||
29.01.2021, 09:08 |
|
|
start [/forum/topic.php?fid=52&msg=40039815&tid=1880481]: |
0ms |
get settings: |
10ms |
get forum list: |
13ms |
check forum access: |
4ms |
check topic access: |
4ms |
track hit: |
27ms |
get topic data: |
12ms |
get forum data: |
3ms |
get page messages: |
71ms |
get tp. blocked users: |
1ms |
others: | 13ms |
total: | 158ms |
0 / 0 |