public ProductLog GetProductLog(int productId, int attrId, int unitId)
{
ProductLog result = null;
using (BaoHienDBDataContext context = new BaoHienDBDataContext(SettingManager.BuildStringConnection()))
{
result = context.ProductLogs.Where(p => p.ProductId == productId && p.AttributeId == attrId && p.UnitId == unitId)
.OrderByDescending(c => c.Id).FirstOrDefault();
if (result == null)
{
result = new ProductLog()
{
ProductId = productId,
AttributeId = attrId,
UnitId = unitId,
Amount = 0,
AfterNumber = 0,
BeforeNumber = 0,
RecordCode = "",
Status = BHConstant.DEACTIVE_STATUS,
Direction = BHConstant.DIRECTION_IN,
UpdatedDate = context.GetSystemDate()
};
context.ProductLogs.InsertOnSubmit(result);
context.SubmitChanges();
}
}
return(result);
}
public List <ProductReport> GetReportsOfProductAttributeUnit(int productId, int attrId, int unitId, DateTime from, DateTime to)
{
List <ProductReport> result = new List <ProductReport>();
int index = 0;
if (from > to)
{
DateTime d = from;
from = to;
to = d;
}
using (BaoHienDBDataContext context = new BaoHienDBDataContext(SettingManager.BuildStringConnection()))
{
var produces = context.ProductLogs.Where(x => x.UpdatedDate >= from && x.UpdatedDate <= to &&
x.ProductId == productId && x.AttributeId == attrId && x.UnitId == unitId &&
x.Status == BHConstant.ACTIVE_STATUS && x.RecordCode != "");
foreach (var item in produces)
{
result.Add(new ProductReport()
{
Index = (index += 1),
RecordCode = item.RecordCode,
Amount = item.Amount.ToString(),
Direction = item.Direction,
UpdatedDate = item.UpdatedDate,
UpdatedDateString = item.UpdatedDate.ToString(BHConstant.DATE_FORMAT)
});
}
result = result.OrderByDescending(x => x.UpdatedDate).ToList();
}
return(result);
}
public List <CustomersReport> GetReportsOfCustomers(DateTime from, DateTime to, ref double total)
{
List <CustomersReport> result = new List <CustomersReport>();
if (from > to)
{
DateTime d = from;
from = to;
to = d;
}
using (BaoHienDBDataContext context = new BaoHienDBDataContext(SettingManager.BuildStringConnection()))
{
List <CustomerLog> logs = context.CustomerLogs.Where(c => c.CreatedDate >= from && c.CreatedDate <= to && c.Status == BHConstant.ACTIVE_STATUS)
.OrderBy(c => c.CreatedDate).ToList();
total = logs.Sum(x => x.Amount);
int index = 0;
foreach (CustomerLog log in logs)
{
result.Add(new CustomersReport
{
CustomerName = log.Customer.CustomerName,
CustomerCode = log.Customer.CustCode,
Date = log.CreatedDate.ToString(BHConstant.DATE_FORMAT),
Amount = Global.formatCurrencyTextWithoutMask(log.Amount.ToString()),
RecordCode = log.RecordCode,
Index = ++index
});
}
}
return(result);
}
public List <ProductAttributeModel> GetProductAndAttributeOfType(int productTypeId)
{
List <ProductAttributeModel> list = new List <ProductAttributeModel>();
using (BaoHienDBDataContext context = new BaoHienDBDataContext(SettingManager.BuildStringConnection()))
{
var products = context.Products.Where(p => p.Status == null && p.ProductType == productTypeId).ToList();
foreach (Product product in products)
{
var attrs = from a in context.BaseAttributes
join pa in context.ProductAttributes on a.Id equals pa.AttributeId
where pa.ProductId == product.Id
select a;
foreach (BaseAttribute attr in attrs)
{
ProductAttributeModel pad = new ProductAttributeModel();
pad.Id = GetProductAttribute(product.Id, attr.Id).Id;
pad.AttributeId = attr.Id;
pad.ProductId = product.Id;
pad.ProductAttribute = product.ProductName + " - " + attr.AttributeName;
list.Add(pad);
}
}
}
return(list);
}
public List <EmployeesReport> GetReportsOfEmployees(DateTime from, DateTime to, ref double total)
{
List <EmployeesReport> result = new List <EmployeesReport>();
if (from > to)
{
DateTime d = from;
from = to;
to = d;
}
using (BaoHienDBDataContext context = new BaoHienDBDataContext(SettingManager.BuildStringConnection()))
{
List <EmployeeLog> logs = context.EmployeeLogs
.Where(c => c.CreatedDate >= from && c.CreatedDate <= to && c.Status == BHConstant.ACTIVE_STATUS)
.OrderByDescending(x => x.CreatedDate).ToList();
int index = 0;
foreach (EmployeeLog log in logs)
{
result.Add(new EmployeesReport
{
EmployeeName = log.Employee.FullName,
RecordCode = log.RecordCode,
Amount = Global.formatVNDCurrencyText(log.Amount.ToString()),
CreatedDate = log.CreatedDate.ToString(BHConstant.DATE_FORMAT),
Index = ++index
});
total += log.Amount;
}
}
return(result);
}
public List <EmployeeReport> GetReportsOfEmployee(int employeeId, DateTime from, DateTime to, ref double total)
{
List <EmployeeReport> result = new List <EmployeeReport>();
if (from > to)
{
DateTime d = from;
from = to;
to = d;
}
using (BaoHienDBDataContext context = new BaoHienDBDataContext(SettingManager.BuildStringConnection()))
{
List <EmployeeLog> logs = context.EmployeeLogs
.Where(c => c.EmployeeId == employeeId && c.CreatedDate >= from && c.CreatedDate <= to && c.Status == BHConstant.ACTIVE_STATUS)
.OrderByDescending(c => c.CreatedDate).ToList();
List <Order> orders = context.Orders.Where(x => logs.Select(y => y.RecordCode).Contains(x.OrderCode))
.OrderByDescending(x => x.CreatedDate).ToList();
List <OrderDetail> details = context.OrderDetails.Where(x => orders.Select(y => y.Id).Contains(x.OrderId)).ToList();
int curr_month = from.Month, curr_year = from.Year, month = curr_month, year = curr_year, index = 0;
result.Add(new EmployeeReport
{
CustomerName = "Tháng " + curr_month.ToString() + "/" + curr_year.ToString()
});
foreach (Order item in orders)
{
month = item.CreatedDate.Month;
year = item.CreatedDate.Year;
if (month != curr_month || year != curr_year)
{
curr_month = month;
curr_year = year;
result.Add(new EmployeeReport
{
CustomerName = "Tháng " + curr_month.ToString() + "/" + curr_year.ToString()
});
}
List <OrderDetail> details_of_order = details.Where(x => x.OrderId == item.Id).ToList();
for (int i = 0; i < details_of_order.Count; i++)
{
result.Add(new EmployeeReport
{
Index = (++index).ToString(),
Date = i == 0 ? item.CreatedDate.ToString(BHConstant.DATE_FORMAT) : "",
CustomerName = item.Customer.CustomerName,
ProductName = details_of_order[i].Product.ProductName,
AttrName = details_of_order[i].BaseAttribute.AttributeName,
Number = details_of_order[i].NumberUnit.ToString(),
Unit = details_of_order[i].MeasurementUnit.Name,
Price = Global.formatCurrencyTextWithoutMask(details_of_order[i].Price.ToString()),
Commission = Global.formatCurrencyTextWithoutMask(details_of_order[i].Commission.ToString()),
RecordCode = item.OrderCode,
});
total += details_of_order[i].Commission;
}
}
}
return(result);
}
public void DeactiveProductLog(string RecordCode)
{
using (BaoHienDBDataContext context = new BaoHienDBDataContext(SettingManager.BuildStringConnection()))
{
context.ProductLogs.Where(p => p.RecordCode == RecordCode && p.Status == BHConstant.ACTIVE_STATUS)
.ToList().ForEach(x => x.Status = BHConstant.DEACTIVE_STATUS);
context.SubmitChanges();
}
}
public ProductAttribute GetProductAttribute(int ProductId, int AttributeId)
{
ProductAttribute pa = new ProductAttribute();
using (BaoHienDBDataContext context = new BaoHienDBDataContext(SettingManager.BuildStringConnection()))
{
pa = context.ProductAttributes.Where(p => p.ProductId == ProductId && p.AttributeId == AttributeId).SingleOrDefault();
}
return(pa);
}
public string GetNameOfProductLog(ProductLog pl)
{
ProductLog result = null;
using (BaoHienDBDataContext context = new BaoHienDBDataContext(SettingManager.BuildStringConnection()))
{
result = context.ProductLogs.Where(p => p.ProductId == pl.ProductId && p.AttributeId == pl.AttributeId && p.UnitId == pl.UnitId)
.OrderByDescending(c => c.Id).FirstOrDefault();
return(result.Product.ProductName + " " + result.BaseAttribute.AttributeName + " (" + result.MeasurementUnit.Name + ")");
}
}
public ProductLog GetNewestProductLog(int productId, int attrId)
{
ProductLog result = null;
using (BaoHienDBDataContext context = new BaoHienDBDataContext(SettingManager.BuildStringConnection()))
{
result = context.ProductLogs.Where(p => p.ProductId == productId && p.AttributeId == attrId)
.OrderByDescending(c => c.Id).FirstOrDefault();
}
return(result);
}
private bool UpdateAttribute(long newProductId)
{
DataGridViewRowCollection selectedRows = dgvBaseAttributes.Rows;
ProductAttributeService productAttributeService = new ProductAttributeService();
bool result = true;
List <int> newAttr = new List <int>();
List <int> addAttr, removeAttr;
foreach (DataGridViewRow dgv in selectedRows)
{
DataGridViewCheckBoxCell checkbox = (DataGridViewCheckBoxCell)dgv.Cells[0];
if (checkbox.Value != null &&
(checkbox.Value.ToString().Equals(bool.TrueString) || checkbox.Value.ToString().Equals("1")))
{
newAttr.Add(((BaseAttribute)dgv.DataBoundItem).Id);
}
}
addAttr = newAttr.Except(oldAttr).ToList();
removeAttr = oldAttr.Except(newAttr).ToList();
foreach (int add in addAttr)
{
ProductAttribute pa = new ProductAttribute()
{
ProductId = (int)newProductId,
AttributeId = add
};
result = productAttributeService.AddProductAttribute(pa);
}
foreach (int remove in removeAttr)
{
int removeId = -1;
using (BaoHienDBDataContext context = new BaoHienDBDataContext(SettingManager.BuildStringConnection()))
{
ProductAttribute pa = context.ProductAttributes
.Where(p => p.AttributeId == remove && p.ProductId == (int)newProductId).SingleOrDefault();
if (pa != null)
{
removeId = pa.Id;
}
}
if (removeId != -1)
{
result = productAttributeService.DeleteProductAttribute(removeId);
}
}
BaoHienRepository.ResetDBDataContext();
return(result);
}
public List <MeasurementUnit> GetUnitsOfProductAttribute(int productId, int attrId)
{
List <MeasurementUnit> result = new List <MeasurementUnit>();
using (BaoHienDBDataContext context = new BaoHienDBDataContext(SettingManager.BuildStringConnection()))
{
List <ProductLog> logs = context.ProductLogs.Where(p => p.ProductId == productId && p.AttributeId == attrId)
.OrderByDescending(x => x.UpdatedDate).GroupBy(p => p.UnitId).Select(p => p.First()).ToList();
foreach (ProductLog log in logs)
{
result.Add(log.MeasurementUnit);
}
}
return(result);
}
public void MoveEmployeeLogOfCustomer(int customerId, int old_employeeId, int new_employeeId)
{
using (BaoHienDBDataContext context = new BaoHienDBDataContext(SettingManager.BuildStringConnection()))
{
var epls = context.EmployeeLogs.Join(context.Orders,
child => child.RecordCode, parent => parent.OrderCode,
(child, parent) => new { EmployeeLog = child, Order = parent }).Where(x =>
x.EmployeeLog.EmployeeId == old_employeeId && x.Order.CustId == customerId)
.Select(x => x.EmployeeLog);
foreach (var item in epls)
{
item.EmployeeId = new_employeeId;
context.SubmitChanges();
}
}
}
public List <ProductsReport> GetReportsOfProducts(int productTypeId, int productId, int attrId, int unitId, DateTime from, DateTime to)
{
List <ProductsReport> result = new List <ProductsReport>();
List <ProductDetail> items = new List <ProductDetail>();
if (from > to)
{
DateTime d = from;
from = to;
to = d;
}
using (BaoHienDBDataContext context = new BaoHienDBDataContext(SettingManager.BuildStringConnection()))
{
// Products has transaction on period of time
var produces = context.ProductLogs.Where(x => x.UpdatedDate <= to && x.UpdatedDate >= from && x.RecordCode != "");
if (unitId > 0)
{
produces = produces.Where(x => x.UnitId == unitId);
}
if (attrId > 0)
{
produces = produces.Where(x => x.AttributeId == attrId);
}
if (productId > 0)
{
produces = produces.Where(x => x.ProductId == productId);
}
if (productTypeId > 0)
{
produces = produces.Where(x => x.Product.ProductType == productTypeId);
}
foreach (var item in produces)
{
items.Add(new ProductDetail()
{
ID = item.Id,
UnitId = item.UnitId,
AttributeId = item.AttributeId,
Direction = item.Direction,
ProductId = item.ProductId,
ProductTypeId = item.Product.ProductType,
Amount = item.Amount,
Jampo = item.BaseAttribute.Jampo,
CreatedDate = item.UpdatedDate,
BeforeNumber = item.BeforeNumber,
AfterNUmber = item.AfterNumber,
Status = item.Status
});
}
// Products out period of time
var old_products = context.ProductLogs.AsEnumerable().Where(x => x.UpdatedDate < from &&
!items.Select(y => y.ProductId.ToString() + '_' +
y.AttributeId.ToString() + '_' + y.UnitId.ToString()).Contains(x.ProductId.ToString() + '_' +
x.AttributeId.ToString() + '_' + x.UnitId.ToString()))
.GroupBy(
x => new { x.ProductId, x.AttributeId, x.UnitId },
(x, y) => new
{
Key = new { x.ProductId, x.AttributeId, x.UnitId },
Value = y.OrderByDescending(z => z.Id).First()
})
.Select(x => x.Value);
if (unitId > 0)
{
old_products = old_products.Where(x => x.UnitId == unitId);
}
if (attrId > 0)
{
old_products = old_products.Where(x => x.AttributeId == attrId);
}
if (productId > 0)
{
old_products = old_products.Where(x => x.ProductId == productId);
}
if (productTypeId > 0)
{
old_products = old_products.Where(x => x.Product.ProductType == productTypeId);
}
foreach (var item in old_products)
{
items.Add(new ProductDetail()
{
ID = item.Id,
UnitId = item.UnitId,
AttributeId = item.AttributeId,
Direction = item.Direction,
ProductId = item.ProductId,
ProductTypeId = item.Product.ProductType,
Amount = 0,
Jampo = item.BaseAttribute.Jampo,
CreatedDate = item.UpdatedDate,
BeforeNumber = item.AfterNumber,
AfterNUmber = item.AfterNumber,
Status = item.Status
});
}
// Start collecting report
var dits = items.GroupBy(x => new { x.ProductTypeId, x.ProductId, x.AttributeId, x.UnitId });
List <int> type_distincts = items.GroupBy(x => x.ProductTypeId).Select(x => x.First()).Select(x => x.ProductTypeId).ToList();
List <ProductType> types = context.ProductTypes.OrderBy(x => x.TypeName).ToList();
int index = 0;
foreach (ProductType type in types)
{
if (type_distincts.Contains(type.Id))
{
List <ProductsReport> type_products = new List <ProductsReport>();
type_products.Add(new ProductsReport()
{
ProductName = type.TypeName
});
var item_types = items.Where(x => x.ProductTypeId == type.Id);
var distincts = item_types.GroupBy(x => new { x.ProductId, x.AttributeId, x.UnitId }).Select(x => x.First());
foreach (var item in distincts)
{
Product p = context.Products.Where(x => x.Id == item.ProductId).First();
BaseAttribute b = context.BaseAttributes.Where(x => x.Id == item.AttributeId).First();
MeasurementUnit m = context.MeasurementUnits.Where(x => x.Id == item.UnitId).First();
ProductsReport pr = new ProductsReport()
{
ProductCode = p.ProductCode, //p.Id + " - " + b.Id + " - " + m.Id,//p.ProductCode,
ProductName = p.ProductName + " - " + b.AttributeName,
Jampo = item.Jampo ? "Jampo" : "",
UnitName = m.Name,
Index = (++index).ToString(),
ProductId = p.Id,
AttrId = b.Id,
UnitId = m.Id
};
var a = items.Where(x => x.ProductId == item.ProductId && x.AttributeId == item.AttributeId &&
x.UnitId == item.UnitId).OrderBy(x => x.ID);
pr.FirstNumber = a.First().BeforeNumber.ToString();
pr.LastNumber = a.Last().AfterNUmber.ToString();
pr.ImportNumber = a.Where(x => x.Direction == BHConstant.DIRECTION_IN && x.Status == BHConstant.ACTIVE_STATUS).Sum(x => x.Amount).ToString();
pr.ExportNumber = a.Where(x => x.Direction == BHConstant.DIRECTION_OUT && x.Status == BHConstant.ACTIVE_STATUS).Sum(x => x.Amount).ToString();
//if (pr.LastNumber != "0")
if (!(pr.FirstNumber == "0" && pr.LastNumber == "0" && pr.ImportNumber == "0" && pr.ExportNumber == "0"))
{
type_products.Add(pr);
}
}
type_products = type_products.OrderBy(x => x.ProductCode).ThenBy(x => x.Jampo).ToList();
result.AddRange(type_products);
}
}
}
return(result);
}
