public static DataTable GetAllData()
{
using (SqlCeCommand command = dbWorker.NewQuery("SELECT * FROM AcceptanceOfNewComponentsDetails"))
{
return(command.SelectToTable());
}
}
/// <summary>Синхронизировать ("Отправить") данные по перемещениям</summary>
private void SyncMovement()
{
string tableName = typeof(Movement).Name;
//sync
string docCommand = string.Format("SELECT {0},{1},Date,Operation,Map,Register,Position FROM {2}",
dbObject.BARCODE_NAME, dbObject.SYNCREF_NAME, tableName);
DataTable table = null;
using (SqlCeCommand query = dbWorker.NewQuery(docCommand))
{
table = query.SelectToTable(new Dictionary <string, Enum>
{
{ BaseFormatName.DateTime, DateTimeFormat.OnlyDate }
});
}
PerformQuery("SyncMovement", table);
if (ResultParameters != null && (bool)ResultParameters[0])
{
//Видалення записів
string delCommand = string.Concat("DELETE FROM ", tableName);
using (SqlCeCommand query = dbWorker.NewQuery(delCommand))
{
query.ExecuteNonQuery();
}
}
}
/// <summary>Получить все проведенные документы</summary>
/// <returns>ID всех проведенных приймок</returns>
public static DataTable GetAcceptedDocuments()
{
using (SqlCeCommand command = dbWorker.NewQuery(ACCEPTED_ID_QUERY))
{
DataTable acceptedDocuments = command.SelectToTable();
return(acceptedDocuments);
}
}
/// <summary>Выборка данных для обновления сервера после синхронизации</summary>
/// <param name="tableName"></param>
private void updateObjOnServDb <T>(string tableName) where T : dbObject
{
//Штрихкода элементов, которые нужно обновить на "сервере"
DataTable changesForServ = ResultParameters[2] as DataTable;
using (SqlCeCommand query = dbWorker.NewQuery(string.Empty))
{
StringBuilder where = new StringBuilder();
if (changesForServ != null && changesForServ.Rows.Count != 0)
{
//Формирование комманды для выбора данных по всем элементам
//из таблицы tableName, которые необходимо обновить на "сервере"
int index = 0;
foreach (DataRow row in changesForServ.Rows)
{
//Добавление параметров
query.AddParameter(string.Concat(PARAMETER, index), row[dbObject.SYNCREF_NAME]);
@where.AppendFormat(" RTRIM([{0}].[{1}])=RTRIM(@{2}{3}) OR",
tableName, dbObject.SYNCREF_NAME, PARAMETER, index);
index++;
}
string whereStr = @where.ToString(0, @where.Length - 3);
//Обновление статуса синхронизации для локальной базы
query.CommandText = string.Format("UPDATE {0} SET {1}=1 WHERE {2}",
tableName,
dbObject.IS_SYNCED,
whereStr);
query.ExecuteNonQuery();
if (changesForServ.Rows.Count > 0)
{
//Выборка данных
query.CommandText = getUnsyncLinkedData(typeof(T), whereStr);
DataTable changes = query.SelectToTable(new Dictionary <string, Enum>
{
{ BaseFormatName.DateTime, DateTimeFormat.OnlyDate }
});
PerformQuery("SyncChangesForServer", tableName, changes);
}
}
}
}
/// <summary>Синхронизация данных "Приемка с обмена" для сервера</summary>
private void SyncOutAcceptanceFromExchange()
{
string tableName = typeof(AcceptanceAccessoriesFromExchangeDetails).Name;
string command = string.Format(
"SELECT d.Id,m.{0} Nomenclature,d.{1} FROM {2} d LEFT JOIN Models m ON m.Id=d.Nomenclature ORDER BY Id,Nomenclature",
dbObject.SYNCREF_NAME, dbObject.BARCODE_NAME, tableName);
DataTable table = null;
using (SqlCeCommand query = dbWorker.NewQuery(command))
{
table = query.SelectToTable();
}
//Send
PerformQuery("SetSendingExchangeDocs", table);
//Clear
dbArchitector.ClearAllDataFromTable(tableName);
}
private void createLongControls()
{
dbFieldAtt attribute = Attribute.GetCustomAttribute(
accessory.GetType().GetProperty(propertyName),
typeof(dbFieldAtt)) as dbFieldAtt;
if (attribute != null && attribute.dbObjectType != null)
{
DataTable sourceTable = new DataTable();
sourceTable.Columns.AddRange(new[]
{
new DataColumn("Number", typeof(int)),
new DataColumn("Description", typeof(string))
});
MobileTable visualTable = MainProcess.CreateTable("Table", 200, 65);
visualTable.OnChangeSelectedRow += visualTable_OnChangeSelectedRow;
visualTable.DT = sourceTable;
visualTable.AddColumn("№", "Number", 34);
visualTable.AddColumn("Назва", "Description", 180);
string command = string.Format("SELECT Id,Description FROM {0} WHERE MarkForDeleting=0",
attribute.dbObjectType.Name);
DataTable table = null;
using (SqlCeCommand query = dbWorker.NewQuery(command))
{
table = query.SelectToTable();
}
foreach (DataRow row in table.Rows)
{
visualTable.AddRow(row["Id"], row["Description"]);
}
visualTable.Focus();
controls.Add(visualTable);
}
else
{
MainProcess.CreateLabel("Справочник/Документ пуст!", 5, 150, 230,
MobileFontSize.Normal, MobileFontPosition.Center, MobileFontColors.Warning,
FontStyle.Bold);
controls.Add(MainProcess.CreateTable("Table", 200, 65));
}
}
private void fillLikePrev()
{
DataTable table = null;
using (SqlCeCommand command = dbWorker.NewQuery(@"
SELECT m.Id MapId,m.Description,m.RegisterFrom,m.RegisterTo,c.Register
FROM Cases c
JOIN Maps m ON m.Id=c.Map
WHERE c.Status=1
ORDER BY DateOfActuality DESC"))
{
table = command.SelectToTable();
}
if (table != null && table.Rows.Count > 0)
{
DataRow row = table.Rows[0];
MapInfo = new MapInfo(
row["MapId"], row[CatalogObject.DESCRIPTION].ToString(),
Convert.ToInt32(row["RegisterFrom"]), Convert.ToInt32(row["RegisterTo"]));
Register = row["Register"].ToString();
clearPosition();
}
}
/// <summary>Синхронизировать изменения по док "Отправить на .." на сервере</summary>
/// <typeparam name="T">Документ</typeparam>
/// <typeparam name="S">Таблица</typeparam>
/// <param name="mode">Режим синхронізації</param>
private void SyncOutSending <T, S>(SyncModes mode)
where T : Sending
where S : SubSending
{
string docName = typeof(T).Name;
string tableName = typeof(S).Name;
//1. Обновление на сервере
string command = string.Format("SELECT Id, Document FROM {0} WHERE IsSynced=0", tableName);
DataTable table = null;
using (SqlCeCommand query = dbWorker.NewQuery(command))
{
table = query.SelectToTable();
}
PerformQuery("SetSendingDocs", docName, tableName, table, (int)mode);
bool fullDeleteAccepted = typeof(T) == typeof(SendingToRepair) || typeof(T) == typeof(SendingToCharge);
if (fullDeleteAccepted)
{
command = string.Format("SELECT DISTINCT Id FROM {0} WHERE IsSynced=0", tableName);
using (SqlCeCommand query = dbWorker.NewQuery(command))
{
table = query.SelectToTable();
}
StringBuilder removeCommand = new StringBuilder("DELETE FROM {0} WHERE 1=0 ");
Dictionary <string, object> parameters = new Dictionary <string, object>();
int index = 0;
foreach (DataRow row in table.Rows)
{
object id = row["Id"];
parameters.Add(string.Concat(PARAMETER, index), id);
removeCommand.AppendFormat(" OR Id=@{0}{1}", PARAMETER, index);
index++;
}
using (SqlCeCommand query = dbWorker.NewQuery(string.Format(removeCommand.ToString(), docName)))
{
query.AddParameters(parameters);
query.ExecuteNonQuery();
}
using (SqlCeCommand query = dbWorker.NewQuery(string.Format(removeCommand.ToString(), tableName)))
{
query.AddParameters(parameters);
query.ExecuteNonQuery();
}
}
else
{
//2. Удаление обновленных (? а нужно ли ... может когда весь документ удаляется)
command = string.Format("DELETE FROM {0} WHERE IsSynced=0", tableName);
using (SqlCeCommand query = dbWorker.NewQuery(command))
{
query.ExecuteNonQuery();
}
//3. Удаление полностью принятого документа
command = string.Format(@"SELECT s.Id
FROM {0} s
LEFT JOIN (
SELECT t1.Id, Count(1) Count
FROM {0} t1
JOIN {1} t2 ON t2.Id=t1.Id
GROUP BY t1.Id)t ON s.Id=t.Id
WHERE t.Count=0 OR t.Id IS NULL", docName, tableName);
using (SqlCeCommand query = dbWorker.NewQuery(command))
{
table = query.SelectToTable();
}
StringBuilder removeCommand = new StringBuilder();
removeCommand.AppendFormat("DELETE FROM {0} WHERE 1=0", docName);
Dictionary <string, object> parameters = new Dictionary <string, object>();
int index = 0;
foreach (DataRow row in table.Rows)
{
object id = row["Id"];
removeCommand.AppendFormat(" OR {0}=@{1}{2}", dbObject.IDENTIFIER_NAME, PARAMETER, index);
parameters.Add(string.Concat(PARAMETER, index), id);
index++;
}
using (SqlCeCommand query = dbWorker.NewQuery(removeCommand.ToString()))
{
query.AddParameters(parameters);
query.ExecuteNonQuery();
}
}
}
/// <summary>Синхронизация объекта</summary>
/// <param name="tableName">Имя таблицы</param>
/// <param name="wayOfSync">Способ синхронизации</param>
/// <param name="filter">Фильтры</param>
/// <param name="skipExists">Пропустить существующие</param>
/// <param name="updId">Нужно обновить ID</param>
private bool SyncObjects <T>(string tableName, WaysOfSync wayOfSync, FilterSettings filter, bool skipExists, bool updId) where T : dbObject
{
CatalogSynchronizer synchronizer = null;
synchronizer = this.getCatalogSynchronizer(tableName);
logBuilder.AppendLine();
logBuilder.AppendLine();
logBuilder.AppendLine(string.Format("{0}:", typeof(T).Name));
Stopwatch totalWatch = new Stopwatch();
totalWatch.Start();
Stopwatch stopWatch = new Stopwatch();
stopWatch.Start();
string forWarehousesAndMapsSelect = string.Format(@"SELECT RTRIM({0}){0},RTRIM({1}){1},RTRIM({2}) {2} FROM {3} WHERE {4}=0",
dbObject.IS_SYNCED,
dbObject.BARCODE_NAME,
dbObject.SYNCREF_NAME,
tableName,
CatalogObject.MARK_FOR_DELETING);
string forByLogProcessedSelect = string.Format(@"SELECT RTRIM({0}){0},RTRIM({1}){1},RTRIM({2}) {2} FROM {3} WHERE {4}=0 AND {5} = 0",
dbObject.IS_SYNCED,
dbObject.BARCODE_NAME,
dbObject.SYNCREF_NAME,
tableName,
CatalogObject.MARK_FOR_DELETING, CatalogObject.IS_SYNCED);
bool isWarehouseTable = tableName.Equals("Contractors") || tableName.Equals("Maps");
//Выбрать (Признак синхронизации, Штрих-код) всех не удаленных элементов с таблицы tableName
string command = (useLoggingSyncronization & !isWarehouseTable) ?
forByLogProcessedSelect :
forWarehousesAndMapsSelect;
DataTable table = null;
using (SqlCeCommand query = dbWorker.NewQuery(command))
{
table = query.SelectToTable();
}
int rowsCount = (table ?? new DataTable()).Rows.Count;
logBuilder.AppendLine(string.Format("init pdt query: {0} msec; rows: {1}", stopWatch.ElapsedMilliseconds, rowsCount));
stopWatch.Reset();
stopWatch.Start();
if (filter == FilterSettings.None)
{
PerformQuery("StartSyncProcess", this.serverIdProvider.ServerId, tableName, table, (int)FilterSettings.NotMarkForDelete);
}
else
{
PerformQuery("StartSyncProcess", this.serverIdProvider.ServerId, tableName, table, (int)FilterSettings.NotMarkForDelete, (int)filter);
}
logBuilder.AppendLine(string.Format("StartSyncProcess: {0} msec; rows: {1}", stopWatch.ElapsedMilliseconds, rowsCount));
stopWatch.Reset();
stopWatch.Start();
if (IsAnswerIsTrue)
{
removeMarkedObject(tableName);
logBuilder.AppendLine(string.Format("removeMarkedObject: {0} msec", stopWatch.ElapsedMilliseconds));
stopWatch.Reset();
stopWatch.Start();
updateObjOnLocalDb <T>(synchronizer, skipExists, updId);
int localRowsCount = ((ResultParameters[1] as DataTable) ?? new DataTable()).Rows.Count;
logBuilder.AppendLine(string.Format("updateObjOnLocalDb: {0} msec; rows: {1}", stopWatch.ElapsedMilliseconds, localRowsCount));
stopWatch.Reset();
stopWatch.Start();
if (wayOfSync == WaysOfSync.TwoWay)
{
int remoteTableRows = ((ResultParameters[2] as DataTable) ?? new DataTable()).Rows.Count;
updateObjOnServDb <T>(tableName);
logBuilder.AppendLine(string.Format("update greenhouse: {0} msec; rows:{1}", stopWatch.ElapsedMilliseconds, remoteTableRows));
stopWatch.Reset();
stopWatch.Start();
}
logBuilder.AppendLine(string.Format("{0} total: {1} msec", typeof(T).Name, totalWatch.ElapsedMilliseconds));
return(true);
}
else
{
return(false);
}
}