public static DropDownItem[] GetReasonCodes(string connectString, string providerName, string reasonType)
{
const string strQuery = @"
SELECT reason.reason_code AS reason_code,
reason.description AS description
FROM tab_reason reason WHERE 1 = 1 <if c=""$reasonType = 'Pickslip'""> AND reason.pickslip_cancel_flag = 'Y'</if>";
using (OracleDataSource ds = new OracleDataSource())
{
ds.ConnectionString = connectString;
ds.SelectSql = strQuery;
ds.ProviderName = providerName;
ds.SysContext.ModuleName = "Retrieving reason codes";
ds.SelectParameters.Add("reasonType", reasonType);
ds.SelectSql = strQuery;
//ds.CancelSelectOnValidationFailure = false;
return((from object row in ds.Select(DataSourceSelectArguments.Empty)
select new DropDownItem()
{
Text = string.Format("{0}: {1}", DataBinder.Eval(row, "reason_code"),
DataBinder.Eval(row, "description")),
Value = DataBinder.Eval(row, "reason_code", "{0}"),
}).ToArray());
}
}
/// <summary>
/// If you are calling from a webmethod then pass storesWhat = CTN for getting carton area, SKU for getting SKU areas.
/// If you want to select all areas pass empty string.
/// Pass usableInventory as "true" if you want to select only usable inventory areas otherwise pass empty.
/// </summary>
/// <param name="connectString"></param>
/// <param name="providerName"></param>
/// <param name="storesWhat"></param>
/// <param name="usableInventory"></param>
/// <returns></returns>
public static DropDownItem[] GetAreas(string connectString, string providerName, string storesWhat, string usableInventory)
{
string strQuery = @"
select inventory_storage_area,
short_name || ':' || description as description,
stores_what
from tab_inventory_area
where 1 = 1
<if>AND stores_what = :stores_what</if>
<if c='$unusable_inventory'>AND unusable_inventory IS NULL</if>
order by short_name,warehouse_location_id
";
using (OracleDataSource ds = new OracleDataSource())
{
ds.ConnectionString = connectString;
ds.SelectSql = strQuery;
ds.ProviderName = providerName;
ds.SysContext.ModuleName = "Selecting Areas";
//ds.CancelSelectOnValidationFailure = false;
ds.SelectParameters.Add("stores_what", DbType.String, storesWhat);
ds.SelectParameters.Add("unusable_inventory", DbType.String, usableInventory);
var query = from object row in ds.Select(DataSourceSelectArguments.Empty)
select new DropDownItem()
{
Text = string.Format("{0}", DataBinder.Eval(row, "description")),
Value = DataBinder.Eval(row, "inventory_storage_area", "{0}"),
OptionGroup = DataBinder.Eval(row, "stores_what", "{0}")
};
return(query.ToArray());
}
}
public static DropDownItem[] GetLabels(string connectString, string providerName, bool labelGroup)
{
string QUERY;
if (labelGroup)
{
QUERY = @"
select tsl.label_id as label_id,
MAX(twl.warehouse_location_id) as max_warehouse_location_id,
MAX(twl.description) as building_description,
COUNT(DISTINCT twl.warehouse_location_id) as count_warehouse_location_id,
MAX(tsl.description) as label_description
from <proxy />tab_style_label tsl
left outer join <proxy />tab_label_group tlg on tsl.label_id = tlg.label_id
left outer join <proxy />tab_warehouse_location twl on tlg.label_group =
twl.label_group
group by tsl.label_id
order by tsl.label_id
";
}
else
{
QUERY = @"select tsl.label_id as label_id,
tsl.description as label_description
from <proxy />tab_style_label tsl
order by tsl.label_id";
}
using (OracleDataSource ds = new OracleDataSource())
{
ds.ConnectionString = connectString;
ds.SelectSql = QUERY;
ds.ProviderName = providerName;
ds.SysContext.ModuleName = "Reteriving labels";
//ds.CancelSelectOnValidationFailure = false;
var query = from object row in ds.Select(DataSourceSelectArguments.Empty)
select new DropDownItem()
{
Text = string.Format("{0}: {1}", DataBinder.Eval(row, "label_id"),
DataBinder.Eval(row, "label_description")),
Value = DataBinder.Eval(row, "label_id", "{0}")
};
return(query.ToArray());
}
}
public static DropDownItem[] GetBuildings(string connectString, string providerName)
{
const string strQuery = "select warehouse_location_id, description AS description from <proxy/>tab_warehouse_location order by 1";
using (OracleDataSource ds = new OracleDataSource())
{
ds.ProviderName = providerName;
ds.SysContext.ModuleName = "Retrieving Buildings";
ds.ConnectionString = connectString;
ds.SelectSql = strQuery;
//ds.CancelSelectOnValidationFailure = false;
return((from object row in ds.Select(DataSourceSelectArguments.Empty)
select new DropDownItem()
{
Text = string.Format("{0}: {1}", DataBinder.Eval(row, "warehouse_location_id"),
DataBinder.Eval(row, "description")),
Value = DataBinder.Eval(row, "warehouse_location_id", "{0}"),
}).ToArray());
}
}
/// <summary>
/// Sharad 23 Aug 2011: Retrieving dates for +/- 2 years
/// </summary>
static BusinessDateTextBox()
{
__monthStartDates = new List <DateTime>(24);
const string QUERY = @"
SELECT t.month_start_date as month_start_date
FROM tab_fdc_calendar t
where t.fiscal_year <= (:curYear + 2) and t.fiscal_year >= (:curYear - 2)
ORDER BY t.month_start_date DESC
";
using (OracleDataSource ds = new OracleDataSource(ConfigurationManager.ConnectionStrings["dcmslive"]))
{
ds.SelectSql = QUERY;
ds.SelectParameters.Add("curYear", TypeCode.Int32, DateTime.Today.Year.ToString());
ds.SysContext.ModuleName = "Month Start Date";
foreach (var row in ds.Select(DataSourceSelectArguments.Empty))
{
__monthStartDates.Add(Convert.ToDateTime(DataBinder.Eval(row, "month_start_date")));
}
}
}
public static DropDownItem[] GetCustomerType(string connectString, string providerName)
{
const string QUERY = @"
select tct.customer_type, tct.description from tab_customer_type tct order by tct.description
";
using (OracleDataSource ds = new OracleDataSource())
{
ds.ConnectionString = connectString;
ds.SelectSql = QUERY;
ds.ProviderName = providerName;
ds.SysContext.ModuleName = "Customer Type";
//ds.CancelSelectOnValidationFailure = false;
return((from object row in ds.Select(DataSourceSelectArguments.Empty)
select new DropDownItem()
{
Text = string.Format("{0}: {1}", DataBinder.Eval(row, "customer_type"),
DataBinder.Eval(row, "description")),
Value = DataBinder.Eval(row, "customer_type", "{0}")
}).ToArray());
}
}
/// <summary>
/// This is static to make it possible to call from a web service
/// </summary>
/// <param name="connectString"></param>
/// <param name="providerName"></param>
/// <param name="printerType"></param>
/// <returns></returns>
public static DropDownItem[] GetPrinters(string connectString, string providerName, string printerType)
{
const string strQuery = @"
select
tabprinter.name AS name,
tabprinter.description AS description,
<if c='$printer_type'>
printer_type As printer_type
</if>
<else>
NULL as printer_type
</else>
from <proxy />tab_printer tabprinter
where 1 = 1
<if c='$printer_type'>
AND upper(printer_type) = upper(:printer_type)
</if>
order by lower(name)";
using (OracleDataSource ds = new OracleDataSource())
{
ds.ProviderName = providerName;
ds.ConnectionString = connectString;
ds.SelectSql = strQuery;
ds.SysContext.ModuleName = "Retrieving printers";
//ds.CancelSelectOnValidationFailure = false;
ds.SelectParameters.Add("printer_type", DbType.String, printerType);
return((from object row in ds.Select(DataSourceSelectArguments.Empty)
select new DropDownItem()
{
Text = string.Format("{0}: {1}", DataBinder.Eval(row, "name"),
DataBinder.Eval(row, "description")),
Value = DataBinder.Eval(row, "name", "{0}"),
OptionGroup = DataBinder.Eval(row, "printer_type", "{0}")
}).ToArray());
}
}
/// <summary>
/// Called once to retrieve customer constraints.
/// </summary>
/// <param name="ds"></param>
/// <param name="customer_id"></param>
/// <returns>Returns true if each SKU must have the min max pieces specified</returns>
private bool RetrieveFromDataBase(string customer_id)
{
_ds.SelectParameters.Clear();
//TODO: We will include the customer constraints also in future.
_ds.SelectSql = @"select c.required_case_id as required_case_id,
c.min_pieces_per_box,
c.max_pieces_per_box from <proxy />cust c
where c.customer_id=:customer_id";
_ds.SelectParameters.Add("customer_id", DbType.String, customer_id);
var c = (from object obj in _ds.Select(DataSourceSelectArguments.Empty)
select obj).FirstOrDefault();
string requiredCaseId = DataBinder.Eval(c, "required_case_id", "{0}");
_ds.SelectSql = @"
with const as
(select s.splh_id as splh_id,
s.splh_value as splh_value
from <proxy />splh s
WHERE s.splh_id IN ('$SSB', '$PSMINMAX','$BOXMAXWT','$BOXMINWT','$CSEMINVOL','$CSEMAXVOL', '$MAXSKUPB' )
),
cust_const as
(select c.splh_id, c.splh_value
from <proxy />custsplh c
where c.customer_id = :customer_id
and c.splh_id IN ('$SSB', '$PSMINMAX','$BOXMAXWT','$BOXMINWT','$CSEMINVOL','$CSEMAXVOL','$MAXSKUPB')
and c.splh_value is not null)
select co.splh_id, nvl(c.splh_value,co.splh_value) splh_value
from const co
left outer join cust_const c
on co.splh_id = c.splh_id
where nvl(c.splh_value,co.splh_value) is not null
";
bool bMinMaxPiecesRequired = false;
_ds.SelectParameters.Clear();
_ds.SelectParameters.Add("customer_id", DbType.String, customer_id);
//Getting the constraint values and set in respective properties.
decimal?minVolume = null;
decimal?maxVolume = null;
foreach (var row in _ds.Select(DataSourceSelectArguments.Empty))
{
object obj;
switch (DataBinder.Eval(row, "splh_id", "{0}"))
{
//Setting the Single SKU per Box
case ("$SSB"):
switch (DataBinder.Eval(row, "splh_value", "{0}"))
{
case "Y":
case "SCDZ":
this.SkuMixing = SkuPerBox.SingleSku;
break;
case "SC":
this.SkuMixing = SkuPerBox.SingleStyleColor;
break;
default:
this.SkuMixing = SkuPerBox.NoConstraint;
break;
}
break;
//Setting the pickslip level min max setting
case ("$PSMINMAX"):
switch (DataBinder.Eval(row, "splh_value", "{0}"))
{
case "REQUIRED":
// Every SKU must have min/max defined
bMinMaxPiecesRequired = true;
this.SkuMixing = SkuPerBox.SingleSku;
break;
case "Y":
bMinMaxPiecesRequired = true;
break;
default:
break;
}
break;
//Setting Box Max Weight Constraint if defined.
case ("$BOXMAXWT"):
obj = DataBinder.Eval(row, "splh_value");
if (obj != DBNull.Value)
{
BoxMaxWeight = Convert.ToDecimal(obj);
}
break;
//setting the Box Min Weight Constraint if defined.
case ("$BOXMINWT"):
obj = DataBinder.Eval(row, "splh_value");
if (obj != DBNull.Value)
{
BoxMinWeight = Convert.ToDecimal(obj);
}
break;
//Setting the Case Min Volume Constraint is defined
case ("$CSEMINVOL"):
obj = DataBinder.Eval(row, "splh_value");
if (obj != DBNull.Value)
{
minVolume = Convert.ToDecimal(obj);
}
break;
//Setting the Case Max Volume Constraint if defined
case ("$CSEMAXVOL"):
obj = DataBinder.Eval(row, "splh_value");
if (obj != DBNull.Value)
{
maxVolume = Convert.ToDecimal(obj);
}
break;
//Setting the Maximum SKUs per Box.
case ("$MAXSKUPB"):
MaxSkuPerBox = Convert.ToInt32(DataBinder.Eval(row, "splh_value"));
break;
default:
break;
}
}
//Maximum SKUs per Box(Honored only in case when single sku per box is set as single style color)
if (SkuMixing != SkuPerBox.SingleStyleColor)
{
MaxSkuPerBox = 0;
}
//Getting the all box cases available
RetrieveBoxCases(requiredCaseId, minVolume, maxVolume);
return(bMinMaxPiecesRequired);
}
private IEnumerable <ReportStat> GetReportStats()
{
ReportStat[] statistics;
try
{
statistics = (ReportStat[])Cache["ReportStatData"];
}
catch (InvalidCastException)
{
// This happens when default.aspx page is recompiled. Just Ignore the cache
statistics = null;
}
if (statistics != null)
{
return(statistics);
}
using (OracleDataSource dsHits = new OracleDataSource())
{
dsHits.SelectSql = @"
select t.report_id as report_id,
MIN(t.query_seconds) as min_query_seconds,
MAX(t.query_seconds) as max_query_seconds,
ROUND(AVG(t.query_seconds), 4) as avg_query_seconds,
MAX(t.hit_index) as hit_count
from xreporter_report_hits t
where t.application_name = :application_name
group by t.report_id
";
dsHits.SysContext.ModuleName = "DCMS Live 2009 Home";
dsHits.ConnectionString = ConfigurationManager.ConnectionStrings["DCMSLIVE"].ConnectionString;
dsHits.ProviderName = ConfigurationManager.ConnectionStrings["DCMSLIVE"].ProviderName;
Parameter appName = new Parameter("application_name", DbType.String);
appName.DefaultValue = ProfileManager.Provider.ApplicationName;
dsHits.SelectParameters.Add(appName);
try
{
statistics =
(from object stat in dsHits.Select(DataSourceSelectArguments.Empty)
select new ReportStat(DataBinder.Eval(stat, "report_id", "{0}"))
{
MinQuerySeconds = DataBinder.Eval(stat, "min_query_seconds"),
MaxQuerySeconds = DataBinder.Eval(stat, "max_query_seconds"),
AverageQuerySeconds = DataBinder.Eval(stat, "avg_query_seconds"),
HitCount = DataBinder.Eval(stat, "hit_count")
}).ToArray();
Cache.Insert("ReportStatData", statistics, null, DateTime.UtcNow.AddHours(1),
System.Web.Caching.Cache.NoSlidingExpiration);
__statUpdateTime = DateTime.Now;
}
catch (DbException ex)
{
Trace.Warn(ex.ToString());
// Empty array
statistics = new ReportStat[0];
mvStats.ActiveViewIndex = 1;
lblError.Text = ex.Message;
}
}
return(statistics);
}
