// internal DataTable GetModelListByPart(String partNo) // { // StringBuilder sql = new StringBuilder(); // sql.Append(" Select model_name "); // sql.Append(" From model "); // SqlCommand sqlCmd = new SqlCommand(sql.ToString(),this.sqlConn); // //sqlCmd.Parameters.AddWithValue // DataTable _dt = null; // using (SqlDataReader dr = sqlCmd.ExecuteReader()) // { // _dt = new DataTable(); // _dt.Load(dr); // } // return _dt; //} public static DataTable getMainCategoryList(string ModelName) { var _sql = new StringBuilder(); var sql = new StringBuilder(); var DT = new DataTable(); _sql.AppendLine("SELECT model_name,Category_id,isnull(MainCategory,'') as MainCategory FROM [Category_Model] "); _sql.AppendLine("where model_name='" + ModelName + "'"); DataTable _dt = SqlProvider.dbGetDataTable("PIS", _sql.ToString()); DT.TableName = "Main Categories List"; DT.Columns.Add("Path"); DT.Columns.Add("CategoryID"); DT.Columns.Add("Main_Category"); if (_dt.Rows.Count > 0) { foreach (DataRow _row in _dt.Rows) { string _Path = ""; DataTable dtPath = null; string _Category = _row[1].ToString(); string _CategoryID = ""; Boolean _MainCategory; if (_row[2].ToString() == "Y") { _MainCategory = true; } else { _MainCategory = false; } for (var i = 0; i <= 6; i++) { if (i == 0) { _CategoryID = _Category; } sql.Clear(); sql.AppendLine(" SELECT isnull([PARENT_CATEGORY_ID],'') as PARENT_CATEGORY_ID,isnull([DISPLAY_NAME],'') as DISPLAY_NAME "); sql.AppendLine(" FROM [CATEGORY] With(nolock) "); sql.AppendLine(" where CATEGORY_ID='" + _CategoryID + "' "); dtPath = SqlProvider.dbGetDataTable("PIS", sql.ToString()); if (dtPath.Rows.Count != 0) { if (dtPath.Rows[0][0].ToString() == "root") { _Path = dtPath.Rows[0][1].ToString() + " / " + _Path; //抓取Display name break; //已經到root後,抓取完成就離開 } else { if (string.IsNullOrEmpty(_Path)) { _Path = dtPath.Rows[0][1].ToString();//抓取Display name } else { _Path = dtPath.Rows[0][1].ToString() + " / " + _Path; //抓取Display name } _CategoryID = dtPath.Rows[0][0].ToString(); //抓取Parent categoryID } } else { break; } } if (!string.IsNullOrEmpty(_Path)) { DataRow newRow_DT = DT.NewRow(); newRow_DT["Path"] = _Path; newRow_DT["CategoryID"] = _Category; newRow_DT["Main_Category"] = _MainCategory; DT.Rows.Add(newRow_DT); } } } return(DT); }
/// <summary> /// /// </summary> /// <param name="sales_code"></param> /// <returns></returns> public static DataTable GetSaleseManager(string sales_code) { return(SqlProvider.dbGetDataTable("MY", string.Format(" select id_sap as sales_code,id_rbu,id_email,id_sector from EAI_IDMAP where id_sap = '{0}' and id_fact_zone='North America' and id_sector like '%AOnline%' ", sales_code))); }
/// <summary> /// Get Siebel Sales Position by email /// </summary> /// <param name="emial"></param> /// <returns>POSITION</returns> public static object GetSiebelSalesPositionByEmail(string emial) { //return SqlProvider.dbExecuteScalar("MY", string.Format(" select top 1 POSITION from SIEBEL_SALES_HIERARCHY where EMAIL = '{0}' ", emial)); //ICC 2015/11/11 SIEBEL_SALES_HIERARCHY data is not accurate, so change to SIEBEL_POSITION for instead. return(SqlProvider.dbExecuteScalar("MY", string.Format(" select top 1 PRIMARY_POSITION_NAME from SIEBEL_POSITION where EMAIL_ADDR = '{0}' order by CREATED desc", emial))); }
public static DataTable getGP_ParameterByMargin(string RBU, decimal margin) { DataTable dt = SqlProvider.dbGetDataTable("EQ", string.Format("SELECT [RBU] ,isnull(GP_Percent,0) as [GP_Percent] ,isnull(Approver,'') as [Approver] FROM [dbo].[GP_Parameters] WHERE [RBU]='{0}' and GP_Percent>={1} Order by GP_Percent Desc, SEQ", RBU, margin)); return(dt); }