public static void SetStringRecord_NullEmpty(SqlDataReader dr, string columnName, SqlDataRecord record, int ordinal)
{
if (dr[columnName] == DBNull.Value)
record.SetString(ordinal, "");
else
record.SetString(ordinal, Convert.ToString(dr[columnName]));
}
private static SqlDataRecord FillRecord(Int32 pk, SqlDataRecord record)
{
Int32 age = SlowRandom(16, 99);
string sourceString = "Age: " + age.ToString();
DateTime sourceDate = DateTime.UtcNow;
var data = /*salt + */sourceString;
string key = "Top Secret Key";
var encData = AES.EncryptBytes(data, key);
//var encDataBytes = Encoding.Unicode.GetBytes(encData);
var decData = AES.DecryptBytes(encData, key);
var sha = new SHA256Managed();
byte[] dataSHA256 = sha.ComputeHash(encData/*Bytes*/);
sha.Dispose();
// конвертирую хеш из byte[16] в строку шестнадцатиричного формата
// (вида «3C842B246BC74D28E59CCD92AF46F5DA»)
// это опциональный этап, если вам хеш нужен в строковом виде
// string sha512hex = BitConverter.ToString(dataSHA512).Replace("-", string.Empty);
record.SetInt32(0, pk);
record.SetDateTime(1, sourceDate);
record.SetString(2, sourceString);
record.SetString(3, Convert.ToBase64String(dataSHA256)); // sha256
record.SetString(4, Convert.ToBase64String(encData)); // Encrypted
record.SetString(5, decData); // Decrypted
return record;
}
protected static IEnumerable<SqlDataRecord> GenerateMetaPropertyTable(MetaObject o)
{
var metaFields = new List<SqlDataRecord>();
try
{
SqlMetaData[] metaData = new SqlMetaData[2];
metaData[0] = new SqlMetaData("FieldName", SqlDbType.VarChar, 30);
metaData[1] = new SqlMetaData("FieldValue", SqlDbType.VarChar, -1);
foreach (KeyValuePair<string, JToken> prop in o.MetaPropertiesObject)
{
SqlDataRecord record = new SqlDataRecord(metaData);
record.SetString(0, prop.Key);
// coming from the DB the value will be an object representing the field, with a "Value" key
// coming from the client the value will be a single value
var value = prop.Value.SelectToken("Value") ?? prop.Value;
if (value.Type == JTokenType.Null)
{
record.SetDBNull(1);
}
else
{
record.SetString(1, value.ToString());
}
metaFields.Add(record);
}
}
catch (Exception e)
{
}
return metaFields;
}
public static void IR_SM_AvailableObjective()
{
using (SqlConnection con = new SqlConnection("context connection=true"))
{
SqlPipe pipe = SqlContext.Pipe;
List<SqlCommand> commands = new List<SqlCommand>();
commands.Add(new SqlCommand("SELECT distinct [BUDGETTYPECODE] as ObjectiveType, [YEAR] as YearT, BRANDCODE as BrandCode FROM [SALESBUDGET] inner join SAPRODUCTS on SAPRODUCTS.saproductcode = salesbudget.saproductcode order by BRANDCODE"));
commands.Add(new SqlCommand("SELECT distinct [type] as ObjectiveType, [year] as YearT, [brandcode] as BrandCode FROM [IR_Brand_Budget] order by brandcode"));
SqlDataRecord record = new SqlDataRecord(new SqlMetaData("ObjectiveType", SqlDbType.NVarChar, 50),
new SqlMetaData("YearT", SqlDbType.Int),
new SqlMetaData("BrandCode", SqlDbType.NVarChar, 50),
new SqlMetaData("TotalProvince", SqlDbType.NVarChar, 50));
pipe.SendResultsStart(record);
bool isTotal = true;
foreach (SqlCommand cmd in commands)
{
try
{
cmd.Connection = con;
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
string objectiveType = Convert.ToString(dr["objectivetype"]);
int year = Convert.ToInt32(dr["yeart"]);
string brandCode = Convert.ToString(dr["brandcode"]);
string totalProvince = (isTotal) ? "T" : "P";
record.SetString(0, objectiveType);
record.SetInt32(1, year);
record.SetString(2, brandCode);
record.SetString(3, totalProvince);
pipe.SendResultsRow(record);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con != null)
con.Close();
isTotal = false;
}
}
pipe.SendResultsEnd();
}
}
public static void IR_SM_PriceList_PerType()
{
using (SqlConnection con = new SqlConnection("context connection=true"))
{
SqlPipe pipe = SqlContext.Pipe;
List<SqlCommand> commands = new List<SqlCommand>();
try
{
commands.Add(new SqlCommand("SELECT DISTINCT IR_PriceList.PRICE, IR_PriceList.YEAR AS YearT, IR_PriceList.MONTH AS MonthT, IR_PriceList.SAPRODUCTCODE, IR_PriceList.PRICETYPE FROM IR_PriceList"));
SqlDataRecord record = new SqlDataRecord(new SqlMetaData("PRICE", SqlDbType.Float),
new SqlMetaData("MonthT", SqlDbType.Int),
new SqlMetaData("YearT", SqlDbType.Int),
new SqlMetaData("SAPRODUCTCODE", SqlDbType.NVarChar, 20),
new SqlMetaData("PRICETYPE", SqlDbType.NVarChar, 20));
pipe.SendResultsStart(record);
foreach (SqlCommand cmd in commands)
{
try
{
cmd.Connection = con;
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
double price = Convert.ToDouble(dr["PRICE"]);
int month = Convert.ToInt32(dr["montht"]);
int year = Convert.ToInt32(dr["yeart"]);
string saproductcode = Convert.ToString(dr["SAPRODUCTCODE"]);
string thePriceType = Convert.ToString(dr["PRICETYPE"]);
record.SetDouble(0, price);
record.SetInt32(1, month);
record.SetInt32(2, year);
record.SetString(3, saproductcode);
record.SetString(4, thePriceType);
pipe.SendResultsRow(record);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con != null)
con.Close();
}
}
pipe.SendResultsEnd();
}
catch (Exception ex)
{
throw ex;
}
}
}
private static void BindFile(FileInfo _file, SqlDataRecord record)
{
record.SetSqlDateTime(0, _file.Exists ? _file.CreationTime : SqlDateTime.Null);
record.SetSqlDateTime(1, _file.Exists ? _file.LastAccessTime : SqlDateTime.Null);
record.SetSqlDateTime(2, _file.Exists ? _file.LastWriteTime : SqlDateTime.Null);
record.SetSqlBoolean(3, _file.Exists);
record.SetString(4, _file.Name);
record.SetString(5, _file.DirectoryName);
record.SetString(6, _file.Extension);
record.SetSqlInt64(7, _file.Exists ? _file.Length : SqlInt64.Null);
}
public static void GerarEventoAuditoria(this DbContext context, int codigoTipoEventoAuditoria, Func<int> idUsuarioFunc, params ParametroEvento[] parametrosEvento)
{
string siglaSistemaPermisys = ConfigurationManager.AppSettings["Permisys.SiglaSistema"];
string siglaModuloPermisys = ConfigurationManager.AppSettings["Permisys.SiglaModulo"];
if (string.IsNullOrWhiteSpace("Permisys.SiglaSistema") || string.IsNullOrWhiteSpace("Permisys.SiglaModulo")) {
throw new Exception("As configurações \"Permisys.SiglaSistema\" e \"Permisys.SiglaModulo\" são obrigatórias.");
}
if (idUsuarioFunc == null) {
throw new Exception("A função de obtenção do ID do usuário é obrigatória.");
}
int idUsuarioPermisys = idUsuarioFunc();
List<SqlDataRecord> parametros = null;
if (parametrosEvento != null && parametrosEvento.Count() > 0) {
parametros = new List<SqlDataRecord>();
SqlMetaData[] rowMetadata = new SqlMetaData[] {
new SqlMetaData("ORDEM", SqlDbType.Int),
new SqlMetaData("NOME", SqlDbType.VarChar, 20),
new SqlMetaData("VALOR", SqlDbType.VarChar, 100)
};
foreach (var parametro in parametrosEvento) {
if (string.IsNullOrWhiteSpace(parametro.Nome) || string.IsNullOrWhiteSpace(parametro.Valor)) {
throw new Exception("O \"Nome\" e \"Valor\" são obrigatórios para todos os parâmetros.");
}
SqlDataRecord row = new SqlDataRecord(rowMetadata);
row.SetInt32(0, parametro.Ordem);
row.SetString(1, parametro.Nome);
row.SetString(2, parametro.Valor);
parametros.Add(row);
}
}
context.Database.ExecuteSqlCommand("LOGSYS.SP_GERAR_EVENTO_AUDITORIA @SIGLA_SISTEMA_PERMISYS, @SIGLA_MODULO_PERMISYS, @ID_USUARIO_PERMISYS, @CODIGO_TIPO_EVENTO_AUDITORIA, @PARAMETROS_EVENTO",
new object[] {
new SqlParameter("SIGLA_SISTEMA_PERMISYS", SqlDbType.VarChar, 30) { Value = siglaSistemaPermisys },
new SqlParameter("SIGLA_MODULO_PERMISYS", SqlDbType.VarChar, 30) { Value = siglaModuloPermisys },
new SqlParameter("ID_USUARIO_PERMISYS", SqlDbType.Int) { Value = idUsuarioPermisys },
new SqlParameter("CODIGO_TIPO_EVENTO_AUDITORIA", SqlDbType.Int) { Value = codigoTipoEventoAuditoria },
new SqlParameter("PARAMETROS_EVENTO", SqlDbType.Structured) { TypeName = "LOGSYS.LOGSYS_LISTA_PARAMETROS", Value = parametros }
}
);
}
public static void Shop_CalculateRefund(string StoreName, string APIKey, string Password, long OrderID, string JsonString)
{
ShopifyClient sp = new ShopifyClient(StoreName, APIKey, Password);
SqlPipe p = SqlContext.Pipe;
// Create a new record with the column metadata. The constructor is
// able to accept a variable number of parameters.
SqlDataRecord record = new SqlDataRecord(
new SqlMetaData[] { new SqlMetaData("ShippingAmount", SqlDbType.NVarChar,18),
new SqlMetaData("ShippingTax", SqlDbType.NVarChar,18),
new SqlMetaData("MaximumRefundable", SqlDbType.NVarChar,18),
new SqlMetaData("RefundLineItems", SqlDbType.Int,4),
new SqlMetaData("Transactions", SqlDbType.Int,4)}
);
Refund r = sp.CalculateRefund(OrderID, JsonString);
// Mark the begining of the result-set.
SqlContext.Pipe.SendResultsStart(record);
// Set the record fields.
record.SetString(0,r.shipping.amount);
record.SetString(1, r.shipping.tax);
record.SetString(2, r.shipping.maximum_refundable);
record.SetInt32(3, r.refund_line_items.Count);
record.SetInt32(4, r.transactions.Count);
//record.SetInt32(1, 42);
//record.SetDateTime(2, DateTime.Now);
// Send the row back to the client.
SqlContext.Pipe.SendResultsRow(record);
// Mark the end of the result-set.
SqlContext.Pipe.SendResultsEnd();
// Send the record to the calling program.
SqlContext.Pipe.Send(record);
}
public void SendRow(SqlDataRecord dataRecord, object[] items)
{
for (int i = 0; i < items.Length; i++)
{
string value = items[i].ToStringSafe();
if (string.IsNullOrEmpty(value))
{
dataRecord.SetDBNull(i);
}
else
{
dataRecord.SetString(i, value);
}
}
if (SqlContext.IsAvailable && SqlContext.Pipe != null)
{
// Send the row back to the client.
SqlContext.Pipe.SendResultsRow(dataRecord);
}
}
public void Update(IList<TreeViewNode> treeViewNodes)
{
var sqlConnection = new SqlConnection(ConnectionString);
var sqlCommand = new SqlCommand("dbo.UpdateTreeViewData", sqlConnection)
{
CommandType = CommandType.StoredProcedure,
};
sqlCommand.Parameters.Add("@p_TreeViewData", SqlDbType.Structured);
var list = new List<SqlDataRecord>();
foreach (var treeViewNode in treeViewNodes)
{
var sqlDataRecord = new SqlDataRecord(
new SqlMetaData("Id", SqlDbType.Int),
new SqlMetaData("ParentId", SqlDbType.Int),
new SqlMetaData("NodeName", SqlDbType.NVarChar, 50),
new SqlMetaData("IsSelected", SqlDbType.Bit));
sqlDataRecord.SetValue(0, treeViewNode.Id);
sqlDataRecord.SetValue(1, treeViewNode.ParentId);
sqlDataRecord.SetString(2, treeViewNode.NodeName);
sqlDataRecord.SetValue(3, treeViewNode.IsSelected);
list.Add(sqlDataRecord);
}
sqlCommand.Parameters["@p_TreeViewData"].Value = list;
try
{
sqlConnection.Open();
sqlCommand.ExecuteNonQuery();
}
finally
{
sqlConnection.Close();
}
}
public static void Publisher(SqlString hostname, SqlString route, SqlString message)
{
SqlDataRecord record = new SqlDataRecord(
new SqlMetaData("Result", SqlDbType.NVarChar, 512)
);
using (Realtime.Publisher.Publisher pub = new Realtime.Publisher.Publisher(new ServerConfig()
{
Host = hostname.ToString(),
Port = 8081,
Route = route.ToString(),
Debug = true
}))
{
pub.Publish(message.ToString()
, () =>
{
record.SetString(0, "Message is correct sended to Server.");
SqlContext.Pipe.SendResultsStart(record);
SqlContext.Pipe.SendResultsEnd();
});
}
}
protected List<SqlDataRecord> GenerateStringTableParameterRecords(IEnumerable<string> items)
{
SqlMetaData[] tableDefinition = { new SqlMetaData("Id", SqlDbType.NVarChar, 100) };
return items.Select(item =>
{
var record = new SqlDataRecord(tableDefinition);
record.SetString(0, item);
return record;
}).ToList();
}
public static void IR_SM_Pos_Objective(string brandCodes, int startMonth, int startYear, int endMonth, int endYear, string dsIds)
{
using (SqlConnection con = new SqlConnection("context connection=true"))
{
SqlPipe pipe = SqlContext.Pipe;
List<SqlCommand> commands = new List<SqlCommand>();
try
{
SqlInt32 smallyear = (startYear < endYear) ? startYear : endYear;
SqlInt32 bigyear = (startYear > endYear) ? startYear : endYear;
List<string> brdcods = new List<string>();
string[] split = brandCodes.Split('-');
foreach (string s in split)
if (s.ToLower().Trim() != "")
brdcods.Add(s.Trim().ToLower());
List<string> dsids = new List<string>();
string[] split2 = dsIds.Split('-');
foreach (string s in split2)
if (s.ToLower().Trim() != "")
dsids.Add(s.Trim().ToLower());
for (int year = (int)smallyear; year <= bigyear; year++)
{
foreach (string brandCode in brdcods)
{
foreach (string dsid in dsids)
{
commands.Add(new SqlCommand("SELECT [Counter], [Year], [Month], [DSID], [BudgetValue], [Brandcode] FROM [IR_POS_OBJ] where DSID = @dsid AND (BRANDCODE LIKE '%' + @brandcode + '%') AND ([YEAR] = @year) AND ([MONTH] BETWEEN @startmonth AND @endmonth)"));
commands[commands.Count - 1].Parameters.AddWithValue("@brandcode", brandCode);
commands[commands.Count - 1].Parameters.AddWithValue("@startmonth", (year == smallyear) ? startMonth : 1);
commands[commands.Count - 1].Parameters.AddWithValue("@endmonth", (year == bigyear) ? endMonth : 12);
commands[commands.Count - 1].Parameters.AddWithValue("@year", year);
commands[commands.Count - 1].Parameters.AddWithValue("@dsid", dsid);
}
}
}
SqlDataRecord record = new SqlDataRecord(new SqlMetaData("ID", SqlDbType.Int),
new SqlMetaData("DSID", SqlDbType.Int),
new SqlMetaData("MonthT", SqlDbType.Int),
new SqlMetaData("YearT", SqlDbType.Int),
new SqlMetaData("VAL", SqlDbType.Float),
new SqlMetaData("BrandCode", SqlDbType.NVarChar, 255));
pipe.SendResultsStart(record);
int idCounter = 1;
foreach (SqlCommand cmd in commands)
{
try
{
cmd.Connection = con;
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
int dsid = Convert.ToInt32(dr["DSID"]);
int month = Convert.ToInt32(dr["month"]);
int year = Convert.ToInt32(dr["year"]);
double val = Convert.ToDouble(((dr["BudgetValue"] == DBNull.Value) ? 0 : dr["BudgetValue"]));
string brandcode = Convert.ToString(dr["Brandcode"]);
record.SetInt32(0, idCounter);
record.SetInt32(1, dsid);
record.SetInt32(2, month);
record.SetInt32(3, year);
record.SetDouble(4, val);
record.SetString(5, brandcode);
pipe.SendResultsRow(record);
idCounter++;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con != null)
con.Close();
}
}
pipe.SendResultsEnd();
}
catch (Exception ex)
{
throw ex;
}
}
}
public static void CopyBlob(
SqlString destinationAccount, SqlString destinationSharedKey, SqlBoolean useHTTPS,
SqlString sourceAccountName,
SqlString sourceContainerName, SqlString sourceBlobName,
SqlGuid sourceLeaseId, SqlGuid destinationLeaseId,
SqlString destinationContainerName, SqlString destinationBlobName,
SqlString xmsclientrequestId)
{
AzureBlobService absDest = new AzureBlobService(destinationAccount.Value, destinationSharedKey.Value, useHTTPS.Value);
Container contDest = absDest.GetContainer(destinationContainerName.Value);
ITPCfSQL.Azure.Blob bbDest = new Azure.Blob(contDest, destinationBlobName.Value);
AzureBlobService absSrc = new AzureBlobService(sourceAccountName.Value, "", useHTTPS.Value);
Container contSrc = absSrc.GetContainer(sourceContainerName.Value);
ITPCfSQL.Azure.Blob bbSrc = new Azure.Blob(contSrc, sourceBlobName.Value);
Responses.CopyBlobResponse resp = bbSrc.Copy(bbDest,
sourceLeaseID: sourceLeaseId.IsNull ? (Guid?)null : sourceLeaseId.Value,
destinationLeaseID: destinationLeaseId.IsNull ? (Guid?)null : destinationLeaseId.Value,
xmsclientrequestId: xmsclientrequestId.IsNull ? null : xmsclientrequestId.Value);
SqlDataRecord record = new SqlDataRecord(
new SqlMetaData[]
{
new SqlMetaData("BlobCopyStatus", System.Data.SqlDbType.NVarChar, 255),
new SqlMetaData("CopyId", System.Data.SqlDbType.NVarChar, 255),
new SqlMetaData("Date", System.Data.SqlDbType.DateTime),
new SqlMetaData("ETag", System.Data.SqlDbType.NVarChar, 255),
new SqlMetaData("LastModified", System.Data.SqlDbType.DateTime),
new SqlMetaData("RequestID", System.Data.SqlDbType.UniqueIdentifier),
new SqlMetaData("Version", System.Data.SqlDbType.NVarChar, 255)
});
SqlContext.Pipe.SendResultsStart(record);
record.SetString(0, resp.BlobCopyStatus.ToString());
record.SetString(1, resp.CopyId);
record.SetDateTime(2, resp.Date);
record.SetString(3, resp.ETag);
record.SetDateTime(4, resp.LastModified);
record.SetGuid(5, resp.RequestID);
record.SetString(6, resp.Version);
SqlContext.Pipe.SendResultsRow(record);
SqlContext.Pipe.SendResultsEnd();
}
public static void CreateNewRecordProc()
{
DateTime now = DateTime.UtcNow;
SqlDataRecord record = new SqlDataRecord(new SqlMetaData("PK", SqlDbType.Int),
new SqlMetaData("UTC_DateTime", SqlDbType.DateTime),
new SqlMetaData("Source", SqlDbType.NVarChar, 128),
new SqlMetaData("Encrypted_SHA256", SqlDbType.NVarChar, 32),
new SqlMetaData("Encrypted_AES", SqlDbType.NVarChar, 512),
new SqlMetaData("Decrypted", SqlDbType.NVarChar, 128));
SqlContext.Pipe.SendResultsStart(record);
for (int i = 0; i < SlowRandom(1, 50); i++)
{
SqlContext.Pipe.SendResultsRow(FillRecord(i, record));
}
TimeSpan delta = DateTime.UtcNow - now;
record.SetInt32(0, 0);
record.SetDateTime(1, DateTime.UtcNow);
record.SetString(2, "Total ms:");
record.SetString(3, delta.Milliseconds.ToString());
record.SetString(4, "");
record.SetString(5, "");
SqlContext.Pipe.SendResultsRow(record);
SqlContext.Pipe.SendResultsEnd();
}
/// <summary>
/// Pays with existing payment.
/// </summary>
/// <param name="paymentMethodIds">The list of paymentMethodIds.</param>
/// <param name="amount">The amount.</param>
/// <param name="userId">The userId.</param>
/// <param name="postingDate">The posting date.</param>
/// <param name="orderIds">The order ids.</param>
/// <param name="cn">The sql connection (or null).</param>
/// <param name="trans">The sql transaction (or null).</param>
/// <returns>{error:0,desc:"error description"}.</returns>
public static Dictionary<string, object> PayWithExistingPaymentMethods( List<object> paymentMethodIds,
decimal amount, int userId, DateTime postingDate, List<object> orderIds,
SqlConnection cn, SqlTransaction trans )
{
int errorId = 0;
string desc = "";
List<int> intIds = orderIds.ConvertAll( delegate( object i ) {
return Convert.ToInt32( i );
} );
Dictionary<string, object> j = new Dictionary<string, object>();
/* before updating - check to ensure that there really is enouch left over on this paymentMethodId(s)
* to attach the desiered amount to the selected order
*/
using( SqlCommand cmd = new SqlCommand() ) {
List<SqlDataRecord> rec_paymentMethodIds = new List<SqlDataRecord>();
List<SqlDataRecord> rec_orderIds = new List<SqlDataRecord>();
SqlMetaData[] hashTable = {
new SqlMetaData("keyName",SqlDbType.VarChar,100),
new SqlMetaData("keyValue",SqlDbType.Variant),
new SqlMetaData("primary_key",SqlDbType.Bit),
new SqlMetaData("dataType",SqlDbType.VarChar,50),
new SqlMetaData("dataLength",SqlDbType.Int),
new SqlMetaData("varCharMaxValue",SqlDbType.VarChar,-1)
};
foreach( string id in paymentMethodIds ) {
SqlDataRecord rec = new SqlDataRecord( hashTable );
rec.SetValue( 0, "paymentMethodId" );
rec.SetValue( 1, id );
rec.SetBoolean( 2, false );
rec.SetString( 3, "uniqueidentifier" );
rec.SetValue( 4, 32 );
rec_paymentMethodIds.Add( rec );
}
foreach( int id in intIds ) {
SqlDataRecord rec = new SqlDataRecord( hashTable );
rec.SetValue( 0, "orderId" );
rec.SetValue( 1, id );
rec.SetBoolean( 2, false );
rec.SetString( 3, "int" );
rec.SetValue( 4, 8 );
rec_orderIds.Add( rec );
}
cmd.Connection = cn;
cmd.Transaction = trans;
cmd.CommandType = CommandType.StoredProcedure;
/* this SP will return a single row with error, desc saying if the procedure was successfull.
* the SP sums the remaning total value left on the selected paymentMethods and compares
* it to the amount trying to be paid. If the remaining amount is >= the amount trying to
* be paid the payments will be attached, if the remaining amount is < the amoun trying to
* be paid an error will be returned saying as much.
*/
cmd.CommandText = "dbo.attachPaymentMethods";
cmd.Parameters.Add( "@amountTryingToBePaid", SqlDbType.Money ).Value = amount;
cmd.Parameters.Add( "@paymentMethodIds", SqlDbType.Structured );
cmd.Parameters[ "@paymentMethodIds" ].Direction = ParameterDirection.Input;
if( rec_paymentMethodIds.Count == 0 ) {
string message = "You must select at least one payment method.";
message.Debug( 7 );
Exception ex = new Exception( message );
throw ex;
} else {
cmd.Parameters[ "@paymentMethodIds" ].Value = rec_paymentMethodIds;
}
cmd.Parameters.Add( "@orderIds", SqlDbType.Structured );
cmd.Parameters[ "@orderIds" ].Direction = ParameterDirection.Input;
if( rec_orderIds.Count == 0 ) {
string message = "You must select at least one payment method.";
message.Debug( 7 );
Exception ex = new Exception( message );
throw ex;
} else {
cmd.Parameters[ "@orderIds" ].Value = rec_orderIds;
}
using( SqlDataReader r = cmd.ExecuteReader() ) {
/* batch 1 is the status */
r.Read();
Dictionary<string, object> s = new Dictionary<string, object>();
errorId = r.GetInt32( 0 );
desc = r.GetString( 1 );
/* NOTE: Addtional callback information for attaching payments to orders
* I don't really care about this stuff so I'm not going to write anything to capture it
* but there is is for anyone who does want to capture it.
*/
/* batch 2 is the actual payment detail inserts (paymentMethodDetailId,paymentMethodId,refId,amount)*/
/* batch 3 is the actual upated orders (orderId, paid) */
}
}
if( errorId != 0 ) {
j.Add( "error", errorId );
j.Add( "description", desc );
} else {
j.Add( "error", 0 );
j.Add( "description", "" );
}
return j;
}
public static void IR_SM_Province_PerCustomer_PerDistributor(string productIds, DateTime startDate, DateTime endDate, string provinceIds, string distributorIds)
{
using (SqlConnection con = new SqlConnection("context connection=true"))
{
SqlPipe pipe = SqlContext.Pipe;
List<SqlCommand> commands = new List<SqlCommand>();
try
{
List<string> prodIds = new List<string>();
string[] split2 = productIds.Split('-');
foreach (string s in split2)
if (s.ToLower().Trim() != "")
prodIds.Add(s.Trim().ToLower());
List<string> provIds = new List<string>();
string[] split = provinceIds.Split('-');
foreach (string s in split)
if (s.ToLower().Trim() != "")
provIds.Add(s.Trim().ToLower());
if (distributorIds != "")
{
List<string> distIdsList = new List<string>();
string[] splited_distIds = distributorIds.Split('-');
foreach (string distCode in splited_distIds)
if (distCode.ToLower().Trim() != "")
distIdsList.Add(distCode.Trim().ToLower());
foreach (string distId in distIdsList)
{
foreach (string provId in provIds)
{
foreach (string proId in prodIds)
{
commands.Add(new SqlCommand("select [Sales].[DS].[DSID], [Sales].[DS].[DSName],[Global].[Products].[ProductID], [Sales].[DSSales].[SalesQty], [Sales].[DSSales].[CheckGDate] ,[Global].[City].[ProvinceID], [Global].[Distributors].[DistID], [Global].[Distributors].[DistNameEnglish] from [Sales].[DS] inner join [Sales].[DSSales] on [Sales].[DS].[DSID] = [Sales].[DSSales].[DSID] inner join [Global].[Products] on [Global].[Products].[ProductID] = [Sales].[DSSales].[ProductID] inner join [Global].[City] on [Global].[City].[CityID] = [Sales].[DS].[DSCityID] inner join [Global].[Distributors] on [Global].[Distributors].[DistID] = [Sales].[DSSales].[DistID] where [Sales].[DSSales].[CheckGDate] between @startdate and @enddate and [Global].[Products].[ProductID] = @productid and [Global].[City].[ProvinceID] = @provinceid and [Global].[Distributors].[DistID] = @distid"));
commands[commands.Count - 1].Parameters.AddWithValue("@productid", proId);
commands[commands.Count - 1].Parameters.AddWithValue("@startdate", startDate);
commands[commands.Count - 1].Parameters.AddWithValue("@enddate", endDate);
commands[commands.Count - 1].Parameters.AddWithValue("@provinceid", provId);
commands[commands.Count - 1].Parameters.AddWithValue("@distid", distId);
}
}
}
}
else
{
foreach (string provId in provIds)
{
foreach (string proId in prodIds)
{
commands.Add(new SqlCommand("select [Sales].[DS].[DSID], [Sales].[DS].[DSName],[Global].[Products].[ProductID], [Sales].[DSSales].[SalesQty], [Sales].[DSSales].[CheckGDate] ,[Global].[City].[ProvinceID], [Global].[Distributors].[DistID], [Global].[Distributors].[DistNameEnglish] from [Sales].[DS] inner join [Sales].[DSSales] on [Sales].[DS].[DSID] = [Sales].[DSSales].[DSID] inner join [Global].[Products] on [Global].[Products].[ProductID] = [Sales].[DSSales].[ProductID] inner join [Global].[City] on [Global].[City].[CityID] = [Sales].[DS].[DSCityID] inner join [Global].[Distributors] on [Global].[Distributors].[DistID] = [Sales].[DSSales].[DistID] where [Sales].[DSSales].[CheckGDate] between @startdate and @enddate and [Global].[Products].[ProductID] = @productid and [Global].[City].[ProvinceID] = @provinceid"));
commands[commands.Count - 1].Parameters.AddWithValue("@productid", proId);
commands[commands.Count - 1].Parameters.AddWithValue("@startdate", startDate);
commands[commands.Count - 1].Parameters.AddWithValue("@enddate", endDate);
commands[commands.Count - 1].Parameters.AddWithValue("@provinceid", provId);
}
}
}
SqlDataRecord record = new SqlDataRecord(new SqlMetaData("DSID", SqlDbType.Int),
new SqlMetaData("DSName", SqlDbType.NVarChar, 255, 1033, SqlCompareOptions.None),
new SqlMetaData("ProductID", SqlDbType.Int),
new SqlMetaData("SalesQty", SqlDbType.Int),
new SqlMetaData("CheckGDate", SqlDbType.DateTime),
new SqlMetaData("ProvinceID", SqlDbType.Int),
new SqlMetaData("DistID", SqlDbType.Int),
new SqlMetaData("DistNameEnglish", SqlDbType.NVarChar, 255, 1033, SqlCompareOptions.None));
pipe.SendResultsStart(record);
foreach (SqlCommand cmd in commands)
{
try
{
cmd.Connection = con;
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
int DSID = Convert.ToInt32(reader["DSID"]);
string DSName = Convert.ToString(reader["DSName"]);
int ProductID = Convert.ToInt32(reader["ProductID"]);
int SalesQty = Convert.ToInt32(reader["SalesQty"]);
DateTime CheckGDate = Convert.ToDateTime(reader["CheckGDate"]);
int ProvinceID = Convert.ToInt32(reader["ProvinceID"]);
int DistID = Convert.ToInt32(reader["DistID"]);
string DistName = Convert.ToString(reader["DistNameEnglish"]);
record.SetInt32(0, DSID);
record.SetString(1, DSName);
record.SetInt32(2, ProductID);
record.SetInt32(3, SalesQty);
record.SetDateTime(4, CheckGDate);
record.SetInt32(5, ProvinceID);
record.SetInt32(6, DistID);
record.SetString(7, DistName);
pipe.SendResultsRow(record);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con != null)
con.Close();
}
}
pipe.SendResultsEnd();
}
catch (Exception ex)
{
throw ex;
}
}
}
public static void FileStringSplitToTable(
SqlString fileName, SqlString delimiter)
{
string[] delimiters = new string[] { delimiter.Value };
using (System.IO.StreamReader sr = new System.IO.StreamReader(new System.IO.FileStream(
fileName.Value, System.IO.FileMode.Open, System.IO.FileAccess.Read, System.IO.FileShare.Read)))
{
string str;
SqlMetaData[] sqlMetadatas = null;
bool fFirst = true;
while ((str = sr.ReadLine()) != null)
{
string[] tokens = str.Split(delimiters, StringSplitOptions.None);
if (sqlMetadatas == null)
{
sqlMetadatas = new SqlMetaData[tokens.Length];
for (int iToken = 0; iToken < tokens.Length; iToken++)
{
sqlMetadatas[iToken] = new SqlMetaData("Field_" + iToken, System.Data.SqlDbType.NVarChar, -1);
}
}
#region Output fields
SqlDataRecord record = new SqlDataRecord(sqlMetadatas);
int i;
for (i = 0; i < tokens.Length; i++)
{
record.SetString(i, tokens[i]);
}
for (; i < sqlMetadatas.Length; i++) // add NULLs if need be.
{
record.SetDBNull(i);
}
if (fFirst)
{
SqlContext.Pipe.SendResultsStart(record);
fFirst = false;
}
SqlContext.Pipe.SendResultsRow(record);
#endregion
}
SqlContext.Pipe.SendResultsEnd();
}
}
internal static void PushSingleRecordResult(object result, System.Data.SqlDbType sqlDBType)
{
//SqlContext.Pipe.Send("Response output:\n");
//SqlContext.Pipe.Send(result.ToString());
SqlDataRecord record = null;
switch (sqlDBType)
{
case System.Data.SqlDbType.NVarChar:
case System.Data.SqlDbType.VarChar:
record = new SqlDataRecord(new SqlMetaData[] { new SqlMetaData("Result", sqlDBType, -1) });
record.SetString(0, result.ToString());
break;
case System.Data.SqlDbType.Xml:
record = new SqlDataRecord(new SqlMetaData[] { new SqlMetaData("Result", sqlDBType) });
SqlXml xml;
using (System.Xml.XmlReader reader = System.Xml.XmlReader.Create(new System.IO.StringReader(result.ToString())))
{
xml = new SqlXml(reader);
}
record.SetSqlXml(0, xml);
break;
case System.Data.SqlDbType.Int:
record = new SqlDataRecord(new SqlMetaData[] { new SqlMetaData("Result", sqlDBType) });
record.SetInt32(0, (Int32)result);
break;
default:
throw new ArgumentException("SqlDbType " + sqlDBType.ToString() + " is not supported by PushSingleRecordResult.");
}
SqlContext.Pipe.SendResultsStart(record);
SqlContext.Pipe.SendResultsRow(record);
SqlContext.Pipe.SendResultsEnd();
}
public static void PushLeaseBlobResponse(Responses.LeaseBlobResponse lbr)
{
SqlDataRecord record = new SqlDataRecord(new SqlMetaData[] {
new SqlMetaData("LeaseId", System.Data.SqlDbType.UniqueIdentifier),
new SqlMetaData("Date", System.Data.SqlDbType.DateTime),
new SqlMetaData("LeaseBreakTimeSeconds", System.Data.SqlDbType.Int),
new SqlMetaData("RequestId", System.Data.SqlDbType.UniqueIdentifier),
new SqlMetaData("Version", System.Data.SqlDbType.NVarChar, 4000)
});
if (lbr.LeaseId.HasValue)
record.SetGuid(0, lbr.LeaseId.Value);
record.SetDateTime(1, lbr.Date);
if (lbr.LeaseTimeSeconds.HasValue)
record.SetInt32(2, lbr.LeaseTimeSeconds.Value);
record.SetGuid(3, lbr.RequestID);
record.SetString(4, lbr.Version);
SqlContext.Pipe.SendResultsStart(record);
SqlContext.Pipe.SendResultsRow(record);
SqlContext.Pipe.SendResultsEnd();
}
public static void StoredProcedure1()
{
Builder builder = new Builder();
var articles = builder.Build<Article>("exec usp_get_articles");
foreach (var article in articles)
{
article.Contributors = builder.Build<Contributor>("exec usp_get_article_contributors " + article.ArticleId);
foreach (var contributor in article.Contributors)
{
contributor.SocialLinks = builder.Build<SocialLink>("select * from contributor_social_link where contributor_id = " + contributor.Id);
}
article.Categories = builder.Build<Category>("exec usp_get_article_categories " + article.ArticleId);
article.Pages = builder.Build<Page>("exec usp_get_article_pages " + article.ArticleId);
foreach (var page in article.Pages)
{
var paragraphs = builder.Build<Paragraph>("exec usp_get_paragraphs " + page.Id);
foreach (var paragraph in paragraphs)
{
var images = builder.Build<Image>("exec usp_get_paragraph_image " + paragraph.Id);
paragraph.Image = (images.Count > 0) ? images.ToArray()[0] : null;
page.PageElements.Add(paragraph);
}
var tables = builder.Build<Table>("exec usp_get_tables " + page.Id);
foreach (var table in tables)
{
page.PageElements.Add(table);
}
var imageGroups = builder.Build<ImageGroup>("exec usp_get_image_groups " + page.Id);
foreach (var imageGroup in imageGroups)
{
var images = builder.Build<Image>("exec usp_get_image_group_images " + imageGroup.Id);
imageGroup.Images = (images.Count > 0) ? images : new List<Image>();
page.PageElements.Add(imageGroup);
}
var inlineImages = builder.Build<Image>("exec usp_get_inline_images " + page.Id);
foreach (var inlineImage in inlineImages)
{
page.PageElements.Add(inlineImage);
}
var videos = builder.Build<Video>("exec usp_get_videos " + page.Id);
foreach (var video in videos)
{
page.PageElements.Add(video);
}
var blockQuotes = builder.Build<BlockQuote>("exec usp_get_block_quotes " + page.Id);
foreach (var blockQuote in blockQuotes)
{
page.PageElements.Add(blockQuote);
}
}
}
SqlPipe sp;
sp = SqlContext.Pipe;
var dataRecord = new SqlDataRecord(
new SqlMetaData("id", SqlDbType.Int),
new SqlMetaData("json", SqlDbType.Text));
sp.SendResultsStart(dataRecord);
foreach (var article in articles)
{
dataRecord.SetInt32(0, article.Id);
dataRecord.SetString(1, article.ToString());
sp.SendResultsRow(dataRecord);
}
sp.SendResultsEnd();
}
public static void xp_getfiledetails(string filePath)
{
//pipe to sql server
SqlPipe pipe = SqlContext.Pipe;
if (File.Exists(filePath))
{
//try and open the requested file
FileInfo file;
try
{
file = new FileInfo(filePath);
}
catch (Exception e)
{
try { pipe.ExecuteAndSend(new SqlCommand("raiserror ('xp_getfiledetails() returned error 2, ''The system cannot find the file specified.''',16,1)")); }
// ReSharper disable EmptyGeneralCatchClause
catch
// ReSharper restore EmptyGeneralCatchClause
{ }
//if I don't re-throw here I get errors below
throw (e);
}
//Build retrun record
SqlMetaData alternateName = new SqlMetaData("Alternate Name", SqlDbType.NVarChar, 4000);
SqlMetaData size = new SqlMetaData("Size", SqlDbType.BigInt);
SqlMetaData creationDate = new SqlMetaData("Creation Date", SqlDbType.NChar, 8);
SqlMetaData creationTime = new SqlMetaData("Creation Time", SqlDbType.NChar, 6);
SqlMetaData lastWrittenDate = new SqlMetaData("Last Written Date", SqlDbType.NChar, 8);
SqlMetaData lastWrittenTime = new SqlMetaData("Last Written Time", SqlDbType.NChar, 6);
SqlMetaData lastAccessedDate = new SqlMetaData("Last Accessed Date", SqlDbType.NChar, 8);
SqlMetaData lastAccessedTime = new SqlMetaData("Last Accessed Time", SqlDbType.NChar, 6);
SqlMetaData attributes = new SqlMetaData("Attributes", SqlDbType.Int);
SqlDataRecord record = new SqlDataRecord(new[] {
alternateName,
size,
creationDate,
creationTime,
lastWrittenDate,
lastWrittenTime,
lastAccessedDate,
lastAccessedTime,
attributes});
//try to add data to the retrun record
try
{
record.SetString(0, file.Name);
record.SetInt64(1, file.Length);
record.SetString(2, file.CreationTime.ToString("yyyyMMdd"));
record.SetString(3, file.CreationTime.ToString("HHmmss"));
record.SetString(4, file.LastWriteTime.ToString("yyyyMMdd"));
record.SetString(5, file.LastWriteTime.ToString("HHmmss"));
record.SetString(6, file.LastAccessTime.ToString("yyyyMMdd"));
record.SetString(7, file.LastAccessTime.ToString("HHmmss"));
record.SetInt32(8, (int)file.Attributes);
}
catch (Exception)
{
try { pipe.ExecuteAndSend(new SqlCommand("raiserror ('xp_getfiledetails() returned error 2, ''The system cannot find the file specified.''',16,1)")); }
// ReSharper disable EmptyGeneralCatchClause
catch { }
// ReSharper restore EmptyGeneralCatchClause
}
//send record back to sql server
try
{
pipe.Send(record);
}
catch (Exception e)
{
throw (e);
}
}
else
{
try { pipe.ExecuteAndSend(new SqlCommand("raiserror ('xp_getfiledetails() returned error 2, ''The system cannot find the file specified.''',16,1)")); }
// ReSharper disable EmptyGeneralCatchClause
catch { }
// ReSharper restore EmptyGeneralCatchClause
}
}
/// <summary>
/// Retrieves file from database as sql recordset in raw encrypted and compressed form
/// </summary>
/// <param name="fileId">file id</param>
public static void ExtractFileToRecordEncryptedCompressed(long fileId)
{
SqlPipe pipe = SqlContext.Pipe;
SqlDataReader sqlReader;
using (SqlConnection cn = new SqlConnection("context connection=true"))
{
cn.Open();
SqlCommand sqlCmd = new SqlCommand("RetrieveFile", cn) { CommandType = CommandType.StoredProcedure };
sqlCmd.Parameters.Add("@Id", SqlDbType.BigInt);
sqlCmd.Parameters[0].Value = fileId;
try
{
sqlReader = sqlCmd.ExecuteReader();
}
catch (Exception e)
{
pipe.Send("Failed to retrieve data");
pipe.Send(e.Message);
throw;
}
if (sqlReader != null)
if (sqlReader.HasRows)
{
sqlReader.Read();
string fileName = (string)sqlReader.GetSqlString(0);
int origionalFileSize = (int)sqlReader.GetSqlInt64(3);
int storageType = sqlReader.GetByte(8);
MemoryStream sqlDataStream = new MemoryStream(origionalFileSize);
const int length = 4096;
byte[] fileBlob = new byte[length];
int startPoint = 0;
long retval = sqlReader.GetBytes(10, startPoint, fileBlob, 0, length);
sqlDataStream.Write(fileBlob, 0, (int)retval);
while (retval == length)
{
startPoint += length;
retval = sqlReader.GetBytes(10, startPoint, fileBlob, 0, length);
sqlDataStream.Write(fileBlob, 0, (int)retval);
}
sqlReader.Close();
sqlDataStream.Seek(0, SeekOrigin.End);
SqlMetaData fileNameInfo = new SqlMetaData("OrigionalFileName", SqlDbType.NVarChar, 255);
SqlMetaData fileBlobInfo = new SqlMetaData("FileData", SqlDbType.Image);
// Create a new record with the column metadata.
SqlDataRecord record = new SqlDataRecord(new[]
{
fileNameInfo,
fileBlobInfo,
});
// Set the record fields.
record.SetString(0, fileName);
//if it is encrypted decrypt it.
if (storageType == 4)
{
record.SetBytes(1, 0, sqlDataStream.GetBuffer(), 0, (int)sqlDataStream.Position);
pipe.Send(record);
}
else
{
pipe.Send("File not encrypted and compressed");
}
}
else
{
pipe.Send("Invalid FileId");
}
}
}
public static void IR_SM_Province_PerDistributor(string productIds, DateTime startDate, DateTime endDate, string provinceIds)
{
using (SqlConnection con = new SqlConnection("context connection=true"))
{
SqlPipe pipe = SqlContext.Pipe;
List<SqlCommand> commands = new List<SqlCommand>();
List<string> prodIds = new List<string>();
string[] split2 = productIds.Split('-');
foreach (string s in split2)
{
if (s.ToLower().Trim() != "")
prodIds.Add(s.Trim().ToLower());
}
List<string> provIds = new List<string>();
string[] split = provinceIds.Split('-');
foreach (string s in split)
{
if (s.ToLower().Trim() != "")
provIds.Add(s.Trim().ToLower());
}
foreach (string provId in provIds)
{
foreach (string prodId in prodIds)
{
commands.Add(new SqlCommand("select sum(Sales.DSSales.SalesQty) as QTY, DATEPART(month, Sales.DSSales.CheckGDate) as MonthT, DATEPART(year, Sales.DSSales.CheckGDate) as YearT, ProductID, [Global].Distributors.DistNameEnglish as DISTRIBUTORNAME ,[Global].Distributors.DistID from Sales.DSSales inner join [Global].Distributors on Sales.DSSales.DistID = [Global].Distributors.DistID inner join Sales.DS on Sales.DS.DSID = Sales.DSSales.DSID inner join [Global].City on [Global].City.CityID = Sales.DS.DSCityID where (Sales.DSSales.CheckGDate between @startdate and @enddate) and (Sales.DSSales.ProductID = @productid) and ([Global].City.ProvinceID = @provinceid) Group by DATEPART(month, Sales.DSSales.CheckGDate), DATEPART(year, Sales.DSSales.CheckGDate), ProductID, [Global].Distributors.DistNameEnglish, [Global].Distributors.DistID"));
commands[commands.Count - 1].Parameters.AddWithValue("@productid", prodId);
commands[commands.Count - 1].Parameters.AddWithValue("@startdate", startDate);
commands[commands.Count - 1].Parameters.AddWithValue("@enddate", endDate);
commands[commands.Count - 1].Parameters.AddWithValue("@provinceid", provId);
}
}
SqlDataRecord record = new SqlDataRecord(new SqlMetaData("QTY", SqlDbType.Int),
new SqlMetaData("MonthT", SqlDbType.Int),
new SqlMetaData("YearT", SqlDbType.Int),
new SqlMetaData("ProductID", SqlDbType.Int),
new SqlMetaData("DISTRIBUTORNAME", SqlDbType.NVarChar, 50),
new SqlMetaData("DistID", SqlDbType.Int));
pipe.SendResultsStart(record);
foreach (SqlCommand cmd in commands)
{
try
{
cmd.Connection = con;
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
try
{
int qty = Convert.ToInt32(dr["qty"]);
int month = Convert.ToInt32(dr["montht"]);
int year = Convert.ToInt32(dr["yeart"]);
int productid = Convert.ToInt32(dr["ProductID"]);
string distributorName = Convert.ToString(dr["DISTRIBUTORNAME"]);
int distId = Convert.ToInt32(dr["DistID"]);
record.SetInt32(0, qty);
record.SetInt32(1, month);
record.SetInt32(2, year);
record.SetInt32(3, productid);
record.SetString(4, distributorName);
record.SetInt32(5, distId);
pipe.SendResultsRow(record);
}
catch (Exception ex)
{
ex.ToString();
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con != null)
con.Close();
}
}
pipe.SendResultsEnd();
}
}
private static SqlDataRecord CreateStringRecord(string value)
{
var record = new SqlDataRecord(new SqlMetaData("Value", SqlDbType.NVarChar, MaxStringLength));
if (!string.IsNullOrWhiteSpace(value))
{
if(value.Length > MaxStringLength)
throw new SisoDbException(ExceptionMessages.SqlServerTableParams_ToLongString.Inject(MaxStringLength, value));
record.SetString(0, value);
}
else
record.SetDBNull(0);
return record;
}
/// <summary>
/// Gets the orders by order ids.
/// </summary>
/// <param name="ids">The ids.</param>
/// <param name="fcn">SQL connection.</param>
/// <param name="ftrns">SQL transaction.</param>
/// <returns>The matching orders.</returns>
public static List<Order> GetOrdersByOrderIds(int[] ids, SqlConnection fcn, SqlTransaction ftrns)
{
List<Order> orders = new List<Order>();
if(ids.Length == 0) { return orders; };
List<SqlDataRecord> rowData = new List<SqlDataRecord>();
SqlMetaData[] hashTable = {
new SqlMetaData("keyName",SqlDbType.VarChar,100),
new SqlMetaData("keyValue",SqlDbType.Variant),
new SqlMetaData("primary_key",SqlDbType.Bit),
new SqlMetaData("dataType",SqlDbType.VarChar,50),
new SqlMetaData("dataLength",SqlDbType.Int),
new SqlMetaData("varCharMaxValue",SqlDbType.VarChar,-1)
};
StringBuilder s = new StringBuilder();
foreach(int id in ids) {
SqlDataRecord rec = new SqlDataRecord(hashTable);
rec.SetValue(0, "orderId");
rec.SetValue(1, id);
rec.SetBoolean(2, false);
rec.SetString(3, "int");
rec.SetValue(4, 8);
rowData.Add(rec);
}
SqlConnection cn;
if(fcn != null) {
cn = fcn;
} else {
cn = Site.SqlConnection;
}
using(SqlCommand cmd = cn.CreateCommand()) {
if(fcn != null) {
cmd.Transaction = ftrns;
}
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "dbo.getOrders";
cmd.Parameters.Add("@orderIds", SqlDbType.Structured);
cmd.Parameters["@orderIds"].Direction = ParameterDirection.Input;
cmd.Parameters["@orderIds"].Value = rowData;
using(SqlDataReader u = cmd.ExecuteReader()) {
int orderId = -1;
DateTime orderDate = DateTime.MinValue;
decimal grandTotal = 0;
decimal taxTotal = 0;
decimal subTotal = 0;
decimal shippingTotal = 0;
decimal service1 = 0;
decimal service2 = 0;
string manifest = "";
string purchaseOrder = "";
decimal discount = 0;
string comment = "";
decimal paid = 0;
Guid billToAddressId = Guid.Empty;
bool closed = false;
bool canceled = false;
Guid paymentMethodId = Guid.Empty;
int termId = -1;
int userId = -1;
string orderNumber = "";
bool creditMemo = false;
string scanned_order_image = "";
DateTime readyForExport = DateTime.MinValue;
DateTime recalculatedOn = DateTime.MinValue;
Guid sessionId = Guid.Empty;
int soldBy = -1;
int requisitionedBy = -1;
int approvedBy = -1;
DateTime deliverBy = DateTime.MinValue;
string vendor_accountNo = "";
string FOB = "";
int parentOrderId = -1;
int order_status = -1;
List<Line> lines = new List<Line>();
while(u.Read()) {
/* #44 is orderId */
if(u.GetInt32(44) != orderId && orderId != -1) {
/*the orderId has changed, add the previous order */
orders.Add(new Order(
orderId, orderDate, grandTotal, taxTotal, subTotal, shippingTotal, service1,
service2, manifest, purchaseOrder, discount, comment, paid, billToAddressId, closed,
canceled, paymentMethodId, termId, userId, orderNumber, creditMemo, scanned_order_image,
readyForExport, recalculatedOn, sessionId, soldBy, requisitionedBy, approvedBy, deliverBy,
vendor_accountNo, FOB, parentOrderId, lines, order_status, cn, ftrns
));
lines = new List<Line>();/* create a new list of lines for the next order */
}
orderId = u.GetInt32(44);
orderDate = u.GetDateTime(132);
grandTotal = u.GetDecimal(133);
taxTotal = u.GetDecimal(134);
subTotal = u.GetDecimal(135);
shippingTotal = u.GetDecimal(136);
service1 = u.GetDecimal(137);
service2 = u.GetDecimal(138);
manifest = u.GetString(139);
purchaseOrder = u.GetString(140);
discount = u.GetDecimal(164);
comment = u.GetString(141);
paid = u.GetDecimal(190);
billToAddressId = u.GetGuid(103);
closed = u.GetBoolean(191);
canceled = u.GetBoolean(191);
termId = u.GetInt32(84);
userId = u.GetInt32(55);
orderNumber = u.GetString(46);
creditMemo = u.GetBoolean(193);
scanned_order_image = u.GetString(53);
readyForExport = u.GetDateTime(7);
recalculatedOn = u.GetDateTime(194);
sessionId = u.GetGuid(38);
soldBy = u.GetInt32(195);
requisitionedBy = u.GetInt32(196);
approvedBy = u.GetInt32(197);
deliverBy = u.GetDateTime(198);
vendor_accountNo = u.GetString(199);
FOB = u.GetString(200);
parentOrderId = u.GetInt32(201);
order_status = u.GetInt32(5);
/* always add every line */
lines.Add(new Line(
u.GetGuid(37)/*cartId*/,
u.GetGuid(38)/*sessionId*/,
u.GetInt32(39)/*qty*/,
u.GetString(0)/*itemNumber*/,
u.GetDecimal(41)/*price*/,
u.GetDateTime(42)/*add time*/,
u.GetInt32(44)/*orderId*/,
u.GetInt32(45)/*serialId*/,
u.GetString(46)/*orderNumber*/,
u.GetString(47)/*serialNumber*/,
u.GetGuid(48)/*addressId*/,
u.GetInt32(49)/*shipmentId*/,
u.GetString(50)/*shipmentNumber*/,
u.GetInt32(51)/*lineNumber*/,
u.GetString(52)/*epsmmcsoutput*/,
u.GetString(54)/*epsmmcsfilename*/,
u.GetDecimal(170)/*valueCostTotal*/,
u.GetDecimal(171)/*noTaxValueCostTotal*/,
u.GetDateTime(203)/*fullfillmentDate*/,
u.GetDateTime(204)/*estimatedFulfillmentDate*/,
u.GetGuid(202)/*parentCartId*/,
u.GetInt32(12)/*backorderedqty*/,
u.GetInt32(13)/*canceledQty*/,
u.GetString(174)/*customLineNumber*/,
u.GetInt32(205)/*kitAllocationId*/,
u.GetInt32(206)/*kitQty*/,
u.GetBoolean(207)/*showAsSeperateLineOnInvoice*/,
u.GetGuid(208)/*vendorItemKitAssignmentId*/,
u.GetGuid(209)/*kitAllocationCartId*/,
u.GetInt32(1)/*line_status*/
));
}
/* add the last order */
orders.Add(new Order(
orderId, orderDate, grandTotal, taxTotal, subTotal, shippingTotal, service1,
service2, manifest, purchaseOrder, discount, comment, paid, billToAddressId, closed,
canceled, paymentMethodId, termId, userId, orderNumber, creditMemo, scanned_order_image,
readyForExport, recalculatedOn, sessionId, soldBy, requisitionedBy, approvedBy, deliverBy,
vendor_accountNo, FOB, parentOrderId, lines, order_status, cn, ftrns
));
/* now all the shipments that belong to the orders */
u.NextResult();
while(u.Read()) {
int shipmentOrderId = u.GetInt32(0);
/* find the order that goes to this shipment */
Commerce.Order sOrd = orders.Find(delegate(Commerce.Order ord) {
return ord.OrderId == shipmentOrderId;
});
if(sOrd == null) { continue; }
/*
cart.orderId,addressUpdateId,cart.shipmentNumber,tracking,
dateShipped,actualWeight,actualService,actualCost,
actualBilledWeight,packageLength,packageWidth,
packageHeight,thirdPartyAccount,voidStatus,
emailSent,addDate
*/
Shipment shp = new Shipment(sOrd.ShipToAddress, sOrd,
u.GetGuid(1), u.GetString(2), u.GetString(3),
u.GetString(4), u.GetString(5),
u.GetString(6), u.GetString(7),
u.GetString(8), u.GetString(9),
u.GetString(10), u.GetString(11),
u.GetString(12), u.GetString(13),
u.GetDateTime(14), u.GetDateTime(15));
sOrd.Shipments.Add(shp);
}
/* next batch... line detail
cartDetailId, cartDetail.cartId,
inputName, value, cartDetail.sessionId */
u.NextResult();
while(u.Read()) {
LineDetail lineDetail = new LineDetail(
u.GetGuid(0),
u.GetGuid(1),
u.GetGuid(4),
u.GetString(2),
u.GetString(3)
);
/* find the line to attach this line detail to */
Line line = lines.Find(delegate(Commerce.Line l) {
return l.CartId == lineDetail.CartId;
});
if(line != null) {
line.LineDetail.Add(lineDetail);
}
}
/* next batch... form source
* order_line_forms.cartId, sourceCode, formName */
u.NextResult();
while(u.Read()) {
Guid id = u.GetGuid(0);
Line line = lines.Find(delegate(Commerce.Line l) {
return l.CartId == id;
});
if(line != null) {
if(u.IsDBNull(1)) {
line.SourceCode = "";
line.FormName = "NO FORM";
} else {
line.SourceCode = u.GetString(1);
line.FormName = u.GetString(2);
}
}
}
}
}
return orders;
}
public static void IR_SM_Total_PerCustomer_PerChannel(string productIds, DateTime startDate, DateTime endDate)
{
using (SqlConnection con = new SqlConnection("context connection=true"))
{
SqlPipe pipe = SqlContext.Pipe;
List<SqlCommand> commands = new List<SqlCommand>();
List<string> productIdsList = new List<string>();
string[] splited_ids = productIds.Split('-');
foreach (string diCode in splited_ids)
{
if (diCode.ToLower().Trim() != "")
productIdsList.Add(diCode.Trim().ToLower());
}
foreach (string pId in productIdsList)
{
commands.Add(new SqlCommand("select [Sales].[DS].[DSID], [Sales].[DS].[DSName],[Global].[Products].[ProductID] , [Sales].[DSSales].[SalesQty], [Sales].[DSSales].[CheckGDate] ,[Global].[City].[ProvinceID] , [dbo].[IR_POS_Channel].[POS_Channel], [dbo].[IR_POS_Type].[POS_Type] from [Sales].[DS] inner join [Sales].[DSSales] on [Sales].[DS].[DSID] = [Sales].[DSSales].[DSID] inner join [Global].[Products] on [Global].[Products].[ProductID] = [Sales].[DSSales].[ProductID] left outer join [Global].[City] on [Global].[City].[CityID] = [Sales].[DS].[DSCityID] left outer join [dbo].[IR_POS_Classification] on [dbo].[IR_POS_Classification].[POS_ID] = [Sales].[DS].[DSID] left outer join [dbo].[IR_POS_Type] on [dbo].[IR_POS_Type].[Type_ID] = [dbo].[IR_POS_Classification].[POS_Type_ID] left outer join [dbo].[IR_POS_Channel] on [dbo].[IR_POS_Channel].[Channel_ID] = [dbo].[IR_POS_Classification].[POS_Channel_ID] where [Sales].[DSSales].[CheckGDate] between @startdate and @enddate and [Global].[Products].[ProductID] = @productid"));
commands[commands.Count - 1].Parameters.AddWithValue("@productid", pId);
commands[commands.Count - 1].Parameters.AddWithValue("@startdate", startDate);
commands[commands.Count - 1].Parameters.AddWithValue("@enddate", endDate);
}
SqlDataRecord record = new SqlDataRecord(new SqlMetaData("ID", SqlDbType.Int),
new SqlMetaData("DSID", SqlDbType.Int),
new SqlMetaData("DSName", SqlDbType.NVarChar, 255, 1033, SqlCompareOptions.None),
new SqlMetaData("ProductID", SqlDbType.Int),
new SqlMetaData("SalesQty", SqlDbType.Int),
new SqlMetaData("CheckGDate", SqlDbType.DateTime),
new SqlMetaData("ProvinceID", SqlDbType.Int),
new SqlMetaData("ChannelType", SqlDbType.NVarChar, 255),
new SqlMetaData("Channel", SqlDbType.NVarChar, 255));
pipe.SendResultsStart(record);
foreach (SqlCommand cmd in commands)
{
try
{
cmd.Connection = con;
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
int IdCounter = 1;
while (reader.Read())
{
try
{
int DSID = Convert.ToInt32(reader["DSID"]);
string DSName = Convert.ToString(reader["DSName"]);
int ProductID = Convert.ToInt32(reader["ProductID"]);
int SalesQty = Convert.ToInt32(reader["SalesQty"]);
DateTime CheckGDate = Convert.ToDateTime(reader["CheckGDate"]);
record.SetInt32(0, IdCounter);
record.SetInt32(1, DSID);
record.SetString(2, DSName);
record.SetInt32(3, ProductID);
record.SetInt32(4, SalesQty);
record.SetDateTime(5, CheckGDate);
Utils.SetIntRecord(reader, "ProvinceID", record, 6);
Utils.SetStringRecord_NullEmpty(reader,"POS_Type",record,7);
Utils.SetStringRecord_NullEmpty(reader, "POS_Channel", record, 8);
pipe.SendResultsRow(record);
IdCounter++;
}
catch { }
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con != null)
con.Close();
}
}
pipe.SendResultsEnd();
}
}
public void SetValue(ref SqlDataRecord sqlDataRecord, SqlDescriptionAttribute sqlDescription, object value,
int ordinal)
{
if (!sqlDescription.HasDbType)
{
throw new InvalidDataException("SqlDbType can not be null");
}
if (value == null)
{
sqlDataRecord.SetDBNull(ordinal);
return;
}
switch (sqlDescription.SqlDbType)
{
case SqlDbType.BigInt:
var ll = value as long?;
if (!ll.HasValue)
{
throw new Exception("Value is not BigInt");
}
sqlDataRecord.SetInt64(ordinal, ll.Value);
break;
case SqlDbType.Binary:
var bb = value as byte?;
if (!bb.HasValue)
{
throw new Exception("Value is not BigInt");
}
sqlDataRecord.SetSqlByte(ordinal, bb.Value);
break;
case SqlDbType.Bit:
var bit = value as bool?;
if (!bit.HasValue)
{
throw new Exception("Value is not Bit");
}
sqlDataRecord.SetBoolean(ordinal, bit.Value);
break;
case SqlDbType.NChar:
case SqlDbType.Char:
var chr = value as char?;
if (!chr.HasValue)
{
throw new Exception("Value is not Char");
}
sqlDataRecord.SetChar(ordinal, chr.Value);
break;
case SqlDbType.DateTime:
case SqlDbType.SmallDateTime:
case SqlDbType.Date:
case SqlDbType.DateTime2:
var dt = value as DateTime?;
if (!dt.HasValue)
{
throw new Exception("Value is not DateTime");
}
sqlDataRecord.SetDateTime(ordinal, dt.Value);
break;
case SqlDbType.Decimal:
case SqlDbType.Money:
case SqlDbType.SmallMoney:
var dc = value as decimal?;
if (!dc.HasValue)
{
throw new Exception("Value is not Decimal");
}
sqlDataRecord.SetDecimal(ordinal, dc.Value);
break;
case SqlDbType.Float:
var d = value as double?;
if (!d.HasValue)
{
throw new Exception("Value is not Double");
}
sqlDataRecord.SetDouble(ordinal, d.Value);
break;
case SqlDbType.Image:
case SqlDbType.VarBinary:
var bytes = value as byte[];
if (bytes == null)
{
throw new Exception("Value is not byte array");
}
sqlDataRecord.SetBytes(ordinal, 0, bytes, 0, bytes.Length);
break;
case SqlDbType.Int:
var integer = value as int?;
if (integer == null)
{
var ushortValue = (value as ushort?);
if (ushortValue == null)
{
throw new Exception("Value is not int or ushort");
}
integer = ushortValue.Value;
}
sqlDataRecord.SetInt32(ordinal, integer.Value);
break;
case SqlDbType.NText:
case SqlDbType.NVarChar:
case SqlDbType.VarChar:
case SqlDbType.Text:
case SqlDbType.Xml:
var str = value as string;
if (str == null)
{
var chars = value as char[];
if (chars == null)
{
throw new Exception("Value is not string or char array");
}
str = new string(chars);
}
sqlDataRecord.SetString(ordinal, str);
break;
case SqlDbType.Real:
var f = value as float?;
if (f == null)
{
throw new Exception("Value is not float");
}
sqlDataRecord.SetFloat(ordinal, f.Value);
break;
case SqlDbType.UniqueIdentifier:
var guid = value as Guid?;
if (guid == null)
{
throw new Exception("Value is not Guid");
}
sqlDataRecord.SetGuid(ordinal, guid.Value);
break;
case SqlDbType.SmallInt:
var sh = value as short?;
if (sh == null)
{
var uByte = value as sbyte?;
if (uByte == null)
{
throw new Exception("Value is not short or sbyte");
}
sh = uByte.Value;
}
sqlDataRecord.SetInt16(ordinal, sh.Value);
break;
case SqlDbType.TinyInt:
var b = value as byte?;
if (b == null)
{
throw new Exception("Value is not byte");
}
sqlDataRecord.SetByte(ordinal, b.Value);
break;
case SqlDbType.Time:
var timeSpan = value as TimeSpan?;
if (timeSpan == null)
{
throw new Exception("Value is not TimeSpan");
}
sqlDataRecord.SetTimeSpan(ordinal, timeSpan.Value);
break;
case SqlDbType.DateTimeOffset:
var dateTimeOffset = value as DateTimeOffset?;
if (dateTimeOffset == null)
{
throw new Exception("Value is not DateTimeOffset");
}
sqlDataRecord.SetDateTimeOffset(ordinal, dateTimeOffset.Value);
break;
case SqlDbType.Structured:
case SqlDbType.Udt:
case SqlDbType.Timestamp:
case SqlDbType.Variant:
throw new NotImplementedException();
default:
throw new ArgumentOutOfRangeException();
}
}
public static void IR_SM_Province_Clients_PerDist_PerQuarter(string productIds, DateTime startDate, DateTime endDate, string provinceIds)
{
using (SqlConnection con = new SqlConnection("context connection=true"))
{
SqlPipe pipe = SqlContext.Pipe;
List<SqlCommand> commands = new List<SqlCommand>();
List<string> prIds = new List<string>();
string[] split2 = productIds.Split('-');
foreach (string s in split2)
{
if (s.ToLower().Trim() != "")
prIds.Add(s.Trim().ToLower());
}
List<string> provIds = new List<string>();
string[] split = provinceIds.Split('-');
foreach (string s in split)
{
if (s.ToLower().Trim() != "")
provIds.Add(s.Trim().ToLower());
}
foreach (string provId in provIds)
{
foreach (string productId in prIds)
{
commands.Add(new SqlCommand("SELECT distinct DATEPART(MONTH,[CheckGDate]) AS MonthT, DATEPART(YEAR,[CheckGDate]) AS YearT, [CD].[Sales].[DSSales].[DSID], [DistNameEnglish] , [Global].[Distributors].DistID FROM [CD].[Sales].[DSSales] inner join [CD].[Global].[Distributors] on [CD].[Global].[Distributors].[DistID] = [CD].[Sales].[DSSales].[DistID] inner join [CD].[Sales].[DS] on [CD].[Sales].[DS].[DSID] = [CD].[Sales].[DSSales].[DSID] inner join [CD].[Global].[City] on [CD].[Global].[City].[CityID] = [CD].[Sales].[DS].[DSCityID] where [CD].[Sales].[DSSales].[CheckGDate] between @startdate and @enddate and [CD].[Sales].[DSSales].[ProductID] = @productid and [CD].[Global].[City].ProvinceID = @provinceid"));
commands[commands.Count - 1].Parameters.AddWithValue("@productid", productId);
commands[commands.Count - 1].Parameters.AddWithValue("@startdate", startDate);
commands[commands.Count - 1].Parameters.AddWithValue("@enddate", endDate);
commands[commands.Count - 1].Parameters.AddWithValue("@provinceid", provId);
}
}
SqlDataRecord record = new SqlDataRecord(new SqlMetaData("Quarter", SqlDbType.Int),
new SqlMetaData("YearT", SqlDbType.Int),
new SqlMetaData("DISTRIBUTORNAME", SqlDbType.NVarChar, 50),
new SqlMetaData("DistID", SqlDbType.Int),
new SqlMetaData("NUMOFCLIENTS", SqlDbType.Int));
Dictionary<string, Dictionary<string, List<int>>> distClientsPerQuarter = new Dictionary<string, Dictionary<string, List<int>>>();
Dictionary<string, int> distNameId = new Dictionary<string, int>();
pipe.SendResultsStart(record);
foreach (SqlCommand cmd in commands)
{
try
{
cmd.Connection = con;
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
try
{
int month = Convert.ToInt32(dr["montht"]);
int year = Convert.ToInt32(dr["yeart"]);
string quarterId = Utils.GetQuarter(month) + "_" + year;
string distributorName = Convert.ToString(dr["DistNameEnglish"]);
int distid = Convert.ToInt32(dr["DistID"]);
int dsid = Convert.ToInt32(dr["DSID"]);
if (distClientsPerQuarter.ContainsKey(distributorName))
{
if (distClientsPerQuarter[distributorName].ContainsKey(quarterId))
{
if (!distClientsPerQuarter[distributorName][quarterId].Contains(dsid))
distClientsPerQuarter[distributorName][quarterId].Add(dsid);
}
else
{
distClientsPerQuarter[distributorName].Add(quarterId, new List<int>() { dsid });
}
}
else
{
distClientsPerQuarter.Add(distributorName, new Dictionary<string, List<int>>());
distClientsPerQuarter[distributorName].Add(quarterId, new List<int>() { dsid });
distNameId.Add(distributorName, distid);
}
}
catch (Exception ex)
{
ex.ToString();
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con != null)
con.Close();
}
}
foreach (string dn in distClientsPerQuarter.Keys)
{
foreach (string q in distClientsPerQuarter[dn].Keys)
{
record.SetInt32(0, Utils.GetQuarter(q));
record.SetInt32(1, Utils.GetYear(q));
record.SetString(2, dn);
record.SetInt32(3, distNameId[dn]);
record.SetInt32(4, distClientsPerQuarter[dn][q].Count);
pipe.SendResultsRow(record);
}
}
pipe.SendResultsEnd();
}
}
public static void ListQueuesProc(
SqlString accountName, SqlString sharedKey, SqlBoolean useHTTPS,
SqlString prefix,
SqlBoolean IncludeMetadata,
SqlInt32 timeoutSeconds,
SqlGuid xmsclientrequestId)
{
ITPCfSQL.Azure.AzureQueueService aqs = new AzureQueueService(accountName.Value, sharedKey.Value, useHTTPS.Value);
//SqlContext.Pipe.Send("Created " + aqs);
List<ITPCfSQL.Azure.Queue> lQueues = aqs.ListQueues(
prefix != null ? prefix.Value : null,
IncludeMetadata.Value,
timeoutSeconds.Value,
xmsclientrequestId.IsNull ? (Guid?)null : xmsclientrequestId.Value);
for (int i = 0; i < lQueues.Count; i++)
{
SqlDataRecord record = new SqlDataRecord(new SqlMetaData[] {
new SqlMetaData("Name", System.Data.SqlDbType.NVarChar, 4000),
new SqlMetaData("Url", System.Data.SqlDbType.NVarChar, 4000),
new SqlMetaData("Metadata", System.Data.SqlDbType.Xml)
});
record.SetString(0, lQueues[i].Name);
record.SetString(1, lQueues[i].Url.ToString());
if (IncludeMetadata)
{
System.IO.MemoryStream ms = new System.IO.MemoryStream();
using (System.Xml.XmlWriter wr = System.Xml.XmlWriter.Create(ms))
{
wr.WriteStartElement("MetadataList");
foreach (string s in lQueues[i].Metadata.Keys)
{
wr.WriteStartElement(s);
wr.WriteString(lQueues[i].Metadata[s]);
wr.WriteEndElement();
}
wr.WriteEndElement();
wr.Flush();
wr.Close();
}
ms.Seek(0, System.IO.SeekOrigin.Begin);
record.SetSqlXml(2, new SqlXml(ms));
}
if (i == 0)
SqlContext.Pipe.SendResultsStart(record);
SqlContext.Pipe.SendResultsRow(record);
if ((i + 1) >= lQueues.Count)
SqlContext.Pipe.SendResultsEnd();
}
}
