/// <summary>
/// Creates command for updating client on the server (Enable/Disable)
/// </summary>
/// <param name="clientId"></param>
/// <param name="active"></param>
/// <returns></returns>
public static SqlCommand CreateUpdateClientCommand(int clientId, bool active)
{
string sql = "UPDATE CLIENTS SET Enabled = {1} WHERE ClientID = {0};".format(clientId, Convert.ToInt32(active));
var cmd = MSSQL.CreateCommand(ConnectionString, sql);
return(cmd);
}
/// <summary>
/// Selects all available files from client database
/// </summary>
/// <returns></returns>
public static IEnumerable <DbClientFileInfo> SelectAllFiles()
{
const string sql = "SELECT * FROM FILES WHERE VoucherImage IS NOT NULL;";
var comm = MSSQL.CreateCommand(ConnectionString, sql);
var list = SQL.ExecuteYieldReader <DbClientFileInfo>(comm);
return(list);
}
/// <summary>
/// Creates command for deleting older than N days messages
/// </summary>
/// <param name="days"></param>
/// <returns></returns>
private static SqlCommand CreateDeleteOlderSentMessages(int days)
{
const string sql = "DELETE FROM MESSAGES WHERE DateExported < @Date;";
return(MSSQL.CreateCommand(ConnectionString,
sql,
new SqlParameter("@Date", DateTime.Now.AddDays(-days))));
}
/// <summary>
/// Creates a command for deleting files by batch id
/// </summary>
/// <param name="clientID"></param>
/// <param name="batchID"></param>
/// <returns></returns>
private static SqlCommand CreateDeleteFilesByBatchIDCommand(int clientID, Guid batchID)
{
const string sql = "DELETE FILES WHERE ClientID = @ClientID AND BatchID = @BatchID;";
var cmd = MSSQL.CreateCommand(ConnectionString, sql, CommandType.Text,
new SqlParameter("@ClientID", clientID),
new SqlParameter("@BatchID", batchID));
return(cmd);
}
/// <summary>
/// Creates command for setting messages as exported to the client database
/// </summary>
/// <param name="messageId"></param>
/// <returns></returns>
public static SqlCommand CreateSetMessageExportedCommand(int messageId)
{
const string sql = "UPDATE MESSAGES SET DateExported = @DateExported WHERE MessageID = @MessageID;";
SqlCommand command = MSSQL.CreateCommand(ConnectionString, sql,
new SqlParameter("@MessageID", messageId),
new SqlParameter("@DateExported", DateTime.Now));
return(command);
}
/// <summary>
/// Creates command for setting file as exported to the client database
/// </summary>
/// <param name="siteCode"></param>
/// <returns></returns>
public static SqlCommand CreateSetFileExportedCommand(string siteCode)
{
const string sql = "UPDATE FILES SET DateExported = @DateExported WHERE FileID = @FileID;";
SqlCommand command = MSSQL.CreateCommand(ConnectionString, sql,
new SqlParameter("@FileID", siteCode),
new SqlParameter("@DateExported", DateTime.Now));
return(command);
}
/// <summary>
/// Creates a command for creating client in server database
/// </summary>
/// <param name="name"></param>
/// <param name="ip"></param>
/// <returns></returns>
public static SqlCommand CreateCreateClientCommand(string name, string ip)
{
return(MSSQL.CreateCommand(ConnectionString, "spCreateClient", CommandType.StoredProcedure,
new SqlParameter("@Name", name),
new SqlParameter("@IP", ip),
new SqlParameter("@ID", SqlDbType.Int)
{
Direction = ParameterDirection.Output
}));
}
/// <summary>
/// Selects BatchID
/// </summary>
/// <param name="fileId"></param>
/// <returns></returns>
public static int SelectBatchID(int fileId)
{
const string sql = "SelectBatchID";
var p0 = new SqlParameter("@FileID", fileId);
var p1 = new SqlParameter("@BatchID", 0)
{
Direction = ParameterDirection.Output
};
SqlCommand com = MSSQL.CreateCommand(ConnectionString, sql, CommandType.StoredProcedure, p0, p1);
SQL.ExecuteNonQuery(com);
return(p1.Value.Cast <int>());
}
/// <summary>
/// Creates command for inserting message to the client database
/// </summary>
/// <param name="message"></param>
/// <param name="source"></param>
/// <param name="stacktrase"></param>
/// <param name="onServer"></param>
/// <returns></returns>
private static SqlCommand CreateInsertMessage(string message, eSources source, eMessageTypes type, string stackTrase)
{
const string sql = "INSERT INTO MESSAGES(Message, Source, Type, StackTrace, DateInserted) VALUES " +
"(@Message, @Source, @Type, @StackTrace, @DateInserted);";
return(MSSQL.CreateCommand(ConnectionString,
sql,
new SqlParameter("@Message", message.Top(1024)),
new SqlParameter("@Source", Convert.ToInt32(source)),
new SqlParameter("@Type", Convert.ToInt32(type)),
new SqlParameter("@StackTrace", stackTrase.Top(2028)),
new SqlParameter("@DateInserted", DateTime.Now)));
}
/// <summary>
/// Creates command for inserting configuration value to the client database
/// </summary>
/// <param name="key"></param>
/// <param name="value"></param>
/// <returns></returns>
public static SqlCommand CreateInsertConfigurationCommand(string key, byte[] value)
{
const string sql = @"IF(EXISTS(SELECT NULL FROM [CONFIG] WHERE [Key]=@Key))
UPDATE [CONFIG]
Set [Value] = @Value
WHERE [Key]= @Key
ELSE
INSERT INTO CONFIG([Key],[Value])VALUES(@Key,@Value)";
return(MSSQL.CreateCommand(ConnectionString, sql,
new SqlParameter("@Key", key.Top(100)),
new SqlParameter("@Value", value)));
}
/// <summary>
/// Creates an insert fileid command
/// </summary>
/// <param name="siteCode"></param>
/// <returns></returns>
public static SqlCommand CreateInsertFileCommand(int countryId, string siteCode)
{
if (string.IsNullOrWhiteSpace(siteCode))
{
throw new ArgumentNullException("siteCode");
}
const string sql = "INSERT FILES( SiteCode, CountryID, DateAllocated) VALUES (@SiteCode, @CountryID, @DateAllocated);";
SqlCommand command = MSSQL.CreateCommand(ConnectionString, sql,
new SqlParameter("@CountryID", countryId),
new SqlParameter("@SiteCode", siteCode),
new SqlParameter("@DateAllocated", DateTime.Now));
return(command);
}
/// <summary>
/// Creates a command for inserting message
/// </summary>
/// <param name="message"></param>
/// <param name="source"></param>
/// <param name="stacktrase"></param>
/// <param name="onServer"></param>
/// <returns></returns>
private static SqlCommand CreateInsertMessage(string message, int clientID,
eSources source, eMessageTypes type,
string stackTrace, DateTime dateGenerated)
{
const string sql = "spInsertMessage";
return(MSSQL.CreateCommand(ConnectionString,
sql,
CommandType.StoredProcedure,
new SqlParameter("@ClientID", clientID),
new SqlParameter("@Message", message),
new SqlParameter("@Type", Convert.ToInt32(type)),
new SqlParameter("@SourceID", Convert.ToInt32(source)),
new SqlParameter("@StackTrace", stackTrace),
new SqlParameter("@DateGenerated", dateGenerated)));
}
private static SqlCommand CreateInsertFileCommand(
int clientID,
int countryID,
string siteCode,
DateTime dateAllocated)
{
Debug.Assert(!string.IsNullOrEmpty(ConnectionString));
const string SQL = @"INSERT FILES( ClientID, CountryID, SiteCode, DateAllocated )
VALUES ( @ClientID, @CountryID, @SiteCode, @DateAllocated );";
SqlCommand command = MSSQL.CreateCommand(ConnectionString,
SQL,
CommandType.Text,
new SqlParameter("@ClientID", clientID),
new SqlParameter("@CountryID", countryID),
new SqlParameter("@SiteCode", siteCode),
new SqlParameter("@DateAllocated", dateAllocated));
return(command);
}
/// <summary>
/// Selects (allocates) image file ids into server database.
/// </summary>
/// <param name="clientId"></param>
/// <param name="count"></param>
/// <param name="fromId"></param>
/// <param name="toId"></param>
public static void SelectAuditIDS(int clientId, int count, out int fromId, out int toId)
{
const string sql = "spGenerateAuditIDs";
SqlParameter
pFrom = new SqlParameter("@AuditIDFrom", SqlDbType.Int)
{
Direction = ParameterDirection.Output
},
pTo = new SqlParameter("@AuditIDTo", SqlDbType.Int)
{
Direction = ParameterDirection.Output
};
SQL.ExecuteNonQuery(MSSQL.CreateCommand(ConnectionString,
sql, CommandType.StoredProcedure,
new SqlParameter("@ClientID", clientId),
new SqlParameter("@Count", count),
pFrom, pTo));
fromId = Convert.ToInt32(pFrom.Value);
toId = Convert.ToInt32(pTo.Value);
}
/// <summary>
/// Creates command object that insert file
/// </summary>
/// <param name="fileName"></param>
/// <param name="clientID"></param>
/// <param name="batchID"></param>
/// <param name="countryCode"></param>
/// <param name="fileID"></param>
/// <param name="relatedId"></param>
/// <param name="siteCode"></param>
/// <param name="comment"></param>
/// <param name="retailerID"></param>
/// <param name="voucherID"></param>
/// <param name="voucherImage"></param>
/// <param name="barCodeImage"></param>
/// <param name="voucherData"></param>
/// <param name="dateScanned"></param>
/// <returns></returns>
private static SqlCommand CreateUpdateFileCommand(
int clientID,
int countryID,
int retailerID,
string voucherID,
string siteCode,
byte[] voucherImage,
byte[] barCodeImage,
string comment,
DateTime dateScanned)
{
Debug.Assert(!string.IsNullOrEmpty(ConnectionString));
const string SQL = @"UPDATE FILES
SET ClientID = @ClientID,
RetailerID = @RetailerID,
VoucherID = @VoucherID,
VoucherImage = @VoucherImage,
BarCodeImage = @BarCodeImage,
DateScanned = @DateScanned,
Comment = @Comment
WHERE SiteCode = @SiteCode AND CountryID = @CountryID";
SqlCommand command = MSSQL.CreateCommand(ConnectionString,
SQL,
CommandType.Text,
new SqlParameter("@ClientID", clientID),
new SqlParameter("@CountryID", countryID),
new SqlParameter("@RetailerID", retailerID),
new SqlParameter("@VoucherID", voucherID),
new SqlParameter("@SiteCode", siteCode),
new SqlParameter("@VoucherImage", voucherImage),
new SqlParameter("@BarCodeImage", barCodeImage),
new SqlParameter("@DateScanned", dateScanned),
new SqlParameter("@Comment", comment));
return(command);
}
/// <summary>
/// Creates command for updating files into database table by concrete values
/// </summary>
/// <param name="fileName"></param>
/// <param name="barcode"></param>
/// <param name="fileId"></param>
/// <param name="content"></param>
/// <returns></returns>
public static SqlCommand CreateUpdateFileCommand(DbClientVoucher voucher)
{
const string sql = @"UPDATE FILES " +
"SET " +
"RetailerID = @RetailerID, " +
"VoucherID = @VoucherID, " +
"BarCode = @BarCode, " +
"VoucherImage = @VoucherImage, " +
"BarCodeImage = @BarCodeImage, " +
"DateInserted = @DateInserted " +
"WHERE SiteCode = @SiteCode AND CountryID = @CountryID";
SqlCommand command = MSSQL.CreateCommand(ConnectionString,
sql,
new SqlParameter("@CountryID", voucher.CountryID),
new SqlParameter("@RetailerID", voucher.RetailerID),
new SqlParameter("@VoucherID", voucher.VoucherID),
new SqlParameter("@BarCode", voucher.BarCode),
new SqlParameter("@VoucherImage", voucher.VoucherImage),
new SqlParameter("@BarCodeImage", voucher.BarCodeImage),
new SqlParameter("@DateInserted", DateTime.Now),
new SqlParameter("@SiteCode", voucher.SiteCode));
return(command);
}
/// <summary>
/// Inserts file by serializetion data saved in a hashtable
/// </summary>
/// <param name="serializationData"></param>
/// <param name="error"></param>
public static void InsertFileAsync(Hashtable serializationData, ThreadExceptionEventHandler error)
{
SqlCommand command = MSSQL.CreateCommand(ConnectionString, serializationData);
SQLWorker.Default.Add(command, error);
}