public static void SetDoubleRecord(SqlDataReader dr, string columnName, SqlDataRecord record, int ordinal)
{
if (dr[columnName] == DBNull.Value)
record.SetDBNull(ordinal);
else
record.SetDouble(ordinal, Convert.ToDouble(dr[columnName]));
}
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 SqlDataRecord CreateFractalRecord(double? value)
{
var record = new SqlDataRecord(new SqlMetaData("Value", SqlDbType.Float));
if (value.HasValue)
record.SetDouble(0, value.Value);
else
record.SetDBNull(0);
return record;
}
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 IR_SM_TotalSalesIn_PerMonth(string brandCodes, int startMonth, int startYear, int endMonth, int endYear)
{
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());
}
for (int year = (int)smallyear; year <= bigyear; year++)
{
foreach (string brandCode in brdcods)
{
commands.Add(new SqlCommand("SELECT [MONTH] AS MonthT, [YEAR] AS YearT, [SalesIN_ACT] AS VAL FROM [IR_SalesIN_Brand] WHERE (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);
}
}
SqlDataRecord record = new SqlDataRecord(new SqlMetaData("MonthT", SqlDbType.Int),
new SqlMetaData("YearT", SqlDbType.Int),
new SqlMetaData("VAL", SqlDbType.Float));
pipe.SendResultsStart(record);
foreach (SqlCommand cmd in commands)
{
try
{
cmd.Connection = con;
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
int month = Convert.ToInt32(dr["montht"]);
int year = Convert.ToInt32(dr["yeart"]);
double val = Convert.ToDouble(((dr["val"] == DBNull.Value) ? 0 : dr["val"]));
record.SetInt32(0, month);
record.SetInt32(1, year);
record.SetDouble(2, val);
pipe.SendResultsRow(record);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con != null)
con.Close();
}
}
pipe.SendResultsEnd();
}
catch (Exception ex)
{
throw ex;
}
}
}
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_TotalObjective_PerMonth(string brandCodes, int startMonth, int startYear, int endMonth, int endYear, string saCodes)
{
using (SqlConnection con = new SqlConnection("context connection=true"))
{
SqlPipe pipe = SqlContext.Pipe;
List<SqlCommand> commands = new List<SqlCommand>();
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> sacods = new List<string>();
string[] split2 = saCodes.Split('-');
foreach (string s in split2)
if (s.ToLower().Trim() != "")
sacods.Add(s.Trim().ToLower());
if (sacods.Count == 0)
sacods.Add("");
foreach (string brandCode in brdcods)
{
for (int year = (int)smallyear; year <= bigyear; year++)
{
foreach (string saCode in sacods)
{
commands.Add(new SqlCommand("SELECT SALESBUDGET.BUDGETTYPECODE AS TypeT, SALESBUDGET.YEAR AS YearT, SALESBUDGET.MONTH AS MonthT, SALESBUDGET.QTY, SALESBUDGET.VALUE AS VAL, SAPRODUCTS.SAPRODUCTCODE FROM SALESBUDGET INNER JOIN SAPRODUCTS ON SAPRODUCTS.SAPRODUCTCODE = SALESBUDGET.SAPRODUCTCODE WHERE (SAPRODUCTS.BRANDCODE = @brandcode) AND (SALESBUDGET.YEAR = @year) AND (SALESBUDGET.MONTH BETWEEN @startmonth AND @endmonth) AND (SAPRODUCTS.SAPRODUCTCODE LIKE '%' + @sacode + '%')"));
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("@sacode", saCode);
}
}
}
SqlDataRecord record = new SqlDataRecord(new SqlMetaData("QTY", SqlDbType.Float),
new SqlMetaData("MonthT", SqlDbType.Int),
new SqlMetaData("YearT", SqlDbType.Int),
new SqlMetaData("TypeT", SqlDbType.NVarChar, 50),
new SqlMetaData("SAPRODUCTCODE", SqlDbType.NVarChar, 20),
new SqlMetaData("VAL", SqlDbType.Float));
pipe.SendResultsStart(record);
foreach (SqlCommand cmd in commands)
{
try
{
cmd.Connection = con;
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
if (dr["montht"] == DBNull.Value || dr["yeart"] == DBNull.Value)
continue;
int month = Convert.ToInt32(dr["montht"]);
int year = Convert.ToInt32(dr["yeart"]);
double qty = Convert.ToDouble((dr["qty"] == DBNull.Value) ? 0 : dr["qty"]);
if (dr["typet"] == DBNull.Value)
continue;
string type = Convert.ToString(dr["typet"]);
if (dr["SAPRODUCTCODE"] == DBNull.Value)
continue;
string saproductcode = Convert.ToString(dr["SAPRODUCTCODE"]);
double val = Convert.ToDouble((dr["val"] == DBNull.Value) ? 0 : dr["val"]);
record.SetDouble(0, qty);
record.SetInt32(1, month);
record.SetInt32(2, year);
record.SetString(3, type);
record.SetString(4, saproductcode);
record.SetDouble(5, val);
pipe.SendResultsRow(record);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con != null)
con.Close();
}
}
pipe.SendResultsEnd();
}
}
public static void IR_SM_TotalActual_PerMonth(string brandCodes, int startMonth, int startYear, int endMonth, int endYear, string saCodes)
{
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> sacods = new List<string>();
string[] split2 = saCodes.Split('-');
foreach (string s in split2)
if (s.ToLower().Trim() != "")
sacods.Add(s.Trim().ToLower());
if (sacods.Count == 0)
sacods.Add("");
for (int year = (int)smallyear; year <= bigyear; year++)
{
foreach (string brandCode in brdcods)
{
foreach (string saCode in sacods)
{
commands.Add(new SqlCommand("SELECT SUM(IR_Total_Country.SaleoutQTY) AS QTY, IR_Total_Country.RelatedMonth AS MonthT, IR_Total_Country.RelatedYear AS YearT, SAPRODUCTS.SAPRODUCTCODE, IR_Total_Country.OutValue AS VAL FROM IR_Total_Country INNER JOIN SAPRODUCTS ON SAPRODUCTS.SAPRODUCTCODE = IR_Total_Country.SAProductCode WHERE (SAPRODUCTS.BRANDCODE LIKE '%' + @brandcode + '%') AND (IR_Total_Country.RelatedYear = @year) AND (IR_Total_Country.RelatedMonth BETWEEN @startmonth AND @endmonth) AND (SAPRODUCTS.SAPRODUCTCODE LIKE '%' + @sacode + '%') GROUP BY IR_Total_Country.RelatedMonth, IR_Total_Country.RelatedYear, SAPRODUCTS.SAPRODUCTCODE, IR_Total_Country.OutValue"));
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("@sacode", saCode);
}
}
}
SqlDataRecord record = new SqlDataRecord(new SqlMetaData("QTY", SqlDbType.Float),
new SqlMetaData("MonthT", SqlDbType.Int),
new SqlMetaData("YearT", SqlDbType.Int),
new SqlMetaData("SAPRODUCTCODE", SqlDbType.NVarChar,20),
new SqlMetaData("VAL", SqlDbType.Float));
pipe.SendResultsStart(record);
foreach (SqlCommand cmd in commands)
{
try
{
cmd.Connection = con;
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
double qty = Convert.ToDouble(dr["qty"]);
int month = Convert.ToInt32(dr["montht"]);
int year = Convert.ToInt32(dr["yeart"]);
string saproductcode = Convert.ToString(dr["SAPRODUCTCODE"]);
double val = Convert.ToDouble(dr["val"]);
record.SetDouble(0, qty);
record.SetInt32(1, month);
record.SetInt32(2, year);
record.SetString(3, saproductcode);
record.SetDouble(4, val);
pipe.SendResultsRow(record);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con != null)
con.Close();
}
}
pipe.SendResultsEnd();
}
catch (Exception ex)
{
throw ex;
}
}
}
