public virtual List <StoreModels> GetStoresByName(string name)//店铺查询
{
string sqlStr = "select * from m_store where s_storeCName like '%" + name + "%' or s_storeEName like '%" + name + "%' ";
DataTable dt = SqlSeverProvider.ExecuteQuery(sqlStr);
List <StoreModels> listStores = new List <StoreModels>();
StoreModels store = null;
if (dt != null)
{
foreach (DataRow row in dt.Rows)
{
store = new StoreModels
{
storeID = (int)row["s_storeId"],
areaKeyID = (int)row["s_areaKeyId"],
storeCName = (string)(row["s_storeCName"] == DBNull.Value ? string.Empty : row["s_storeCName"]),
storeEName = (string)(row["s_storeEName"] == DBNull.Value ? string.Empty : row["s_storeEName"]),
brandsID = (int)row["s_brandsId"],
address = (string)(row["s_address"] == DBNull.Value ? string.Empty : row["s_address"]),
phoneNumber = (string)(row["s_phoneNumber"] == DBNull.Value ? string.Empty : row["s_phoneNumber"]),
arrivalNum = (int)row["s_arrivalNum"]
};
listStores.Add(store);
}
return(listStores);
}
return(null);
}
public virtual StoreModels GetById(int id)
{
string sqlStr = "select * from m_store where s_storeId='" + id + "'";
SqlDataReader dr = SqlSeverProvider.ExecuteReader(sqlStr);
StoreModels store = null;
if (dr.Read())
{
store = new StoreModels
{
storeID = dr.GetInt32(0),
areaKeyID = dr.GetInt32(1),
storeCName = dr.GetString(2),
storeEName = dr.GetString(3),
categoryID = dr.GetInt32(4),
logo = dr.GetString(5),
brandsID = dr.GetInt32(6),
address = dr.GetString(7),
phoneNumber = dr.GetString(8),
arrivalNum = dr.GetInt32(9)
};
dr.Close();
return(store);
}
return(null);
}
//Edit existing store
public async Task <StoreModels> editStore(StoreModels store)
{
_db.Entry(store).State = EntityState.Modified;
await _db.SaveChangesAsync();
return(store);
}
/// <summary>
/// 分类查询商铺
/// </summary>
/// <param name="cateID"></param>
/// <param name="tradeAreaId"></param>
/// <returns></returns>
public virtual List <StoreModels> GetStoresByCategory(int cateID, int tradeAreaId)
{
string sqlStr = "dbo.dz_Store_GetSpecialStore";
SqlParameter[] parm =
{
new SqlParameter("@TradeAreaId", tradeAreaId),
new SqlParameter("@CategoryId", cateID)
};
DataTable dt = SqlSeverProvider.ExecuteQuery(sqlStr, CommandType.StoredProcedure, parm);
List <StoreModels> listStores = new List <StoreModels>();
StoreModels store = null;
if (dt != null)
{
foreach (DataRow row in dt.Rows)
{
store = new StoreModels
{
storeID = (int)row["s_storeId"],
areaKeyID = (int)row["s_areaKeyId"],
storeCName = (string)(row["s_storeCName"] == DBNull.Value ? string.Empty : row["s_storeCName"]),
storeEName = (string)(row["s_storeEName"] == DBNull.Value ? string.Empty : row["s_storeEName"]),
brandsID = (int)row["s_brandsId"],
address = (string)(row["s_address"] == DBNull.Value ? string.Empty : row["s_address"]),
phoneNumber = (string)(row["s_phoneNumber"] == DBNull.Value ? string.Empty : row["s_phoneNumber"]),
arrivalNum = (int)row["s_arrivalNum"]
};
listStores.Add(store);
}
return(listStores);
}
return(null);
}
public async Task <ActionResult> Create(StoreModels store)
{
try
{
if (ModelState.IsValid)
{
//Add new store
await storeRepository.createStore(store);
//Add date on last input
var storeLast = await storeRepository.result();
storeLast.Date = DateTime.Now;
storeRepository.SaveData();
//Create new log
await storeRepository.log(storeLast.Name, storeLast.Date, storeLast.Location);
return(RedirectToAction("Index"));
}
return(View(store));
}
catch (Exception e)
{
Console.WriteLine("{0} Exception caught.", e);
}
return(View());
}
//Create new store
public async Task <StoreModels> createStore(StoreModels store)
{
_db.StoreModels.Add(store);
await _db.SaveChangesAsync();
return(store);
}
// GET: Store
public ActionResult Index()
{
var store = new StoreModels();
var result = store.GetList();
return(View(result));
}
public virtual void Add(StoreModels store)
{
string sqlStr = string.Format("insert into m_store values({0},'{1}','{2}',{3},'{4}',{5},'{6}','{7}',{8})",
store.areaKeyID, store.storeCName, store.storeEName, store.categoryID, store.logo, store.brandsID,
store.address, store.phoneNumber, store.arrivalNum);
SqlSeverProvider.ExecuteNonQuery(sqlStr);
}
public ActionResult DeleteConfirmed(int id)
{
StoreModels storeModels = db.StoreModels.Find(id);
db.StoreModels.Remove(storeModels);
db.SaveChanges();
return(RedirectToAction("Index"));
}
//Delete some store
public async Task <StoreModels> deleteStore(int?id)
{
StoreModels store = await _db.StoreModels.FindAsync(id);
_db.StoreModels.Remove(store);
await _db.SaveChangesAsync();
return(store);
}
public ActionResult AddStore()
{
_stores = new Stores();
StoreModels store = new StoreModels();
store.IntervalList = GenerateSelectListItems(intervalsList, "");
store.OrderStatus = _stores.GetPublicOrdersStatus();
return(View(store));
}
public ActionResult Edit([Bind(Include = "Id,city,address,zipcode,geoLat,getLong")] StoreModels storeModels)
{
if (ModelState.IsValid)
{
db.Entry(storeModels).State = EntityState.Modified;
db.SaveChanges();
return(RedirectToAction("Index"));
}
return(View(storeModels));
}
public ActionResult Create([Bind(Include = "Id,city,address,zipcode,geoLat,getLong")] StoreModels storeModels)
{
if (ModelState.IsValid)
{
db.StoreModels.Add(storeModels);
db.SaveChanges();
return(RedirectToAction("Index"));
}
return(View(storeModels));
}
//Search and Paging
public async Task <object> pageCount(int pageSize, StoreModels store)
{
int pageCount = store.Child.Count();
int pages = pageCount / pageSize;
//ViewBag.pageCount = pages;
int rest = pageCount % pageSize;
if (rest < 10)
{
pages = pages + 1;
ViewBag.pageCount = pages;
}
return(ViewBag.pageCount);
}
public ActionResult EditStore(string id)
{
_stores = new Stores();
StoreDto dto = _stores.GetStoreById(int.Parse(id));
if (dto != null)
{
StoreModels model = new StoreModels();
model.StoreId = int.Parse(id);
model.ApiKey = dto.ApiKey;
model.StoreName = dto.Name;
model.Url = dto.Url;
model.IntervalList = GenerateSelectListItems(intervalsList, dto.Interval.ToString());
model.OrderStatusIds = _stores.GetAllPublicOrderStatusByStore(dto.Id);
model.CustomOrderStatus = _stores.GetNonPublicOrderStatusByStore(dto.Id);
List <CustomShipmentsDto> list = _stores.GetCustomShipmentByStoreId(dto.Id);
model.CustomShipments = list;
model.OrderStatus = _stores.GetPublicOrdersStatus();
foreach (var orde in model.OrderStatusIds)
{
model.listOfIds += orde + ",";
}
if (model.listOfIds != null && !model.listOfIds.Equals(""))
{
model.listOfIds = model.listOfIds.Remove(model.listOfIds.Count() - 1);
}
int counter = 0;
foreach (var customShipmentsDto in list)
{
if (counter == 0)
{
model.CustomShip1 = customShipmentsDto.Name;
}
if (counter == 1)
{
model.CustomShip2 = customShipmentsDto.Name;
}
if (counter == 2)
{
model.CustomShip3 = customShipmentsDto.Name;
}
counter++;
}
return(View(model));
}
return(RedirectToAction("Index", "Store"));
}
// GET: Store/Delete/5
public ActionResult Delete(int?id)
{
if (id == null)
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest));
}
StoreModels storeModels = db.StoreModels.Find(id);
if (storeModels == null)
{
return(HttpNotFound());
}
return(View(storeModels));
}
public HttpResponseMessage GetStoreByEmail([FromUri] StoreModels store)
{
string email = store.Email;
if (email == null)
{
return(Request.CreateErrorResponse(HttpStatusCode.NotFound, "Not found"));
}
List <StoreModels> stores = (from k in _db.StoreModels where k.Email == email select k).ToList();
HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.OK, stores);
return(response);
}
public HttpResponseMessage GetStoreByLocation([FromUri] StoreModels store)
{
string name = store.Location;
if (name == null)
{
return(Request.CreateErrorResponse(HttpStatusCode.NotFound, "Not found"));
}
List <StoreModels> stores = (from k in _db.StoreModels where k.Location == name select k).ToList();
HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.OK, stores);
return(response);
}
public HttpResponseMessage GetStoreByQuantity([FromUri] StoreModels store)
{
int?qop = store.QoP;
if (qop == null)
{
return(Request.CreateErrorResponse(HttpStatusCode.NotFound, "Not found"));
}
List <StoreModels> stores = (from k in _db.StoreModels where k.QoP == qop select k).ToList();
HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.OK, stores);
return(response);
}
public HttpResponseMessage GetStoreByTelephoneNumber([FromUri] StoreModels store)
{
string telephone = store.Telephone;
if (telephone == null)
{
return(Request.CreateErrorResponse(HttpStatusCode.NotFound, "Not found"));
}
List <StoreModels> stores = (from k in _db.StoreModels where k.Telephone == telephone select k).ToList();
HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.OK, stores);
return(response);
}
public HttpResponseMessage GetStoreByZipCode([FromUri] StoreModels store)
{
int?zipCode = store.ZipCode;
if (zipCode == null)
{
return(Request.CreateErrorResponse(HttpStatusCode.NotFound, "Not found"));
}
List <StoreModels> stores = (from k in _db.StoreModels where k.ZipCode == zipCode select k).ToList();
HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.OK, stores);
return(response);
}
public HttpResponseMessage Get([FromUri] StoreModels store)
{
string name = store.Name;
if (name == null)
{
return(Request.CreateErrorResponse(HttpStatusCode.NotFound, "Not found"));
}
var storeSearch = store.Child.FirstOrDefault((p) => p.Name == name);
// var abcd = store.Ascending;
HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.OK, storeSearch);
return(response);
}
public async Task <ActionResult> Edit(StoreModels store)
{
try
{
if (ModelState.IsValid)
{
//Modify store, return store
await storeRepository.editStore(store);
return(RedirectToAction("Index"));
}
return(View(store));
}
catch (Exception e)
{
Console.WriteLine("{0} Exception caught.", e);
}
return(View());
}
public virtual void Update(int id, StoreModels newStore)
{
string sqlStr = string.Format("update m_store set s_areaKeyId=");
SqlSeverProvider.ExecuteNonQuery(sqlStr);
}
public ActionResult GetStoreById(int id)
{
StoreModels store = _StoreManager.GetById(id);
return(Json(store, JsonRequestBehavior.AllowGet));
}
public ActionResult AddStore(StoreModels store)
{
_stores = new Stores();
if (ModelState.IsValid)
{
if (!Utilities.RegularExpressions.UrlIsValid(store.Url))
{
ModelState.AddModelError("URL", "Invalid Url");
store.IntervalList = GenerateSelectListItems(intervalsList, store.DefaultInterval);
store.OrderStatus = _stores.GetPublicOrdersStatus();
return(View(store));
}
if (!Utilities.RegularExpressions.ApiKeyIsvalid(store.ApiKey))
{
store.IntervalList = GenerateSelectListItems(intervalsList, store.DefaultInterval);
store.OrderStatus = _stores.GetPublicOrdersStatus();
ModelState.AddModelError("ApiKey", "The API KEY must be a 32 characters long and ONLY numbers");
return(View(store));
}
string[] CharList = Request.Form["rdOrderStatus"].Split(',');
if (CharList.Length == 0 && store.CustomOrderStatus.Equals(""))
{
store.IntervalList = GenerateSelectListItems(intervalsList, store.DefaultInterval);
store.OrderStatus = _stores.GetPublicOrdersStatus();
ModelState.AddModelError("OrderStatus", "You need to select at least one Order Status or create your custom one");
return(View(store));
}
List <string> orderStatusList = new List <string>();
foreach (string item in CharList)
{
orderStatusList.Add(item);
}
StoreDto storeDto = new StoreDto();
storeDto.Name = store.StoreName;
storeDto.ApiKey = store.ApiKey;
storeDto.Url = store.Url;
storeDto.Interval = int.Parse(store.DefaultInterval);
string customOrderStatus = "";
if (store.CustomOrderStatus != null && !store.CustomOrderStatus.Equals(""))
{
string[] customStatuses = store.CustomOrderStatus.Split(';');
foreach (var stat in customStatuses)
{
customOrderStatus = _stores.AddCustomOrderStatus(stat).ToString();
if (!customOrderStatus.Equals(""))
{
orderStatusList.Add(customOrderStatus);
}
customOrderStatus = "";
}
}
int storeId = _stores.AddStore(storeDto);
if (storeId != 0)
{
_stores.AddOrderStatusByStoreId(orderStatusList, storeId);
List <string> customShipments = new List <string>();
if (store.CustomShip1 != null && !store.CustomShip1.Equals(""))
{
customShipments.Add(store.CustomShip1);
}
if (store.CustomShip2 != null && !store.CustomShip2.Equals(""))
{
customShipments.Add(store.CustomShip2);
}
if (store.CustomShip3 != null && !store.CustomShip3.Equals(""))
{
customShipments.Add(store.CustomShip3);
}
if (customShipments.Count > 0)
{
_stores.CreateCustomShipments(customShipments, storeId);
}
return(RedirectToAction("Index", "Store"));
}
return(RedirectToAction("Index", "Store"));
}
else
{
store.IntervalList = GenerateSelectListItems(intervalsList, store.DefaultInterval);
store.OrderStatus = _stores.GetPublicOrdersStatus();
return(View(store));
}
}
public XLWorkbook Report(BaseReportModel model, List <StoreModels> lstStore)
{
XLWorkbook wb = new XLWorkbook();
IXLWorksheet ws = wb.Worksheets.Add("Monthly_Transactions_Report");
CreateReportHeaderNew(ws, 15, model.FromDate, model.ToDate, _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Monthly Transactions Report").ToUpper());
ws.Range(1, 1, 4, 15).Style.Border.InsideBorder = XLBorderStyleValues.Thin;
ws.Range(1, 1, 4, 15).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
ws.Range(1, 1, 4, 15).Style.Border.InsideBorderColor = XLColor.FromHtml("#000000");
ws.Range(1, 1, 4, 15).Style.Border.OutsideBorderColor = XLColor.FromHtml("#000000");
var _lstBusDayAllStore = _baseFactory.GetBusinessDays(model.FromDate, model.ToDate, model.ListStores, model.Mode);
int row = 5;
string storeName = string.Empty, storeId = string.Empty;
for (int i = 0; i < lstStore.Count; i++)
{
//Get StoreName
StoreModels store = lstStore[i];
storeName = store.Name;
storeId = store.Id;
//header report
FormatStoreHeader(storeName, ref ws, ref row);
row++;
int startRow = row;
var businessInStoreId = _lstBusDayAllStore.Where(ww => ww.StoreId == storeId).Select(ss => ss.Id).ToList();
List <DailyTransactionsReportModels> ingredients = null;
if (businessInStoreId != null && businessInStoreId.Any())
{
ingredients = GetListIngredientUsage(model, storeId, businessInStoreId);
}
var listDataSumary = new List <DailyTransactionsReportModels>();
if (ingredients != null && ingredients.Count > 0)
{
//Get all business day in store
var businessInStore = _lstBusDayAllStore.Where(ww => ww.StoreId == storeId).ToList();
// Group by year, month of business day
//var businessMonthInStore = businessInStore.GroupBy(r => new { r.DateFrom.Year, r.DateFrom.Month })
// .Select(x => new {
// year = x.Key.Year,
// month = x.Key.Month,
// startDateFrom = x.Select(r => r.DateFrom).FirstOrDefault(),
// endDateFrom = x.Select(r => r.DateFrom).LastOrDefault(),
// endDateTo = x.Select(r => r.DateTo).LastOrDefault(),
// }).OrderBy(x => x.year).ThenBy(x => x.month).ToList();
var businessMonthInStore = businessInStore.GroupBy(r => new { Year = r.DateFrom.Year, Month = r.DateFrom.Month })
.OrderBy(x => x.Key.Year).ThenBy(x => x.Key.Month).ToList();
BusinessDayDisplayModels minBD = null;
BusinessDayDisplayModels maxBD = null;
List <DailyTransactionsReportModels> lstDataInMonthOpenBal = null;
List <string> lstBusinessIds = new List <string>();
for (int d = 0; d < businessMonthInStore.Count; d++)
{
minBD = businessMonthInStore[d].OrderBy(oo => oo.DateFrom).FirstOrDefault();
maxBD = businessMonthInStore[d].OrderBy(oo => oo.DateFrom).LastOrDefault();
var listOpenBals = ingredients.Where(ww => ww.BusinessId == minBD.Id && ww.StoreId == storeId).ToList();
lstBusinessIds = businessMonthInStore[d].Select(ss => ss.Id).ToList();
lstDataInMonthOpenBal = ingredients.Where(m => lstBusinessIds.Contains(m.BusinessId) && m.StoreId == storeId).ToList();
List <DailyTransactionsReportModels> lstDataInMonth = ingredients.Where(m =>
lstBusinessIds.Contains(m.BusinessId) && m.StoreId == storeId)
.GroupBy(m => new { m.IngredientCode, m.IngredientName, m.BaseUOMName, m.TypeName })
.Select(mm => new DailyTransactionsReportModels()
{
IngredientCode = mm.Key.IngredientCode,
IngredientName = mm.Key.IngredientName,
BaseUOMName = mm.Key.BaseUOMName,
TypeName = mm.Key.TypeName,
OpenBal = listOpenBals.Where(ww => ww.IngredientCode == mm.Key.IngredientCode && ww.TypeName == mm.Key.TypeName)
.Select(m => m.OpenBal).FirstOrDefault(),
Received = mm.Sum(m => m.Received),
TransferIn = mm.Sum(m => m.TransferIn),
TransferOut = mm.Sum(m => m.TransferOut),
Return = mm.Sum(m => m.Return),
Damage = mm.Sum(m => m.Damage),
Wast = mm.Sum(m => m.Wast),
Others = mm.Sum(m => m.Others),
UseForSelfMade = mm.Sum(m => m.UseForSelfMade),
Sales = mm.Sum(m => m.Sales)
}).OrderBy(mm => mm.TypeName).ThenBy(mm => mm.IngredientName).ToList();
listDataSumary.AddRange(lstDataInMonth);
// Business day
if (lstDataInMonth != null && lstDataInMonth.Any())
{
// Name of month
ws.Range(row, 1, row, 15).Merge().SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey(CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(businessMonthInStore[d].Key.Month)));
ws.Range(row, 1, row, 15).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
ws.Range(row, 1, row++, 15).Style.Fill.BackgroundColor = XLColor.FromHtml("#ffcc99");
// Infomation of business day in month
//var startDateFrom = businessMonthInStore[d].startDateFrom.ToString("MM/dd/yyyy");
//var endDateFrom = businessMonthInStore[d].endDateTo.ToString("MM/dd/yyyy");
if (minBD.DateFrom.Date == maxBD.DateTo.Date)
{
ws.Range(row, 1, row, 15).Merge().SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Date") + ": " + minBD.DateFrom.ToString("MM/dd/yyyy"));
}
else
{
ws.Range(row, 1, row, 15).Merge().SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Date")
+ ": " + _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("From") + " " + minBD.DateFrom.ToString("MM/dd/yyyy") + " "
+ _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("To") + " " + maxBD.DateTo.ToString("MM/dd/yyyy"));
}
ws.Range(row, 1, row, 15).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
ws.Range(row - 1, 1, row++, 15).Style.Font.Bold = true;
decimal closeBal = 0;
int rowTbl = row;
for (int j = 0; j < lstDataInMonth.Count; j++)
{
closeBal = (decimal)lstDataInMonth[j].OpenBal + (decimal)lstDataInMonth[j].Received + (decimal)lstDataInMonth[j].TransferIn - (decimal)lstDataInMonth[j].Return - (decimal)lstDataInMonth[j].TransferOut - (decimal)lstDataInMonth[j].Damage - (decimal)lstDataInMonth[j].Wast - (decimal)lstDataInMonth[j].Others - lstDataInMonth[j].UseForSelfMade - (decimal)lstDataInMonth[j].Sales;
ws.Cell("A" + row).Value = "'" + lstDataInMonth[j].IngredientCode;
ws.Cell("B" + row).Value = lstDataInMonth[j].IngredientName;
ws.Cell("C" + row).Value = lstDataInMonth[j].BaseUOMName;
ws.Cell("D" + row).Value = lstDataInMonth[j].TypeName;
ws.Cell("E" + row).Value = lstDataInMonth[j].OpenBal;
ws.Cell("F" + row).Value = lstDataInMonth[j].Received;
ws.Cell("G" + row).Value = lstDataInMonth[j].TransferIn;
ws.Cell("H" + row).Value = lstDataInMonth[j].Return;
ws.Cell("I" + row).Value = lstDataInMonth[j].TransferOut;
ws.Cell("J" + row).Value = lstDataInMonth[j].Damage;
ws.Cell("K" + row).Value = lstDataInMonth[j].Wast;
ws.Cell("L" + row).Value = lstDataInMonth[j].Others;
ws.Cell("M" + row).Value = lstDataInMonth[j].UseForSelfMade;
ws.Cell("N" + row).Value = lstDataInMonth[j].Sales;
ws.Cell("O" + row++).Value = closeBal;
}
ws.Range(rowTbl, 5, row - 1, 15).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
ws.Range(rowTbl, 5, row - 1, 15).Style.NumberFormat.Format = "#,##0.000";
}
}
}
ws.Range(startRow, 1, row - 1, 15).Style.Border.InsideBorder = XLBorderStyleValues.Thin;
ws.Range(startRow, 1, row - 1, 15).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
ws.Range(startRow, 1, row - 1, 15).Style.Border.InsideBorderColor = XLColor.FromHtml("#000000");
ws.Range(startRow, 1, row - 1, 15).Style.Border.OutsideBorderColor = XLColor.FromHtml("#000000");
row += 2;
// Summary
int rowSum = row;
ws.Range(row, 1, row, 11).Merge().SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Summary").ToUpper());
ws.Range(row, 1, row++, 11).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
ws.Cell("A" + row).Value = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Ingredient Code");
ws.Cell("B" + row).Value = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Ingredient Name");
ws.Cell("C" + row).Value = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Base UOM");
ws.Cell("D" + row).Value = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Type");
ws.Cell("E" + row).Value = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Open Balance");
ws.Cell("F" + row).Value = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Purchase");
ws.Cell("G" + row).Value = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Transfer");
ws.Cell("H" + row).Value = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Data Entry");
ws.Cell("I" + row).Value = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Used for Self Made Ingredient");
ws.Cell("I" + row).Style.Alignment.WrapText = true;
ws.Cell("J" + row).Value = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Sale");
ws.Cell("K" + row).Value = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Close Balance");
ws.Range(row - 1, 1, row, 11).Style.Fill.BackgroundColor = XLColor.FromHtml("#d9d9d9");
ws.Range(row, 1, row, 11).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
ws.Range(row, 1, row, 11).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
ws.Range(row - 1, 1, row++, 11).Style.Font.Bold = true;
var listSumary = listDataSumary.GroupBy(o => new { o.IngredientCode, o.IngredientName, o.BaseUOMName, o.TypeName })
.Select(o => new DailyTransactionsReportModels()
{
IngredientCode = o.Key.IngredientCode,
IngredientName = o.Key.IngredientName,
BaseUOMName = o.Key.BaseUOMName,
TypeName = o.Key.TypeName
}).OrderBy(o => o.TypeName).ThenBy(o => o.IngredientName).ToList();
decimal openBalance = 0;
decimal purchase = 0;
decimal transfer = 0;
decimal dataEntry = 0;
decimal sale = 0;
decimal usaForSelfMade = 0;
int rowTblSum = row;
foreach (var data in listSumary)
{
var listChild = listDataSumary.Where(o => o.IngredientCode == data.IngredientCode &&
o.IngredientName == data.IngredientName && o.BaseUOMName == data.BaseUOMName).OrderBy(o => o.StartedOn).ToList();
openBalance = listChild.Select(o => (decimal)o.OpenBal).FirstOrDefault();
purchase = listChild.Sum(o => (decimal)o.Received - (decimal)o.Return);
transfer = listChild.Sum(o => (decimal)o.TransferIn - (decimal)o.TransferOut);
dataEntry = listChild.Sum(o => (decimal)o.Damage + (decimal)o.Wast + (decimal)o.Others);
sale = listChild.Sum(o => (decimal)o.Sales);
usaForSelfMade = listChild.Sum(o => (decimal)o.UseForSelfMade);
ws.Cell("A" + row).Value = "'" + data.IngredientCode;
ws.Cell("B" + row).Value = data.IngredientName;
ws.Cell("C" + row).Value = data.BaseUOMName;
ws.Cell("D" + row).Value = data.TypeName;
ws.Cell("E" + row).Value = openBalance;
ws.Cell("F" + row).Value = purchase;
ws.Cell("G" + row).Value = transfer;
ws.Cell("H" + row).Value = dataEntry;
ws.Cell("I" + row).Value = usaForSelfMade;
ws.Cell("J" + row).Value = sale;
ws.Cell("K" + row++).Value = (openBalance + purchase + transfer - dataEntry - sale - usaForSelfMade);
}
ws.Range(rowSum, 1, row - 1, 11).Style.Border.InsideBorder = XLBorderStyleValues.Thin;
ws.Range(rowSum, 1, row - 1, 11).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
ws.Range(rowSum, 1, row - 1, 11).Style.Border.InsideBorderColor = XLColor.FromHtml("#000000");
ws.Range(rowSum, 1, row - 1, 11).Style.Border.OutsideBorderColor = XLColor.FromHtml("#000000");
ws.Range(rowTblSum, 5, row - 1, 11).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
ws.Range(rowTblSum, 5, row - 1, 11).Style.NumberFormat.Format = "#,##0.000";
row++;
List <int> lstWidCol = new List <int>()
{
20, 20, 20, 20, 18, 18, 18, 18, 30, 18, 18, 18, 30, 18, 20
};
for (int y = 0; y < lstWidCol.Count; y++)
{
ws.Column(y + 1).Width = lstWidCol[y];
}
}
return(wb);
}
//Find some store
public async Task <StoreModels> findStore(int?id)
{
StoreModels store = await _db.StoreModels.FindAsync(id);
return(store);
}
public List <SelectListItem> GetListStoreForMerchantExtend(string hostUrl, List <string> lstOrgId, List <MerchantExtendModel> lstMerchantExtends, ref List <StoreModels> listStoreReturn)
{
List <SelectListItem> listStores = new List <SelectListItem>();
List <StoreModels> listData = new List <StoreModels>();
try
{
GetStoreWeb2Request paraBody = new GetStoreWeb2Request();
paraBody.ListOrgID = lstOrgId;
var result = (ResponseApiModels)ApiResponse.Post <ResponseApiModels>(Commons.Store_Get_Web2, null, paraBody);
dynamic data = result.Data;
var lstC = data["ListStore"];
var lstContent = JsonConvert.SerializeObject(lstC);
listData = JsonConvert.DeserializeObject <List <StoreModels> >(lstContent);
//listData.ForEach(ss => ss.HostUrlExtend = hostUrl);
foreach (var item in listData)
{
item.HostUrlExtend = hostUrl;
item.NameExtend = string.Format("{0} in {1}", item.Name, item.OrganizationName);
}
//for extend
foreach (var item in lstMerchantExtends)
{
paraBody = new GetStoreWeb2Request();
paraBody.ListStoreID = item.ListStoreIds;
result = (ResponseApiModels)ApiResponse.PostWithoutHostConfig <ResponseApiModels>(item.HostApiURL + "/" + Commons.Store_Get_Web2, null, paraBody);
data = result.Data;
lstC = data["ListStore"];
lstContent = JsonConvert.SerializeObject(lstC);
List <StoreModels> tmp = JsonConvert.DeserializeObject <List <StoreModels> >(lstContent);
foreach (var subitem in tmp)
{
subitem.HostUrlExtend = item.HostApiURL;
subitem.NameExtend = string.Format("{0} in {1}", subitem.Name, subitem.OrganizationName);
}
//tmp.ForEach(ss => ss.HostUrlExtend = item.HostApiURL, SS);
listData.AddRange(tmp);
}
var currentUser = (UserSession)System.Web.HttpContext.Current.Session["User"];
if (currentUser != null)
{
listData = listData.Where(x => currentUser.ListStoreID.Contains(x.Id)).ToList();
}
listStoreReturn = listData;
listStores = listData.Select(ss => new SelectListItem()
{
Value = ss.Id,
Text = ss.Name
}).ToList();
//for FJ Daily Sales report
StoreModels stall14 = listData.Where(ww => ww.StoreCode == Commons.Stall14StoreCode).FirstOrDefault();
if (stall14 != null)
{
Commons.Stall14StoreId = stall14.Id;
Commons.Stall14StoreName = stall14.Name;
}
return(listStores);
}
catch (Exception e)
{
_logger.Error("Get store extend list: " + e);
return(listStores);
}
}
public XLWorkbook Report(BaseReportModel model, List <StoreModels> lstStore)
{
XLWorkbook wb = new XLWorkbook();
IXLWorksheet ws = wb.Worksheets.Add("Daily_Transactions_Report");
CreateReportHeaderNew(ws, 17, model.FromDate, model.ToDate, _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Daily Transactions Report").ToUpper());
ws.Range(1, 1, 4, 17).Style.Border.InsideBorder = XLBorderStyleValues.Thin;
ws.Range(1, 1, 4, 17).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
ws.Range(1, 1, 4, 17).Style.Border.InsideBorderColor = XLColor.FromHtml("#000000");
ws.Range(1, 1, 4, 17).Style.Border.OutsideBorderColor = XLColor.FromHtml("#000000");
var _lstBusDayAllStore = _baseFactory.GetBusinessDays(model.FromDate, model.ToDate, model.ListStores, model.Mode);
int row = 5;
string storeName = string.Empty, storeId = string.Empty;
for (int i = 0; i < lstStore.Count; i++)
{
//Get StoreName
StoreModels store = lstStore[i];
storeName = store.Name;
storeId = store.Id;
//header report
FormatStoreHeader(storeName, ref ws, ref row);
row++;
int startRow = row;
var businessInStoreId = _lstBusDayAllStore.Where(ww => ww.StoreId == storeId).Select(ss => ss.Id).ToList();
List <DailyTransactionsReportModels> ingredients = null;
if (businessInStoreId != null && businessInStoreId.Any())
{
ingredients = GetListIngredientUsage(model, storeId, businessInStoreId);
}
var listDataSumary = new List <DailyTransactionsReportModels>();
if (ingredients != null && ingredients.Count > 0)
{
//Get all business day in store
var businessInStore = _lstBusDayAllStore.Where(ww => ww.StoreId == storeId).ToList();
List <DailyTransactionsReportModels> lstDataInDate = null;
for (int d = 0; d < businessInStore.Count; d++)
{
//List<DailyTransactionsReportModels> lstDataInDate =
// ingredients.Where(m =>
// m.StartedOn >= businessInStore[d].DateFrom
// && m.ClosedOn <= businessInStore[d].DateTo
// && m.StoreId == storeId)
// .OrderBy(m => m.TypeName).ThenBy(m => m.IngredientName).ToList();
lstDataInDate =
ingredients.Where(m =>
m.BusinessId == businessInStore[d].Id
&& m.StoreId == storeId)
.OrderBy(m => m.TypeName).ThenBy(m => m.IngredientName).ToList();
listDataSumary.AddRange(lstDataInDate);
// Business day
if (lstDataInDate.Count > 0)
{
ws.Range(row, 1, row, 17).Merge().SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Business day") + ": " + _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Open") + " " + businessInStore[d].DateFrom.ToString("MM/dd/yyyy - HH:mm") + " " + _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Close") + " " + businessInStore[d].DateTo.ToString("MM/dd/yyyy - HH:mm"));
ws.Range(row, 1, row, 17).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
ws.Range(row, 1, row++, 17).Style.Font.Bold = true;
int rowTbl = row;
for (int j = 0; j < lstDataInDate.Count; j++)
{
ws.Cell("A" + row).Value = "'" + lstDataInDate[j].IngredientCode;
ws.Cell("B" + row).Value = lstDataInDate[j].IngredientName;
ws.Cell("C" + row).Value = lstDataInDate[j].BaseUOMName;
ws.Cell("D" + row).Value = lstDataInDate[j].TypeName;
ws.Cell("E" + row).Value = lstDataInDate[j].OpenBal;
ws.Cell("F" + row).Value = lstDataInDate[j].Received;
ws.Cell("G" + row).Value = lstDataInDate[j].TransferIn;
ws.Cell("H" + row).Value = lstDataInDate[j].Return;
ws.Cell("I" + row).Value = lstDataInDate[j].TransferOut;
ws.Cell("J" + row).Value = lstDataInDate[j].Damage;
ws.Cell("K" + row).Value = lstDataInDate[j].Wast;
ws.Cell("L" + row).Value = lstDataInDate[j].Others;
ws.Cell("M" + row).Value = lstDataInDate[j].UseForSelfMade;
ws.Cell("N" + row).Value = lstDataInDate[j].Sales;
ws.Cell("O" + row).Value = lstDataInDate[j].AutoCloseBal;//auto
ws.Cell("P" + row).Value = lstDataInDate[j].CloseBal;
ws.Cell("Q" + row++).Value = lstDataInDate[j].AdjustValue;
}
ws.Range(rowTbl, 5, row - 1, 17).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
ws.Range(rowTbl, 5, row - 1, 17).Style.NumberFormat.Format = "#,##0.000";
}
}
}
ws.Range(startRow, 1, row - 1, 17).Style.Border.InsideBorder = XLBorderStyleValues.Thin;
ws.Range(startRow, 1, row - 1, 17).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
ws.Range(startRow, 1, row - 1, 17).Style.Border.InsideBorderColor = XLColor.FromHtml("#000000");
ws.Range(startRow, 1, row - 1, 17).Style.Border.OutsideBorderColor = XLColor.FromHtml("#000000");
row += 2;
// Summary
int rowSum = row;
ws.Range(row, 1, row, 11).Merge().SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Summary").ToUpper());
ws.Range(row, 1, row++, 11).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
ws.Cell("A" + row).Value = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Ingredient Code");
ws.Cell("B" + row).Value = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Ingredient Name");
ws.Cell("C" + row).Value = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Base UOM");
ws.Cell("D" + row).Value = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Type");
ws.Cell("E" + row).Value = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Open Balance");
ws.Cell("F" + row).Value = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Purchase");
ws.Cell("G" + row).Value = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Transfer");
ws.Cell("H" + row).Value = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Data Entry");
ws.Cell("I" + row).Value = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Used for Self Made Ingredient");
ws.Cell("I" + row).Style.Alignment.WrapText = true;
ws.Cell("J" + row).Value = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Sale");
ws.Cell("K" + row).Value = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Close Balance");
ws.Range(row - 1, 1, row, 11).Style.Fill.BackgroundColor = XLColor.FromHtml("#d9d9d9");
ws.Range(row, 1, row, 11).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
ws.Range(row, 1, row, 11).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
ws.Range(row - 1, 1, row++, 11).Style.Font.Bold = true;
var listSumary = listDataSumary.GroupBy(o => new { o.IngredientCode, o.IngredientName, o.BaseUOMName, o.TypeName })
.Select(o => new DailyTransactionsReportModels()
{
IngredientCode = o.Key.IngredientCode,
IngredientName = o.Key.IngredientName,
BaseUOMName = o.Key.BaseUOMName,
TypeName = o.Key.TypeName
}).OrderBy(o => o.TypeName).ThenBy(o => o.IngredientName).ToList();
decimal openBalance = 0;
decimal purchase = 0;
decimal transfer = 0;
decimal dataEntry = 0;
decimal sale = 0;
decimal usaForSelfMade = 0;
int rowTblSum = row;
foreach (var data in listSumary)
{
var listChild = listDataSumary.Where(o => o.IngredientCode == data.IngredientCode &&
o.IngredientName == data.IngredientName && o.BaseUOMName == data.BaseUOMName).OrderBy(o => o.StartedOn).ToList();
openBalance = listChild.Select(o => (decimal)o.OpenBal).FirstOrDefault();
purchase = listChild.Sum(o => (decimal)o.Received - (decimal)o.Return);
transfer = listChild.Sum(o => (decimal)o.TransferIn - (decimal)o.TransferOut);
dataEntry = listChild.Sum(o => (decimal)o.Damage + (decimal)o.Wast + (decimal)o.Others);
sale = listChild.Sum(o => (decimal)o.Sales);
usaForSelfMade = listChild.Sum(o => (decimal)o.UseForSelfMade);
ws.Cell("A" + row).Value = "'" + data.IngredientCode;
ws.Cell("B" + row).Value = data.IngredientName;
ws.Cell("C" + row).Value = data.BaseUOMName;
ws.Cell("D" + row).Value = data.TypeName;
ws.Cell("E" + row).Value = openBalance;
ws.Cell("F" + row).Value = purchase;
ws.Cell("G" + row).Value = transfer;
ws.Cell("H" + row).Value = dataEntry;
ws.Cell("I" + row).Value = usaForSelfMade;
ws.Cell("J" + row).Value = sale;
ws.Cell("K" + row++).Value = (openBalance + purchase + transfer - dataEntry - sale - usaForSelfMade);
}
ws.Range(rowSum, 1, row - 1, 11).Style.Border.InsideBorder = XLBorderStyleValues.Thin;
ws.Range(rowSum, 1, row - 1, 11).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
ws.Range(rowSum, 1, row - 1, 11).Style.Border.InsideBorderColor = XLColor.FromHtml("#000000");
ws.Range(rowSum, 1, row - 1, 11).Style.Border.OutsideBorderColor = XLColor.FromHtml("#000000");
ws.Range(rowTblSum, 5, row - 1, 11).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
ws.Range(rowTblSum, 5, row - 1, 11).Style.NumberFormat.Format = "#,##0.000";
row++;
List <int> lstWidCol = new List <int>()
{
20, 20, 20, 20, 18, 18, 18, 18, 30, 18, 18, 18, 30, 18, 20, 25
};
for (int y = 0; y < lstWidCol.Count; y++)
{
ws.Column(y + 1).Width = lstWidCol[y];
}
}
return(wb);
}
