public DataTable GETFS(int period, int oemid) { DataTable dt = new DataTable(); string oem, baan, plant; using (Multek.SqlDB db = new Multek.SqlDB(__conn)) { SqlCommand cmd = new SqlCommand(); cmd.CommandText = "[sp_gam_ForecastCustomerPart_Get]"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@period", period); cmd.Parameters.AddWithValue("@oemid", oemid); cmd.Parameters.AddWithValue("@periodLong", 3); cmd.Parameters.AddWithValue("@totalPeriod", 11); SqlParameter _oem = cmd.Parameters.AddWithValue("@oem", ""); _oem.Size = 100; SqlParameter _baan = cmd.Parameters.AddWithValue("@baanName", ""); _baan.Size = 100; SqlParameter _plant = cmd.Parameters.AddWithValue("@plant", ""); _plant.Size = 3; _plant.SqlDbType = SqlDbType.NVarChar; _oem.Direction = ParameterDirection.Output; _baan.Direction = ParameterDirection.Output; _plant.Direction = ParameterDirection.Output; DataSet ds = db.getDataSetCmd(ref cmd); oem = _oem.Value.ToString(); baan = _baan.Value.ToString(); plant = _plant.Value.ToString(); bool isCEM = false; DataTable cems = Forecast.getCEM_OEMS(); //temp.Text = cems.Rows[0][0].ToString(); foreach(DataRow row in cems.Rows) { if (row[0].ToString().Trim().ToUpper() == baan.Trim().ToUpper()) { isCEM = true; break; } } dt = modDS(ds); if (isCEM) { for(int i = dt.Rows.Count-1; i >=0; i--) { DataRow row = dt.Rows[i]; if (row["cus_part_no"].ToString().IndexOf("[") < 0) dt.Rows.Remove(row); } } cmd.Dispose(); } return dt; }
private string GETFS(int period, int oemid) { string rq = ""; string oem, baan, plant; using (Multek.SqlDB db = new Multek.SqlDB(__conn)) { SqlCommand cmd = new SqlCommand(); cmd.CommandText = "[sp_gam_ForecastCustomerPart_Get]"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@period", period); cmd.Parameters.AddWithValue("@oemid", oemid); cmd.Parameters.AddWithValue("@periodLong", 3); SqlParameter _oem = cmd.Parameters.AddWithValue("@oem", ""); _oem.Size = 100; SqlParameter _baan = cmd.Parameters.AddWithValue("@baanName", ""); _baan.Size = 100; SqlParameter _plant = cmd.Parameters.AddWithValue("@plant", ""); _plant.Size = 3; _plant.SqlDbType = SqlDbType.NVarChar; _oem.Direction = ParameterDirection.Output; _baan.Direction = ParameterDirection.Output; _plant.Direction = ParameterDirection.Output; DataSet ds = db.getDataSetCmd(ref cmd); DataTable dt = modDS(ds);// ds.Tables[0]; oem = _oem.Value.ToString(); baan = _baan.Value.ToString(); plant = _plant.Value.ToString(); cmd.Dispose(); rq = Multek.Util.DT2JSON(dt); dt.Dispose(); } return "ob = {oem:{id:\"" + oemid + "\",oem:\"" + oem + "\",baan:\"" + baan + "\",plant:\"" + plant + "\"},result:" + rq + "}"; }
private void loadData() { DataSet ds = new DataSet(); using (Multek.SqlDB sqldb = new Multek.SqlDB(__conn)) { string sql = "sp_gam_cem_forecast_view"; SqlCommand cmd = new SqlCommand(sql); cmd.CommandType = CommandType.Text; ds = sqldb.getDataSetCmd(ref cmd); cmd.Connection.Dispose(); cmd.Dispose(); } DataColumn[] OEM; DataColumn[] DATA; OEM = new DataColumn[] { ds.Tables[1].Columns["oem"], ds.Tables[1].Columns["plant"]}; DATA = new DataColumn[] { ds.Tables[0].Columns["oem"], ds.Tables[0].Columns["plant"]}; DataRelation drl = new DataRelation("myDataRelation", OEM, DATA,true); drl.Nested = true; ds.Relations.Add(drl); main.DataSource = ds.Tables[1]; main.DataBind(); }
public static DataSet getRportFCP2P(int p1, int p2, out string message) { message = ""; DataSet ds = new DataSet(); using (Multek.SqlDB db = new Multek.SqlDB(__conn)) { SqlCommand cmd = new SqlCommand("[sp_gam_CompareForecastOEM_delta]"); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@basePeriod", p1); cmd.Parameters.AddWithValue("@comparePeriod", p2); SqlParameter _msg = cmd.Parameters.AddWithValue("@message", ""); _msg.Size = 1000; _msg.Direction = ParameterDirection.Output; ds = db.getDataSetCmd(ref cmd); message = _msg.Value.ToString(); cmd.Dispose(); } return ds; }
public static DataTable getAdjustFC(int list_by) { DataSet ds = new DataSet(); using (Multek.SqlDB sqldb = new Multek.SqlDB(__conn)) { SqlCommand cmd = new SqlCommand("sp_gam_FC_adjustment"); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@bySales", list_by); ds = sqldb.getDataSetCmd(ref cmd); cmd.Dispose(); } DataTable dt3 = new DataTable(); if (ds.Tables[0].Rows.Count > 0) { dt3 = ds.Tables[1].DefaultView.ToTable(true, new string[] { "salesman", "OEMID", "CusOEM" }); DataRow row = dt3.NewRow(); row["salesman"] = "All"; row["OEMID"] = "0"; row["cusOEM"] = "TOTAL"; dt3.Rows.Add(row); foreach (DataRow rx in ds.Tables[0].Rows) { DataColumn colFC = new DataColumn(rx["iperiod"].ToString() + "_FC", typeof(double)); DataColumn colAD = new DataColumn(rx["iperiod"].ToString() + "_AD", typeof(double)); colFC.DefaultValue = colAD.DefaultValue = 0; dt3.Columns.Add(colFC); dt3.Columns.Add(colAD); } foreach (DataRow roem in dt3.Rows) { foreach(DataRow rf in ds.Tables[1].Select("OEMID="+ roem[1].ToString() +" and fiscal_period >0 ")) { roem[rf["fiscal_period"] + "_FC"] = rf["fcst_amt"].ToString(); roem[rf["fiscal_period"] + "_AD"] = rf["adjust_amt"].ToString(); } } int line = dt3.Rows.Count - 1; for (int i = 3; i < dt3.Columns.Count; i++) { string o = dt3.Compute("sum([" + dt3.Columns[i].ColumnName + "])", null).ToString(); dt3.Rows[line][i] = Convert.ToInt32(o); ; } } ds.Dispose(); return dt3; }
private static DataSet getDSbyAny(int startPeriod, int endPeriod, int salesman, string oem, string plant, string group, bool bk1, bool bk2) { DataSet ds = new DataSet(); using (Multek.SqlDB sqldb = new Multek.SqlDB(__conn)) { SqlCommand cmd = new SqlCommand("[sp_gam_ForecastData_getAllCriteria]"); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@startPeriod", startPeriod); cmd.Parameters.AddWithValue("@endPeriod", endPeriod); cmd.Parameters.AddWithValue("@salesman", salesman); cmd.Parameters.AddWithValue("@oemgroup", group); cmd.Parameters.AddWithValue("@oem", oem); cmd.Parameters.AddWithValue("@plant", plant); cmd.Parameters.AddWithValue("@subSales", false); cmd.Parameters.AddWithValue("@bkSales", false); ds = sqldb.getDataSetCmd(ref cmd); cmd.Dispose(); } return ds; }
private static DataSet getDS(int startPeriod, int endPeriod, int salesman, string uid) { DataSet ds = new DataSet(); using (Multek.SqlDB sqldb = new Multek.SqlDB(__conn)) { SqlCommand cmd = new SqlCommand("sp_gam_ForecastData_getBySalesId"); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@startPeriod", startPeriod); cmd.Parameters.AddWithValue("@endPeriod", endPeriod); cmd.Parameters.AddWithValue("@salesman", salesman); cmd.Parameters.AddWithValue("@uid", uid); ds = sqldb.getDataSetCmd(ref cmd); cmd.Dispose(); } return ds; }
public static DataSet getForecastHistoryOLS(int oem_id, int forecast_period) { DataSet ds = new DataSet(); using (Multek.SqlDB db = new Multek.SqlDB(__conn)) { SqlCommand cmd = new SqlCommand("sp_gam_forecastData_History_OLS"); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@oemid", oem_id); cmd.Parameters.AddWithValue("@period", forecast_period); ds = db.getDataSetCmd(ref cmd); cmd.Dispose(); } return ds; //exec sp_gam_forecastData_History_OLS @oemid,@period }
public static DataSet getForecast(bool isCurrent) { DataSet ds = new DataSet(); using (Multek.SqlDB db = new Multek.SqlDB(__conn)) { SqlCommand cmd = new SqlCommand("sp_gam_forecast_download_all"); cmd.Parameters.AddWithValue("@isCurrent", isCurrent); cmd.CommandType = CommandType.StoredProcedure; ds = db.getDataSetCmd(ref cmd); cmd.Dispose(); } return ds; }