} // DatabaseRequestViewModel
// присвоение данных для контейнеров входящих данных/рузульлтатов
private void SetValues()
{
// данные для первого запроса
Query01Data = _context.GetStateNumbers();
// данные для второго запроса
Query02Owners = _context.GetOwners();
Query02Result = new ObservableCollection <Car>();
// данные для третьего запроса
Query03Owners = _context.GetOwners();
Query3Result = new ObservableCollection <Malfunction>();
// данные для четвертого запроса
Query04Malfunctions = _context.GetMalfunctionsStr();
Query04Owners = _context.GetOwners();
// данные для пятого запроса
Query05Malfunctions = _context.GetMalfunctionsStr();
// данные для шестого запроса
Query06Marks = _context.GetMarksStr();
// данные для седьмого запроса
Query07Result = _context.Query07();
// справка
ReferenceCountOfAuto = _context.GetOrders().Count(o => !o.IsReady);
ReferenceFreeWorkers = _context.GetWorkersAtWorkAndFree().Count();
// месячный отчет
MonthReport = new MonthReport(_context.GetMonthRepairOrders().ToList());
} // SetValues
protected void OnAction14Activated(object sender, EventArgs e)
{
MonthReport report = new MonthReport();
report.Show();
report.Run();
report.Destroy();
}
public bool AddMonthReport(int month, int year, MonthReport newInstance)
{
var db = new MaterialMeasurementEntities();
try
{
var monthReport = db.MonthReports.Where(i => i.MaterialId == newInstance.MaterialId &&
i.ParcelCode == newInstance.ParcelCode && i.ReportDate.Month == month &&
i.ReportDate.Year == year).FirstOrDefault();
if (monthReport == null)
{
db.MonthReports.Add(newInstance);
SaveChanges(db);
}
if (monthReport != null)
{
monthReport.RemainQuantity = monthReport.RemainQuantity == 0 ? newInstance.RemainQuantity : monthReport.RemainQuantity;
monthReport.ImportQuantity = monthReport.ImportQuantity == 0 ? newInstance.ImportQuantity : monthReport.ImportQuantity;
monthReport.ExportQuantity = monthReport.ExportQuantity == 0 ? newInstance.ExportQuantity : monthReport.ExportQuantity;
db.Entry(monthReport).State = System.Data.Entity.EntityState.Modified;
SaveChanges(db);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return(false);
}
return(true);
}
private async Task <MonthReport> GetMonthReport(ElementType type, int year, int month, int entityId)
{
var report = await GetBaseReport(type, new DateTime(year, month, 1),
new DateTime(year, month, 1).AddMonths(1).AddSeconds(-1), entityId, "dd.MM");
MonthReport monthReport = new MonthReport();
monthReport.Entities = report.Entities;
monthReport.BarChart = report.BarChart;
monthReport.Years = GetYears(year);
monthReport.Months = GetMonths(month);
return(monthReport);
}
private static Collection<MonthReport> ReadReport(SqlDataReader reader)
{
Collection<MonthReport> report = new Collection<MonthReport>();
while (reader.Read())
{
int i = -1;
MonthReport mr = new MonthReport
{
Date = reader.GetDateTime(++i),
Surename = reader.GetString(++i),
Adress = reader.GetString(++i),
AbsVolume = reader.GetDouble(++i),
MaterialName = reader.GetString(++i),
Payed = reader.GetBoolean(++i),
Price = reader.GetDouble(++i)
};
report.Add(mr);
}
return report;
}
//we dont use this method :3
public bool Add(MonthReport newInstance)
{
var db = new MaterialMeasurementEntities();
try
{
var monthReport = db.MonthReports.Where(i => i.MaterialId == newInstance.MaterialId &&
i.ParcelCode == newInstance.ParcelCode && i.ReportDate == newInstance.ReportDate.Date);
if (monthReport.Count() == 0)
{
db.MonthReports.Add(newInstance);
SaveChanges(db);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return(false);
}
return(true);
}
private static void Save(string filename, string symbolId, ISeries<IMarketPosition> positions) {
if (File.Exists(filename)) {
File.Delete(filename);
}
PositionSeries cPositions = positions as PositionSeries;
StringBuilder cBuilder = new StringBuilder(1024 * 1024);
cBuilder.Append("NO.").Append(',').Append("SymbolID").Append(',').Append("Category").Append(',').Append("Action").Append(',').Append("Volume").Append(',').Append("Price").Append(',').Append("Profit").Append(',').Append("Fee").Append(',').Append("Tax").Append(',').Append("Trading time").Append(',').Append("Description").Append(',').Append("MaxRunUp").Append(',').AppendLine("MaxDrawDown");
Dictionary<string, MonthReport> cMonthRpts = new Dictionary<string, MonthReport>();
double dWin = 0, dLoss = 0, dWinT = 0, dLossT = 0, dLossCount = 0, dMaxLossCount = 0;
int iCount = cPositions.Count;
for (int i = 0; i < iCount; i++) {
IMarketPosition cPosition = cPositions[i];
if (cPosition.Value > 0) {
List<ITrade> cTrades = cPosition.ClosedTrades;
foreach (ITrade cTrade in cTrades) {
ITradeOrder cOpenO = cTrade.EntryOrder;
ITradeOrder cCloseO = cTrade.ExitOrder;
MonthReport cReport = null;
string sMonth = cCloseO.Time.ToString("yyyy/MM");
if (!cMonthRpts.TryGetValue(sMonth, out cReport)) {
cReport = new MonthReport();
cMonthRpts.Add(sMonth, cReport);
}
++cReport.Count;
double dProfit = cTrade.Profit;
if (dProfit < 0) {
++dLoss;
dLossT += dProfit;
++dLossCount;
++cReport.LossCount;
cReport.LossTotals += dProfit;
cReport.SetMaxLossCount(dLossCount);
cReport.SetMaxRunUp(dProfit);
cReport.SetMaxDrawDown(dProfit);
if (dLossCount > dMaxLossCount) {
dMaxLossCount = dLossCount;
}
} else {
++dWin;
dWinT += dProfit;
dLossCount = 0;
++cReport.WinCount;
cReport.WinTotals += dProfit;
cReport.SetMaxRunUp(dProfit);
cReport.SetMaxDrawDown(dProfit);
}
cBuilder.Append(cOpenO.Ticket).Append(',').Append(symbolId).Append(',').Append(cOpenO.Category).Append(',').Append(cOpenO.Action).Append(',').Append(cOpenO.Contracts).Append(',').Append(cOpenO.Price).Append(',').Append(string.Empty).Append(',').Append(cOpenO.Fee).Append(',').Append(cOpenO.Tax).Append(',').Append(cOpenO.Time.ToString("yyyy/MM/dd HH:mm:ss")).Append(',').Append(cOpenO.Name).Append(',').Append(string.Empty).Append(',').AppendLine(string.Empty);
cBuilder.Append(cCloseO.Ticket).Append(',').Append(symbolId).Append(',').Append(cCloseO.Category).Append(',').Append(cCloseO.Action).Append(',').Append(cCloseO.Contracts).Append(',').Append(cCloseO.Price).Append(',').Append(cTrade.Profit).Append(',').Append(cCloseO.Fee).Append(',').Append(cCloseO.Tax).Append(',').Append(cCloseO.Time.ToString("yyyy/MM/dd HH:mm:ss")).Append(',').Append(cCloseO.Name).Append(',').Append(cPosition.MaxRunUp.ToString()).Append(',').AppendLine(cPosition.MaxDrawDown.ToString());
}
}
}
cBuilder.AppendLine();
cBuilder.Append(',').Append("Max Loss:").Append(',').AppendLine(dMaxLossCount.ToString());
cBuilder.Append(',').Append("Win totals:").Append(',').Append(dWinT.ToString()).Append(',').Append(',').Append("Loss totals:").Append(',').AppendLine(dLossT.ToString());
cBuilder.Append(',').Append("Win (%):").Append(',').Append((dWin / iCount * 100).ToString("F2")).Append(',').Append(',').Append("Loss (%):").Append(',').AppendLine((dLoss / iCount * 100).ToString("F2"));
cBuilder.AppendLine();
cBuilder.Append(',').Append("Month").Append(',').Append("Win totals").Append(',').Append("Win (%)").Append(',').Append("Loss totals").Append(',').Append("Loss (%)").Append(',').Append("Max Loss").Append(',').Append("MaxRunUp").Append(',').AppendLine("MaxDrawDown");
foreach (string sMonth in cMonthRpts.Keys) {
MonthReport cReport = cMonthRpts[sMonth];
cBuilder.Append(',').Append(sMonth).Append(',').Append(cReport.WinTotals).Append(',').Append((cReport.WinCount / cReport.Count * 100).ToString("F2")).Append(',').Append(cReport.LossTotals).Append(',').Append((cReport.LossCount / cReport.Count * 100).ToString("F2")).Append(',').Append(cReport.MaxLossCount.ToString()).Append(',').Append(cReport.MaxRunUp).Append(',').AppendLine(cReport.MaxDrawDown.ToString());
}
File.WriteAllText(filename, cBuilder.ToString(), Encoding.UTF8);
}
public static void SetMonthReport(this AcroFields fields, CardOrder cardOrder, MonthReport report)
{
//var initIndex = getInitIndexForMonth(formNumber, report.Month);
var formNumber = cardOrder == CardOrder.First ? "1" : "2";
//fields.SetFieldProperty("Text" + (initIndex + 1), "textsize", 8f, null);
fields.SetField($"{formNumber}-Place_{report.Month}", report.Placements.ToString());
//fields.SetFieldProperty("Text" + (initIndex + 2), "textsize", 8f, null);
fields.SetField($"{formNumber}-Video_{report.Month}", report.VideoShowings.ToString());
//fields.SetFieldProperty("Text" + (initIndex + 3), "textsize", 8f, null);
fields.SetField($"{formNumber}-Hours_{report.Month}", report.Hours.ToString());
//fields.SetFieldProperty("Text" + (initIndex + 4), "textsize", 8f, null);
fields.SetField($"{formNumber}-RV_{report.Month}", report.ReturnVisits.ToString());
//fields.SetFieldProperty("Text" + (initIndex + 5), "textsize", 8f, null);
fields.SetField($"{formNumber}-Studies_{report.Month}", report.Studies.ToString());
//fields.SetFieldProperty("Text" + (initIndex + 6), "textsize", 8f, null);
//fields.SetField("Text" + (initIndex + 6), report.Remarks);
}
public static void SetTotalReport(this AcroFields fields, CardOrder cardOrder, MonthReport report)
{
var initIndex = getInitIndexForMonth(formNumber, 13);
fields.SetFieldProperty("Text" + (initIndex + 1), "textsize", 8f, null);
fields.SetField("Text" + (initIndex + 1), report.Placements.ToString());
fields.SetFieldProperty("Text" + (initIndex + 2), "textsize", 8f, null);
fields.SetField("Text" + (initIndex + 2), report.VideoShowings.ToString());
fields.SetFieldProperty("Text" + (initIndex + 3), "textsize", 8f, null);
fields.SetField("Text" + (initIndex + 3), report.Hours.ToString());
fields.SetFieldProperty("Text" + (initIndex + 4), "textsize", 8f, null);
fields.SetField("Text" + (initIndex + 4), report.ReturnVisits.ToString());
fields.SetFieldProperty("Text" + (initIndex + 6), "textsize", 8f, null);
fields.SetField("Text" + (initIndex + 6), report.Remarks);
}
static YearRecord getTotals(IList <YearRecord> records, PublisherTypes type)
{
var rv = new YearRecord()
{
Anointed = false,
DateOfBirth = null,
E = false,
Gender = null,
HomeAddress = string.Empty,
HomeTelephone = string.Empty,
ImmersedDate = null,
MobileTelephone = string.Empty,
MS = false,
Number = null,
RP = false,
Year = records.FirstOrDefault().Year
};
switch (type)
{
case PublisherTypes.RegularPioneer:
rv.Name = "PRECURSORES REGULARES";
break;
case PublisherTypes.AuxiliarPioneer:
rv.Name = "PRECURSORES AUXILIARES";
break;
case PublisherTypes.Publisher:
default:
rv.Name = "PUBLICADORES";
break;
}
var monthsCount = records.Max(r => r.Reports.Count);
//del 1 al 12
for (int month = 1; month <= monthsCount; month++)
{
var monthReport = new MonthReport
{
Month = month,
Placements = records.Sum(yr => yr.Reports.Where(mr => mr.Month == month && mr.Type == type).Sum(mr => mr.Placements)),
VideoShowings = records.Sum(yr => yr.Reports.Where(mr => mr.Month == month && mr.Type == type).Sum(mr => mr.VideoShowings)),
Hours = records.Sum(yr => yr.Reports.Where(mr => mr.Month == month && mr.Type == type).Sum(mr => mr.Hours)),
ReturnVisits = records.Sum(yr => yr.Reports.Where(mr => mr.Month == month && mr.Type == type).Sum(mr => mr.ReturnVisits)),
Studies = records.Sum(yr => yr.Reports.Where(mr => mr.Month == month && mr.Type == type).Sum(mr => mr.Studies)),
Remarks = records.SelectMany(yr => yr.Reports).Count(mr => mr.Month == month && mr.Type == type).ToString()
};
rv.Reports.Add(monthReport);
}
rv.Totals = new MonthReport
{
Placements = rv.Reports.Sum(r => r.Placements),
VideoShowings = rv.Reports.Sum(r => r.VideoShowings),
Hours = rv.Reports.Sum(r => r.Hours),
ReturnVisits = rv.Reports.Sum(r => r.ReturnVisits)
};
return(rv);
}
public bool Edit(MonthReport newInstance)
{
throw new NotImplementedException();
}
// add security monthly by year and month
public async Task <string> AddSeStockMonthRevenue(string yearMonth)
{
try
{
string url = String.Format("https://www.twse.com.tw/statistics/count?url=%2FstaticFiles%2Finspection%2Finspection%2F04%2F003%2F{0}_C04003.zip&l1=%E4%B8%8A%E5%B8%82%E5%85%AC%E5%8F%B8%E6%9C%88%E5%A0%B1&l2=%E3%80%90%E5%9C%8B%E5%85%A7%E4%B8%8A%E5%B8%82%E5%85%AC%E5%8F%B8%E7%87%9F%E6%A5%AD%E6%94%B6%E5%85%A5%E5%BD%99%E7%B8%BD%E8%A1%A8%E3%80%91%E6%9C%88%E5%A0%B1", yearMonth);
WebClient webClient = new WebClient();
byte[] dataByte = webClient.DownloadData(url);
byte[] deCompressByte = Extensions.Decompress(dataByte);
MemoryStream stream = new MemoryStream(deCompressByte);
Workbook wb = new Workbook(stream);
Worksheet worksheet = wb.Worksheets[0];
Cells cells = worksheet.Cells;
List <StockBasic> stockList = new List <StockBasic>();
List <StockBasic> dbStockList = _stockBasicDAO.FindAll().ToList();
List <MonthReport> mReportList = new List <MonthReport>();
List <MonthReport> dbMReportList = _monthReportDAO.FindAll().ToList();
string typeName = "";
int startIndex = 10;
int last_row = worksheet.Cells.GetLastDataRow(1) - 2; //最後列不要(總額/平均)
//to save each value to List, 10 is 水泥工業類
for (int i = startIndex; i <= last_row; i++)
{
string columA = cells[i, 0].Value.ToString().Trim(); //取得類別
int stockCode = columA.Split(" ")[0].ToInt();
if (stockCode == 0) //篩選掉英文類別
{
continue;
}
if (stockCode < 100) //篩選掉中文類別
{
typeName = columA;
continue;
}
string[] codeNName = columA.Split(" ");
long monthValu = cells[i, 2].Value.ToLong(); //get 本月 val
long preMonthValu = cells[i, 4].Value.ToLong(); //get 去年本月 val
StockBasic stockModel = new StockBasic();
stockModel.Id = stockCode;
stockModel.Name = codeNName[1];
stockModel.Type = typeName;
stockModel.Size = 1; //上市
//if exist in db not add in addList
if (!dbStockList.Any(x => x.Id == stockModel.Id))
{
stockList.Add(stockModel);
}
MonthReport mReportModel = new MonthReport();
mReportModel.StockId = stockCode;
mReportModel.Revenue = monthValu.ToInt();
mReportModel.PreRevenue = preMonthValu.ToInt();
mReportModel.YearMonth = yearMonth.Trim();
//if exist in db not add in addList
if (!dbMReportList.Any(x => x.StockId == mReportModel.StockId && x.YearMonth == yearMonth.Trim()))
{
mReportList.Add(mReportModel);
}
}
string result = "";
if (stockList.Count > 0)
{
foreach (var model in stockList)
{
_stockBasicDAO.Add(model);
}
if (!await _stockBasicDAO.SaveAll())
{
result = yearMonth;
}
}
if (mReportList.Count > 0)
{
foreach (var model in mReportList)
{
_monthReportDAO.Add(model);
}
if (!await _monthReportDAO.SaveAll())
{
result = yearMonth;
}
}
return(result);
}
catch (Exception ex)
{
return(yearMonth);
}
}
public async Task <string> AddSe2StockMonthRevenue(string yearMonth)
{
try
{
string url = String.Format("https://www.tpex.org.tw/storage/statistic/sales_revenue/O_{0}.xls", yearMonth);
WebClient webClient = new WebClient();
byte[] dataByte = webClient.DownloadData(url);
//byte[] deCompressByte = Extensions.Decompress(dataByte);
MemoryStream stream = new MemoryStream(dataByte);
Workbook wb = new Workbook(stream);
Worksheet worksheet = wb.Worksheets[0];
Cells cells = worksheet.Cells;
List <StockBasic> stockList = new List <StockBasic>();
List <StockBasic> dbStockList = _stockBasicDAO.FindAll().ToList();
List <MonthReport> mReportList = new List <MonthReport>();
List <MonthReport> dbMReportList = _monthReportDAO.FindAll().ToList();
string typeName = "";
int startIndex = 10;
int last_row = cells.MaxDataRow - 5;
//to save each value to List, 10 is 水泥工業類
for (int i = startIndex; i <= last_row; i++)
{
string columA = cells[i, 0].Value.ToString().Trim().Replace(" ", " "); //取得類別
if (columA.Split(" ")[0].ToInt() < 100) //篩選掉中文類別
{
typeName = columA;
continue;
}
long monthValu = cells[i, 2].Value.ToLong(); //get 本月 val
long preMonthValu = cells[i, 2].Value.ToLong(); //get 去年本月 val
string[] codeNName = columA.Split(" ");
StockBasic stockModel = new StockBasic();
stockModel.Id = codeNName[0].ToInt();
stockModel.Name = codeNName[1];
stockModel.Type = typeName;
stockModel.Size = 2; //上櫃
//if exist in db not add in addList
if (!dbStockList.Any(x => x.Id == stockModel.Id))
{
stockList.Add(stockModel);
}
MonthReport mReportModel = new MonthReport();
mReportModel.StockId = codeNName[0].ToInt();
mReportModel.Revenue = monthValu.ToInt();
mReportModel.PreRevenue = preMonthValu.ToInt();
mReportModel.YearMonth = yearMonth.Trim();
//if exist in db not add in addList
if (!dbMReportList.Any(x => x.StockId == mReportModel.StockId && x.YearMonth == yearMonth.Trim()))
{
mReportList.Add(mReportModel);
}
}
//第二頁
Worksheet worksheet2 = wb.Worksheets[1];
Cells cells2 = worksheet2.Cells;
int last_row2 = cells2.MaxDataRow - 5;
for (int i = startIndex; i <= last_row2; i++)
{
string columA = cells2[i, 0].Value.ToString().Trim().Replace(" ", " "); //取得類別
if (columA.Split(" ")[0].ToInt() < 100) //篩選掉中文類別
{
typeName = columA;
continue;
}
long monthValu = cells2[i, 2].Value.ToLong(); //get 本月 val
long preMonthValu = cells[i, 2].Value.ToLong(); //get 去年本月 val
string[] codeNName = columA.Split(" ");
StockBasic stockModel = new StockBasic();
stockModel.Id = codeNName[0].ToInt();
stockModel.Name = codeNName[1];
stockModel.Type = typeName;
stockModel.Size = 2; //上櫃
//if exist in db not add in addList
if (!dbStockList.Any(x => x.Id == stockModel.Id))
{
stockList.Add(stockModel);
}
MonthReport mReportModel = new MonthReport();
mReportModel.StockId = codeNName[0].ToInt();
mReportModel.Revenue = monthValu.ToInt();
mReportModel.PreRevenue = preMonthValu.ToInt();
mReportModel.YearMonth = yearMonth.Trim();
//if exist in db not add in addList
if (!dbMReportList.Any(x => x.StockId == mReportModel.StockId && x.YearMonth == yearMonth.Trim()))
{
mReportList.Add(mReportModel);
}
}
string result = "";
if (stockList.Count > 0)
{
foreach (var model in stockList)
{
_stockBasicDAO.Add(model);
}
if (!await _stockBasicDAO.SaveAll())
{
result = yearMonth;
}
}
if (mReportList.Count > 0)
{
foreach (var model in mReportList)
{
_monthReportDAO.Add(model);
}
if (!await _monthReportDAO.SaveAll())
{
result = yearMonth;
}
}
return(result);
}
catch (Exception ex)
{
return(yearMonth);
}
}
public JsonResult MonthReportPrint(DataQueryContent queryTime)
{
string str1 = queryTime.bDate;
string str2 = queryTime.eDate;
str1 += " 00:00:00";
str2 += " 23:59:59";
string str3 = str1;
String str4 = queryTime.bDate;
str4 += " 23:59:59";
DateTime bdate = Convert.ToDateTime(str1);
DateTime edate = Convert.ToDateTime(str2);
DateTime bdate_B = Convert.ToDateTime(str3);
DateTime bdate_E = Convert.ToDateTime(str4);
if (bdate > edate)
{
return(Json("输入时间不符！"));
}
else
{
//预冲账报表
List <yysj> yysjlist = baseDAL.Get <yysj>().Where(c => c.skrq >= bdate).
Where(c => c.skrq <= edate).
Where(c => c.zfbz == 1).ToList();
List <YchongzhangReport> yczlist = new List <YchongzhangReport>();
for (int i = 0; i < yysjlist.Count; i++)
{
YchongzhangReport ychongzhang = new YchongzhangReport();
ychongzhang.ywbh = yysjlist[i].ywbh;
ychongzhang.skcs = yysjlist[i].skcs;
ychongzhang.skql = yysjlist[i].skql;
ychongzhang.skje = (double)yysjlist[i].skje;
ychongzhang.skrq = yysjlist[i].skrq.ToString("yyyy-MM-dd");
ychongzhang.fpbh = yysjlist[i].fpbh;
yczlist.Add(ychongzhang);
}
//代售点月报表
List <MonthReport> monthReportList = new List <MonthReport>();
while (bdate_E <= edate)
{
var date_Yysj = baseDAL.Get <yysj>().Where(c => c.skrq >= bdate_B).
Where(c => c.skrq <= bdate_E);
int date_cs = date_Yysj.Count();
if (date_cs > 0)
{
double date_ql = date_Yysj.Sum(c => c.skql);
double date_je = (double)date_Yysj.Sum(c => c.skje);
MonthReport mR = new MonthReport
{
date = bdate_B.ToString("yyyy-MM-dd"),
date_cs = date_cs,
date_ql = date_ql,
date_je = date_je
};
monthReportList.Add(mR);
bdate_B = bdate_B.AddDays(1);
bdate_E = bdate_E.AddDays(1);
}
else
{
bdate_B = bdate_B.AddDays(1);
bdate_E = bdate_E.AddDays(1);
}
}
MonthAndYczReport monthAndYczReport = new MonthAndYczReport();
monthAndYczReport.monthReportList = monthReportList;
monthAndYczReport.yczList = yczlist;
if (monthReportList.Count() == 0)
{
return(Json("无营业数据！"));
}
else
{
return(Json(monthAndYczReport));
}
}
}
