// Group the articles by day and count
public List <DailySales> GetDailySalesCount()
{
var salesListGroupedByArticles = salesList.GroupBy(salesList => salesList.Day);
foreach (var group in salesListGroupedByArticles)
{
Console.WriteLine("Number of sold articles on " + group.Key + ":");
Console.WriteLine(group.Count());
DailySales temp_list = new DailySales(group.Key, group.Count());
dailySalesList.Add(temp_list);
}
return(dailySalesList);
}
public ActionResult DailySales()
{
emed.Models.DB53Entities db = new emed.Models.DB53Entities();
//CrMVCApp.Models.Customer c;
//var c = (from b in db.DailySales select b).ToList();
var c = (from b in db.DailySales select b).ToList();
DailySales rpt = new DailySales();
rpt.Load();
rpt.SetDataSource(c);
Stream s = rpt.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
return(File(s, "C:\\Users\\M.ALI\\Documents\\ProjectAreports\\DailySalesReport.pdf"));
}
public void DailySalesReportPropertiesTest()
{
//Arrange
DailySales dailysales = new DailySales();
//Act
dailysales.ProductNumber = "1";
dailysales.Quantity = 2;
dailysales.SinglePrice = 12;
dailysales.Total = 24;
dailysales.Description = "Factory New";
//Assert
Assert.AreEqual(dailysales.ProductNumber, "1");
Assert.AreEqual(dailysales.SinglePrice, 12);
Assert.AreEqual(dailysales.Quantity, 2);
Assert.AreEqual(dailysales.Total, 24);
Assert.AreEqual(dailysales.Description, "Factory New");
}
private static DailySales ChangString(string filename, string line)
{
DailySales dailSales = new DailySales();
try
{
//Common.WriteLog("切割字串 " + line);
//去除多空白
line = new Regex("[\\s]+").Replace(line, " ");
//空白切割
string[] words = line.Split(' ');
dailSales = new DailySales
{
StoreCode = filename,
DepartmentCode = words[0].Substring(0, 2),
ItemCode = words[0].Substring(2, 6),
SubCode = words[0].Substring(8, 3),
SalesDate = words[0].Substring(11, 10),
UnitCode = words[0].Substring(21, 2),
StatusPromotion = words[0].Substring(23, 1),
SPType = words[0].Substring(24, 1),
VatRate = words[1],
SalesQty = words[2],
SalesAmount = words[3],
SalesPrice = words[4],
PurchasePrice = words[5],
Rebate = words[6],
VatAmount = words[7],
OriginalSellingPrice = words[8],
CSTAmount = words[9]
};
return(dailSales);
}
catch (Exception ex)
{
Common.WriteLog("切割字串 " + line + " 失敗");
Common.WriteLog(ex.ToString());
return(dailSales);
}
}
private void frmdailysalesReportPreview_Load(object sender, EventArgs e)
{
try
{
string query;
DataTable dailySales = new DataTable();
SqlDataAdapter da = new SqlDataAdapter();
cn.Open();
if (f.cboCashier.Text == "All Cashier")
{
query = string.Format("Select c.id ,c.transno,c.pcode, p.pdesc, c.price, c.qty,c.disc,c.total from tblCart as c inner join tblproduct as p on c.pcode=p.pcode where status like 'Sold' and sdate between'{0}' and '{1}'", Convert.ToString(f.dt1.Value), Convert.ToString(f.dt2.Value));
cm = new SqlCommand(query, cn);
}
else
{
query = string.Format("Select c.id ,c.transno,c.pcode, p.pdesc, c.price, c.qty,c.disc,c.total from tblCart as c inner join tblproduct as p on c.pcode=p.pcode where status like 'Sold' and sdate between'{0}' and '{1}' and c.cashier = '{2}'", Convert.ToString(f.dt1.Value), Convert.ToString(f.dt2.Value), Convert.ToString(f.cboCashier.Text));
//String query = string.Format("Select c.id ,c.transno,c.pcode, p.pdesc, c.price, c.qty,c.disc,c.total from tblCart as c inner join tblproduct as p on c.pcode=p.pcode where status like 'Sold' and sdate between '{0}' and '{1}'", dt1.Value, dt2.Value);
//String query = string.Format("Select c.id ,c.transno,c.pcode, p.pdesc, c.price, c.qty,c.disc,c.total from tblCart as c inner join tblproduct as p on c.pcode=p.pcode where status like 'Sold'");
cm = new SqlCommand(query, cn);
}
da.SelectCommand = new SqlCommand(query, cn);
da.Fill(dailySales);
cn.Close();
DailySales crptDailySales = new DailySales();
TextObject dateFrom = (TextObject)crptDailySales.ReportDefinition.Sections["Section1"].ReportObjects["txtDateFrom"];
TextObject DateTo = (TextObject)crptDailySales.ReportDefinition.Sections["Section1"].ReportObjects["txtDateTo"];
TextObject username = (TextObject)crptDailySales.ReportDefinition.Sections["Section1"].ReportObjects["txtReportUserName"];
dateFrom.Text = f.dt1.Text;
DateTo.Text = f.dt2.Text;
username.Text = f.cboCashier.Text;
crptDailySales.Database.Tables["dtSoldReport"].SetDataSource(dailySales);
crvDailySalesViewer.ReportSource = null;
crvDailySalesViewer.ReportSource = crptDailySales;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Report Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public void UpdateCreateSalesReportBLLGenerateProfitOfTheDayTest()
{
//Arrange
var data = new List <Items>
{
new Items()
{
Id = "1", Quantity = 12, OriginalPrice = 1, SalesPrice = 2,
ProductName = "Pen", Status = "Factory New"
}
}.AsQueryable();
var mockSet = new Mock <DbSet <Items> >();
mockSet.As <IQueryable <Items> >().Setup(m => m.Provider).Returns(data.Provider);
mockSet.As <IQueryable <Items> >().Setup(m => m.Expression).Returns(data.Expression);
mockSet.As <IQueryable <Items> >().Setup(m => m.ElementType).Returns(data.ElementType);
mockSet.As <IQueryable <Items> >().Setup(m => m.GetEnumerator()).Returns(data.GetEnumerator());
var mockContext = new Mock <OfficeStoreContext>();
mockContext.Setup(c => c.Items).Returns(mockSet.Object);
var updateCreateSalesReportBLL = new UpdateCreateSalesReportBLL(mockContext.Object);
var CartItem = new DailySales()
{
ProductNumber = "1", Quantity = 1, SinglePrice = 1
, Description = "Item", Total = 1
};
var ItemsList = new List <DailySales>()
{
CartItem
};
//Act
double ProfitOfTheDay = updateCreateSalesReportBLL.GenerateProfitOfTheDay(ItemsList);
//Assert
Assert.AreEqual(ProfitOfTheDay, 1);
}
public static bool StartInsert(string path, string finame)
{
bool result = true;
DailySales daily = new DailySales();
List <DailySales> sales = new List <DailySales>();
string line;
try
{
//使用FileStream讀取檔案
FileStream fileStream = File.OpenRead(path);
StreamReader reader = new StreamReader(fileStream, Encoding.UTF8);
string filename = Path.GetFileName(finame).Substring(0, 3);
int i = 0;
line = reader.ReadLine();
Common.WriteLog("切割字串");
//Console.WriteLine("切割字串");
//start = DateTime.Now;
while (line != null)
{
line = new Regex("[\\s]+").Replace(line, " ");
if (line == " " || line == "")
{
line = reader.ReadLine();
continue;
}
daily = ChangString(filename, line);
sales.Add(daily);
//Read the next line
line = reader.ReadLine();
i++;
}
//end = DateTime.Now;
//Console.WriteLine(start.ToString("hh:mm:ss"));
//Console.WriteLine(end.ToString("hh:mm:ss"));
reader.Close();
Common.WriteLog("新增DB");
result = InsertToSql(sales);
Common.WriteLog("新增 " + finame + " 成功，一共 " + i + " 筆");
return(result);
}
catch (Exception ex)
{
result = false;
Common.WriteLog(ex.ToString());
return(result);
}
}
public async Task <ActionResult> DailySales(string sortOrder, string currentFilter, DailySales dailysales, int?page)
{
ViewBag.CurrentSort = sortOrder;
ViewBag.NameSortParm = String.IsNullOrEmpty(sortOrder) ? "name_desc" : "";
ViewBag.PriceSortParm = sortOrder == "Price" ? "price_desc" : "Price";
string searchString = String.Empty;
if (dailysales.Date != null)
{
var searchdate = (DateTime)dailysales.Date;
searchString = searchdate.ToShortDateString();
}
if (searchString != null)
{
page = 1;
}
else
{
searchString = currentFilter;
}
ViewBag.CurrentFilter = searchString;
string checkName = User.Identity.GetUserId();
var todayDate = DateTime.Now.ToString("dd/MM/yyyy");
IEnumerable <Sale> sales = new List <Sale>();
if (Request.IsAuthenticated && User.IsInRole("Admin"))
{
sales = await _db.Sales.AsNoTracking().ToListAsync();
}
else
{
sales = _db.Sales.AsNoTracking().Where(s => s.CustomerId.Equals(checkName));
}
//var sales = from o in _db.Sales
// select o;
if (!String.IsNullOrEmpty(searchString))
{
// var salesresult = sales.Where(s => s.SaleDate.Date.ToString());
sales = sales.Where(s => s.SaleDate.ToShortDateString().Equals(searchString));
}
else
{
sales = sales.Where(x => x.SaleDate.ToString("dd/MM/yyyy").Equals(todayDate));
}
switch (sortOrder)
{
case "name_desc":
sales = sales.OrderByDescending(s => s.Customer.UserName);
break;
case "Price":
sales = sales.OrderBy(s => s.Total);
break;
case "price_desc":
sales = sales.OrderByDescending(s => s.Total);
break;
default: // Name ascending
sales = sales.OrderBy(s => s.Customer.UserName);
break;
}
int pageSize = 10;
int pageNumber = (page ?? 1);
return(View(sales.ToPagedList(pageNumber, pageSize)));
//return View(await _db.Orders.ToListAsync());
}
// GET: DailySales
public ActionResult Index(int?id, string salesmanId, string searchString, DateTime?SaleDate)
{
var dailySales = db.DailySales.Include(d => d.Salesman).Where(c => DbFunctions.TruncateTime(c.SaleDate) == DbFunctions.TruncateTime(DateTime.Today)).OrderByDescending(c => c.SaleDate).ThenByDescending(c => c.DailySaleId);
if (id != null && id == 101)
{
dailySales = db.DailySales.Include(d => d.Salesman).OrderByDescending(c => c.SaleDate).ThenByDescending(c => c.DailySaleId);
}
//Fixed Query
var totalSale = dailySales.Where(c => c.IsManualBill == false).Sum(c => (decimal?)c.Amount) ?? 0;
var totalManualSale = dailySales.Where(c => c.IsManualBill == true).Sum(c => (decimal?)c.Amount) ?? 0;
var totalMonthlySale = db.DailySales.Where(c => DbFunctions.TruncateTime(c.SaleDate).Value.Month == DbFunctions.TruncateTime(DateTime.Today).Value.Month).Sum(c => (decimal?)c.Amount) ?? 0;
var duesamt = db.DuesLists.Where(c => c.IsRecovered == false).Sum(c => (decimal?)c.Amount) ?? 0;
var cashinhand = (decimal)0.00;
try
{
cashinhand = db.CashInHands.Where(c => DbFunctions.TruncateTime(c.CIHDate) == DbFunctions.TruncateTime(DateTime.Today)).FirstOrDefault().InHand;
}
catch (Exception)
{
Utils.ProcessOpenningClosingBalance(db, DateTime.Today, false, true);
cashinhand = (decimal)0.00;
//Log.Error("Cash In Hand is null");
}
// Fixed UI
ViewBag.TodaySale = totalSale;
ViewBag.ManualSale = totalManualSale;
ViewBag.MonthlySale = totalMonthlySale;
ViewBag.DuesAmount = duesamt;
ViewBag.CashInHand = cashinhand;
// By Salesman
var salesmanList = new List <string>();
var smQry = from d in db.Salesmen
orderby d.SalesmanName
select d.SalesmanName;
salesmanList.AddRange(smQry.Distinct());
ViewBag.salesmanId = new SelectList(salesmanList);
//By Date
var dateList = new List <DateTime>();
var opdQry = from d in db.DailySales
orderby d.SaleDate
select d.SaleDate;
dateList.AddRange(opdQry.Distinct());
ViewBag.dateID = new SelectList(dateList);
//By Invoice No Search
if (!String.IsNullOrEmpty(searchString))
{
var dls = db.DailySales.Include(d => d.Salesman).Where(c => c.InvNo == searchString);
return(View(dls));
}
else if (!String.IsNullOrEmpty(salesmanId) || SaleDate != null)
{
IEnumerable <DailySale> DailySales;
if (SaleDate != null)
{
DailySales = db.DailySales.Include(d => d.Salesman).Where(c => DbFunctions.TruncateTime(c.SaleDate) == DbFunctions.TruncateTime(SaleDate)).OrderByDescending(c => c.DailySaleId);
}
else
{
DailySales = db.DailySales.Include(d => d.Salesman).Where(c => DbFunctions.TruncateTime(c.SaleDate) == DbFunctions.TruncateTime(DateTime.Today)).OrderByDescending(c => c.SaleDate).ThenByDescending(c => c.DailySaleId);
}
if (!String.IsNullOrEmpty(salesmanId))
{
DailySales = DailySales.Where(c => c.Salesman.SalesmanName == salesmanId);
}
return(View(DailySales.ToList()));
}
return(View(dailySales.ToList()));
}
