public DataTable SelectZeroCostMaterial()
{
//static methodd to connect database
SqlConnection conn = new SqlConnection(myconnstrng);
//to hold the data from database
DataTable dt = new DataTable();
try
{
//sql query to get data from database
String sql = "SELECT * FROM tbl_material INNER JOIN tbl_item ON tbl_material.material_code = tbl_item.item_code WHERE material_zero_cost = 1 ORDER BY tbl_material.material_cat ASC, tbl_material.material_name ASC";
//for executing command
SqlCommand cmd = new SqlCommand(sql, conn);
//getting data from database
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
//database connection open
conn.Open();
//fill data in our database
adapter.Fill(dt);
}
catch (Exception ex)
{
//throw message if any error occurs
Module.Tool tool = new Module.Tool(); tool.saveToText(ex);
}
finally
{
//closing connection
conn.Close();
}
return(dt);
}
public DataTable checkItemCustTable(string keywords)
{
//static methodd to connect database
SqlConnection conn = new SqlConnection(myconnstrng);
//to hold the data from database
DataTable dt = new DataTable();
try
{
//sql query to get data from database
String sql = "SELECT * FROM tbl_item_cust WHERE item_code = @keywords";
//for executing command
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@keywords", keywords);
//getting data from database
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
//database connection open
conn.Open();
//fill data in our database
adapter.Fill(dt);
}
catch (Exception ex)
{
//throw message if any error occurs
Module.Tool tool = new Module.Tool(); tool.saveToText(ex);
}
finally
{
//closing connection
conn.Close();
}
return(dt);
}
public DataTable LastRecordSelect()
{
//static methodd to connect database
SqlConnection conn = new SqlConnection(myconnstrng);
//to hold the data from database
DataTable dt = new DataTable();
try
{
//sql query to get data from database
String sql = "SELECT TOP 1 * FROM tbl_production_record ORDER BY sheet_id DESC";
//for executing command
SqlCommand cmd = new SqlCommand(sql, conn);
//getting data from database
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
//database connection open
conn.Open();
//fill data in our database
adapter.Fill(dt);
}
catch (Exception ex)
{
//throw message if any error occurs
Module.Tool tool = new Module.Tool(); tool.saveToText(ex);
}
finally
{
//closing connection
conn.Close();
}
return(dt);
}
public DataTable HabitSearch(string belongTo)
{
//static methodd to connect database
SqlConnection conn = new SqlConnection(myconnstrng);
//to hold the data from database
DataTable dt = new DataTable();
try
{
//sql query to get data from database
String sql = @"SELECT * FROM tbl_habit WHERE belong_to = @belong_to";
//for executing command
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@belong_to", belongTo);
//getting data from database
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
//database connection open
conn.Open();
//fill data in our database
adapter.Fill(dt);
}
catch (Exception ex)
{
//throw message if any error occurs
Module.Tool tool = new Module.Tool(); tool.saveToText(ex);
}
finally
{
//closing connection
conn.Close();
}
return(dt);
}
public DataTable SearchByNewDate(string month, string year)
{
SqlConnection conn = new SqlConnection(myconnstrng);
DataTable dt = new DataTable();
try
{
String sql = @"SELECT * FROM tbl_pmma INNER JOIN tbl_item
ON tbl_pmma.pmma_item_code = tbl_item.item_code
AND tbl_pmma.pmma_month = @month AND tbl_pmma.pmma_year = @year";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@itemCode", itemCode);
cmd.Parameters.AddWithValue("@month", month);
cmd.Parameters.AddWithValue("@year", year);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
conn.Open();
adapter.Fill(dt);
}
catch (Exception ex)
{
Module.Tool tool = new Module.Tool(); tool.saveToText(ex);
}
finally
{
conn.Close();
}
return(dt);
}
public DataTable Select()
{
SqlConnection conn = new SqlConnection(myconnstrng);
DataTable dt = new DataTable();
try
{
String sql = "SELECT * FROM tbl_history";
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
conn.Open();
adapter.Fill(dt);
}
catch (Exception ex)
{
Module.Tool tool = new Module.Tool(); tool.saveToText(ex);
}
finally
{
conn.Close();
}
dt.DefaultView.Sort = "history_date DESC";
DataTable sortedDt = dt.DefaultView.ToTable();
return(sortedDt);
}
public DataTable userSearch(int userID)
{
SqlConnection conn = new SqlConnection(myconnstrng);
DataTable dt = new DataTable();
try
{
String sql = "SELECT * FROM tbl_history WHERE history_by = @userID ";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@userID", userID);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
conn.Open();
adapter.Fill(dt);
}
catch (Exception ex)
{
Module.Tool tool = new Module.Tool(); tool.saveToText(ex);
}
finally
{
conn.Close();
}
dt.DefaultView.Sort = "history_date DESC";
DataTable sortedDt = dt.DefaultView.ToTable();
return(sortedDt);
}
public bool Update(ordBLL u)
{
bool isSuccess = false;
SqlConnection conn = new SqlConnection(myconnstrng);
try
{
String sql = @"UPDATE tbl_ord SET
ord_item_code=@ord_item_code,
ord_required_date=@ord_required_date,
ord_status=@ord_status,
ord_qty=@ord_qty,
ord_pending=@ord_pending,
ord_received=@ord_received,
ord_updated_date=@ord_updated_date,
ord_type=@ord_type,
ord_po_no=@ord_po_no,
ord_updated_by=@ord_updated_by
WHERE ord_id=@ord_id";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@ord_id", u.ord_id);
cmd.Parameters.AddWithValue("@ord_item_code", u.ord_item_code);
cmd.Parameters.AddWithValue("@ord_status", u.ord_status);
cmd.Parameters.AddWithValue("@ord_required_date", u.ord_required_date);
cmd.Parameters.AddWithValue("@ord_qty", u.ord_qty);
cmd.Parameters.AddWithValue("@ord_pending", u.ord_pending);
cmd.Parameters.AddWithValue("@ord_received", u.ord_received);
cmd.Parameters.AddWithValue("@ord_updated_date", u.ord_updated_date);
cmd.Parameters.AddWithValue("@ord_updated_by", u.ord_updated_by);
cmd.Parameters.AddWithValue("@ord_type", u.ord_type);
cmd.Parameters.AddWithValue("@ord_po_no", u.ord_po_no);
conn.Open();
int rows = cmd.ExecuteNonQuery();
//if the query is executed successfully then the rows' value = 0
if (rows > 0)
{
//query successful
isSuccess = true;
}
else
{
//Query falled
isSuccess = false;
}
}
catch (Exception ex)
{
Module.Tool tool = new Module.Tool(); tool.saveToText(ex);
}
finally
{
conn.Close();
}
return(isSuccess);
}
public void deactivate(int actionID)
{
SqlConnection conn = new SqlConnection(myconnstrng);
try
{
String sql = @"UPDATE [dbo].[tbl_order_action]
SET [active] = 0
WHERE order_action_id = @actionID";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@actionID", actionID);
conn.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Module.Tool tool = new Module.Tool(); tool.saveToText(ex);
}
finally
{
conn.Close();
}
}
public DataTable SelectByPlanningID(int planningID)
{
//static methodd to connect database
SqlConnection conn = new SqlConnection(myconnstrng);
//to hold the data from database
DataTable dt = new DataTable();
try
{
//sql query to get data from database
String sql = "SELECT * FROM tbl_planning_action WHERE planning_id = @planningID ORDER BY added_date DESC";
//for executing command
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@planningID", planningID);
//getting data from database
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
//database connection open
conn.Open();
//fill data in our database
adapter.Fill(dt);
}
catch (Exception ex)
{
//throw message if any error occurs
Module.Tool tool = new Module.Tool(); tool.saveToText(ex);
}
finally
{
//closing connection
conn.Close();
}
return(dt);
}
public DataTable idSearch(string custID)
{
//static methodd to connect database
SqlConnection conn = new SqlConnection(myconnstrng);
//to hold the data from database
DataTable dt = new DataTable();
try
{
//sql query to get data from database
String sql = "SELECT * FROM tbl_cust WHERE cust_id=@custID";
//for executing command
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@custID", custID);
//getting data from database
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
//database connection open
conn.Open();
//fill data in our database
adapter.Fill(dt);
}
catch (Exception ex)
{
Module.Tool tool = new Module.Tool();
tool.saveToText(ex);
}
finally
{
//closing connection
conn.Close();
}
return(dt);
}
public DataTable idSearch(string keywords)
{
//static methodd to connect database
SqlConnection conn = new SqlConnection(myconnstrng);
//to hold the data from database
DataTable dt = new DataTable();
try
{
//sql query to get data from database
String sql = "SELECT * FROM tbl_trf_cat WHERE trf_cat_id LIKE '%" + keywords + "%'";
//for executing command
SqlCommand cmd = new SqlCommand(sql, conn);
//getting data from database
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
//database connection open
conn.Open();
//fill data in our database
adapter.Fill(dt);
}
catch (Exception ex)
{
Module.Tool tool = new Module.Tool();
tool.saveToText(ex);
}
finally
{
//closing connection
conn.Close();
}
return(dt);
}
public DataTable matSearch(string itemCode)
{
//static methodd to connect database
SqlConnection conn = new SqlConnection(myconnstrng);
//to hold the data from database
DataTable dt = new DataTable();
try
{
//sql query to get data from database
String sql = "SELECT * FROM tbl_material_used INNER JOIN tbl_item ON tbl_material_used.item_code = tbl_item.item_code AND tbl_item.item_material = @itemCode";
//for executing command
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@itemCode", itemCode);
//getting data from database
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
//database connection open
conn.Open();
//fill data in our database
adapter.Fill(dt);
}
catch (Exception ex)
{
//throw message if any error occurs
Module.Tool tool = new Module.Tool(); tool.saveToText(ex);
}
finally
{
//closing connection
conn.Close();
}
return(dt);
}
public DataTable userNameSearch(string username)
{
SqlConnection conn = new SqlConnection(myconnstrng);
DataTable dt = new DataTable();
try
{
String sql = "SELECT * FROM tbl_user WHERE user_name = @username";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@username", username);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
conn.Open();
adapter.Fill(dt);
}
catch (Exception ex)
{
Module.Tool tool = new Module.Tool();
tool.saveToText(ex);
}
finally
{
conn.Close();
}
return(dt);
}
public DataTable existCheck(string parent, string child)
{
SqlConnection conn = new SqlConnection(myconnstrng);
DataTable dt = new DataTable();
try
{
String sql = "SELECT * FROM tbl_join WHERE join_parent_code = @parent_code AND join_child_code = @child_code";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@parent_code", parent);
cmd.Parameters.AddWithValue("@child_code", child);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
conn.Open();
adapter.Fill(dt);
}
catch (Exception ex)
{
//throw message if any error occurs
Module.Tool tool = new Module.Tool(); tool.saveToText(ex);
}
finally
{
//closing connection
conn.Close();
}
return(dt);
}
public DataTable itemSearch(string keywords)
{
//static methodd to connect database
SqlConnection conn = new SqlConnection(myconnstrng);
//to hold the data from database
DataTable dt = new DataTable();
try
{
//sql query to get data from database
String sql = "SELECT * FROM ((tbl_item_cust INNER JOIN tbl_item ON tbl_item.item_name LIKE '%" + keywords + "%'AND tbl_item_cust.item_code = tbl_item.item_code) INNER JOIN tbl_cust ON tbl_item_cust.cust_id = tbl_cust.cust_id)";
//for executing command
SqlCommand cmd = new SqlCommand(sql, conn);
//getting data from database
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
//database connection open
conn.Open();
//fill data in our database
adapter.Fill(dt);
}
catch (Exception ex)
{
//throw message if any error occurs
Module.Tool tool = new Module.Tool(); tool.saveToText(ex);
}
finally
{
//closing connection
conn.Close();
}
return(dt);
}
public DataTable Select()
{
SqlConnection conn = new SqlConnection(myconnstrng);
DataTable dt = new DataTable();
try
{
String sql = "SELECT * FROM tbl_pmma INNER JOIN tbl_item ON tbl_pmma.pmma_item_code = tbl_item.item_code";
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
conn.Open();
adapter.Fill(dt);
}
catch (Exception ex)
{
Module.Tool tool = new Module.Tool();
tool.saveToText(ex);
}
finally
{
conn.Close();
}
return(dt);
}
public DataTable Select(pmmaBLL u)
{
SqlConnection conn = new SqlConnection(myconnstrng);
DataTable dt = new DataTable();
try
{
String sql = "SELECT * FROM tbl_pmma WHERE pmma_item_code=@pmma_item_code AND pmma_month=@pmma_month AND pmma_year=@pmma_year";
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
cmd.Parameters.AddWithValue("@pmma_item_code", u.pmma_item_code);
cmd.Parameters.AddWithValue("@pmma_month", u.pmma_month);
cmd.Parameters.AddWithValue("@pmma_year", u.pmma_year);
conn.Open();
adapter.Fill(dt);
}
catch (Exception ex)
{
Module.Tool tool = new Module.Tool();
tool.saveToText(ex);
}
finally
{
conn.Close();
}
return(dt);
}
public DataTable SelectWithSBBInfo()
{
//static methodd to connect database
SqlConnection conn = new SqlConnection(myconnstrng);
//to hold the data from database
DataTable dt = new DataTable();
try
{
//sql query to get data from database
String sql = @"SELECT tbl_join.join_parent_code as parent_code,
tbl_item.item_name as parent_name ,
tbl_join.join_child_code as child_code ,
a.item_name as child_name ,join_qty, join_max, join_min, join_added_date, join_added_by,
join_updated_date,join_updated_by ,
tbl_item.item_quo_pw_pcs,
tbl_item.item_quo_rw_pcs,
tbl_item.item_part_weight,
tbl_item.item_runner_weight,
tbl_item.item_wastage_allowed,
tbl_spp_type.type_name,
tbl_spp_size.size_numerator,
tbl_spp_size.size_denominator,
tbl_spp_size.size_unit,
tbl_spp_stdpacking.qty_per_bag,
a.item_qty
FROM tbl_join
JOIN tbl_item
ON tbl_join.join_parent_code = tbl_item.item_code
JOIN tbl_item a ON tbl_join.join_child_code = a.item_code
INNER JOIN tbl_spp_type
ON a.type_tbl_code = tbl_spp_type.tbl_code
INNER JOIN tbl_spp_size
ON a.size_tbl_code_1 = tbl_spp_size.tbl_code
FULL JOIN tbl_spp_stdpacking
ON a.item_code = tbl_spp_stdpacking.item_code";
//for executing command
SqlCommand cmd = new SqlCommand(sql, conn);
//getting data from database
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
//database connection open
conn.Open();
//fill data in our database
adapter.Fill(dt);
}
catch (Exception ex)
{
//throw message if any error occurs
Module.Tool tool = new Module.Tool(); tool.saveToText(ex);
}
finally
{
//closing connection
conn.Close();
}
return(dt);
}
public bool insertWithDataID(historyBLL u)
{
bool isSuccess = false;
SqlConnection conn = new SqlConnection(myconnstrng);
try
{
String sql = @"INSERT INTO tbl_history
(" + HistoryDate + ","
+ HistoryBy + ","
+ HistoryAction + ","
+ HistoryDetail + ","
+ HistoryPageName + ","
+ HistoryDataID + ") VALUES " +
"(@history_date," +
"@history_by," +
"@history_action," +
"@history_detail," +
"@page_name," +
"@data_id)";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@history_date", u.history_date);
cmd.Parameters.AddWithValue("@history_by", u.history_by);
cmd.Parameters.AddWithValue("@history_action", u.history_action);
cmd.Parameters.AddWithValue("@history_detail", u.history_detail);
cmd.Parameters.AddWithValue("@page_name", u.page_name);
cmd.Parameters.AddWithValue("@data_id", u.data_id);
conn.Open();
int rows = cmd.ExecuteNonQuery();
//if the query is executed successfully then the rows' value = 0
if (rows > 0)
{
//query successful
isSuccess = true;
}
else
{
//Query falled
isSuccess = false;
}
}
catch (Exception ex)
{
Module.Tool tool = new Module.Tool(); tool.saveToText(ex);
}
finally
{
conn.Close();
}
return(isSuccess);
}
public bool insert(userBLL u)
{
bool isSuccess = false;
SqlConnection conn = new SqlConnection(myconnstrng);
try
{
String sql = @"INSERT INTO tbl_user
(" + Username + ","
+ Password + ","
+ Permission + ","
+ AddedDate + ","
+ AddedBy + ") VALUES " +
"(@user_name," +
"@user_password," +
"@user_permissions," +
"@added_date," +
"@added_by)";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@user_name", u.user_name);
cmd.Parameters.AddWithValue("@user_password", u.user_password);
cmd.Parameters.AddWithValue("@user_permissions", u.user_permissions);
cmd.Parameters.AddWithValue("@added_date", u.added_date);
cmd.Parameters.AddWithValue("@added_by", u.added_by);
conn.Open();
int rows = cmd.ExecuteNonQuery();
//if the query is executed successfully then the rows' value = 0
if (rows > 0)
{
//query successful
isSuccess = true;
}
else
{
//Query falled
isSuccess = false;
}
}
catch (Exception ex)
{
Module.Tool tool = new Module.Tool();
tool.saveToText(ex);
}
finally
{
conn.Close();
}
return(isSuccess);
}
public bool UpdateAdjustAndNote(pmmaBLL u)
{
bool isSuccess = false;
SqlConnection conn = new SqlConnection(myconnstrng);
try
{
String sql = @"UPDATE tbl_pmma SET "
+ Adjust + "=@pmma_adjust,"
+ Note + "=@pmma_note,"
+ UpdatedDate + "=@pmma_updated_date,"
+ UpdatedBy + "=@pmma_updated_by" +
" WHERE pmma_item_code=@pmma_item_code " +
"AND pmma_month=@pmma_month AND pmma_year=@pmma_year";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@pmma_item_code", u.pmma_item_code);
cmd.Parameters.AddWithValue("@pmma_adjust", u.pmma_adjust);
cmd.Parameters.AddWithValue("@pmma_note", u.pmma_note);
cmd.Parameters.AddWithValue("@pmma_month", u.pmma_month);
cmd.Parameters.AddWithValue("@pmma_year", u.pmma_year);
cmd.Parameters.AddWithValue("@pmma_updated_date", u.pmma_updated_date);
cmd.Parameters.AddWithValue("@pmma_updated_by", u.pmma_updated_by);
conn.Open();
int rows = cmd.ExecuteNonQuery();
//if the query is executed successfully then the rows' value = 0
if (rows > 0)
{
//query successful
isSuccess = true;
}
else
{
//Query falled
isSuccess = false;
}
}
catch (Exception ex)
{
Module.Tool tool = new Module.Tool(); tool.saveToText(ex);
}
finally
{
conn.Close();
}
return(isSuccess);
}
public bool scheduleDataAndRecordingUpdate(PlanningBLL u)
{
bool isSuccess = false;
SqlConnection conn = new SqlConnection(myconnstrng);
try
{
String sql = @"UPDATE tbl_plan SET
plan_status=@plan_status,family_with=@family_with, production_start_date=@production_start_date, production_end_date=@production_end_date,
plan_note=@plan_note,
plan_updated_date=@plan_updated_date,
plan_updated_by=@plan_updated_by,
recording=@recording
WHERE plan_id=@plan_id";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@plan_id", u.plan_id);
cmd.Parameters.AddWithValue("@plan_status", u.plan_status);
cmd.Parameters.AddWithValue("@family_with", u.family_with);
cmd.Parameters.AddWithValue("@production_start_date", u.production_start_date);
cmd.Parameters.AddWithValue("@production_end_date", u.production_end_date);
cmd.Parameters.AddWithValue("@plan_note", u.plan_note);
cmd.Parameters.AddWithValue("@plan_updated_date", u.plan_updated_date);
cmd.Parameters.AddWithValue("@plan_updated_by", u.plan_updated_by);
cmd.Parameters.AddWithValue("@recording", u.recording);
conn.Open();
int rows = cmd.ExecuteNonQuery();
//if the query is executed successfully then the rows' value = 0
if (rows > 0)
{
//query successful
isSuccess = true;
}
else
{
//Query falled
isSuccess = false;
}
}
catch (Exception ex)
{
Module.Tool tool = new Module.Tool(); tool.saveToText(ex);
}
finally
{
conn.Close();
}
return(isSuccess);
}
public DataTable Select()
{
//static methodd to connect database
SqlConnection conn = new SqlConnection(myconnstrng);
//to hold the data from database
DataTable dt = new DataTable();
try
{
//sql query to get data from database
String sql = @"SELECT tbl_cust.cust_name,
tbl_item.item_code,
tbl_item.item_name,
tbl_item.item_material,
tbl_item.item_qty,
tbl_item.item_assembly,
tbl_item.item_production,
tbl_item.item_part_weight,
tbl_item.item_runner_weight,
tbl_item.item_wastage_allowed,
tbl_item_cust.forecast_current_month,
tbl_item_cust.forecast_one,
tbl_item_cust.forecast_two,
tbl_item_cust.forecast_three,
tbl_item_cust.item_cust_added_date,
tbl_item_cust.item_cust_added_by
FROM ((tbl_item_cust
INNER JOIN tbl_item
ON tbl_item_cust.item_code = tbl_item.item_code)
INNER JOIN tbl_cust
ON tbl_item_cust.cust_id = tbl_cust.cust_id) ORDER BY tbl_cust.cust_name ASC, tbl_item.item_name ASC";
//for executing command
SqlCommand cmd = new SqlCommand(sql, conn);
//getting data from database
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
//database connection open
conn.Open();
//fill data in our database
adapter.Fill(dt);
}
catch (Exception ex)
{
//throw message if any error occurs
Module.Tool tool = new Module.Tool(); tool.saveToText(ex);
}
finally
{
//closing connection
conn.Close();
}
return(dt);
}
public bool MatPrepareUpdate(matPlanBLL u)
{
bool isSuccess = false;
SqlConnection conn = new SqlConnection(myconnstrng);
try
{
String sql = @"UPDATE tbl_matplan
SET "
+ Prepare + "=@mat_preparing, "
+ Transferred + "=@mat_transferred, "
+ MatFrom + "=@mat_from, "
+ UpdatedDate + "=@updated_date, "
+ UpdatedBy + "=@updated_by " +
" WHERE mat_code=@mat_code AND plan_id=@plan_id";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@mat_code", u.mat_code);
cmd.Parameters.AddWithValue("@plan_id", u.plan_id);
cmd.Parameters.AddWithValue("@mat_transferred", u.mat_transferred);
cmd.Parameters.AddWithValue("@mat_preparing", u.mat_preparing);
cmd.Parameters.AddWithValue("@mat_from", u.mat_from);
cmd.Parameters.AddWithValue("@updated_date", u.updated_date);
cmd.Parameters.AddWithValue("@updated_by", u.updated_by);
conn.Open();
int rows = cmd.ExecuteNonQuery();
//if the query is executed successfully then the rows' value = 0
if (rows > 0)
{
//query successful
isSuccess = true;
}
else
{
//Query falled
isSuccess = false;
}
}
catch (Exception ex)
{
Module.Tool tool = new Module.Tool();
tool.saveToText(ex);
}
finally
{
conn.Close();
}
return(isSuccess);
}
public DataTable SelectwithChildInfo()
{
//static methodd to connect database
SqlConnection conn = new SqlConnection(myconnstrng);
//to hold the data from database
DataTable dt = new DataTable();
try
{
//sql query to get data from database
String sql = @"SELECT tbl_join.join_parent_code as parent_code,
tbl_item.item_name as parent_name ,
tbl_join.join_child_code as child_code ,
a.item_name as child_name ,
a.item_cat as child_cat ,
a.item_material as child_material ,
a.item_mb as child_mb ,
a.item_mb_rate as child_mb_rate ,
a.item_part_weight as child_part_weight ,
a.item_runner_weight as child_runner_weight ,
a.item_quo_pw_pcs as child_quo_part_weight ,
a.item_quo_rw_pcs as child_quo_runner_weight ,
a.item_wastage_allowed as child_wastage_allowed ,
a.item_qty as child_qty ,
a.item_assembly as child_assembly ,
a.item_production as child_production ,
join_qty
FROM tbl_join
JOIN tbl_item
ON tbl_join.join_parent_code = tbl_item.item_code
JOIN tbl_item a ON tbl_join.join_child_code = a.item_code";
//for executing command
SqlCommand cmd = new SqlCommand(sql, conn);
//getting data from database
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
//database connection open
conn.Open();
//fill data in our database
adapter.Fill(dt);
}
catch (Exception ex)
{
//throw message if any error occurs
Module.Tool tool = new Module.Tool(); tool.saveToText(ex);
}
finally
{
//closing connection
conn.Close();
}
return(dt);
}
public bool UpdateWithMaxMin(joinBLL u)
{
bool isSuccess = false;
SqlConnection conn = new SqlConnection(myconnstrng);
try
{
String sql = @"UPDATE tbl_join
SET "
+ JoinQty + "=@join_qty,"
+ JoinMax + "=@join_max,"
+ JoinMin + "=@join_min,"
+ JoinUpdatedDate + "=@join_updated_date,"
+ JoinUpdatedBy + "=@join_updated_by" +
" WHERE join_parent_code=@join_parent_code AND join_child_code=@join_child_code";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@join_parent_code", u.join_parent_code);
cmd.Parameters.AddWithValue("@join_child_code", u.join_child_code);
cmd.Parameters.AddWithValue("@join_qty", u.join_qty);
cmd.Parameters.AddWithValue("@join_max", u.join_max);
cmd.Parameters.AddWithValue("@join_min", u.join_min);
cmd.Parameters.AddWithValue("@join_updated_date", u.join_updated_date);
cmd.Parameters.AddWithValue("@join_updated_by", u.join_updated_by);
conn.Open();
int rows = cmd.ExecuteNonQuery();
//if the query is executed successfully then the rows' value = 0
if (rows > 0)
{
//query successful
isSuccess = true;
}
else
{
//Query falled
isSuccess = false;
}
}
catch (Exception ex)
{
Module.Tool tool = new Module.Tool(); tool.saveToText(ex);
}
finally
{
conn.Close();
}
return(isSuccess);
}
public bool scheduleAndProDayUpdate(PlanningBLL u)
{
bool isSuccess = false;
SqlConnection conn = new SqlConnection(myconnstrng);
try
{
String sql = @"UPDATE tbl_plan SET
production_hour=@production_hour,production_day=@production_day, production_start_date=@production_start_date, production_end_date=@production_end_date,
production_hour_per_day=@production_hour_per_day,
plan_updated_date=@plan_updated_date,
plan_updated_by=@plan_updated_by
WHERE plan_id=@plan_id";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@plan_id", u.plan_id);
cmd.Parameters.AddWithValue("@production_hour", u.production_hour);
cmd.Parameters.AddWithValue("@production_day", u.production_day);
cmd.Parameters.AddWithValue("@production_hour_per_day", u.production_hour_per_day);
cmd.Parameters.AddWithValue("@production_start_date", u.production_start_date);
cmd.Parameters.AddWithValue("@production_end_date", u.production_end_date);
cmd.Parameters.AddWithValue("@plan_updated_date", u.plan_updated_date);
cmd.Parameters.AddWithValue("@plan_updated_by", u.plan_updated_by);
conn.Open();
int rows = cmd.ExecuteNonQuery();
//if the query is executed successfully then the rows' value = 0
if (rows > 0)
{
//query successful
isSuccess = true;
}
else
{
//Query falled
isSuccess = false;
}
}
catch (Exception ex)
{
Module.Tool tool = new Module.Tool(); tool.saveToText(ex);
}
finally
{
conn.Close();
}
return(isSuccess);
}
public DataTable SPPCustSearchWithTypeAndSize(string keywords)
{
//static methodd to connect database
SqlConnection conn = new SqlConnection(myconnstrng);
//to hold the data from database
DataTable dt = new DataTable();
try
{
//sql query to get data from database
String sql = @"SELECT * FROM
((tbl_item_cust
INNER JOIN tbl_cust
ON tbl_cust.cust_name=@keywords
AND tbl_item_cust.cust_id = tbl_cust.cust_id)
INNER JOIN tbl_item
ON tbl_item_cust.item_code = tbl_item.item_code)
INNER JOIN tbl_spp_type
ON tbl_item.type_tbl_code = tbl_spp_type.tbl_code
INNER JOIN tbl_spp_size
ON tbl_item.size_tbl_code_1 = tbl_spp_size.tbl_code
INNER JOIN tbl_spp_stdpacking
ON tbl_item.item_code = tbl_spp_stdpacking.item_code
ORDER BY tbl_item.item_name ASC";
//for executing command
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@keywords", keywords);
//getting data from database
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
//database connection open
conn.Open();
//fill data in our database
adapter.Fill(dt);
}
catch (Exception ex)
{
//throw message if any error occurs
Module.Tool tool = new Module.Tool(); tool.saveToText(ex);
}
finally
{
//closing connection
conn.Close();
}
return(dt);
}
public bool Update(itemCustBLL u)
{
bool isSuccess = false;
Module.Tool tool = new Module.Tool();
Module.Text text = new Module.Text();
SqlConnection conn = new SqlConnection(myconnstrng);
try
{
String sql = "UPDATE tbl_item_cust SET forecast_one=@forecast_one, forecast_two=@forecast_two, forecast_three=@forecast_three,forecast_four=@forecast_four, forecast_current_month=@forecast_current_month, forecast_updated_date=@forecast_updated_date, forecast_updated_by=@forecast_updated_by WHERE item_code=@item_code AND cust_id = @cust_id";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@forecast_one", u.forecast_one);
cmd.Parameters.AddWithValue("@forecast_two", u.forecast_two);
cmd.Parameters.AddWithValue("@forecast_three", u.forecast_three);
cmd.Parameters.AddWithValue("@forecast_four", u.forecast_four);
cmd.Parameters.AddWithValue("@forecast_current_month", u.forecast_current_month);
cmd.Parameters.AddWithValue("@forecast_updated_date", u.forecast_updated_date);
cmd.Parameters.AddWithValue("@forecast_updated_by", u.forecast_updated_by);
cmd.Parameters.AddWithValue("@item_code", u.item_code);
cmd.Parameters.AddWithValue("@cust_id", u.cust_id);
conn.Open();
int rows = cmd.ExecuteNonQuery();
//if the query is executed successfully then the rows' value = 0
if (rows > 0)
{
//query successful
isSuccess = true;
}
else
{
//Query falled
isSuccess = false;
}
}
catch (Exception ex)
{
tool.saveToText(ex);
}
finally
{
conn.Close();
}
return(isSuccess);
}