public override void Run()
{
string sqlCode = "";
string sqlRun = "";
if (errorCode.Value != null && !string.IsNullOrEmpty(errorCode.Value.ToString()))
{
sqlCode = $@" and error_code ='{errorCode.Value.ToString()}' ";
}
sqlRun = Sqls["SqlErrorCode"] + sqlCode;
RunSqls.Add(sqlRun);
OleExec SFCDB = DBPools["SFCDB"].Borrow();
try
{
DataSet dsUresymptom = SFCDB.RunSelect(sqlRun);
ReportTable reportTable = new ReportTable();
reportTable.LoadData(dsUresymptom.Tables[0], null);
reportTable.Tittle = "ErrorCodeTable";
reportTable.ColNames.RemoveAt(0);
Outputs.Add(reportTable);
DBPools["SFCDB"].Return(SFCDB);
}
catch (Exception exception)
{
DBPools["SFCDB"].Return(SFCDB);
throw exception;
}
}
public void Build_TextValue_CorrectCells()
{
IReportTable <ReportCell> table = new ReportTable <ReportCell>()
{
HeaderRows = new List <IEnumerable <ReportCell> >()
{
new ReportCell[] { new ReportCell <string>("Value"), },
},
Rows = new List <IEnumerable <ReportCell> >()
{
new ReportCell[] { new ReportCell <string>("Test"), },
},
};
ReportConverter <HtmlReportCell> converter = new ReportConverter <HtmlReportCell>(Enumerable.Empty <IPropertyHandler <HtmlReportCell> >());
IReportTable <HtmlReportCell> htmlReportTable = converter.Convert(table);
HtmlReportCell[][] headerCells = this.GetHeaderCellsAsArray(htmlReportTable);
headerCells.Should().HaveCount(1);
headerCells[0][0].GetValue <string>().Should().Be("Value");
headerCells[0][0].ColumnSpan.Should().Be(1);
headerCells[0][0].RowSpan.Should().Be(1);
HtmlReportCell[][] cells = this.GetBodyCellsAsArray(htmlReportTable);
cells.Should().HaveCount(1);
cells[0][0].GetValue <string>().Should().Be("Test");
cells[0][0].ColumnSpan.Should().Be(1);
cells[0][0].RowSpan.Should().Be(1);
}
public override void Run()
{
//base.Run();
string wo = inputWo.Value.ToString();
string eventName = inputEventName.Value.ToString().ToUpper();
string sqlRun = string.Empty;
DataTable snListTable = new DataTable();
DataTable linkTable = new DataTable();
DataRow linkRow = null;
OleExec SFCDB = DBPools["SFCDB"].Borrow();
try
{
if (eventName.Equals("REPAIRWIP"))
{
sqlRun = $@"select distinct sn,next_station as station,edit_time from r_sn where REPAIR_FAILED_FLAG = 1 and workorderno ='{wo}'";
}
else if (eventName.Equals("MRB"))
{
sqlRun = $@"select distinct sn,'MRB' as station,edit_time from r_mrb where workorderno = '{wo}' and rework_wo is null";
}
else
{
//sqlRun = $@"select sn,next_station as station,edit_time from r_sn where workorderno='{wo}' and next_station='{eventName}'";
//sqlRun = $@"select a.sn,a.next_station as station,a.edit_time,b.panel from r_sn a,r_panel_sn b where a.workorderno='{wo}' and a.next_station='{eventName}' and a.sn=b.sn";
sqlRun = $@" select a.sn,a.next_station as station,a.edit_time,b.panel from r_sn a left join r_panel_sn b on a.sn=b.sn where a.workorderno='{wo}' and a.next_station='{eventName}' ";
}
RunSqls.Add(sqlRun);
//OleExec SFCDB = DBPools["SFCDB"].Borrow();
//try
//{
snListTable = SFCDB.RunSelect(sqlRun).Tables[0];
DBPools["SFCDB"].Return(SFCDB);
linkTable.Columns.Add("SN");
linkTable.Columns.Add("STATION");
linkTable.Columns.Add("EDIT_TIME");
linkTable.Columns.Add("PANEL");
for (int i = 0; i < snListTable.Rows.Count; i++)
{
linkRow = linkTable.NewRow();
linkRow["SN"] = "Link#/FunctionPage/Report/Report.html?ClassName=MESReport.BaseReport.SNReport&RunFlag=1&SN=" + snListTable.Rows[i]["SN"].ToString();
linkRow["STATION"] = "";
linkRow["EDIT_TIME"] = "";
linkRow["PANEL"] = "";
linkTable.Rows.Add(linkRow);
}
ReportTable reportTable = new ReportTable();
reportTable.LoadData(snListTable, linkTable);
reportTable.Tittle = "SNList";
//reportTable.ColNames.RemoveAt(0);
Outputs.Add(reportTable);
}
catch (Exception exception)
{
DBPools["SFCDB"].Return(SFCDB);
throw exception;
}
}
private void ExportTable(ExcelWorksheet workSheet, ReportTable table, ICollection<string> names)
{
if (table == null)
{
throw new ArgumentNullException("table");
}
if (workSheet == null)
{
throw new ArgumentNullException("workSheet");
}
const int firstColumn = 2;
var firstLine = workSheet.Dimension != null ? workSheet.Dimension.End.Row + 2 : 2;
var xlWriter = new ExcelWriter(workSheet, firstLine, firstColumn, names.Count);
xlWriter.PutTableHead(table.Name, names);
ICollection<int> totalRating = null;
foreach (var group in table.ReportGroups)
{
var groupResults = GenerateGroup(xlWriter, group);
totalRating = totalRating == null ? groupResults : SummarizeResults(totalRating, groupResults);
}
xlWriter.PutTableResults(totalRating);
xlWriter.SetGlobalStyles();
}
private void update_report()
{
ReportTable reportTable = this.fReport.CreateTable(this.fReportType, this.fBreakdownType, this.fEncounter);
this.Browser.DocumentText = HTML.EncounterReportTable(reportTable, DisplaySize.Small);
this.Graph.ShowTable(reportTable);
}
private void LoadToTable()
{
InvokeOnMainThread(delegate
{
for (int i = 0; i < 3; i++)
{
/*UIAlertView alert = new UIAlertView()
* {
* Title = "Loading...",
* Message = i.ToString() + " time(s) try"
* };*/
//alert.Show();
var costManager = CostManager.Create();
ReportTable.Source = new ReportResource(costManager.Costs, this);
ReportTable.RowHeight = UITableView.AutomaticDimension;
ReportTable.EstimatedRowHeight = 40f;
ReportTable.ReloadData();
DisplaySuggestion(costManager.Costs);
//alert.Dispose();
}
});
}
static string GenerateTableRow(ReportBuilder reportBuilder, ReportTable table)
{
ReportColumns[] columns = table.ReportDataColumns;
ReportTextBoxControl ColumnCell = new ReportTextBoxControl();
ReportScale colHeight = ColumnCell.Size;
ReportDimensions padding = new ReportDimensions();
if (columns == null)
{
return("");
}
string strTableRow = "";
strTableRow = @"<TablixRow>
<Height>0.6cm</Height>
<TablixCells>";
for (int i = 0; i < columns.Length; i++)
{
ColumnCell = columns[i].ColumnCell;
padding = ColumnCell.Padding;
strTableRow += @"<TablixCell>
<CellContents>
" + GenerateTextBox("txtCell_" + table.ReportName + "_", ColumnCell.Name, "", true, padding) + @"
</CellContents>
</TablixCell>";
}
strTableRow += @"</TablixCells></TablixRow>";
return(strTableRow);
}
public JsonResult getEcozone(string Dev)
{
try
{
var code = Dev.Split('~');
var DevName = code[0];
var EcoName = code[1];
JavaScriptSerializer serializer = new JavaScriptSerializer();
ApplicationDbContext context = new ApplicationDbContext();
//DeveloperDetailsViewModel samp = new DeveloperDetailsViewModel();
ReportTable DeveloperList = new ReportTable();
List <devdetails> samp = new List <devdetails>();
DeveloperDetails1 devd = new DeveloperDetails1();
var userid = User.Identity.GetUserId();
string zoneGroup = context.Users.SingleOrDefault(m => m.Id == userid).ZoneGroup;
var ZoneGroupId = db.ZoneGroup.FirstOrDefault(x => x.ZoneGroupCode == zoneGroup).ZoneGroupId.ToString();
var ZoneCode = db.Zone.Where(x => x.ZoneGroup == ZoneGroupId).Select(x => x.ZoneCode).ToString();
if (Dev.Length >= 4)
{
var dev = db.Database.SqlQuery <DeveloperDetails>("SELECT DISTINCT Dev_Comp_Code, Zone_Code from AdminFees left join Zones on AdminFees.Zone_Code = Zones.ZoneCode where AdminFees.Developer = '" + DevName + "' AND AdminFees.Ecozone = '" + EcoName + "'").ToList().Take(1);
return(Json(dev));
}
}
catch { }
return(Json(null));
}
private void CurrentToggle_CheckedChanged(object sender, EventArgs e)
{
ClearTables();
prod_data.Clear();
current_month = !current_month;
// clear data and tables, reset sales and renenue count
_totalSales = 0;
_totalRevenue = 0;
if (current_month)
{
_to = DateTime.Now;
_from = firstDayOfThisMonth;
}
else
{
_to = firstDayOfThisMonth.AddDays(-1);
_from = firstDayOfThisMonth.AddMonths(-1);
}
_SalesList.Clear();
_SalesList = _PHPRepo.GetSaleByDate(_from, _to);
DisplaySales();
ReportTable.Update();
TotalsTable.Update();
}
static string GetTableColumns(ReportBuilder reportBuilder, ReportTable table)
{
ReportColumns[] columns = table.ReportDataColumns;
ReportTextBoxControl ColumnCell = new ReportTextBoxControl();
if (columns == null)
{
return("");
}
string strColumnHeirarchy = "";
strColumnHeirarchy = @"
<TablixColumns>";
for (int i = 0; i < columns.Length; i++)
{
ColumnCell = columns[i].ColumnCell;
strColumnHeirarchy += @" <TablixColumn>
<Width>" + ColumnCell.Size.Width.ToString() + @"cm</Width>
</TablixColumn>";
}
strColumnHeirarchy += @"</TablixColumns>";
return(strColumnHeirarchy);
}
private static List <CalculatedClauseItem> GetSummaryCalcFields(LoginUser loginUser, SummaryReport summaryReport)
{
List <CalculatedClauseItem> result = new List <CalculatedClauseItem>();
ReportSubcategory sub = ReportSubcategories.GetReportSubcategory(loginUser, summaryReport.Subcategory);
ReportTables tables = new ReportTables(loginUser);
tables.LoadAll();
ReportTableFields tableFields = new ReportTableFields(loginUser);
tableFields.LoadAll(false);
TimeSpan offset = loginUser.Offset;
foreach (ReportSummaryCalculatedField field in summaryReport.Fields.Calculated)
{
StringBuilder builder = new StringBuilder();
if (field.Field.IsCustom)
{
CustomField customField = (CustomField)CustomFields.GetCustomField(loginUser, field.Field.FieldID);
if (customField == null)
{
continue;
}
string fieldName = DataUtils.GetReportPrimaryKeyFieldName(customField.RefType);
if (fieldName != "")
{
fieldName = DataUtils.GetCustomFieldColumn(loginUser, customField, fieldName, true, false);
if (customField.FieldType == CustomFieldType.DateTime)
{
fieldName = string.Format("CAST(SWITCHOFFSET(TODATETIMEOFFSET({0}, '+00:00'), '{1}{2:D2}:{3:D2}') AS DATETIME)",
fieldName,
offset < TimeSpan.Zero ? "-" : "+",
Math.Abs(offset.Hours),
Math.Abs(offset.Minutes));
}
result.Add(GetCalcItem(fieldName, customField.Name, field));
}
}
else
{
ReportTableField tableField = tableFields.FindByReportTableFieldID(field.Field.FieldID);
ReportTable table = tables.FindByReportTableID(tableField.ReportTableID);
string fieldName = table.TableName + "." + tableField.FieldName;
if (tableField.DataType.Trim().ToLower() == "datetime")
{
fieldName = string.Format("CAST(SWITCHOFFSET(TODATETIMEOFFSET({0}, '+00:00'), '{1}{2:D2}:{3:D2}') AS DATETIME)",
fieldName,
offset < TimeSpan.Zero ? "-" : "+",
Math.Abs(offset.Hours),
Math.Abs(offset.Minutes));
}
result.Add(GetCalcItem(fieldName, tableField.Alias, field));
}
}
return(result);
}
public override object GetContent()
{
DIV div = new DIV();
foreach (var ccy in Program.CurFile.Book.EnumCommodities().OrderBy(cmdty => cmdty.Identifier))
{
div.Add(new H2(ccy.Identifier));
if (ccy.Identifier == Program.CurFile.Book.BaseCurrencyId)
{
div.Add(new P(Tr.PgExRates.BaseCurrencyMessage)
{
class_ = "aw-info-msg"
});
}
else
{
ReportTable tbl = new ReportTable();
tbl.AddCol(Tr.PgExRates.ColDate);
tbl.AddCol(Tr.PgExRates.ColRate);
tbl.AddCol(Tr.PgExRates.ColRateInverse);
foreach (var pt in ccy.ExRate)
{
tbl.AddRow(null, pt.Key.ToShortDateString(), pt.Value.ToString("0.0000"), (1m / pt.Value).ToString("0.0000"));
}
div.Add(tbl.GetHtml());
}
}
return(div);
}
public override void Run()
{
DateTime stime = Convert.ToDateTime(StartTime.Value);
DateTime etime = Convert.ToDateTime(EndTime.Value);
string svalue = stime.ToString("yyyy/MM/dd HH:mm:ss");
string evalue = etime.ToString("yyyy/MM/dd HH:mm:ss");
OleExec SFCDB = DBPools["SFCDB"].Borrow();
try
{
string sqlOba = $@" SELECT* FROM R_LOT_STATUS WHERE EDIT_TIME BETWEEN TO_DATE('{svalue}', 'YYYY/MM/DD HH24:MI:SS')
AND TO_DATE('{evalue}', 'YYYY/MM/DD HH24:MI:SS')";
DataSet res = SFCDB.RunSelect(sqlOba);
ReportTable retTab = new ReportTable();
retTab.LoadData(res.Tables[0], null);
retTab.Tittle = "OBA";
retTab.ColNames.RemoveAt(0);//不顯示ID列 add by fgg 2018.03.09
Outputs.Add(retTab);
DBPools["SFCDB"].Return(SFCDB);
}
catch (Exception ee)
{
DBPools["SFCDB"].Return(SFCDB);
throw ee;
}
}
public void UseTicketRights(int subCAtID, ReportTables tables, SqlCommand command, StringBuilder builder)
{
ReportSubcategory subCat = ReportSubcategories.GetReportSubcategory(_loginUser, subCAtID);
if (subCat != null)
{
ReportTable catTable = tables.FindByReportTableID((int)subCat.ReportCategoryTableID);
if (catTable.UseTicketRights)
{
GetUserRightsClause(command, builder, catTable.TableName);
return;
}
else if (catTable.ReportTableID == 6)
{
GetCustomerUserRightsClause(command, builder, catTable.TableName);
}
if (subCat.ReportTableID != null)
{
ReportTable reportTable = tables.FindByReportTableID((int)subCat.ReportTableID);
if (reportTable.UseTicketRights)
{
GetUserRightsClause(command, builder, reportTable.TableName);
return;
}
else if (reportTable.ReportTableID == 6)
{
GetCustomerUserRightsClause(command, builder, reportTable.TableName);
}
}
}
}
static string GenerateTableHeaderRow(ReportBuilder reportBuilder, ReportTable table)
{
ReportColumns[] columns = table.ReportDataColumns;
ReportTextBoxControl ColumnCell = new ReportTextBoxControl();
ReportDimensions padding = new ReportDimensions();
if (columns == null)
{
return("");
}
string strTableRow = "";
strTableRow = @"<TablixRow>
<Height>0.6cm</Height>
<TablixCells>";
for (int i = 0; i < columns.Length; i++)
{
ColumnCell = columns[i].ColumnCell;
padding = columns[i].HeaderColumnPadding;
strTableRow += @"<TablixCell>
<CellContents>
" + GenerateTextBox("txtHeader_" + table.ReportName + "_", ColumnCell.Name, columns[i].HeaderText == null || columns[i].HeaderText.Trim() == "" ? ColumnCell.Name : columns[i].HeaderText, false, padding) + @"
</CellContents>
</TablixCell>";
}
strTableRow += @"</TablixCells></TablixRow>";
return(strTableRow);
}
public override void Run()
{
if (SN.Value == null || SN.Value.ToString().Trim().Length <= 0)
{
throw new Exception("SN Can not be null");
}
// string runSql = string.Format(Sqls["strGetWoSN"], WO.Value.ToString());
string runSql = "select * from r_sn where sn=:snno ";
string strSn = SN.Value.ToString().Trim();
RunSqls.Add(runSql);
OleExec SFCDB = DBPools["SFCDB"].Borrow();
try
{
OleDbParameter[] paramet = new OleDbParameter[] {
new OleDbParameter(":snno", strSn)
};
DataTable res = SFCDB.ExecuteDataTable(runSql, CommandType.Text, paramet);
ReportTable retTab = new ReportTable();
retTab.LoadData(res, null);
retTab.Tittle = "SN RMA Message";
retTab.ColNames.RemoveAt(0);
Outputs.Add(retTab);
DBPools["SFCDB"].Return(SFCDB);
}
catch (Exception ex)
{
DBPools["SFCDB"].Return(SFCDB);
throw ex;
}
}
void update_report()
{
ReportTable table = fReport.CreateTable(fReportType, fBreakdownType, fEncounter);
Browser.DocumentText = HTML.EncounterReportTable(table, Session.Preferences.TextSize);
Graph.ShowTable(table);
}
public void ShowEncounterReportTable(ReportTable table)
{
string html = HTML.EncounterReportTable(table, Session.Preferences.PlayerViewTextSize);
set_html(html);
Show();
}
void Demo()
{
var data = TestData.CreateData();
var table = new ReportTable(new PaymentPerHourKpi(), data, "left");
var sb = new StringBuilder();
table.Render(sb);
new OutputFile().Write(sb.ToString());
}
internal void ShowTable(ReportTable table)
{
this.fBreakdown = new Dictionary <string, int>();
foreach (ReportRow row in table.Rows)
{
this.fBreakdown[row.Heading] = row.Total;
}
base.Invalidate();
}
private void PlayerViewBtn_Click(object sender, EventArgs e)
{
if (Session.PlayerView == null)
{
Session.PlayerView = new PlayerViewForm(this);
}
ReportTable reportTable = this.fReport.CreateTable(this.fReportType, this.fBreakdownType, this.fEncounter);
Session.PlayerView.ShowEncounterReportTable(reportTable);
}
public static string GetReportTable(RestCommand command, int reportTableID)
{
ReportTable reportTable = ReportTables.GetReportTable(command.LoginUser, reportTableID);
if (reportTable.OrganizationID != command.Organization.OrganizationID)
{
throw new RestException(HttpStatusCode.Unauthorized);
}
return(reportTable.GetXml("ReportTable", true));
}
public static Dictionary <int, string> GetLookupValues(LoginUser loginUser, int reportTableFieldID, string term, int maxRows)
{
Dictionary <int, string> result = new Dictionary <int, string>();
ReportTableField field = ReportTableFields.GetReportTableField(loginUser, reportTableFieldID);
if (field == null || field.LookupTableID == null)
{
return(null);
}
ReportTable table = ReportTables.GetReportTable(loginUser, (int)field.LookupTableID);
SqlCommand command = new SqlCommand();
string[] orgs = table.OrganizationIDFieldName.Split(',');
StringBuilder orgFields = new StringBuilder("(");
foreach (String s in orgs)
{
if (orgFields.Length > 1)
{
orgFields.Append(" OR " + s + " = @OrganizationID");
}
else
{
orgFields.Append(s + " = @OrganizationID");
}
}
orgFields.Append(")");
string text = "SELECT TOP {0} {1} AS Label, {2} AS ID FROM {3} WHERE {4} AND {1} LIKE '%' + @Term + '%' ORDER BY {5}";
command.CommandText = string.Format(text,
maxRows.ToString(),
table.LookupDisplayClause,
table.LookupKeyFieldName,
table.TableName,
orgFields.ToString(),
table.LookupOrderBy);
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("@Term", term);
command.Parameters.AddWithValue("@OrganizationID", loginUser.OrganizationID);
DataTable dataTable = SqlExecutor.ExecuteQuery(loginUser, command);
if (field.LookupTableID == 11 || field.LookupTableID == 17)
{
result.Add(-2, "The Report Viewer");
}
//result.Add(-1, "Unassigned");
foreach (DataRow row in dataTable.Rows)
{
result.Add((int)row[1], row[0].ToString());
}
return(result);
}
public static object PublishFile(string template, string report, dynamic dy, bool isDownload = true)
{
var result = new ReportTable
{
ErrCode = 0,
ErrMsg = "ok"
};
try
{
var appSettings = new AppSettings();
var contentRootPath = appSettings.Get("HostingEnvironment.ContentRootPath", HostContext.AppHost.MapProjectPath("~/"));
var templatePath = HostContext.AppHost.MapProjectPath($"{ Path.Combine(contentRootPath, "template")}");
var reportPath = HostContext.AppHost.MapProjectPath($"{ Path.Combine(contentRootPath, "report")}");
if (!Directory.Exists(templatePath))
{
Directory.CreateDirectory(templatePath);
}
if (!Directory.Exists(reportPath))
{
Directory.CreateDirectory(reportPath);
}
var templateFilePath = Path.Combine(templatePath, template);
var reportFilePath = Path.Combine(reportPath, report);
Console.WriteLine("templateFilePath:" + templateFilePath);
Console.WriteLine("reportFilePath:" + reportFilePath);
FileInfo templateFile = new FileInfo(templateFilePath);
//将现有文件复制到新文件，不允许覆盖现有文件
templateFile.CopyTo(reportFilePath);
if (Report(reportFilePath, dy))
{
result.FilePath = reportFilePath;
if (isDownload)
{
var obj = FileDownload.Download(report, "report");
return(obj);
}
}
return(result);
}
catch (Exception ex)
{
Console.WriteLine("PublishFile报错:" + ex.Message);
return(result);
}
}
// GET: VManagerReports
public ActionResult WaybillStatistics(uint clientId, ReportTable <WaybillStatisticsFilter, WaybillStatisticsData> model)
{
model.TableHead.Headers = new[] { "Поставщик", "Аптека", "Количество заявок на данного поставщика", " Количество накладных", "Количество разобранных накладных" };
model.TableFilter.ClientId = clientId;
model.TablePaginator.TotalItems = WaybillStatisticsData.GetReportTotalItemsNumber(DbSession, model);
model.TableData = WaybillStatisticsData.GetReportData(DbSession, model);
//классический интерфейс
ViewBag.ClassicStyle = true;
return(View(model));
}
public ReportAccounts(GncAccount baseacct, HttpRequest request, bool autoAddAcct, bool autoAddCol)
{
_baseAcct = baseacct;
_request = request;
_autoAddAcct = autoAddAcct;
_autoAddCol = autoAddCol;
Table = new ReportTable();
_rowMap = new Dictionary <GncAccount, ReportTable.Row>();
_colMap = new Dictionary <object, ReportTable.Col>();
_colAcctName = Table.AddCol(Program.Tr.ReportTable_ColAccount, "acct_name");
}
/// <summary>
/// 下载文件
/// </summary>
/// <param name="template"></param>
/// <returns></returns>
public static object Download(string file, string folder)
{
var response = new ReportTable
{
ErrCode = 0,
ErrMsg = "ok"
};
try
{
var appSettings = new AppSettings();
var contentRootPath = appSettings.Get("HostingEnvironment.ContentRootPath", HostContext.AppHost.MapProjectPath("~/"));
var folderPath = HostContext.AppHost.MapProjectPath($"{ Path.Combine(contentRootPath, folder)}");
var filePath = Path.Combine(folderPath, file);
if (!System.IO.File.Exists(filePath))
{
response.ErrCode = -1;
response.ErrMsg = $"文件[{filePath}]不存在";
return(response);
}
var reportBytes = System.IO.File.ReadAllBytes(filePath);
var extension = Path.GetExtension(filePath);
var mimeType = MimeMapping.GetMimeType(extension.ToLower());
var result = new HttpResult(reportBytes, mimeType);
string fileName = HttpUtility.UrlEncode(Path.GetFileName(filePath));
if (file.StartsWith("pc"))
{
if (file.Contains("_"))
{
var segs = file.Split("_");
fileName = segs[1] + extension;
}
}
result.Headers.Add("Content-Disposition", $"inline;filename={fileName};");
result.StatusCode = HttpStatusCode.OK;
return(result);
}
catch (Exception ex)
{
response.ErrCode = -1;
response.ErrMsg = ex.Message;
return(response);
}
}
public static List <WaybillStatisticsData> GetReportData(ISession dbSession,
ReportTable <WaybillStatisticsFilter, WaybillStatisticsData> reportTable)
{
var sqlString = @"
SELECT
DISTINCT oh.clientcode as ClientId, c.Name ClientName, pd.FirmCode as SupplierId, s.Name SupplierName,
COUNT(DISTINCT oh.rowid) SupplierOrdersNumber,
COUNT(DISTINCT dl.rowid) WaybillsTotalNumber,
COUNT(DISTINCT dh.id) WaybillsParsedNumber,
COUNT(DISTINCT IF(db.ProductId IS NULL,db.DocumentId, NULL))notwhellknown
FROM (orders.ordershead oh,
usersettings.pricesdata pd,
customers.Suppliers s,
customers.clients c)
LEFT JOIN logs.Document_Logs dl
ON c.id=dl.ClientCode
AND dl.DocumentType=1
AND logtime>=@periodStart
AND oh.writetime<@periodFinish
AND pd.firmcode=dl.FirmCode
LEFT JOIN documents.documentheaders dh
ON dh.DownloadId=dl.rowid AND dh.ClientCode=oh.ClientCode
LEFT JOIN documents.documentbodies db
ON db.DocumentId=dh.Id
WHERE
c.Id = @ClientId
AND pd.firmcode=s.id
AND oh.pricecode=pd.PriceCode
AND oh.writetime>=@periodStart
AND oh.writetime<@periodFinish
AND c.id=oh.ClientCode
GROUP BY pd.firmcode, oh.ClientCode
{0}
{1}
";
string orderby = String.Format("order by {0} {1}",
_sortOrder[Math.Abs(reportTable.TableHead.SortOrder) - 1],
(reportTable.TableHead.SortOrder > 0) ? "asc" : "desc");
string limit = String.Format("limit {0}, {1}",
reportTable.TablePaginator.CurrentPage * reportTable.TablePaginator.PageSize,
reportTable.TablePaginator.PageSize);
return
(dbSession.Connection.Query <WaybillStatisticsData>
(String.Format(sqlString, orderby, limit),
new {
@clientId = reportTable.TableFilter.ClientId,
@periodStart = reportTable.TableFilter.DateBegin.Date,
@periodFinish = reportTable.TableFilter.DateEnd.Date.AddDays(1),
}).ToList());
}
public override void Run()
{
//base.Run();
string sn = snObj.Value.ToString();
string wo = woObj.Value.ToString();
string runSql = $@" select sn.skuno,sn.workorderno,sn.sn,kp.value,kp.partno,kp.kp_name,kp.mpn,kp.scantype,kp.itemseq,
kp.scanseq,kp.detailseq,kp.station,kp.valid_flag,kp.edit_time,kp.edit_emp from r_sn_kp kp, r_sn sn
where kp.r_sn_id = sn.id and sn.valid_flag = '1' ";
if (sn == "" && wo == "")
{
ReportAlart alart = new ReportAlart("Please input a sn or wo");
Outputs.Add(alart);
return;
}
if (sn != "")
{
runSql = runSql + $@" and (sn.sn='{sn}' or sn.boxsn='{sn}') ";
}
if (wo != "")
{
runSql = runSql + $@" and sn.workorderno='{wo}' ";
}
OleExec sfcdb = DBPools["SFCDB"].Borrow();
DataTable dt = new DataTable();
try
{
dt = sfcdb.RunSelect(runSql).Tables[0];
if (sfcdb != null)
{
DBPools["SFCDB"].Return(sfcdb);
}
if (dt.Rows.Count == 0)
{
throw new Exception("NO data");
}
ReportTable reportTable = new ReportTable();
reportTable.LoadData(dt, null);
reportTable.Tittle = "SN keypart detail";
Outputs.Add(reportTable);
}
catch (Exception ex)
{
if (sfcdb != null)
{
DBPools["SFCDB"].Return(sfcdb);
}
ReportAlart alart = new ReportAlart(ex.Message);
Outputs.Add(alart);
}
}
public override void Run()
{
if (WO.Value == null)
{
throw new Exception("WO Can not be null");
}
string runSql = string.Format(Sqls["strGetWoSN"], WO.Value.ToString());
RunSqls.Add(runSql);
OleExec SFCDB = DBPools["SFCDB"].Borrow();
try
{
DataSet res = SFCDB.RunSelect(runSql);
ReportTable retTab = new ReportTable();
retTab.LoadData(res.Tables[0], null);
retTab.Tittle = "SN List";
retTab.ColNames.RemoveAt(0);
// piechart
pieChart retChart_pie = new pieChart();
retChart_pie.GetSample();
Outputs.Add(retChart_pie);
//linechart
LineChart retChart_line = new LineChart();
retChart_line.GetSample1();
LineChart retChart_spline = new LineChart();
retChart_spline.GetSample2();
LineChart retChart_area = new LineChart();
retChart_area.GetSample3();
//columnChart
columnChart retChart_column = new columnChart();
retChart_column.GetSample1();
Outputs.Add(retChart_column);
Outputs.Add(retChart_line);
Outputs.Add(retChart_spline);
Outputs.Add(retChart_area);
Outputs.Add(retTab);
DBPools["SFCDB"].Return(SFCDB);
}
catch (Exception ee)
{
DBPools["SFCDB"].Return(SFCDB);
}
}
public virtual void ProcessData()
{
foreach (User user in RawData)
{
DataRow row = ReportTable.NewRow();
row[0] = user.FirstName;
row[1] = user.LastName;
row[2] = user.LoginName;
row[3] = GetUserLastLogin(user);
ReportTable.Rows.Add(row);
}
}
