private void CreateExcelFromDGV(DataGridView dgv, int minRow, int rowCount, int minColumn, int columnCount)
{
_excelDoc = new ExcelDoc();
WriteHeader(dgv, minColumn, columnCount);
int diffRow = GetDiffRows(minRow);
int diffColumn = GetDiffColumns(minColumn);
int index = 2;
for (int i = minRow; i < rowCount; i++)
{
bool IsAdded = false;
for (int j = minColumn; j < columnCount; j++)
{
if (dgv.Rows[i].Cells[j].Visible)
{
_excelDoc.setValue(index, dgv.Rows[i].Cells[j].ColumnIndex - diffColumn, dgv.Rows[i].Cells[j].Value.ToString());
IsAdded = true;
}
}
if (IsAdded)
{
index++;
}
}
}
public void showNotice(DTP dtp)
{
_excelDoc = openDocumentExcel("Извещение о страховом случае");
Owners owners = Owners.getInstance();
_excelDoc.setValue(6, 5, owners.getItem(Convert.ToInt32(_car.ownerID))); //страхователь
_excelDoc.setValue(7, 6, "а/я 34, 196128"); //почтовый адрес
_excelDoc.setValue(8, 7, "320-40-04"); //телефон
DriverCarList driverCarList = DriverCarList.getInstance();
Driver driver = driverCarList.GetDriver(_car, dtp.Date);
PassportList passportList = PassportList.getInstance();
Passport passport = passportList.getLastPassport(driver);
if (passport.Number != string.Empty)
{
string number = passport.Number;
string[] numbers = number.Split(' ');
_excelDoc.setValue(10, 3, numbers[0]); //серия
_excelDoc.setValue(10, 6, numbers[1]); //номер
_excelDoc.setValue(11, 3, passport.GiveOrg); //кем выдан
_excelDoc.setValue(12, 4, passport.GiveDate.ToShortDateString()); //дата выдачи
}
PolicyList policyList = PolicyList.getInstance();
Policy policy = policyList.getItem(_car, PolicyType.КАСКО);
_excelDoc.setValue(14, 6, policy.Number); //полис
_excelDoc.setValue(16, 6, string.Concat(_car.Mark.Name, " ", _car.info.Model)); //марка а/м
_excelDoc.setValue(18, 6, _car.Grz); //рег номер а/м
_excelDoc.setValue(20, 6, _car.vin); //вин
_excelDoc.setValue(22, 6, dtp.Date.ToShortDateString()); //дата дтп
_excelDoc.setValue(27, 2, driver.GetName(NameType.Full)); //водитель фио
Regions regions = Regions.getInstance();
_excelDoc.setValue(29, 3, regions.getItem(Convert.ToInt32(dtp.IDRegion))); //город
_excelDoc.setValue(31, 14, dtp.Damage); //повреждения
_excelDoc.setValue(33, 2, dtp.Facts); //обстоятельства происшествия
//SsDTP ssDTP = SsDTPList.getInstance().getItem(_car.Mark);
//_excelDoc.setValue(63, 11, ssDTP.ServiceStantion);
//DateTime date = DateTime.Today;
//MyDateTime myDate = new MyDateTime(date.ToShortDateString());
//_excelDoc.setValue(71, 3, string.Concat("« ", date.Day.ToString(), " »"));
//_excelDoc.setValue(71, 4, myDate.MonthToStringGenitive());
//_excelDoc.setValue(71, 8, date.Year.ToString().Substring(2, 2));
_excelDoc.Show();
}
public List<string> Load()
{
using (ExcelDoc excelDoc = new ExcelDoc(path))
{
loaders[fuelReport].Invoke(excelDoc);
}
return erorrs;
}
private void ReadFile(string filename)
{
try
{
using (ExcelDoc excelDoc = new ExcelDoc(filename))
{
try
{
excelDoc.SetList("Расходы по а-м");
string grz = (excelDoc.getValue("B4") != null) ? excelDoc.getValue("B4").ToString() : string.Empty;
Car car = GetCar(grz);
if (car == null)
{
string driverFIO = (excelDoc.getValue("B5") != null) ? excelDoc.getValue("B5").ToString() : string.Empty;
DriverList driverList = DriverList.getInstance();
Driver driver = driverList.getItemByFIO(driverFIO);
if (driver != null)
{
DriverCarList driverCarList = DriverCarList.getInstance();
car = driverCarList.GetCar(driver);
}
if (car == null)
{
_mileageReportList.Add(new MileageReport(null, string.Concat("Не найден автомобиль: ", grz, " сотрудник: ", driverFIO, ". Файл: ", filename)));
}
}
if (car != null)
{
string value = (excelDoc.getValue("C8") != null) ? excelDoc.getValue("C8").ToString() : string.Empty;
SetMileage(car, value);
}
}
catch (IndexOutOfRangeException)
{
_mileageReportList.Add(new MileageReport(null, string.Concat("Ошибка при чтении файла: ", filename)));
}
catch (OverflowException)
{
_mileageReportList.Add(new MileageReport(null, string.Concat("Указан слишком большой пробег в файле: ", filename)));
}
}
}
catch
{
_mileageReportList.Add(new MileageReport(null, string.Concat("Ошибка при чтении файла: ", filename)));
}
}
private void ReadFile(string filename)
{
try
{
using (ExcelDoc excelDoc = new ExcelDoc(filename))
{
try
{
excelDoc.SetList("Расходы по а-м");
string grz = (excelDoc.getValue("B4") != null) ? excelDoc.getValue("B4").ToString() : string.Empty;
Car car = GetCar(grz);
if (car == null)
{
string driverFIO = (excelDoc.getValue("B5") != null) ? excelDoc.getValue("B5").ToString() : string.Empty;
DriverList driverList = DriverList.getInstance();
Driver driver = driverList.getItemByFIO(driverFIO);
if (driver != null)
{
DriverCarList driverCarList = DriverCarList.getInstance();
car = driverCarList.GetCar(driver);
}
if (car == null)
_mileageReportList.Add(new MileageReport(null, string.Concat("Не найден автомобиль: ", grz, " сотрудник: ", driverFIO, ". Файл: ", filename)));
}
if (car != null)
{
string value = (excelDoc.getValue("C8") != null) ? excelDoc.getValue("C8").ToString() : string.Empty;
SetMileage(car, value);
}
}
catch (IndexOutOfRangeException)
{
_mileageReportList.Add(new MileageReport(null, string.Concat("Ошибка при чтении файла: ", filename)));
}
catch (OverflowException)
{
_mileageReportList.Add(new MileageReport(null, string.Concat("Указан слишком большой пробег в файле: ", filename)));
}
}
}
catch
{
_mileageReportList.Add(new MileageReport(null, string.Concat("Ошибка при чтении файла: ", filename)));
}
}
public void StartImport()
{
try
{
using (ExcelDoc excel = new ExcelDoc(FilePath))
{
int i = 7;
string curCell = "H" + i;
while (excel.getValue(curCell) != null)
{
curCell = "A" + i;
if ((excel.getValue(curCell) == null) ||
((excel.getValue(curCell).ToString().ToUpper() != "AM") && (excel.getValue(curCell).ToString().ToUpper() != "АМ")))
{
curCell = "H" + i;
i++;
continue;
}
curCell = "H" + i;
string number = excel.getValue(curCell).ToString();
curCell = "D" + i;
DateTime dateBegin;
DateTime.TryParse(excel.getValue1(curCell).ToString(), out dateBegin);
curCell = "E" + i;
DateTime dateEnd;
DateTime.TryParse(excel.getValue1(curCell).ToString(), out dateEnd);
for (DateTime date = dateBegin; date <= dateEnd; date = date.AddDays(1))
{
Tabel tabel = new Tabel(number, date) { Comment = "businessTrip" };
tabel.Save();
}
curCell = "H" + i;
i++;
}
}
}
catch (NullReferenceException ex)
{
LogManager.Logger.Error(ex, "Error in file {file}", FilePath);
}
catch (COMException ex)
{
LogManager.Logger.Error(ex, "Error in file {file}", FilePath);
}
}
private static void LoadNeste(ExcelDoc excel)
{
int i = 4; //начальный индекс
string currentCell = "A" + i;
while (excel.getValue(currentCell) != null)
{
if (excel.getValue(currentCell).ToString() == "Grand Total")
break;
currentCell = "B" + i;
if (excel.getValue(currentCell) != null)
{
i++;
currentCell = "A" + i;
continue;
}
currentCell = "A" + i;
string number = excel.getValue(currentCell).ToString().Split(' ')[1]; //split example Карта: 7105066553656018
FuelCard fuelCard = fuelCardList.getItem(number);
if (fuelCard == null)
{
i++;
erorrs.Add("Не найдена карта №" + number); //throw new NullReferenceException("Не найдена карта №" + number);
continue;
}
currentCell = "C" + i;
DateTime datetime;
DateTime.TryParse(excel.getValue(currentCell).ToString(), out datetime);//присутствует время, не забываем убирать
currentCell = "D" + i;
string engineTypeName = excel.getValue(currentCell).ToString();
EngineType engineType = GetEngineType(engineTypeName);
currentCell = "E" + i;
double value;
double.TryParse(excel.getValue(currentCell).ToString(), out value);
Fuel fuel = new Fuel(fuelCard, datetime.Date, engineType);
fuel.AddValue(value);
fuel.Save();
i++;
currentCell = "A" + i;
}
}
public void ShowAttacheToOrder()
{
_excelDoc = openDocumentExcel("Приложение к приказу");
string fullNameAuto = string.Concat(_car.Mark.Name, " ", _car.info.Model);
_excelDoc.setValue(18, 2, fullNameAuto);
_excelDoc.setValue(18, 3, _car.Grz);
Driver driver = driverList.getItem(Convert.ToInt32(_invoice.DriverToID));
_excelDoc.setValue(18, 4, driver.GetName(NameType.Full));
_excelDoc.setValue(18, 5, driver.Position);
_excelDoc.Show();
}
public void CreatePolicyTable()
{
const int INDEX_BEGIN = 6;
DateTime date = DateTime.Today.AddMonths(1);
_excelDoc = openDocumentExcel("Таблица страхования");
MyDateTime myDate = new MyDateTime(date.ToShortDateString());
_excelDoc.setValue(2, 1, "Страхуем в " + myDate.MonthToStringPrepositive() + " " + myDate.Year + " г.");
PolicyList policyList = PolicyList.getInstance();
List <Policy> list = policyList.GetPolicyList(date);
List <Car> listCar = policyList.GetCarListByPolicyList(list);
DiagCardList diagCardList = DiagCardList.getInstance();
int rowIndex = INDEX_BEGIN;
foreach (Car car in listCar)
{
_excelDoc.setValue(rowIndex, 2, car.Grz);
_excelDoc.setValue(rowIndex, 3, car.Mark.Name);
_excelDoc.setValue(rowIndex, 4, car.info.Model);
_excelDoc.setValue(rowIndex, 5, car.vin);
_excelDoc.setValue(rowIndex, 6, car.Year);
_excelDoc.setValue(rowIndex, 7, GetPolicyBeginDate(list, car, PolicyType.ОСАГО));
_excelDoc.setValue(rowIndex, 8, GetPolicyBeginDate(list, car, PolicyType.КАСКО));
_excelDoc.setValue(rowIndex, 9, car.info.Owner);
_excelDoc.setValue(rowIndex, 10, car.info.Owner);
_excelDoc.setValue(rowIndex, 11, car.info.Owner);
DiagCard diagCard = diagCardList.getItem(car);
if (diagCard != null)
{
_excelDoc.setValue(rowIndex, 12, diagCard.Date.ToShortDateString());
_excelDoc.setValue(rowIndex, 13, diagCard.Number);
}
rowIndex++;
}
_excelDoc.Show();
}
public void ShowInvoice()
{
_excelDoc = openDocumentExcel("Накладная");
_excelDoc.setValue(7, 2, _car.info.Owner);
_excelDoc.setValue(16, 82, _invoice.Number);
_excelDoc.setValue(16, 98, _invoice.Date.ToShortDateString());
string fullNameAuto = string.Concat("Автомобиль ", _car.Mark.Name, " ", _car.info.Model, ", ", _car.Grz);
_excelDoc.setValue(22, 10, fullNameAuto);
_excelDoc.setValue(22, 53, _car.dateGet.ToShortDateString());
GradeList grades = GradeList.getInstance();
Grade grade = grades.getItem(Convert.ToInt32(_car.GradeID));
PTSList ptsList = PTSList.getInstance();
PTS pts = ptsList.getItem(_car);
string fullDetailAuto = string.Concat("VIN ", _car.vin, ", Двигатель ", _car.eNumber, ", № кузова ", _car.bodyNumber, ", Год выпуска ", _car.Year, " г., Паспорт ",
pts.Number, " от ", pts.Date.ToShortDateString(), ", мощность двигателя ", grade.EPower, " л.с.");
_excelDoc.setValue(47, 2, fullDetailAuto);
Driver driver1 = driverList.getItem(Convert.ToInt32(_invoice.DriverFromID));
Driver driver2 = driverList.getItem(Convert.ToInt32(_invoice.DriverToID));
_excelDoc.setValue(9, 10, driver1.Dept);
_excelDoc.setValue(56, 11, driver1.Position);
_excelDoc.setValue(56, 63, driver1.GetName(NameType.Full));
_excelDoc.setValue(11, 13, driver2.Dept);
_excelDoc.setValue(60, 11, driver2.Position);
_excelDoc.setValue(60, 63, driver2.GetName(NameType.Full));
_excelDoc.Show();
}
public void Create()
{
ExcelDoc doc = new ExcelDoc();
try
{
int i = 1;
foreach (MileageReport item in _mileageReportList)
{
if (item.IsFailed)
{
doc.setValue(i, 1, item.ToString());
i++;
}
}
doc.SetList(2);
i = 1;
foreach (MileageReport item in _mileageReportList)
{
if (!(item.IsFailed))
{
doc.setValue(i, 1, item.ToString());
i++;
}
}
doc.Show();
}
catch
{
doc.Dispose();
}
}
public void Create()
{
ExcelDoc doc = new ExcelDoc();
try
{
int i = 1;
foreach (MileageReport item in _mileageReportList)
{
if (item.IsFailed)
{
doc.setValue(i, 1, item.ToString());
i++;
}
}
doc.SetList(2);
i = 1;
foreach (MileageReport item in _mileageReportList)
{
if (!(item.IsFailed))
{
doc.setValue(i, 1, item.ToString());
i++;
}
}
doc.Show();
}
catch
{
doc.Dispose();
}
}
private static void LoadChecks(ExcelDoc excel)
{
throw new NotImplementedException();
}
public void createWaybill(DateTime date, Driver driver = null)
{
date = new DateTime(date.Year, date.Month, 1);
if (driver == null)
{
DriverCarList driverCarList = DriverCarList.getInstance();
driver = driverCarList.GetDriver(_car, date);
if (driver == null)
{
driver = driverCarList.GetDriver(_car);
InvoiceList invoiceList = InvoiceList.getInstance();
Invoice invoice = invoiceList.getItem(_car);
if ((invoice != null) && (!string.IsNullOrEmpty(invoice.DateMove)))
{
DateTime dateMove;
DateTime.TryParse(invoice.DateMove, out dateMove);
if ((dateMove.Year == date.Year) && (dateMove.Month == date.Month))
date = new DateTime(date.Year, date.Month, dateMove.Day);
}
}
}
_excelDoc = openDocumentExcel("Путевой лист");
_excelDoc.setValue(4, 28, _car.BBNumber.ToString());
MyDateTime myDate = new MyDateTime(date.ToShortDateString());
_excelDoc.setValue(4, 39, driver.ID.ToString() + "/01/" + myDate.MonthSlashYear());
_excelDoc.setValue(6, 15, myDate.DaysRange);
_excelDoc.setValue(6, 19, myDate.MonthToStringNominative());
_excelDoc.setValue(6, 32, date.Year.ToString());
_excelDoc.setValue(29, 35, _car.info.Grade.EngineType.ShortName);
Owners owners = Owners.getInstance();
string owner = owners.getItem(1);
_excelDoc.setValue(8, 8, owner);
_excelDoc.setValue(10, 11, string.Concat(_car.Mark.Name, " ", _car.info.Model));
_excelDoc.setValue(11, 17, _car.Grz);
_excelDoc.setValue(12, 6, driver.GetName(NameType.Full));
_excelDoc.setValue(44, 16, driver.GetName(NameType.Short));
_excelDoc.setValue(26, 40, driver.GetName(NameType.Short));
LicenseList licencesList = LicenseList.getInstance();
DriverLicense driverLicense = licencesList.getItem(driver);
_excelDoc.setValue(14, 10, driverLicense.Number);
_excelDoc.setValue(20, 9, owner);
string suppyAddressName;
if (driver.suppyAddress != string.Empty)
{
suppyAddressName = driver.suppyAddress;
}
else
{
SuppyAddressList suppyAddressList = SuppyAddressList.getInstance();
SuppyAddress suppyAddress = suppyAddressList.getItemByRegion(driver.Region.ID);
if (suppyAddress != null)
suppyAddressName = suppyAddress.ToString();
else
{
PassportList passportList = PassportList.getInstance();
Passport passport = passportList.getLastPassport(driver);
suppyAddressName = passport.Address;
}
}
string suppyAddressName2 = string.Empty;
if (suppyAddressName.Length > 40)
{
for (int i = 30; i < suppyAddressName.Length; i++)
{
if (suppyAddressName[i] == ' ')
{
suppyAddressName2 = suppyAddressName.Substring(i, suppyAddressName.Length - i);
suppyAddressName = suppyAddressName.Substring(0, i);
}
}
}
_excelDoc.setValue(25, 8, suppyAddressName);
_excelDoc.setValue(26, 1, suppyAddressName2);
string mechanicName;
EmployeesList employeesList = EmployeesList.getInstance();
Employees accountant = employeesList.getItem(driver.Region, "Бухгалтер Б.Браун");
if (driver.IsOne)
{
mechanicName = driver.GetName(NameType.Short);
}
else
{
Employees mechanic = employeesList.getItem(driver.Region, "Механик", true);
if (mechanic == null)
mechanicName = driver.GetName(NameType.Short);
else
mechanicName = mechanic.Name;
}
Employees dispatcher = employeesList.getItem(driver.Region, "Диспечер-нарядчик");
string dispatcherName = dispatcher.Name;
_excelDoc.setValue(22, 40, mechanicName);
_excelDoc.setValue(44, 40, mechanicName);
_excelDoc.setValue(31, 18, dispatcherName);
_excelDoc.setValue(35, 18, dispatcherName);
_excelDoc.setValue(43, 72, accountant.Name);
}
private static void LoadPetrol(ExcelDoc excel)
{
int i = 4; //начальный индекс
string currentCell = "B" + i;
while (excel.getValue(currentCell) != null)
{
currentCell = "D" + i;
string number = excel.getValue(currentCell).ToString();
FuelCard fuelCard = fuelCardList.getItem(number);
if (fuelCard == null)
{
i++;
currentCell = "B" + i;
erorrs.Add("Не найдена карта №" + number); //throw new NullReferenceException("Не найдена карта №" + number);
continue;
}
currentCell = "B" + i;
string dateString = excel.getValue1(currentCell).ToString();
DateTime datetime;
DateTime.TryParse(dateString, out datetime);//присутствует время, не забываем убирать
currentCell = "G" + i;
string engineTypeName = excel.getValue(currentCell).ToString();
EngineType engineType = GetEngineType(engineTypeName);
currentCell = "H" + i;
double value;
double.TryParse(excel.getValue(currentCell).ToString(), out value);
Fuel fuel = new Fuel(fuelCard, datetime.Date, engineType);
fuel.AddValue(value);
fuel.Save();
i++;
currentCell = "B" + i;
}
}
public void ShowAttacheToOrder()
{
_excelDoc = openDocumentExcel("Приложение к приказу");
string fullNameAuto = string.Concat(_car.Mark.Name, " ", _car.info.Model);
_excelDoc.setValue(18, 2, fullNameAuto);
_excelDoc.setValue(18, 3, _car.Grz);
Driver driver = driverList.getItem(Convert.ToInt32(_invoice.DriverToID));
_excelDoc.setValue(18, 4, driver.GetName(NameType.Full));
_excelDoc.setValue(18, 5, driver.Position);
_excelDoc.Show();
}
public void ShowInvoice()
{
_excelDoc = openDocumentExcel("Накладная");
_excelDoc.setValue(7, 2, _car.info.Owner);
_excelDoc.setValue(16, 82, _invoice.Number);
_excelDoc.setValue(16, 98, _invoice.Date.ToShortDateString());
string fullNameAuto = string.Concat("Автомобиль ", _car.Mark.Name, " ", _car.info.Model, ", ", _car.Grz);
_excelDoc.setValue(22, 10, fullNameAuto);
_excelDoc.setValue(22, 53, _car.dateGet.ToShortDateString());
GradeList grades = GradeList.getInstance();
Grade grade = grades.getItem(Convert.ToInt32(_car.GradeID));
PTSList ptsList = PTSList.getInstance();
PTS pts = ptsList.getItem(_car);
string fullDetailAuto = string.Concat("VIN ", _car.vin, ", Двигатель ", _car.eNumber, ", № кузова ", _car.bodyNumber, ", Год выпуска ", _car.Year, " г., Паспорт ",
pts.Number, " от ", pts.Date.ToShortDateString(), ", мощность двигателя ", grade.EPower, " л.с.");
_excelDoc.setValue(47, 2, fullDetailAuto);
Driver driver1 = driverList.getItem(Convert.ToInt32(_invoice.DriverFromID));
Driver driver2 = driverList.getItem(Convert.ToInt32(_invoice.DriverToID));
_excelDoc.setValue(9, 10, driver1.Dept);
_excelDoc.setValue(56, 11, driver1.Position);
_excelDoc.setValue(56, 63, driver1.GetName(NameType.Full));
_excelDoc.setValue(11, 13, driver2.Dept);
_excelDoc.setValue(60, 11, driver2.Position);
_excelDoc.setValue(60, 63, driver2.GetName(NameType.Full));
_excelDoc.Show();
}
public void showNotice(DTP dtp)
{
_excelDoc = openDocumentExcel("Извещение о страховом случае");
Owners owners = Owners.getInstance();
_excelDoc.setValue(7, 4, owners.getItem(Convert.ToInt32(_car.ownerID)));
_excelDoc.setValue(8, 5, "а/я 34, 196128");
_excelDoc.setValue(9, 6, "320-40-04");
DriverCarList driverCarList = DriverCarList.getInstance();
Driver driver = driverCarList.GetDriver(_car, dtp.Date);
PassportList passportList = PassportList.getInstance();
Passport passport = passportList.getLastPassport(driver);
if (passport.Number != string.Empty)
{
string number = passport.Number;
string[] numbers = number.Split(' ');
_excelDoc.setValue(11, 2, numbers[0]);
_excelDoc.setValue(11, 5, numbers[1]);
_excelDoc.setValue(12, 2, passport.GiveOrg);
_excelDoc.setValue(13, 3, passport.GiveDate.ToShortDateString());
}
PolicyList policyList = PolicyList.getInstance();
Policy policy = policyList.getItem(_car, PolicyType.КАСКО);
_excelDoc.setValue(15, 5, policy.Number);
_excelDoc.setValue(17, 5, string.Concat(_car.Mark.Name, " ", _car.info.Model));
_excelDoc.setValue(19, 5, _car.Grz);
_excelDoc.setValue(21, 5, _car.vin);
_excelDoc.setValue(23, 5, dtp.Date.ToShortDateString());
_excelDoc.setValue(28, 1, driver.GetName(NameType.Full));
Regions regions = Regions.getInstance();
_excelDoc.setValue(30, 2, regions.getItem(Convert.ToInt32(dtp.IDRegion)));
_excelDoc.setValue(32, 13, dtp.Damage);
_excelDoc.setValue(34, 1, dtp.Facts);
SsDTP ssDTP = SsDTPList.getInstance().getItem(_car.Mark);
_excelDoc.setValue(63, 11, ssDTP.ServiceStantion);
DateTime date = DateTime.Today;
MyDateTime myDate = new MyDateTime(date.ToShortDateString());
_excelDoc.setValue(71, 3, string.Concat("« ", date.Day.ToString(), " »"));
_excelDoc.setValue(71, 4, myDate.MonthToStringGenitive());
_excelDoc.setValue(71, 8, date.Year.ToString().Substring(2, 2));
_excelDoc.Show();
}
public void CreatePolicyTable()
{
const int INDEX_BEGIN = 6;
DateTime date = DateTime.Today.AddMonths(1);
_excelDoc = openDocumentExcel("Таблица страхования");
MyDateTime myDate = new MyDateTime(date.ToShortDateString());
_excelDoc.setValue(2, 1, "Страхуем в " + myDate.MonthToStringPrepositive() + " " + myDate.Year + " г.");
PolicyList policyList = PolicyList.getInstance();
List<Policy> list = policyList.GetPolicyList(date);
List<Car> listCar = policyList.GetCarListByPolicyList(list);
DiagCardList diagCardList = DiagCardList.getInstance();
int rowIndex = INDEX_BEGIN;
foreach (Car car in listCar)
{
_excelDoc.setValue(rowIndex, 2, car.Grz);
_excelDoc.setValue(rowIndex, 3, car.Mark.Name);
_excelDoc.setValue(rowIndex, 4, car.info.Model);
_excelDoc.setValue(rowIndex, 5, car.vin);
_excelDoc.setValue(rowIndex, 6, car.Year);
_excelDoc.setValue(rowIndex, 7, GetPolicyBeginDate(list, car, PolicyType.ОСАГО));
_excelDoc.setValue(rowIndex, 8, GetPolicyBeginDate(list, car, PolicyType.КАСКО));
_excelDoc.setValue(rowIndex, 9, car.info.Owner);
_excelDoc.setValue(rowIndex, 10, car.info.Owner);
_excelDoc.setValue(rowIndex, 11, car.info.Owner);
DiagCard diagCard = diagCardList.getItem(car);
if (diagCard != null)
{
_excelDoc.setValue(rowIndex, 12, diagCard.Date.ToShortDateString());
_excelDoc.setValue(rowIndex, 13, diagCard.Number);
}
rowIndex++;
}
_excelDoc.Show();
}
/* Старое извещение
* public void showNotice(DTP dtp)
* {
* _excelDoc = openDocumentExcel("Извещение о страховом случае");
*
* Owners owners = Owners.getInstance();
*
* _excelDoc.setValue(7, 4, owners.getItem(Convert.ToInt32(_car.ownerID)));
* _excelDoc.setValue(8, 5, "а/я 34, 196128");
* _excelDoc.setValue(9, 6, "320-40-04");
*
* DriverCarList driverCarList = DriverCarList.getInstance();
* Driver driver = driverCarList.GetDriver(_car, dtp.Date);
*
* PassportList passportList = PassportList.getInstance();
* Passport passport = passportList.getLastPassport(driver);
*
* if (passport.Number != string.Empty)
* {
* string number = passport.Number;
* string[] numbers = number.Split(' ');
*
* _excelDoc.setValue(11, 2, numbers[0]);
* _excelDoc.setValue(11, 5, numbers[1]);
*
* _excelDoc.setValue(12, 2, passport.GiveOrg);
* _excelDoc.setValue(13, 3, passport.GiveDate.ToShortDateString());
* }
*
* PolicyList policyList = PolicyList.getInstance();
* Policy policy = policyList.getItem(_car, PolicyType.КАСКО);
* _excelDoc.setValue(15, 5, policy.Number);
*
* _excelDoc.setValue(17, 5, string.Concat(_car.Mark.Name, " ", _car.info.Model));
* _excelDoc.setValue(19, 5, _car.Grz);
* _excelDoc.setValue(21, 5, _car.vin);
*
* _excelDoc.setValue(23, 5, dtp.Date.ToShortDateString());
*
* _excelDoc.setValue(28, 1, driver.GetName(NameType.Full));
*
* Regions regions = Regions.getInstance();
*
* _excelDoc.setValue(30, 2, regions.getItem(Convert.ToInt32(dtp.IDRegion)));
* _excelDoc.setValue(32, 13, dtp.Damage);
* _excelDoc.setValue(34, 1, dtp.Facts);
*
* SsDTP ssDTP = SsDTPList.getInstance().getItem(_car.Mark);
*
* _excelDoc.setValue(63, 11, ssDTP.ServiceStantion);
*
* DateTime date = DateTime.Today;
* MyDateTime myDate = new MyDateTime(date.ToShortDateString());
*
* _excelDoc.setValue(71, 3, string.Concat("« ", date.Day.ToString(), " »"));
* _excelDoc.setValue(71, 4, myDate.MonthToStringGenitive());
* _excelDoc.setValue(71, 8, date.Year.ToString().Substring(2, 2));
*
* _excelDoc.Show();
* }
*
*/
public void createWaybill(DateTime date, Driver driver = null)
{
date = new DateTime(date.Year, date.Month, 1);
if (driver == null)
{
DriverCarList driverCarList = DriverCarList.getInstance();
driver = driverCarList.GetDriver(_car, date);
if (driver == null)
{
driver = driverCarList.GetDriver(_car);
InvoiceList invoiceList = InvoiceList.getInstance();
Invoice invoice = invoiceList.getItem(_car);
if ((invoice != null) && (!string.IsNullOrEmpty(invoice.DateMove)))
{
DateTime dateMove;
DateTime.TryParse(invoice.DateMove, out dateMove);
if ((dateMove.Year == date.Year) && (dateMove.Month == date.Month))
{
date = new DateTime(date.Year, date.Month, dateMove.Day);
}
}
}
}
_excelDoc = openDocumentExcel("Путевой лист");
_excelDoc.setValue(4, 28, _car.BBNumber.ToString());
MyDateTime myDate = new MyDateTime(date.ToShortDateString());
_excelDoc.setValue(4, 39, driver.ID.ToString() + "/01/" + myDate.MonthSlashYear());
_excelDoc.setValue(6, 15, myDate.DaysRange);
_excelDoc.setValue(6, 19, myDate.MonthToStringNominative());
_excelDoc.setValue(6, 32, date.Year.ToString());
_excelDoc.setValue(29, 35, _car.info.Grade.EngineType.ShortName);
MileageMonthList mml = new MileageMonthList(_car.ID, date.Year + "-" + date.Month + "-01");
/* Из файла Татьяны Мироновой пробег за месяц */
_excelDoc.setValue(19, 39, mml.PSN);
_excelDoc.setValue(33, 41, mml.Gas);
_excelDoc.setValue(35, 41, mml.GasBegin);
_excelDoc.setValue(36, 41, mml.GasEnd);
_excelDoc.setValue(37, 41, mml.GasNorm);
_excelDoc.setValue(38, 41, mml.GasNorm);
_excelDoc.setValue(43, 39, mml.PSK);
_excelDoc.setValue(41, 59, mml.Mileage);
Owners owners = Owners.getInstance();
string owner = owners.getItem(1);
_excelDoc.setValue(8, 8, owner);
_excelDoc.setValue(10, 11, string.Concat(_car.Mark.Name, " ", _car.info.Model));
_excelDoc.setValue(11, 17, _car.Grz);
_excelDoc.setValue(12, 6, driver.GetName(NameType.Full));
_excelDoc.setValue(44, 16, driver.GetName(NameType.Short));
_excelDoc.setValue(26, 40, driver.GetName(NameType.Short));
LicenseList licencesList = LicenseList.getInstance();
DriverLicense driverLicense = licencesList.getItem(driver);
_excelDoc.setValue(14, 10, driverLicense.Number);
_excelDoc.setValue(20, 9, owner);
string suppyAddressName;
if (driver.suppyAddress != string.Empty)
{
suppyAddressName = driver.suppyAddress;
}
else
{
SuppyAddressList suppyAddressList = SuppyAddressList.getInstance();
SuppyAddress suppyAddress = suppyAddressList.getItemByRegion(driver.Region.ID);
if (suppyAddress != null)
{
suppyAddressName = suppyAddress.ToString();
}
else
{
PassportList passportList = PassportList.getInstance();
Passport passport = passportList.getLastPassport(driver);
suppyAddressName = passport.Address;
}
}
string suppyAddressName2 = string.Empty;
if (suppyAddressName.Length > 40)
{
for (int i = 30; i < suppyAddressName.Length; i++)
{
if (suppyAddressName[i] == ' ')
{
suppyAddressName2 = suppyAddressName.Substring(i, suppyAddressName.Length - i);
suppyAddressName = suppyAddressName.Substring(0, i);
}
}
}
_excelDoc.setValue(25, 8, suppyAddressName);
_excelDoc.setValue(26, 1, suppyAddressName2);
string mechanicName;
EmployeesList employeesList = EmployeesList.getInstance();
Employees accountant = employeesList.getItem(driver.Region, "Бухгалтер Б.Браун");
if (driver.IsOne)
{
mechanicName = driver.GetName(NameType.Short);
}
else
{
Employees mechanic = employeesList.getItem(driver.Region, "Механик", true);
if (mechanic == null)
{
mechanicName = driver.GetName(NameType.Short);
}
else
{
mechanicName = mechanic.Name;
}
}
Employees dispatcher = employeesList.getItem(driver.Region, "Диспечер-нарядчик");
string dispatcherName = dispatcher.Name;
_excelDoc.setValue(22, 40, mechanicName);
_excelDoc.setValue(44, 40, mechanicName);
_excelDoc.setValue(31, 18, dispatcherName);
_excelDoc.setValue(35, 18, dispatcherName);
_excelDoc.setValue(43, 72, accountant.Name);
}
private void CreateExcelFromDGV(DataGridView dgv, int minRow, int rowCount, int minColumn, int columnCount)
{
_excelDoc = new ExcelDoc();
WriteHeader(dgv, minColumn, columnCount);
int diffRow = GetDiffRows(minRow);
int diffColumn = GetDiffColumns(minColumn);
int index = 2;
for (int i = minRow; i < rowCount; i++)
{
bool IsAdded = false;
for (int j = minColumn; j < columnCount; j++)
{
if (dgv.Rows[i].Cells[j].Visible)
{
_excelDoc.setValue(index, dgv.Rows[i].Cells[j].ColumnIndex - diffColumn, dgv.Rows[i].Cells[j].Value.ToString());
IsAdded = true;
}
}
if (IsAdded)
index++;
}
}