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;
}