private static void AddBatterySheet(IReadOnlyDbContext db, ExcelPackage pkg, Dictionary <string, string> centerNames)
{
var sheet = pkg.Workbook.Worksheets.Add("Batteries");
var data = db.Find <RectifierAndBattery>(rb => rb.Deleted != true)
.Project(rb => new { rb.Center, rb.Batteries }).ToList();
sheet.SetValue(1, 1, "مرکز");
for (int i = 0; i < data.Max(d => d.Batteries.Count); i++)
{
sheet.SetValue(1, i * 6 + 2, (i + 1) + ". مدل");
sheet.SetValue(1, i * 6 + 3, (i + 1) + ". ظرفیت");
sheet.SetValue(1, i * 6 + 4, (i + 1) + ". نوع");
sheet.SetValue(1, i * 6 + 5, (i + 1) + ". تعداد سلول ها");
sheet.SetValue(1, i * 6 + 6, (i + 1) + ". تاریخ تولید");
sheet.SetValue(1, i * 6 + 7, (i + 1) + ". تاریخ نصب");
}
for (int row = 0; row < data.Count; row++)
{
if (centerNames.ContainsKey(data[row].Center))
{
sheet.SetValue(row + 2, 1, centerNames[data[row].Center]);
}
for (int i = 0; i < data[row].Batteries.Count; i++)
{
sheet.SetValue(row + 2, i * 6 + 2, data[row].Batteries[i].Model);
sheet.SetValue(row + 2, i * 6 + 3, data[row].Batteries[i].Capacity);
sheet.SetValue(row + 2, i * 6 + 4, data[row].Batteries[i].Type);
sheet.SetValue(row + 2, i * 6 + 5, (int)data[row].Batteries[i].CellsCount);
sheet.SetValue(row + 2, i * 6 + 6, PersianDateUtils.GetPersianDateString(data[row].Batteries[i].ProductionDate));
sheet.SetValue(row + 2, i * 6 + 7, PersianDateUtils.GetPersianDateString(data[row].Batteries[i].InstallationDate));
}
}
}
private ConnectionViewModel ConnectionToViewModel(Connection c, bool addMoreDetails = false)
{
var vm = Mapper.Map <ConnectionViewModel>(c);
if (c.CustomerId != null)
{
vm.Customer = db.FindById <Customer>(c.CustomerId);
vm.CustomerIcon = c.CustomerIcon;
}
foreach (var e in db.Find <EndPoint>(e => e.Connection == c.Id).SortBy(e => e.Index).ToEnumerable())
{
var evm = Mapper.Map <EndPointViewModel>(e);
if (addMoreDetails)
{
var device = db.FindById <Device>(evm.Device);
evm.Center = device.GetCenterId(db);
}
vm.EndPoints.Add(evm);
}
var endPointsIds = vm.EndPoints.Select(e => e.Id).ToList();
var createActivity = db.FindFirst <UserActivity>(a => endPointsIds.Contains(a.ObjId) && a.ActivityType == ActivityType.Insert);
var lastEditActivity = db.Find <UserActivity>(a => endPointsIds.Contains(a.ObjId) && a.ActivityType == ActivityType.Update)
.Project(a => new { a.Time, a.Username })
.SortByDescending(a => a.Time).FirstOrDefault();
if (createActivity != null)
{
vm.CreateDate = PersianDateUtils.GetPersianDateString(createActivity.Time);
var user = db.FindFirst <AuthUserX>(u => u.Username == createActivity.Username);
if (user != null)
{
vm.CreatedUser = user.DisplayName;
}
}
if (lastEditActivity != null)
{
vm.LastEditDate = PersianDateUtils.GetPersianDateString(lastEditActivity.Time);
var user = db.FindFirst <AuthUserX>(u => u.Username == lastEditActivity.Username);
if (user != null)
{
vm.EditedUser = user.DisplayName;
}
}
return(vm);
}
public async Task <ActionResult <List <EquipmentsPmListItemVM> > > List(PmSearchVM search)
{
stringNormalizer.Preprocess(search);
var query = GetPmList(search);
if (query == null)
{
return(new List <EquipmentsPmListItemVM>());
}
if (search.Limit > 0)
{
query = query.Limit(search.Limit);
}
else
{
query = query.Limit(1000);
}
var pmList = await query.ToListAsync();
var centers = pmList.GroupBy(pm => pm.CenterId)
.Select(g => db.FindById <CommCenterX>(g.Key)).Where(c => c != null).ToDictionary(c => c.Id);
var cities = centers.Values.GroupBy(c => c.City).Select(c => db.FindById <City>(c.Key)).Where(c => c != null).ToDictionary(c => c.Id);
var users = pmList.GroupBy(pm => pm.ReportingUser)
.Select(g => db.FindById <AuthUserX>(g.Key)).ToDictionary(u => u.Id);
return(pmList.Select(pm => new EquipmentsPmListItemVM
{
Id = pm.Id,
Center = centers.ContainsKey(pm.CenterId) ? centers[pm.CenterId].Name : "(مرکز حذف شده)",
City = centers.ContainsKey(pm.CenterId) && cities.ContainsKey(centers[pm.CenterId].City) ? cities[centers[pm.CenterId].City].Name : "",
EditDate = PersianDateUtils.GetPersianDateString(pm.EditDate),
SubmitDate = PersianDateUtils.GetPersianDateString(pm.SubmitDate),
PmDate = PersianDateUtils.GetPersianDateString(pm.PmDate),
ReportingUser = users[pm.ReportingUser]?.DisplayName ?? "(کاربر حذف شده)",
TotalRate = pm.TotalRate
})
.ToList());
}
public DataTable Create <T>(DataTable table, IEnumerable <T> list, bool convertDateToPersian = true, bool includeTimeInDates = true,
bool addIndexColumn = false, string[] excludeColumns = null, Dictionary <string, Dictionary <ObjectId, string> > valuesReferenceReplacement = null)
{
if (list == null)
{
return(null);
}
Dictionary <PropertyInfo, string> displayNames = CreateDataTableColumns <T>(table, convertDateToPersian, includeTimeInDates, addIndexColumn, excludeColumns);
int i = 1;
foreach (T item in list)
{
DataRow row = table.NewRow();
if (addIndexColumn)
{
row[INDEX_COLUMN] = i++;
}
foreach (PropertyInfo p in displayNames.Keys)
{
object value = p.GetValue(item);
if (value is ObjectId)
{
if (valuesReferenceReplacement != null && valuesReferenceReplacement.ContainsKey(p.Name))
{
if (valuesReferenceReplacement[p.Name].ContainsKey((ObjectId)value))
{
value = valuesReferenceReplacement[p.Name][(ObjectId)value];
}
else
{
value = null;
}
}
else
{
value = value.ToString();
}
}
else if (p.PropertyType.IsEnum)
{
value = DisplayUtils.DisplayName(p.PropertyType, value.ToString());
}
else if (value is DateTime && convertDateToPersian)
{
value = PersianDateUtils.GetPersianDateString((DateTime)value, includeTimeInDates);
}
else if (value is IEnumerable && !(value is string))
{
StringBuilder sb = new StringBuilder();
Type itemsType = null;
foreach (var v in (IEnumerable)value)
{
if (itemsType == null)
{
itemsType = v.GetType();
}
sb.Append(DisplayUtils.DisplayName(itemsType, v.ToString())).Append(" ; ");
}
if (sb.Length > 3)
{
sb.Remove(sb.Length - 3, 3);
}
value = sb.ToString();
}
row[displayNames[p]] = value == null ? DBNull.Value : value;
}
table.Rows.Add(row);
}
return(table);
}
private byte[] CreateExcelFile(List <EquipmentsPM> pms)
{
using var memStream = new MemoryStream();
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using var package = new ExcelPackage(memStream);
var citiesDic = Cities.ToDictionary(i => i.Id);
var centers = db.All <CommCenterX>().ToDictionary(i => i.Id);
var usersName = GetUsersName();
// diesels sheet
ExcelWorksheet sheet = package.Workbook.Worksheets.Add("دیزل ها");
sheet.View.RightToLeft = true;
sheet.DefaultColWidth = 20;
int col = 1;
sheet.SetValue(1, col++, "شهر");
sheet.SetValue(1, col++, "مرکز");
sheet.SetValue(1, col++, "کاربر ثبت کننده");
sheet.SetValue(1, col++, "تاریخ ثبت");
sheet.SetValue(1, col++, "تاریخ تغییر");
Dictionary <PropertyInfo, int> columns = new Dictionary <PropertyInfo, int>();
PropertyInfo[] props = typeof(DieselPM).GetProperties();
for (int i = 0; i < 3; i++)
{
foreach (PropertyInfo p in props)
{
if (!columns.ContainsKey(p))
{
columns.Add(p, col);
}
string dispName = DisplayUtils.DisplayName(p);
sheet.SetValue(1, col++, (i + 1) + "-" + dispName);
}
}
sheet.Row(1).Style.Font.Bold = true;
int row = 2;
foreach (var pm in pms)
{
if (centers.ContainsKey(pm.CenterId))
{
if (citiesDic.ContainsKey(centers[pm.CenterId].City))
{
sheet.SetValue(row, 1, citiesDic[centers[pm.CenterId].City].Name);
}
sheet.SetValue(row, 2, centers[pm.CenterId].Name);
}
if (usersName.ContainsKey(pm.ReportingUser))
{
sheet.SetValue(row, 3, usersName[pm.ReportingUser]);
}
sheet.SetValue(row, 4, PersianDateUtils.GetPersianDateString(pm.SubmitDate));
sheet.SetValue(row, 5, PersianDateUtils.GetPersianDateString(pm.EditDate));
for (int i = 0; i < pm.DieselsPM.Count; i++)
{
int colOffset = i * (props.Length + 1);
foreach (PropertyInfo prop in props)
{
object value = prop.GetValue(pm.DieselsPM[i]);
if (value != null)
{
sheet.SetValue(row, columns[prop] + colOffset, value.ToString());
}
}
}
row++;
}
// rectifiers sheet
sheet = package.Workbook.Worksheets.Add("یکسوسازها");
sheet.View.RightToLeft = true;
sheet.DefaultColWidth = 20;
col = 1;
sheet.SetValue(1, col++, "شهر");
sheet.SetValue(1, col++, "مرکز");
sheet.SetValue(1, col++, "کاربر ثبت کننده");
sheet.SetValue(1, col++, "تاریخ ثبت");
sheet.SetValue(1, col++, "تاریخ تغییر");
columns = new Dictionary <PropertyInfo, int>();
props = typeof(RectifierPM).GetProperties();
for (int i = 0; i < 3; i++)
{
foreach (PropertyInfo p in props)
{
if (!columns.ContainsKey(p))
{
columns.Add(p, col);
}
string dispName = DisplayUtils.DisplayName(p);
sheet.SetValue(1, col++, (i + 1) + "-" + dispName);
}
}
sheet.Row(1).Style.Font.Bold = true;
row = 2;
foreach (var pm in pms)
{
if (centers.ContainsKey(pm.CenterId))
{
if (citiesDic.ContainsKey(centers[pm.CenterId].City))
{
sheet.SetValue(row, 1, citiesDic[centers[pm.CenterId].City].Name);
}
sheet.SetValue(row, 2, centers[pm.CenterId].Name);
}
if (usersName.ContainsKey(pm.ReportingUser))
{
sheet.SetValue(row, 3, usersName[pm.ReportingUser]);
}
sheet.SetValue(row, 4, PersianDateUtils.GetPersianDateString(pm.SubmitDate));
sheet.SetValue(row, 5, PersianDateUtils.GetPersianDateString(pm.EditDate));
for (int i = 0; i < pm.RectifiersPM.Count; i++)
{
int colOffset = i * (props.Length + 1);
foreach (PropertyInfo prop in props)
{
object value = prop.GetValue(pm.RectifiersPM[i]);
if (value != null)
{
sheet.SetValue(row, columns[prop] + colOffset, value.ToString());
}
}
}
row++;
}
// batteries sheet
sheet = package.Workbook.Worksheets.Add("باتریها");
sheet.View.RightToLeft = true;
sheet.DefaultColWidth = 20;
col = 1;
sheet.SetValue(1, col++, "شهر");
sheet.SetValue(1, col++, "مرکز");
sheet.SetValue(1, col++, "کاربر ثبت کننده");
sheet.SetValue(1, col++, "تاریخ ثبت");
sheet.SetValue(1, col++, "تاریخ تغییر");
for (int i = 0; i < 4; i++)
{
sheet.SetValue(1, col++, (i + 1) + "-" + DisplayUtils.DisplayName <BatteryPM.BatterySeriesPM>(bs => bs.DistilledWaterAdded));
sheet.SetValue(1, col++, (i + 1) + "-" + DisplayUtils.DisplayName <BatteryPM.BatterySeriesPM>(bs => bs.Temperature));
sheet.SetValue(1, col++, (i + 1) + "-" + DisplayUtils.DisplayName <BatteryPM.BatterySeriesPM>(bs => bs.OutputCurrent));
sheet.SetValue(1, col++, (i + 1) + "-" + DisplayUtils.DisplayName <BatteryPM.BatterySeriesPM>(bs => bs.Description));
for (int j = 1; j <= 25; j++)
{
sheet.SetValue(1, col++, "سری " + (i + 1) + " -ولتاژ سلول " + j);
}
for (int j = 1; j <= 25; j++)
{
sheet.SetValue(1, col++, "سری " + (i + 1) + " -غلظت سلول " + j);
}
}
sheet.Row(1).Style.Font.Bold = true;
row = 2;
foreach (var pm in pms)
{
if (centers.ContainsKey(pm.CenterId))
{
if (citiesDic.ContainsKey(centers[pm.CenterId].City))
{
sheet.SetValue(row, 1, citiesDic[centers[pm.CenterId].City].Name);
}
sheet.SetValue(row, 2, centers[pm.CenterId].Name);
}
if (usersName.ContainsKey(pm.ReportingUser))
{
sheet.SetValue(row, 3, usersName[pm.ReportingUser]);
}
sheet.SetValue(row, 4, PersianDateUtils.GetPersianDateString(pm.SubmitDate));
sheet.SetValue(row, 5, PersianDateUtils.GetPersianDateString(pm.EditDate));
col = 6;
for (int i = 0; i < pm.BatteriesPM.Count; i++)
{
for (int j = 0; j < pm.BatteriesPM[i].Series.Count; j++)
{
sheet.SetValue(row, col++, pm.BatteriesPM[i].Series[j].DistilledWaterAdded);
sheet.SetValue(row, col++, pm.BatteriesPM[i].Series[j].Temperature);
sheet.SetValue(row, col++, pm.BatteriesPM[i].Series[j].OutputCurrent);
sheet.SetValue(row, col++, pm.BatteriesPM[i].Series[j].Description);
for (int k = 0; k < pm.BatteriesPM[i].Series[j].Voltages.Length; k++)
{
sheet.SetValue(row, col++, pm.BatteriesPM[i].Series[j].Voltages[k]);
}
for (int k = 0; k < pm.BatteriesPM[i].Series[j].Densities.Length; k++)
{
sheet.SetValue(row, col++, pm.BatteriesPM[i].Series[j].Densities[k]);
}
}
}
row++;
}
package.Save();
return(memStream.ToArray());
}