private void removeMarkedObject(string tableName)
{
if (ResultParameters.Length >= 4 && ResultParameters[3] != null)
{
DataTable table = ResultParameters[3] as DataTable;
if (table != null && table.Rows.Count > 0)
{
StringBuilder command = new StringBuilder();
command.AppendFormat("DELETE FROM {0} WHERE 1=0 ", tableName);
int index = 0;
Dictionary <string, object> parameters = new Dictionary <string, object>();
foreach (DataRow row in table.Rows)
{
string currParameter = string.Concat(PARAMETER, index++);
command.AppendFormat(" OR RTRIM({0})=RTRIM(@{1})", dbObject.SYNCREF_NAME, currParameter);
parameters.Add(currParameter, row[dbObject.SYNCREF_NAME]);
}
using (SqlCeCommand query = dbWorker.NewQuery(command.ToString()))
{
query.AddParameters(parameters);
query.ExecuteNonQuery();
}
}
}
}
/// <summary>Создание нового объекта</summary>
/// <typeparam name="T"></typeparam>
/// <param name="updId">Нужно обновить ID</param>
/// <returns>Id</returns>
private object CreateNew <T>(bool updId) where T : dbObject
{
Type type = typeof(T);
PropertyInfo[] properties = type.GetProperties();
Dictionary <string, object> parameters = new Dictionary <string, object>();
StringBuilder columnsStr = new StringBuilder();
StringBuilder parameterStr = new StringBuilder();
object newId = 0;
string idName = IDENTIFIER_NAME.ToLower();
foreach (PropertyInfo property in properties)
{
Attribute attribute = Attribute.GetCustomAttribute(property, typeof(dbFieldAtt));
if (attribute != null)
{
object value = property.GetValue(this, null);
if (property.Name.ToLower().Equals(idName))
{
if (updId && Convert.ToInt64(value) == 0)
{
newId = GetNewId();
value = newId;
}
else
{
newId = value;
}
}
parameters.Add(property.Name, value);
columnsStr.Append("[");
columnsStr.Append(property.Name);
columnsStr.Append("],");
parameterStr.Append("@");
parameterStr.Append(property.Name);
parameterStr.Append(",");
}
}
string command = string.Format("INSERT INTO {0}({1}) VALUES({2})",
type.Name,
columnsStr.ToString(0, columnsStr.Length - 1),
parameterStr.ToString(0, parameterStr.Length - 1));
using (SqlCeCommand query = dbWorker.NewQuery(command))
{
query.AddParameters(parameters);
query.ExecuteNonQuery();
}
IsNew = false;
Id = Convert.ToInt64(newId);
return(newId);
}
/// <summary>Обновление объекта</summary>
/// <returns>Id</returns>
private object Update <T>() where T : dbObject
{
object idValue = 0;
Type type = typeof(T);
PropertyInfo[] properties = type.GetProperties();
Dictionary <string, object> parameters = new Dictionary <string, object>();
StringBuilder line = new StringBuilder();
string idName = IDENTIFIER_NAME.ToLower();
foreach (PropertyInfo property in properties)
{
Attribute attribute = Attribute.GetCustomAttribute(property, typeof(dbFieldAtt));
if (attribute != null)
{
object value = property.GetValue(this, null);
parameters.Add(property.Name, value);
if (property.Name.ToLower().Equals(idName))
{
idValue = value;
}
else
{
line.AppendFormat("[{0}]=@{0},", property.Name);
}
}
}
string command = string.Format("UPDATE {0} SET {1} WHERE [{2}]=@Id",
type.Name,
line.ToString(0, line.Length - 1),
IDENTIFIER_NAME);
using (SqlCeCommand query = dbWorker.NewQuery(command))
{
query.AddParameters(parameters);
query.ExecuteNonQuery();
return(idValue);
}
}
/// <summary>Збереження інформації по завершенню прийомки</summary>
private void Accept()
{
StringBuilder whereClause = new StringBuilder();
Dictionary <string, object> parameters = new Dictionary <string, object>();
if (accepted.Count > 0)
{
int index = 0;
whereClause.Append("AND (1=0");
//Data
foreach (KeyValuePair <long, List <KeyValuePair <long, string> > > row in accepted)
{
foreach (KeyValuePair <long, string> v in row.Value)
{
string currParameter = string.Concat(dbSynchronizer.PARAMETER, index++);
whereClause.AppendFormat(" OR RTRIM({0})=RTRIM(@{1})", dbObject.BARCODE_NAME, currParameter);
parameters.Add(currParameter, v.Key.ToString());
AcceptanceAccessoriesFromExchangeDetails details = new AcceptanceAccessoriesFromExchangeDetails
{
Id = v.Key,
BarCode = v.Value,
Nomenclature = (int)row.Key
};
details.Save(false);
}
}
whereClause.Append(")");
}
//Doc
string command = string.Format("UPDATE {0} SET {1}=0 WHERE 1=1 {2}",
docName, dbObject.IS_SYNCED, whereClause);
using (SqlCeCommand query = dbWorker.NewQuery(command))
{
query.AddParameters(parameters);
query.ExecuteNonQuery();
}
}
/// <summary>Збереження інформації</summary>
private void Accept()
{
StringBuilder command = new StringBuilder();
command.AppendFormat("UPDATE {0} SET {1}=0 WHERE 1=0", subTableName, dbObject.IS_SYNCED);
Dictionary <string, object> parameters = new Dictionary <string, object>();
int index = 0;
foreach (string a in accepted)
{
command.AppendFormat(" OR Document=@{0}{1}", dbSynchronizer.PARAMETER, index);
parameters.Add(string.Concat(dbSynchronizer.PARAMETER, index), a);
index++;
}
using (SqlCeCommand query = dbWorker.NewQuery(command.ToString()))
{
query.AddParameters(parameters);
query.ExecuteNonQuery();
}
}
/// <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();
}
}
}