internal List<SliceDataResults> SliceAndQueryAll(string direction, string sliceType, int colNum, float sliceWidth, float startPosition,
float endPosition, DataRecords.ColumnManager columnManager, List<int> variableColumnsToCalculate,
float domainToQuery, out string messages, bool weightByVol, bool weightByTonnes, int domainColumnID,
BackgroundWorker workerProcessData, int startPerc, int endPerc, string statsusTagMessage, bool weightByLength)
{
messages = "";
workerProcessData.ReportProgress(startPerc, "Gathering slice data for " + statsusTagMessage);
List<SliceData> allSlices = columnManager.SliceBy(colNum, sliceWidth, startPosition, endPosition, out messages, workerProcessData, startPerc, endPerc, statsusTagMessage);
// now pick out all the sample IDs for the desired slice
messages += "\n\nSlice num";
foreach (int vc in variableColumnsToCalculate)
{
messages += ",Wt avg (" + vc + "), Vol (" + vc + ") , Num blocks (" + vc + ")";
}
messages += "\n";
int sliceID = 0;
List<SliceDataResults> sliceResults = new List<SliceDataResults>();
foreach (SliceData aSlice in allSlices)
{
SliceDataResults sres = columnManager.QuerySliceData(direction, sliceType, aSlice, variableColumnsToCalculate, sliceID, domainToQuery, weightByVol,weightByTonnes, domainColumnID, weightByLength);
sliceResults.Add(sres);
messages += sres.GetMessages();
sliceID++;
}
return sliceResults;
}
internal List<float> GetDistinctDomains(int columnNumber, DataRecords.ColumnManager columnManager)
{
List<float> domains = null;
domains = columnManager.QueryAllDomains(columnNumber);
return domains;
}
protected override void ReadDoc()
{
base.ReadDoc();
CreateShopList();
bool endrange = true;
int index = 3;
var expression = new Regex("([0-9]-[0-9]{3,18})", RegexOptions.None);
while (endrange)
{
Cells = Sheet.Range[GetColumnIndex(0, index), Type.Missing];
if (Cells.Value2 != null)
{
Match m = expression.Match(Cells.Value2);
if (m.Success)
{
var rec = GetOrderRecord(index);
if (rec != null)
{
DataRecords.Add(rec);
}
}
}
else
{
endrange = false;
}
index++;
}
}
internal PhysicalDimensions QueryLocationColumns(int xCol, int yCol, int zCol, DataRecords.ColumnManager columnManager)
{
ColumnStats x = columnManager.GetXYZColumnData(xCol);
ColumnStats y = columnManager.GetXYZColumnData(yCol);
ColumnStats z = columnManager.GetXYZColumnData(zCol);
PhysicalDimensions dim = new PhysicalDimensions();
dim.originX = x.min;
dim.originY = y.min;
dim.originZ = z.min;
dim.maximumX = x.max;
dim.maximumY = y.max;
dim.maximumZ = z.max;
return dim;
}
/// <exception cref="FormatException">Не правильный формат файла. Лист AssortForOrder не найден.</exception>
protected override void ReadDoc()
{
Excelsheets = Workbook.Worksheets;
Sheet = (Worksheet)Excelsheets.Item[1];
if (Sheet.Name != "ReqAssort")
{
throw new FormatException(@"Не правильный формат файла. Лист ReqAssort не найден.");
}
Cells = Sheet.Range["A1", Type.Missing];
string str = Convert.ToString(Cells.Value2);
if (str != "Код")
{
throw new FormatException(@"Не правильный формат файла. Поле Код не найдено.");
}
bool endrange = true;
int index = 2;
var expression = new Regex("([0-9]-[0-9]{3,18})", RegexOptions.None);
while (endrange)
{
string eindex = string.Format("A{0}", index);
Cells = Sheet.Range[eindex, Type.Missing];
if (Cells.Value2 != null)
{
Match m = expression.Match(Cells.Value2);
if (m.Success)
{
DataRecords.Add(new DataRecord()
{
Code = Convert.ToString(Cells.Value2)
});
}
}
else
{
endrange = false;
}
index++;
}
}
protected override void ReadDoc()
{
NestleFormatCheck();
bool endrange = true;
int index = 2;
while (endrange)
{
Cells = Sheet.Range[GetColumnIndex(0, index), Type.Missing];
if (Cells.Value2 != null)
{
DataRecords.Add(GetNestleOrderRecord(index));
}
else
{
endrange = false;
}
index++;
}
}
public void LoadAllInfo()
{
Console.WriteLine("Conerting starts");
DateTime startLoadTime = DateTime.Now;
PLFUnit.PLFUnitClass plf;
CARS cars = new CARS(fb_con);
cars.LoadAllCars();
DEPARTMENTS depar = new DEPARTMENTS(fb_con);
depar.LoadAllDepartments();
WORKERS workers = new WORKERS(fb_con);
workers.LoadAllWorkers();
DEVICES devices = new DEVICES(fb_con);
devices.LoadAllDevices();
READCYCLE readcycle = new READCYCLE(fb_con);
readcycle.LoadAllReadCycles();
DataBlock datablock = new DataBlock(connectionStringMysql, currentLanguage);
try
{
List <KeyValuePair <int, int> > orgOldNewIds = new List <KeyValuePair <int, int> >();
List <KeyValuePair <int, int> > workOldNewIds = new List <KeyValuePair <int, int> >();
List <KeyValuePair <int, int> > carsOldNewIds = new List <KeyValuePair <int, int> >();
List <KeyValuePair <READCYCLE.readcycle, int> > readCycleAndorgIdList = new List <KeyValuePair <READCYCLE.readcycle, int> >();
List <int> allIds = new List <int>();
int newDeviceId = -1;
int userInfoId;
KeyValuePair <int, int> tempOldNewIds;
List <int> addedWorkers = new List <int>();
datablock.OpenConnection();
datablock.OpenTransaction();
//Создание организация, работников и ТС
foreach (DEPARTMENTS.department department in depar.departments)
{
addedWorkers = new List <int>();
int orgId = datablock.organizationTable.AddNewOrganization(department.DEPARTMENT, 1, 1, 1);
tempOldNewIds = new KeyValuePair <int, int>(department.ID, orgId);
orgOldNewIds.Add(tempOldNewIds);
int carId = -1;
#region "cars"
IEnumerable <CARS.car> carsList =
from car in cars.carsArray
where car.DEPARTMENTID == department.ID
select car;
foreach (CARS.car car in carsList)
{
IEnumerable <int> scoreQuery =
from devId in readcycle.cycles
where devId.CARID == car.ID
select devId.DEVICEID;
allIds = scoreQuery.ToList();
if (allIds.Count > 0)
{
IEnumerable <DEVICES.device> oneDevice =
from devId in devices.devices
where devId.ID == allIds[0]
select devId;
if (oneDevice.ToList().Count > 0)
{
DEVICES.device NewDevice = oneDevice.ToList()[0];
newDeviceId = datablock.deviceTable.AddNewDevice(1, NewDevice.DEVICE, NewDevice.VERSION, DateTime.Now, 1, 23442324);
}
else
{
newDeviceId = datablock.deviceTable.AddNewDevice(1, "UndefinedDevice", "UndefinedVersion", DateTime.Now, 1, 23442324);
}
}
else
{
newDeviceId = datablock.deviceTable.AddNewDevice(1, "UndefinedDevice", "UndefinedVersion", DateTime.Now, 1, 23442324);
}
if (car.DEPARTMENTID == department.ID)
{
int cardId = datablock.cardsTable.CreateNewCard(car.REGNUMBER, "Unknown", datablock.cardsTable.vehicleCardTypeId, orgId, 0, "Created from FIREBIRD database", 0, 1);
carId = datablock.vehiclesTables.AddNewVehicle(car.REGNUMBER, car.CARMODEL, "Unknown", 1, newDeviceId, cardId, DateTime.Now, 1);
tempOldNewIds = new KeyValuePair <int, int>(car.ID, carId);
carsOldNewIds.Add(tempOldNewIds);
}
}
#endregion
#region "workers"
IEnumerable <WORKERS.worker> workersList =
from worker in workers.workers
where worker.DEPARTMENTID == department.ID
select worker;
foreach (WORKERS.worker workerForAdd in workersList)
{
int newWId = datablock.cardsTable.CreateNewCard(workerForAdd.FIRSTNAME + " " + workerForAdd.SURNAME, workerForAdd.WORKERINTID.ToString(), datablock.cardsTable.driversCardTypeId,
orgId, 0, "Created from firebird", 0, 1);
/*userInfoId = datablock.usersTable.GetUserInfoNameId(DataBaseReference.UserInfo_Patronimic);
* datablock.usersTable.EditUserInfo(newWId, userInfoId, workerForAdd.MIDDLENAME);
* userInfoId = datablock.usersTable.GetUserInfoNameId(DataBaseReference.UserInfo_Name);
* datablock.usersTable.EditUserInfo(newWId, userInfoId, workerForAdd.FIRSTNAME);
* userInfoId = datablock.usersTable.GetUserInfoNameId(DataBaseReference.UserInfo_Surname);
* datablock.usersTable.EditUserInfo(newWId, userInfoId, workerForAdd.SURNAME);
* userInfoId = datablock.usersTable.GetUserInfoNameId(DataBaseReference.UserInfo_Birthday);
* datablock.usersTable.EditUserInfo(newWId, userInfoId, workerForAdd.BIRTHDAY.ToShortDateString());
* userInfoId = datablock.usersTable.GetUserInfoNameId(DataBaseReference.UserInfo_DriversCertificate);
* datablock.usersTable.EditUserInfo(newWId, userInfoId, workerForAdd.LICENCE);*/
addedWorkers.Add(workerForAdd.ID);
tempOldNewIds = new KeyValuePair <int, int>(workerForAdd.ID, newWId);
workOldNewIds.Add(tempOldNewIds);
}
#endregion
#region "set orgId to READCYCLES"
if (addedWorkers.Count > 0)
{
for (int i = 0; i < readcycle.cycles.Count; i++)
{
if (addedWorkers.Contains(readcycle.cycles[i].WORKER1ID) || addedWorkers.Contains(readcycle.cycles[i].WORKER2ID))
{
KeyValuePair <READCYCLE.readcycle, int> readCycleAndorgId = new KeyValuePair <READCYCLE.readcycle, int>(readcycle.cycles[i], orgId);
readCycleAndorgIdList.Add(readCycleAndorgId);
}
}
}
#endregion
}
Console.WriteLine("Added workers, departmens, cars");
if (readCycleAndorgIdList.Count != readcycle.cycles.Count)
{
if (false) //база фигово связана
{
throw new Exception("Куда-то пропала часть инфы");
}
}
//Загрузка файлов ПЛФ в ранее созданные организации.
foreach (KeyValuePair <READCYCLE.readcycle, int> readedcycle in readCycleAndorgIdList)
{
SQLDB sqlDb = datablock.sqlDb;
SQLDB_Records sqlDB_rec = new SQLDB_Records(connectionStringMysql, sqlDb.GETMYSQLCONNECTION());
ReflectObjectToTableClass reflectedItemsList;
byte[] bytes = Guid.NewGuid().ToByteArray();
int orgId = readedcycle.Value;
Type type = null;
object myParseObject = new object();
plf = new PLFUnitClass();
plf = LoadLogBook(readedcycle.Key);
plf.cardType = 2;
IEnumerable <string> selCar =
from car in cars.carsArray
where car.ID == readedcycle.Key.CARID
select car.REGNUMBER;
if (selCar.ToList().Count > 0)
{
plf.VEHICLE = selCar.ToList()[0];
}
IEnumerable <string> selDeviceName =
from dev in devices.devices
where dev.ID == readedcycle.Key.DEVICEID
select dev.DEVICE;
if (selDeviceName.ToList().Count > 0)
{
plf.ID_DEVICE = selDeviceName.ToList()[0];
}
int driverId = -1;
foreach (KeyValuePair <int, int> oldNew in workOldNewIds)
{
if (oldNew.Key == readedcycle.Key.WORKER1ID)
{
driverId = oldNew.Value;
}
}
int dataBlockId = datablock.AddPlfTypeData(orgId, bytes,
"AutoGenerated " + plf.VEHICLE + " " + plf.START_PERIOD.ToString() + " - " + plf.END_PERIOD.ToString(),
driverId);
DataRecords dataRecord = new DataRecords(connectionString, dataBlockId, currentLanguage, sqlDb);
//////////////////////устанавливаем PLF карту нужного водителя. Незнаю почему именно здесь, но так получилось.
int plfDriversCardType = sqlDB_rec.Get_DataBlockCardType(dataBlockId);
sqlDb.SetDataBlock_CardId(dataBlockId, plfDriversCardType);
// sqlDB.OpenConnection();
int cardTypeParamId = sqlDb.AddParam("cardType", 0, 255);
//sqlDB.OpenConnection();
sqlDb.DeleteDataRecord(dataBlockId, cardTypeParamId);
// sqlDB.CloseConnection();
//////////////////////
type = plf.GetType();
myParseObject = plf;
//sqlDB.OpenConnection();
SetParseBDate(sqlDb, dataBlockId);
//sqlDB.CloseConnection();
List <ReflectObjectToTableClass> allRecordsToAdd = new List <ReflectObjectToTableClass>();
foreach (PropertyInfo pi in type.GetProperties(BindingFlags.Public | BindingFlags.Instance))
{
reflectedItemsList = new ReflectObjectToTableClass();
string fieldName = pi.Name;
object field2 = pi.GetValue(myParseObject, null);
if (field2 != null)
{
reflectedItemsList.ReflectObjectToTable(fieldName, field2); //Не удалять!
allRecordsToAdd.Add(AddRecords(reflectedItemsList, sqlDb)); //не удалять
}
}
foreach (ReflectObjectToTableClass recordList in allRecordsToAdd)
{
dataRecord.AddDataArray(recordList.reflectedItemsList);
}
//sqlDB.OpenConnection();
SetParseEDate(sqlDb, dataBlockId);
sqlDb.SetDataBlockState(dataBlockId, 2);
int dataBlockParseRecords = sqlDb.SetDataBlockParseRecords(dataBlockId);
Console.WriteLine("" + dataBlockParseRecords.ToString() + " records added");
}
Console.WriteLine("\n\r" + "Время начала импорта " + startLoadTime.ToShortDateString() + " " + startLoadTime.ToShortTimeString());
Console.WriteLine("\n\r" + "Время окончания импорта " + DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToShortTimeString());
//throw new Exception();
datablock.CommitTransaction();
datablock.CloseConnection();
}
catch (Exception ex)
{
datablock.RollbackConnection();
datablock.CloseConnection();
Console.WriteLine(ex.Message);
}
}
private void btnRetrieve_Click(object sender, EventArgs e)
{
String building = String.Empty;
String customer = String.Empty;
if (cmbBuilding.SelectedItem != null)
{
building = cmbBuilding.SelectedItem.ToString();
}
if (cmbCustomer.SelectedItem != null)
{
customer = cmbCustomer.SelectedItem.ToString();
}
DateTime sDate = sDatePicker.Value;
DateTime eDate = eDatePicker.Value;
DateTime startDate = new DateTime(sDate.Year, sDate.Month, sDate.Day, 0, 0, 0);
DateTime endDate = new DateTime(eDate.Year, eDate.Month, eDate.Day, 23, 59, 59);
String query = "SELECT s.building, s.customer, s.number, s.reference, s.message, s.sent, u.username, s.status FROM tblSMS AS s LEFT OUTER JOIN";
query += " tblUsers AS u ON s.sender = u.id WHERE sent >= '" + startDate.ToString() + "' AND sent <= '" + endDate.ToString() + "'";
if (!String.IsNullOrEmpty(building) && building == "All buildings")
{
if (!String.IsNullOrEmpty(customer) && customer == "All customers")
{
}
else if (!String.IsNullOrEmpty(customer))
{
query += " AND s.customer = '" + customer + "'";
}
}
else if (!String.IsNullOrEmpty(building) && !String.IsNullOrEmpty(customer) && customer == "All customers")
{
query += " AND s.building = '" + building + "'";
}
else if (!String.IsNullOrEmpty(building) && !String.IsNullOrEmpty(customer))
{
query += " AND s.building = '" + building + "' AND s.customer = '" + customer + "'";
}
query += " ORDER BY s.sent";
//MessageBox.Show(query);
DataSet results = DataHandler.getData(query, out status);
if (results != null && results.Tables.Count > 0 && results.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in results.Tables[0].Rows)
{
DataRecords drec = new DataRecords();
drec.Building = dr["building"].ToString();
drec.Customer = dr["customer"].ToString();
drec.Cell = dr["number"].ToString();
drec.Reference = dr["reference"].ToString();
drec.Message = dr["message"].ToString();
drec.Sent = DateTime.Parse(dr["sent"].ToString());
drec.Sender = dr["username"].ToString();
drec.Status = dr["status"].ToString();
bs.Add(drec);
}
}
else
{
MessageBox.Show("No results");
}
}
internal string QueryColumn(int colNum, DataRecords.ColumnManager columnManager)
{
string ss = columnManager.GetColumnData(colNum);
return ss;
}
