public int Executar(String sql, Object parametros)
{
using (DbAccessHelper conexao = ObterConexao())
{
return(conexao.Execute(Formatar(sql, parametros), parametros));
}
}
protected void btnSearch_Click(object sender, ImageClickEventArgs e)
{
DbAccessHelper database = new DbAccessHelper("InnoluxDB");
this.lsbAvaliableDefCode.DataSource = database.ExecuteDataSet(string.Format("SELECT DEFECT_CODE, DEFECT_CAT || ':' || DEFECT_CODE AS DEF_GROUP FROM {0} WHERE DEFECT_CODE LIKE '{1}%'", this.DBTableName, this.txtSearch.Text));
this.lsbAvaliableDefCode.DataTextField = "DEFECT_CODE";
this.lsbAvaliableDefCode.DataValueField = "DEF_GROUP";
this.lsbAvaliableDefCode.DataBind();
}
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
DbAccessHelper database = new DbAccessHelper("InnoluxDB");
string shiftDate = database.ExecuteScalar("SELECT T.SHIFT_DATE FROM SHIFT_DATE T WHERE SYSDATE BETWEEN T.CALSTARTDATE AND T.CALENDDATE").ToString();
string year = shiftDate.Substring(0, 4);
string month = shiftDate.Substring(4, 2);
string day = shiftDate.Substring(6, 2);
this.EndDatePicker.SelectedDate = Convert.ToDateTime(year + "-" + month + "-" + day);
this.StartDatePicker.SelectedDate = Convert.ToDateTime(year + "-" + month + "-1").AddDays(-3);
}
}
private void DataBindWeekDdl(DropDownList ddl, string year, bool startOrEnd)
{
DbAccessHelper database = new DbAccessHelper("InnoluxDB");
string fmSql = @"SELECT TO_CHAR(T.TD_WEEK, '00') as TD_WEEK, MIN(T.SHIFT_DATE) START_DATE, MAX(T.SHIFT_DATE) END_DATE
FROM SHIFT_DATE T
WHERE T.YEAR = {0}
AND T.TD_WEEK IS NOT NULL
GROUP BY T.TD_WEEK
ORDER BY TO_CHAR(T.TD_WEEK, '00')" ;
ddl.DataTextField = "TD_WEEK";
ddl.DataValueField = startOrEnd ? "START_DATE" : "END_DATE";
ddl.DataSource = database.ExecuteDataSet(string.Format(fmSql, year)).Tables[0];
ddl.DataBind();
}
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
DbAccessHelper database = new DbAccessHelper("InnoluxDB");
this.lsbDefGroup.DataSource =
database.ExecuteDataSet(string.Format("SELECT DISTINCT DEFECT_CAT FROM {0} WHERE DEFECT_CAT IS NOT NULL", this.DBTableName));
this.lsbDefGroup.DataTextField = "DEFECT_CAT";
this.lsbDefGroup.DataValueField = "DEFECT_CAT";
this.lsbDefGroup.DataBind();
this.RequiredFieldValidator1.ValidationGroup = this.ValidationGroup;
this.btnFilter.Enabled = this.lsbDefGroup.Items.Count > 0;
}
}
private void DatabindDefaultValue()
{
#region SQL
string sql = @" SELECT MIN(X.SHIFT_DATE) AS START_DT,
MAX(Y.MIN_YR) AS START_YR,
MAX(Y.MIN_WK) AS START_WK,
MAX(X.SHIFT_DATE) AS END_DT,
MAX(Y.MAX_YR) AS END_YR,
MAX(Y.MAX_WK) AS END_WK,
MIN(Z.MIN_YR) AS MIN_YR,
MAX(Z.MAX_YR) AS MAX_YR
FROM SHIFT_DATE X,
(SELECT MIN(YEAR) AS MIN_YR,
MAX(TD_WEEK) - 2 AS MIN_WK,
MAX(YEAR) AS MAX_YR,
SUBSTR(MAX(YEAR || TD_WEEK)-1, 5, 2) AS MAX_WK
FROM SHIFT_DATE T
WHERE SYSDATE BETWEEN T.CALSTARTDATE AND ADD_MONTHS(T.CALENDDATE, 12)
ORDER BY SHIFT_DATE) Y,
(SELECT MAX(YEAR) AS MAX_YR, MIN(YEAR) AS MIN_YR FROM SHIFT_DATE) Z
WHERE X.YEAR || X.TD_WEEK = Y.MIN_YR || Y.MIN_WK
OR X.YEAR || X.TD_WEEK = Y.MAX_YR || Y.MAX_WK" ;
#endregion
DbAccessHelper database = new DbAccessHelper("InnoluxDB");
using (IDataReader reader = database.ExecuteReader(sql))
{
if (reader.Read())
{
int minYR = Utils.ConvertObject <int>(reader["MIN_YR"]);
int maxYR = Utils.ConvertObject <int>(reader["MAX_YR"]);
this.DataBindDdlYear(minYR, maxYR);
this.ddlStartYear.Text = Utils.ConvertObject <string>(reader["START_YR"]);
this.DataBindWeekDdl(this.ddlStartWeek, this.ddlStartYear.Text, true);
this.ddlEndYear.Text = Utils.ConvertObject <string>(reader["END_YR"]);
this.DataBindWeekDdl(this.ddlEndWeek, this.ddlEndYear.Text, false);
this.ddlStartWeek.Text = Utils.ConvertObject <string>(reader["START_DT"]);
this.ddlEndWeek.Text = Utils.ConvertObject <string>(reader["END_DT"]);
}
}
}
protected void RadTreeView1_NodeExpand(object sender, RadTreeNodeEventArgs e)
{
string sql = "";
switch (e.Node.Level)
{
case 0:
sql = "SELECT distinct PROD_MODEL as text FROM " + this.ProductTable +
" WHERE PROD_MODEL IS NOT NULL AND PROD_SIZE =" + e.Node.Value +
" and SHOP='" + this.ShopName + "'";
break;
case 1:
sql = "SELECT PROD_NAME as text FROM " + this.ProductTable +
" WHERE ACTIVE_FLAG='1' AND PROD_SIZE=" + e.Node.ParentNode.Value +
" AND PROD_MODEL=" + e.Node.Value +
" and SHOP='" + this.ShopName + "'";
break;
case 2:
sql = "SELECT prod_name as text FROM " + this.ProductTable +
" WHERE prod_name = '{0}' UNION SELECT SUBPROD_NAME AS prod_name FROM " + this.SubProductTable +
" T WHERE T.PROD_NAME = " + e.Node.Value;
break;
default:
return;
}
DbAccessHelper dbHelper = new DbAccessHelper(this.DBConnectionName);
foreach (DataRow row in dbHelper.ExecuteDataSet(sql).Tables[0].Rows)
{
RadTreeNode node = new RadTreeNode();
node.Text = row["text"].ToString();
node.Value = "'" + row["text"].ToString() + "'";
if (e.Node.Level < 2)
{
node.ExpandMode = TreeNodeExpandMode.ServerSideCallBack;
}
e.Node.Nodes.Add(node);
}
e.Node.Expanded = true;
}
private void SetDefaultValue()
{
string sql = @"
with X as (select max(to_number(year)) as max_yr, min(to_number(year)) as min_yr from shift_date ),
Y as
( select max(to_number(td_week)) - 2 as start_wk, year as start_yr, max(to_number(td_week)) as start_max_wk
from shift_date
where (year + 1) = to_char(sysdate, 'yyyy')
group by year
),
Z as
( select year as end_yr, td_week as end_wk, shift_date as end_dt, m.end_max_wk as end_max_wk
from shift_date,
( select max(to_number(td_week)) as end_max_wk
from shift_date
where year = to_char(sysdate, 'yyyy') ) m
where sysdate between calstartdate and calenddate
)
select Y.start_yr, Y.start_wk, Y.start_max_wk, Z.end_dt, Z.end_yr, Z.end_max_wk, Z.end_wk, X.max_yr, X.min_yr
from X, Y, Z
";
DbAccessHelper database = new DbAccessHelper("InnoluxDB");
DataTable dt = database.ExecuteDataSet(sql).Tables[0];
if (dt == null || dt.Rows.Count < 1)
{
return;
}
int minYR = Utils.ConvertObject <int>(dt.Rows[0]["MIN_YR"]);
int maxYR = Utils.ConvertObject <int>(dt.Rows[0]["MAX_YR"]);
int startMaxWeek = Utils.ConvertObject <int>(dt.Rows[0]["START_MAX_WK"]);
int endMaxWeek = Utils.ConvertObject <int>(dt.Rows[0]["END_MAX_WK"]);
int endDefaultWeek = Utils.ConvertObject <int>(dt.Rows[0]["END_WK"]);
this.BindToComobox(this.ddlStartYear, minYR, maxYR, DateTime.Now.Year - 1);
this.BindToComobox(this.ddlEndYear, minYR, maxYR, DateTime.Now.Year);
this.BindToComobox(this.ddlStartWeek, 1, startMaxWeek, startMaxWeek - 2);
this.BindToComobox(this.ddlEndWeek, 1, endMaxWeek, endDefaultWeek);
}
protected void btnFilter_Click(object sender, EventArgs e)
{
string temp = "";
foreach (ListItem item in this.lsbDefGroup.Items)
{
if (item.Selected == true)
{
temp += ("'" + item.Value + "',");
}
}
if (!string.IsNullOrEmpty(temp))
{
DbAccessHelper database = new DbAccessHelper("InnoluxDB");
temp = temp.Substring(0, temp.Length - 1);
this.lsbAvaliableDefCode.DataSource = database.ExecuteDataSet(string.Format("SELECT DEFECT_CODE, DEFECT_CAT || ':' || DEFECT_CODE AS DEF_GROUP FROM {0} WHERE DEFECT_CAT IN ({1})", this.DBTableName, temp));
this.lsbAvaliableDefCode.DataTextField = "DEFECT_CODE";
this.lsbAvaliableDefCode.DataValueField = "DEF_GROUP";
this.lsbAvaliableDefCode.DataBind();
}
}
private void DataBindAndSetDefault()
{
// Bind Year dropdownlist
DbAccessHelper database = new DbAccessHelper("InnoluxDB");
DataTable source = database.ExecuteDataSet("SELECT DISTINCT YEAR FROM SHIFT_DATE ORDER BY YEAR").Tables[0];
this.ddlStartYear.DataTextField = "YEAR";
this.ddlStartYear.DataValueField = "YEAR";
this.ddlEndYear.DataTextField = "YEAR";
this.ddlEndYear.DataValueField = "YEAR";
this.ddlEndYear.DataSource = this.ddlStartYear.DataSource = source;
this.ddlStartYear.DataBind();
this.ddlEndYear.DataBind();
// Set default value
DateTime current = DateTime.Now;
this.ddlStartYear.Text = current.AddYears(-1).Year.ToString();
this.ddlStartMonth.Text = "10";
this.ddlEndYear.Text = current.Year.ToString();
this.ddlEndMonth.Text = current.AddMonths(-1).Month.ToString();
}
private DataTable GetDataSource()
{
DbAccessHelper dbHelper = new DbAccessHelper(this.DBConnectionName);
string sql = @"select prod_size, prod_model, t.prod_name, s.subprod_name
from " + this.ProductTable + @" t, " + this.SubProductTable + @" s
where t.fab_id ='Fab2' and t.prod_size is not null and t.shop='" + this.ShopName + @"'
and t.prod_name = s.prod_name
and T.active_flag = 1" +
@"union
select prod_size, prod_model, t.prod_name, t.prod_name as subprod_name
from " + this.ProductTable + @" t
where t.fab_id ='Fab2' and t.prod_size is not null and t.shop='" + this.ShopName + @"'
and T.active_flag = 1 " +
@"ORDER BY prod_size, prod_model, prod_name";
try
{
return(dbHelper.ExecuteDataSet(sql).Tables[0]);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
private static void PrintRows(DataTableReader reader)
{
try
{
string connStr = "Data Source=(local);Initial Catalog=mydb;integrated security=sspi;Min Pool Size=10";
DbAccessHelper db = new DbAccessHelper(connStr);
EventWaitHandle clearCount = new EventWaitHandle(false, EventResetMode.ManualReset);
int count = 0;
int maxCount = 2;
for (int i = 0; i < maxCount; i++)
{
string sql = string.Format("insert into [AOH_SeckillingWinner] (userid,Category) "
+ " select ROW_NUMBER() over (order by newid()) ,'{0}' from (select 1 as r from sys.objects as a "
+ " cross join sys.objects as b) as c", i);
Thread t = new Thread((p) =>
{
// SqlConnection conn = db.GetOpenConnection();
Interlocked.Increment(ref count);
clearCount.WaitOne();
//db.ExcuteNoQuery(conn, p.ToString());
Console.WriteLine("finish.");
//conn.Close();
Interlocked.
Decrement(ref count);
});
t.Start(sql);
}
while (count < maxCount)
{
Thread.Sleep(500);
}
clearCount.Set();
//Console.WriteLine();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
public override object ExecuteScalar(IDbConnection connection, string cmdText, IList <IDbDataParameter> parameter)
{
return(DbAccessHelper.ExecuteScalar((OleDbConnection)connection, CommandType.Text, cmdText, parameter == null ? null : parameter.Cast <OleDbParameter>().ToArray()));
}
public override int ExecuteNonQuery(IDbTransaction trans, string cmdText, IList <IDbDataParameter> parameter)
{
return(DbAccessHelper.ExecuteNonQuery(trans as OleDbTransaction, CommandType.Text, cmdText, parameter == null ? null : parameter.Cast <OleDbParameter>().ToArray()).ToInt());
}