/// <summary>
/// Создает отчет по данным, добавленным в текущий источник данных (DataSet)
/// </summary>
/// <returns>Построенный отчет</returns>
public override object GenerateReport()
{
StoreReport report = new StoreReport();
report.SetDataSource(_dataSet);
return(report);
}
public IEnumerable <StoreReport> GetPreAmount(QueryCondition queryCondition)
{
var storedProcedure = "Proc_StoreReport_GetPreItemAmount";
var parameter = new Hashtable();
if (queryCondition.ShopID == null)
{
parameter.Add("ShopID", DBNull.Value);
}
else
{
parameter.Add("ShopID", queryCondition.ShopID);
}
parameter.Add("StartDate", queryCondition.StartDate.ToLocalTime());
using (DataAccess dataAccess = new DataAccess())
{
SqlDataReader dataReader = dataAccess.ExecuteReader(storedProcedure, parameter);
while (dataReader.Read())
{
var item = new StoreReport();
for (int i = 0; i < dataReader.FieldCount; i++)
{
var fieldTableName = dataReader.GetName(i);
var fieldTableValue = dataReader.GetValue(i);
var property = item.GetType().GetProperty(fieldTableName);
if (property != null && fieldTableValue != DBNull.Value)
{
property.SetValue(item, fieldTableValue);
}
}
yield return(item);
}
}
}
public StoreAnalysisModel(StoreReport storeReport)
{
_storeReport = storeReport;
Label = "Store: " + LastCommitTimestamp;
foreach (var bt in storeReport.BTrees)
{
Children.Add(new BTreeAnalysisModel(bt));
}
}
private void SetTotalSumRow(PdfPTable pdfTable, decimal currentSum, StoreReport monthReport)
{
var monthAsString = new DateTimeFormatInfo().GetMonthName(monthReport.Date.Month);
var dateString = monthReport.Date.Day + "-" + monthAsString + "-" + monthReport.Date.Year;
var sumHeaderCell = GetCustomizedCell("Total sum for " + dateString, 2, 4, 6);
var totalSumCell = GetCustomizedCell(String.Format("{0:F2}", currentSum), 2, 1, 6);
pdfTable.AddCell(sumHeaderCell);
pdfTable.AddCell(totalSumCell);
}
public static void InsertSalesReports(StoreReport report)
{
String marketName = report.MarketName;
int marketId = -1;
if (!db.Supermarkets.Where(s => s.SupermarketName == marketName).Any())
{
var market = new Supermarket()
{
SupermarketName = marketName
};
db.Supermarkets.Add(market);
db.SaveChanges();
marketId = market.Id;
}
else
{
marketId = db.Supermarkets.First(s => s.SupermarketName == marketName).Id;
}
if (db.DailyReports.Where(x => x.Day == report.Date &&
x.SuperMarketId == marketId).Any())
{
return;
}
var daily = new DailyReport()
{
Day = report.Date, SuperMarketId = marketId
};
db.DailyReports.Add(daily);
db.SaveChanges();
var reportId = daily.Id;
ProductsData.ProductsModel mysqlModel = new ProductsData.ProductsModel();
foreach (var product in report.Products)
{
var prodId = db.Products.First(p => p.OldId == product.ProductId).Id;
db.SalesReports.Add(new SalesReport()
{
ReportId = reportId,
ProductId = prodId,
Quantity = product.QuantityValue,
UnitPrice = product.UnitPrice,
Sum = product.Sum
});
}
db.SaveChanges();
}
private void doanhThuBánHàngToolStripMenuItem_Click(object sender, EventArgs e)
{
StoreReport store_report = new StoreReport(nhanvien);
store_report.MdiParent = this;
store_report.Dock = DockStyle.Fill;
store_report.Top = 5;
store_report.Left = 5;
store_report.Size = this.ClientRectangle.Size;
store_report.Show();
}
public void DisplayDataToUI(Rule rule, StoreReport report)
{
lbNumBookImport.Text = report.NumBookImport.ToString();
lbNumBookInStock.Text = report.NumBookInStock.ToString();
lbRevenue.Text = report.Revenue.ToString();
lbNumBookSold.Text = report.NumBookSold.ToString();
tbMinImport.Text = rule.MinImport.ToString();
tbMaxInventory.Text = rule.MaxInventory.ToString();
tbMinLastInventory.Text = rule.MinInventory.ToString();
tbMaxIndebtedness.Text = rule.MaxIndebtedness.ToString();
}
public ActionResult CreateReport(string id, string srcontent)
{
//var ord = Session["orderid"].ToString();//訂單編號
var sto = Session["Store"].ToString(); //店家帳號
var cu = db.Order.Where(m => m.orderguid == id).FirstOrDefault(); //找訂單
StoreReport sreport = new StoreReport();
sreport.cuserid = cu.cuserid;
//sreport.srstatus = "1";
sreport.suserid = sto;
sreport.orderguid = id;
sreport.srcontent = srcontent;
sreport.stime = DateTime.Now;
db.StoreReport.Add(sreport);
db.SaveChanges();
return(RedirectToAction("DemoReport"));
}
public static byte[] GenerateRandomDataForReport(string name, Guid makerId)
{
var random = new Random();
var report = new StoreReport
{
MakerId = makerId,
Supervisor = name,
UpTimeMeasure = random.Next(300, 22000),
BeanCount = random.Next(100, 33000),
WaterLevel = random.Next(0, 9),
RandomMetricsA = Enumerable.Repeat(0, 500).Select(i => random.Next(0, 99)).ToArray(),
RandomMetricsB = Enumerable.Repeat(0, 500).Select(i => random.Next(0, 99)).ToArray(),
RandomMetricsC = Enumerable.Repeat(0, 500).Select(i => random.Next(0, 99)).ToArray(),
};
return(Encoding.UTF8.GetBytes(JsonConvert.SerializeObject(report, Formatting.None)));
}
private void ProcessExcelStoreSUB(bool twoCreditAccounts)
{
string filePath;
if (twoCreditAccounts)
{
if (StartPeriod >= ReportSettings.July2009)
{
StoreSUB report = GetReportFactory().GetReportStoreSUB(StartPeriod, EndPeriod, twoCreditAccounts, GetClientID());
filePath = GenerateExcelSUB(report.Items, report.Summaries, "Store");
}
else
{
BillingUnit SummaryUnit1 = new BillingUnit();
BillingUnit SummaryUnit2 = new BillingUnit();
StoreReport rpt = new StoreReport(StartPeriod, EndPeriod);
DataTable dtStore = rpt.GenerateDataTable(SummaryUnit1, SummaryUnit2);
filePath = rpt.GenerateExcelFile(dtStore);
}
}
else
{
if (StartPeriod >= ReportSettings.July2009)
{
StoreSUB report = GetReportFactory().GetReportStoreSUB(StartPeriod, EndPeriod, twoCreditAccounts, GetClientID());
filePath = GenerateExcelSUB(report.Items, report.Summaries, "Store");
}
else
{
BillingUnit SummaryUnit1 = new BillingUnit();
BillingUnit SummaryUnit2 = new BillingUnit();
StoreReport rpt = new StoreReport(StartPeriod, EndPeriod);
DataTable dtStore = rpt.GenerateDataTable(SummaryUnit1, SummaryUnit2);
filePath = rpt.GenerateExcelFile(dtStore);
}
}
OutputExcel(filePath);
}
public static List <StoreReport> ParseExcel(string path)
{
List <StoreReport> reportsList = new List <StoreReport>();
string format = "dd-MMM-yyyy";
CultureInfo provider = CultureInfo.InvariantCulture;
using (ZipFile zip = ZipFile.Read(path + "Sample-Sales-Reports.zip"))
{
zip.ExtractAll(path, ExtractExistingFileAction.OverwriteSilently);
string dateAsString = string.Empty;
string folderName = string.Empty;
DateTime entryDate = DateTime.Now;
foreach (ZipEntry e in zip)
{
StoreReport reportEntry = new StoreReport();
var filename = e.FileName.ToString();
if (e.IsDirectory)
{
if (!string.IsNullOrWhiteSpace(folderName))
{
Directory.Delete(path + "\\" + folderName, true);
}
for (int i = 0; i < filename.Length - 1; i++)
{
dateAsString += filename[i];
}
folderName = dateAsString;
entryDate = DateTime.ParseExact(dateAsString, format, provider);
dateAsString = string.Empty;
}
else
{
int index = filename.IndexOf("/");
string excelFileName = filename.Substring(index + 1);
string DbSource = path + folderName + "\\" + excelFileName + ";";
string connectionString =
@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + DbSource +
@"Extended Properties=""Excel 12.0 Xml;HDR=YES"";";
OleDbConnection excelConn = new OleDbConnection(connectionString);
excelConn.Open();
using (excelConn)
{
OleDbCommand cmdExcelTable = new OleDbCommand(
@"SELECT * FROM [Sales$]", excelConn);
OleDbDataReader reader = cmdExcelTable.ExecuteReader();
using (reader)
{
reader.Read();
string supermarketName = (string)reader[0];
reportEntry.MarketName = supermarketName;
//Console.WriteLine(supermarketName);
reader.Read();
decimal totalSum;
while (reader.Read())
{
string productIdAsString = (string)reader[0];
if (productIdAsString == "Total sum:")
{
totalSum = decimal.Parse(reader[3].ToString());
reportEntry.TotalSum = totalSum;
//Console.WriteLine("Total Sum {0}", totalSum);
break;
}
int productId = int.Parse(productIdAsString);
double quantity = double.Parse(reader[1].ToString());
decimal price = decimal.Parse(reader[2].ToString());
decimal sum = decimal.Parse(reader[3].ToString());
reportEntry.Products.Add(new Entry {
ProductId = productId,
QuantityValue = quantity,
UnitPrice = price,
Location = supermarketName,
Sum = sum
});
//Console.WriteLine("ID: {0}; Quantity: {1}, Price: {3}, Sum: {2}", productId, quantity, sum, price);
}
reportEntry.Date = entryDate;
}
reportsList.Add(reportEntry);
}
}
}
Directory.Delete(path + "\\" + folderName, true);
}
return(reportsList);
}
public void LoadData()
{
Rule rule = new Rule();
StoreReport report = new StoreReport();
using (_connection = new SqlConnection(Config.ConnectionString))
{
try
{
_connection.Open();
SqlCommand cmdGetRule =
new SqlCommand("getRule", _connection)
{
CommandType = CommandType.StoredProcedure
};
using (SqlDataReader reader = cmdGetRule.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
rule.MinImport = (int)reader["SoLuongNhapItNhat"];
rule.MaxIndebtedness = Convert.ToInt32(reader["SoTienNoToiDa"]);
rule.MaxInventory = (int)reader["SoLuongTonToiDaTruocNhap"];
rule.MinInventory = (int)reader["SoLuongTonSauToiThieu"];
}
}
}
SqlCommand cmdGetReport =
new SqlCommand("getRevenue", _connection)
{
CommandType = CommandType.StoredProcedure
};
cmdGetReport.Parameters.Add(new SqlParameter("@date", DateTime.Today));
using (SqlDataReader reader = cmdGetReport.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
if (!(reader[reader.GetName(0)] is DBNull))
{
report.Revenue = Convert.ToInt32(reader[reader.GetName(0)]);
}
else
{
report.Revenue = 0;
}
}
}
}
cmdGetReport =
new SqlCommand("getSumsBooksInventories", _connection)
{
CommandType = CommandType.StoredProcedure
};
cmdGetReport.Parameters.Add(new SqlParameter("@date", DateTime.Today));
using (SqlDataReader reader = cmdGetReport.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
if (!(reader[reader.GetName(0)] is DBNull))
{
report.NumBookImport = (int)reader[reader.GetName(0)];
}
else
{
report.NumBookImport = 0;
}
}
}
}
cmdGetReport =
new SqlCommand("getBooksQuantitiesSold", _connection)
{
CommandType = CommandType.StoredProcedure
};
cmdGetReport.Parameters.Add(new SqlParameter("@date", DateTime.Today));
using (SqlDataReader reader = cmdGetReport.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
if (!(reader[reader.GetName(0)] is DBNull))
{
report.NumBookSold = (int)reader[reader.GetName(0)];
}
else
{
report.NumBookSold = 0;
}
}
}
}
cmdGetReport =
new SqlCommand("getQuantitiesBookInStock", _connection)
{
CommandType = CommandType.StoredProcedure
};
using (SqlDataReader reader = cmdGetReport.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
if (!(reader[reader.GetName(0)] is DBNull))
{
report.NumBookInStock = (int)reader[reader.GetName(0)];
}
else
{
report.NumBookInStock = 0;
}
}
}
}
_listener.OnGetDataSuccess(rule, report);
}
catch (Exception exception)
{
Trace.AutoFlush = true;
Trace.TraceInformation("Error Occur");
Trace.TraceError(exception.Message);
Trace.TraceWarning("Careful!");
Trace.Listeners.Add(new TextWriterTraceListener("log.txt"));
}
}
}
public StoreAnalyzerViewModel(StoreReport report)
{
Report = new StoreAnalysisModel(report);
}
public StoreReportDto Find(int id)
{
StoreReport storeReport = storeReportRepository.Find(id);
return(Mapper.Map <StoreReport, StoreReportDto>(storeReport));
}
public void OnGetDataSuccess(Rule rule, StoreReport report)
{
_adminView.DisplayDataToUI(rule, report);
}
