public static string getPartNoHistory(string iPN,string code)
{
//string result = "<span>No user found!</span>";
DataTable dt = new DataTable();
using (Multek.SqlDB sqldb = new Multek.SqlDB(__conn))
{
string sql = "select top 10 OEM,plant,cus_part_no,qty,price,amount,sqft,asp = case when amount=0 or sqft=0 then 0 else amount/sqft end,"+
"Convert(datetime, gamDate, 112) as lastDate from dbo.cemFC_ship_and_mss where int_part_no=@ipn and customer_code=@code order by gamDate desc";
SqlCommand cmd = new SqlCommand(sql);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@ipn", iPN);
cmd.Parameters.AddWithValue("@code", code);
dt = sqldb.getDataTableWithCmd(ref cmd);
cmd.Connection.Dispose();
cmd.Dispose();
}
StringBuilder sb = new StringBuilder();
sb.Append("<table class='standardTable' border='1' cellpadding='1' cellspacing='0' borderColor='#cccccc' width='250' bgcolor='#FFFBBC'>"+
"<tr bgcolor='#C6976B'><th>Part#</th><th>Qty</th><th>Price</th><th>Amount</th><th>Sqft</th><th>ASP</th><th>Date</th></tr>");
foreach (DataRow row in dt.Rows)
{
//sb.Append(String.Format("<tr><td nowrap=true>{0}</td><td align='right'>{1:N0}</td><td>{2:N3}</td><td>{3:d}</tr>", row[2], row[3],row[4],row[8]));
sb.Append(String.Format("<tr bgcolor='#C5EACB'><td>{0}</td><td align='right'>{1:N0}</td><td align='right'>{2:N2}</td><td align='right'>{3:N2}</td><td align='right'>{4:N4}</td><td align='right'>{5:N2}</td><td>{6:dd MMM yyyy}</tr>",
row[2], row[3], row[4],row[5],row[6],row[7], row[8]));
}
sb.Append("</table>");
dt.Dispose();
return sb.ToString();
}
public static DataTable getGroup()
{
DataTable dt = new DataTable();
using (Multek.SqlDB sqldb = new Multek.SqlDB(ConfigurationManager.ConnectionStrings["GAMconn"].ToString()))
{
SqlCommand cmd = new SqlCommand("sp_gam_BaanOEMGroupList");
cmd.CommandType = CommandType.StoredProcedure;
dt = sqldb.getDataTableWithCmd(ref cmd);
cmd.Dispose();
}
return dt;
}
public static DataTable gets()
{
DataTable dt = new DataTable();
using (Multek.SqlDB sqldb = new Multek.SqlDB(__conn))
{
SqlCommand cmd = new SqlCommand("sp_gam_salesmanGet");
cmd.CommandType = CommandType.StoredProcedure;
dt = sqldb.getDataTableWithCmd(ref cmd);
cmd.Dispose();
}
return dt;
}
public string getCusPartActualHistory(int oemid, string customerPart)
{
string rq = "";
using (Multek.SqlDB db = new Multek.SqlDB(__conn))
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "sp_gam_actualItemHistory_get";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@oemid", oemid);
cmd.Parameters.AddWithValue("@cspart", customerPart);
DataTable dt = db.getDataTableWithCmd(ref cmd);
cmd.Dispose();
rq = Multek.Util.DT2JSON(dt);
}
return rq;
}
public OEMBaan(int oemId)
{
using (Multek.SqlDB sqldb = new Multek.SqlDB(__conn))
{
SqlCommand cmd = new SqlCommand("sp_gam_BaanOEMsSearching");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@id", oemId);
DataTable dt = sqldb.getDataTableWithCmd(ref cmd);
cmd.Dispose();
if (dt.Rows.Count == 1)
{
_BaanOEMID = (int)dt.Rows[0]["BaanOEMId"];
OEMName = dt.Rows[0]["oemname"].ToString();
Plant = dt.Rows[0]["plant"].ToString();
GroupName = dt.Rows[0]["groupname"].ToString();
}
}
}
public static DataTable getReportComparePeriodByPeriod(int p1, int p2, out string message)
{
message = "";
DataTable dt = new DataTable();
using (Multek.SqlDB db = new Multek.SqlDB(__conn))
{
SqlCommand cmd = new SqlCommand("sp_gam_CompareForecastOEM");
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;
dt = db.getDataTableWithCmd(ref cmd);
message = _msg.Value.ToString();
cmd.Dispose();
}
return dt;
}
public static DataTable getB2FResult(int period)
{
string msg = "";
DataTable dt = new DataTable();
using (Multek.SqlDB db = new Multek.SqlDB(__conn))
{
string sql = "sp_gam_b2f_report";
SqlCommand cmd = new SqlCommand(sql);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@up", period);
SqlParameter _msg = cmd.Parameters.AddWithValue("@msg", "");
_msg.Size = 200;
_msg.Direction = ParameterDirection.Output;
db.execSqlWithCmd(ref cmd);
msg = _msg.Value.ToString();
dt = db.getDataTableWithCmd(ref cmd);
cmd.Dispose();
}
return dt;
}
public static DataTable getForecastHistory(int oem_id, int forecast_period)
{
DataTable dt = new DataTable();
using (Multek.SqlDB sqldb = new Multek.SqlDB(__conn))
{
SqlCommand cmd = new SqlCommand("sp_gam_ForecastData_History");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@oem_id", oem_id);
cmd.Parameters.AddWithValue("@fiscal_period", forecast_period);
dt = sqldb.getDataTableWithCmd(ref cmd);
cmd.Dispose();
}
return dt;
}
public static DataTable searchOEM(string key, string group, int top)
{
DataTable dt = new DataTable();
using (Multek.SqlDB sqldb = new Multek.SqlDB(ConfigurationManager.ConnectionStrings["GAMconn"].ToString()))
{
SqlCommand cmd = new SqlCommand("sp_gam_BaanOEMsSearching");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@key", key);
cmd.Parameters.AddWithValue("@group", group);
cmd.Parameters.AddWithValue("@top", top);
dt = sqldb.getDataTableWithCmd(ref cmd);
cmd.Dispose();
}
return dt;
}
public static DataTable getForecastOutputVertical()
{
DataTable dt = new DataTable();
using (Multek.SqlDB sqldb = new Multek.SqlDB(__conn))
{
SqlCommand cmd = new SqlCommand("sp_gam_forecast_output_vertical");
cmd.CommandType = CommandType.StoredProcedure;
dt = sqldb.getDataTableWithCmd(ref cmd);
cmd.Dispose();
}
return dt;
}
public static DataTable getForecastOutput_forSalesDL(int startPeriod, int endPeriod, int salesman, string oemName, string plant, string group, bool subSales, bool bkSales)
{
DataTable dt = new DataTable();
using (Multek.SqlDB sqldb = new Multek.SqlDB(__conn))
{
SqlCommand cmd = new SqlCommand("sp_gam_forecast_output_for_salesDL");
cmd.CommandType = CommandType.StoredProcedure;
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", oemName);
cmd.Parameters.AddWithValue("@plant", plant);
cmd.Parameters.AddWithValue("@subSales", subSales);
cmd.Parameters.AddWithValue("@bkSales", bkSales);
dt = sqldb.getDataTableWithCmd(ref cmd);
cmd.Dispose();
}
return dt;
}
public static string oemType(int oemid)
{
string B2 = "normal";
using (Multek.SqlDB db = new Multek.SqlDB(__conn))
{
SqlCommand cmd = new SqlCommand("select right(rtrim(cusOEM),3) as oemType from dbo.vw_gam_OEMcomb where plant='2f' and oemid=@oemid");
cmd.Parameters.AddWithValue("@oemid", oemid);
cmd.CommandType = CommandType.Text;
DataTable dt = db.getDataTableWithCmd(ref cmd);
cmd.Dispose();
if (dt.Rows.Count > 0)
{
if (dt.Rows[0][0].ToString().ToLower() == "fpc")
B2 = "fpc";
else if (dt.Rows[0][0].ToString().ToLower() == "smt")
B2 = "smt";
}
}
return B2;
}
public static DataTable view_finish_forecast(int period, string salesman)
{
salesman = salesman.Trim();
string sql = "select * from vw_gam_salesman_finished_forecast where period=@period order by submit_time desc";
DataTable dt = new DataTable();
using (Multek.SqlDB sqldb = new Multek.SqlDB(__conn))
{
SqlCommand cmd = new SqlCommand(sql);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@period", period);
dt = sqldb.getDataTableWithCmd(ref cmd);
cmd.Dispose();
}
return dt;
}
public string getPartForecastHistory(int period, int oemid,string project)
{
string sql = "select top 6 * from dbo.gam_part_forecast_log where oem_id=@oemid and project=@project and period = @period order by log_period desc";
string rq = "";
/*
int tamt = 0;
string oem, baan, plant;
* */
using (Multek.SqlDB db = new Multek.SqlDB(__conn))
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = sql;
//cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@period", period);
cmd.Parameters.AddWithValue("@oemid", oemid);
cmd.Parameters.AddWithValue("@project", project);
DataTable dt = db.getDataTableWithCmd(ref cmd);
cmd.Dispose();
rq = Multek.Util.DT2JSON(dt);
dt.Dispose();
}
return rq;
}
public static DataTable getCEMOEM_ForecastRows()
{
DataTable dt = new DataTable();
using (Multek.SqlDB sqldb = new Multek.SqlDB(__conn))
{
SqlCommand cmd = new SqlCommand("sp_gam_cem_forecast_mix_result_download_vertical");
cmd.CommandType = CommandType.StoredProcedure;
dt = sqldb.getDataTableWithCmd(ref cmd);
cmd.Dispose();
}
return dt;
}
public string getUsers(string key)
{
string rq = "rs:{[]}";
using (Multek.SqlDB db = new Multek.SqlDB(__conn))
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "sp_gam_salesman_find";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@key", key);
DataTable dt = db.getDataTableWithCmd(ref cmd);
cmd.Dispose();
rq = Multek.Util.DT2JSON(dt);
}
return rq;
}
private static DataTable getDT(string sp)
{
DataTable dt = new DataTable();
using (Multek.SqlDB db = new Multek.SqlDB(__conn))
{
SqlCommand cmd = new SqlCommand(sp);
cmd.CommandType = CommandType.StoredProcedure;
dt = db.getDataTableWithCmd(ref cmd);
cmd.Dispose();
}
return dt;
}
private DataTable getDbUser(string uid, string domain)
{
DataTable dt = new DataTable();
using (Multek.SqlDB sqldb = new Multek.SqlDB(__conn))
{
SqlCommand cmd = new SqlCommand("sp_GAM_UsersGet");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@uid", uid);
cmd.Parameters.AddWithValue("@domain", domain);
dt = sqldb.getDataTableWithCmd(ref cmd);
}
return dt;
}
public DataTable getDbUsersByGroup(string group)
{
DataTable dt = new DataTable();
using (Multek.SqlDB sqldb = new Multek.SqlDB(__conn))
{
SqlCommand cmd = new SqlCommand("sp_GAM_UsersGetGroup");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@group", group);
dt = sqldb.getDataTableWithCmd(ref cmd);
}
return dt;
}
public static DataTable getsByManager(int manager_id)
{
DataTable dt = new DataTable();
using (Multek.SqlDB sqldb = new Multek.SqlDB(__conn))
{
SqlCommand cmd = new SqlCommand("sp_gam_salesmanGet");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@managerId", manager_id);
dt = sqldb.getDataTableWithCmd(ref cmd);
cmd.Dispose();
}
return dt;
}
public static DataTable bkSalesList(int oemid)
{
DataTable dt = new DataTable();
using (Multek.SqlDB sqldb = new Multek.SqlDB(ConfigurationManager.ConnectionStrings["GAMconn"].ToString()))
{
SqlCommand cmd = new SqlCommand("select gam_oem_salesman.id,userName from dbo.GAM_OEM_salesman left join dbo.GAM_USERS on sysUserId = salesman_id where oem_id=@oemid");
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@oemid", oemid);
dt = sqldb.getDataTableWithCmd(ref cmd);
cmd.Dispose();
}
return dt;
}
public string getOEMComments(int oemid)
{
string author = "";
if (HttpContext.Current.Session["usr"] != null)
{
nUser usr = (nUser)Session["usr"];
author = usr.userName;
usr.Dispose();
}
string output = "";
using (Multek.SqlDB db = new Multek.SqlDB(__conn))
{
SqlCommand cmd = new SqlCommand("sp_gam_oem_comments_get");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@oem_id", oemid);
cmd.Parameters.AddWithValue("@author", author);
DataTable dt = db.getDataTableWithCmd(ref cmd);
cmd.Dispose();
output = Multek.Util.DT2JSON(dt);
}
return output;
}
public static DataTable ListBySaleId(int id)
{
DataTable dt = new DataTable();
using (Multek.SqlDB sqldb = new Multek.SqlDB(ConfigurationManager.ConnectionStrings["GAMconn"].ToString()))
{
SqlCommand cmd = new SqlCommand("sp_gam_CusOEMList");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@salesId", id);
dt = sqldb.getDataTableWithCmd(ref cmd);
cmd.Dispose();
}
return dt;
}
public OEMCus(int oid)
{
using (Multek.SqlDB sqldb = new Multek.SqlDB(__conn))
{
SqlCommand cmd = new SqlCommand("select * from gam_oem where oemid=@id");
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@id", oid);
DataTable dt = sqldb.getDataTableWithCmd(ref cmd);
cmd.Dispose();
if (dt.Rows.Count == 1)
{
_OEMID = (int)dt.Rows[0]["oemid"];
CusOEM = dt.Rows[0]["CusOEM"].ToString();
BaanId = (int)dt.Rows[0]["BaanId"];
salesmanId = (int)dt.Rows[0]["SalesmanId"];
isValid = (bool)dt.Rows[0]["isValid"];
viewSalesmanId = (int)dt.Rows[0]["viewsalesmanId"];
}
}
}
//for salesman usage.
public nUser(int system_id)
{
DataTable dt;
using (Multek.SqlDB sqldb = new Multek.SqlDB(__conn))
{
SqlCommand cmd = new SqlCommand("sp_gam_salesmanGet");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@salesmanId", system_id);
dt = sqldb.getDataTableWithCmd(ref cmd);
}
if (dt.Rows.Count == 1)
{
DataRow row = dt.Rows[0];
_isDbUser = true;
_sysUserId = (int)row["sysUserId"];
managerId = (int)row["managerId"];
isReportViewer = (bool)row["isReportViewer"];
department = row["department"].ToString();
jobTitle = row["jobTitle"].ToString();
isSales = (bool)row["isSales"];
isActive = (bool)row["isActive"];
isAdmin = (bool)row["isAdmin"];
isPriceView = (bool)row["isPriceView"];
userName = row["userName"].ToString();
}
}
public string getSale(string key)
{
DataTable dt;
using (Multek.SqlDB db = new Multek.SqlDB(__conn))
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "sp_gam_salesman_find";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@key", key);
cmd.Parameters.AddWithValue("@Salesonly", true);
dt = db.getDataTableWithCmd(ref cmd);
cmd.Dispose();
dt.Dispose();
}
JavaScriptSerializer jss = new JavaScriptSerializer();
string json = jss.Serialize(dt);
return json;
}
private void loadExistsData()
{
displayNormal();
DataTable dt = new DataTable();
using (Multek.SqlDB db = new Multek.SqlDB(__conn))
{
string sql = "sp_gam_b2f_display_data";
SqlCommand cmd = new SqlCommand(sql);
cmd.CommandType = CommandType.StoredProcedure;
dt = db.getDataTableWithCmd(ref cmd);
cmd.Dispose();
}
ListViewDisplay.DataSource = dt;
ListViewDisplay.DataBind();
if (dt.Rows.Count > 0)
{
uploadAgain.Text = "Clear existing " + currentPeriod.Text + " forecast and upload again.";
}
else
{
//uploadAgain.Text = "Upload B2F forecast for " + currentPeriod.Text +".";
displayUpload();
}
}
