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; }