static public string ReturnValueStreamNameForPartNo(string inPartNo) { string retFlag = ""; try { using (MySqlConnection conn = new MySqlConnection(DB.mildew_database_connection_string)) { //open connection to database conn.Open(); string query = ""; query += "SELECT v.value_stream "; query += "FROM master_part_details m "; query += "INNER JOIN master_value_stream v ON m.value_stream_id = v.value_stream_id "; query += "WHERE(m.part_no = '" + NJS_Helper.FormatStringforDB(inPartNo) + "')'"; MySqlCommand cmd = new MySqlCommand(query, conn); object obj = cmd.ExecuteScalar(); if (obj != null) { if (obj.ToString() != "") { retFlag = obj.ToString(); } } } } catch (Exception ex) { retFlag = ""; } return(retFlag); }
static public string InsertIntoPartDetails(string inPartNo, string inValueStreamID) { string retFlag = ""; try { using (MySqlConnection conn = new MySqlConnection(DB.mildew_database_connection_string)) { //open connection to database conn.Open(); string query = "INSERT INTO master_part_details (part_no, value_stream_id) VALUES "; query += "("; query += "'" + NJS_Helper.FormatStringforDB(inPartNo) + "', "; query += "'" + NJS_Helper.FormatStringforDB(inValueStreamID) + "'"; query += ");"; MySqlCommand cmd = new MySqlCommand(query, conn); int rows = cmd.ExecuteNonQuery(); if (rows == 1) { retFlag = "ok"; } } } catch (Exception ex) { retFlag = ""; } return(retFlag); }
static public string ReturnMiLDEWVSNameForVSID(string inVSID) { if (inVSID == "") { return(""); } string retFlag = ""; try { using (MySqlConnection conn = new MySqlConnection(DB.mildew_database_connection_string)) { //open connection to database if (conn.State == ConnectionState.Open) { conn.Close(); } conn.Open(); string query = ""; query += "SELECT value_stream "; query += "FROM master_value_stream "; query += "WHERE(value_stream_id = '" + NJS_Helper.FormatStringforDB(inVSID) + "');"; MySqlCommand cmd = new MySqlCommand(query, conn); object obj = cmd.ExecuteScalar(); if (obj != null) { if (obj.ToString() != "") { retFlag = obj.ToString(); } } } } catch (Exception ex) { retFlag = ""; } return(retFlag); }
public static string ConfigureProductionGoals(string date) { string retStr = "ok"; try { using (MySqlConnection conn = new MySqlConnection(DB.connection_string)) { // open connection to database conn.Open(); string query = ""; int no_of_days_difference = 0; MySqlDataAdapter da = null; DataSet ds = null; MySqlCommand cmd = null; string dt = Convert.ToDateTime(date).ToString("yyyy-MM-dd"); DateTime selected_date = Convert.ToDateTime(dt); // check if rows added for the date selected query = "SELECT * FROM admin_heijuhka_setup WHERE (DATE(shift_start_dt) = '" + dt + "'); "; da = new MySqlDataAdapter(query, conn); ds = new DataSet(); ds.Clear(); da.Fill(ds, "results"); if (ds.Tables["results"].Rows.Count == 0) { //get last data query = "SELECT DISTINCT(CAST(shift_start_dt as DATE)) AS date FROM admin_heijuhka_setup ORDER BY shift_start_dt DESC LIMIT 1"; da = new MySqlDataAdapter(query, conn); ds = new DataSet(); ds.Clear(); da.Fill(ds, "results_row"); if (ds.Tables["results_row"].Rows.Count > 0) { string last_date = ""; foreach (DataRow row in ds.Tables["results_row"].Rows) { last_date = Convert.ToDateTime(row["date"]).ToString("yyyy-MM-dd"); DateTime date1 = Convert.ToDateTime(last_date); TimeSpan difference = selected_date - date1; var days = difference.TotalDays; no_of_days_difference = Convert.ToInt32(days); //last entered data values in database string query1 = "SELECT * FROM admin_heijuhka_setup WHERE (DATE(shift_start_dt) = '" + last_date + "')"; da = new MySqlDataAdapter(query1, conn); ds = new DataSet(); ds.Clear(); da.Fill(ds, "lastdate_results"); if (ds.Tables["lastdate_results"].Rows.Count > 0) { foreach (DataRow row1 in ds.Tables["lastdate_results"].Rows) { string shift = row1["shift"].ToString(); string value_stream_id = row1["value_stream"].ToString(); DateTime dt1 = new DateTime(); DateTime shift_start = new DateTime(); DateTime shift_end = new DateTime(); dt1 = Convert.ToDateTime(row1["datetime"].ToString()).AddDays(no_of_days_difference); shift_start = Convert.ToDateTime(row1["shift_start_dt"].ToString()).AddDays(no_of_days_difference); shift_end = Convert.ToDateTime(row1["shift_end_dt"].ToString()).AddDays(no_of_days_difference); //else if (selected_date < date1) //{ // dt1 = Convert.ToDateTime(row1["datetime"].ToString()).AddDays(-no_of_days_difference); // shift_start = Convert.ToDateTime(row1["shift_start_dt"].ToString()).AddDays(-no_of_days_difference); // shift_end = Convert.ToDateTime(row1["shift_end_dt"].ToString()).AddDays(-no_of_days_difference); //} query = ""; query += "INSERT INTO admin_heijuhka_setup VALUES "; query += "("; query += "NULL, "; query += "'" + NJS_Helper.FormatStringforDB(shift) + "', "; query += "'" + NJS_Helper.FormatStringforDB(value_stream_id) + "', "; query += "'" + NJS_Helper.FormatStringforDB(dt1.ToString("yyyy-MM-dd HH:mm:ss")) + "', "; query += "'" + NJS_Helper.FormatStringforDB(shift_start.ToString("yyyy-MM-dd HH:mm:ss")) + "', "; query += "'" + NJS_Helper.FormatStringforDB(shift_end.ToString("yyyy-MM-dd HH:mm:ss")) + "' "; query += ") "; cmd = new MySqlCommand(query, conn); int rows = cmd.ExecuteNonQuery(); } } } } } } } catch (Exception ex) { retStr = ex.Message; } return(retStr); }
static public void InsertIntoMildew(string paint_auto_id) { try { using (MySqlConnection conn = new MySqlConnection(DB.connection_string)) { using (MySqlConnection mildew_conn = new MySqlConnection(DB.mildew_database_connection_string)) { conn.Open(); mildew_conn.Open(); string query = ""; MySqlCommand cmd = null; string mildew_auto_id = ""; query = "SELECT auto_id FROM mildew_tracker WHERE (from_app = '" + App_Helper.GetAppName() + "' AND from_app_auto_id = " + paint_auto_id + ");"; cmd = new MySqlCommand(query, mildew_conn); object obj1 = cmd.ExecuteScalar(); if (obj1 != null) { if (obj1.ToString() != "") { mildew_auto_id = obj1.ToString(); } } query = "SELECT * FROM paint_tracker WHERE (auto_id = " + paint_auto_id + ");"; MySqlDataAdapter da = new MySqlDataAdapter(query, conn); DataSet ds = new DataSet(); ds.Clear(); da.Fill(ds, "results"); foreach (DataRow row in ds.Tables["results"].Rows) { string value_stream_id = row["value_stream_id"].ToString(); string emp_no_takedown = row["emp_no_takedown"].ToString(); string dt_takedown = row["dt_takedown"].ToString(); string part_no = row["part_no"].ToString(); string qty_rejected = row["qty_rejected"].ToString(); string job_order_no = row["job_order_no"].ToString(); string eco_nmr_psr_di_no = row["nmr_no"].ToString(); string sequence_no = row["sequence_no"].ToString(); string color = row["paint_color"].ToString(); string reject_code = row["rejection_code"].ToString(); string first_why_id = ""; if (reject_code != "") { string first_why_query = "SELECT * FROM master_paint_reject_first_why WHERE (reject_code = " + reject_code + ");"; MySqlDataAdapter first_why_da = new MySqlDataAdapter(first_why_query, mildew_conn); DataSet first_why_ds = new DataSet(); first_why_ds.Clear(); first_why_da.Fill(first_why_ds, "first_why_results"); foreach (DataRow first_why_row in first_why_ds.Tables["first_why_results"].Rows) { first_why_id = first_why_row["first_why_id"].ToString(); } } string direct_cause_detail = row["rejection_notes"].ToString(); string direct_cause_id = ""; if (first_why_id != "") { string direct_cause_id_query = "SELECT direct_cause_id FROM master_first_whys WHERE (first_why_id = " + first_why_id + ");"; MySqlDataAdapter direct_cause_da = new MySqlDataAdapter(direct_cause_id_query, mildew_conn); DataSet direct_cause_ds = new DataSet(); direct_cause_ds.Clear(); direct_cause_da.Fill(direct_cause_ds, "direct_cause_results"); foreach (DataRow direct_cause_row in direct_cause_ds.Tables["direct_cause_results"].Rows) { direct_cause_id = direct_cause_row["direct_cause_id"].ToString(); } } string gFrom_app_auto_id = row["auto_id"].ToString(); if (emp_no_takedown == "") { emp_no_takedown = "0"; } if (part_no == "") { part_no = "null"; } if (job_order_no == "") { job_order_no = "null"; } if (eco_nmr_psr_di_no == "") { eco_nmr_psr_di_no = "null"; } if (sequence_no == "") { sequence_no = "null"; } if (color == "") { color = "null"; } if (first_why_id == "") { first_why_id = "0"; } if (direct_cause_detail == "") { direct_cause_detail = "null"; } string mildew_query = ""; if (mildew_auto_id == "") { mildew_query = "INSERT INTO mildew_tracker (value_stream_id, requester_id, requested_date_time, part_no, "; mildew_query += "quantity, job_order_no, eco_nmr_psr_di_no, sequence_no, color, direct_cause_id, first_why_id, direct_cause_detail, "; mildew_query += "supplier_id, location, owner_id, promise_date_time, actual_date_time, root_cause_department_id, root_cause_team_id, "; mildew_query += "closed_by_id, last_updated_date_time, from_app, from_app_auto_id) "; mildew_query += " VALUES "; mildew_query += " ( "; mildew_query += "" + NJS_Helper.FormatStringforDB(value_stream_id) + ", "; mildew_query += "" + NJS_Helper.FormatStringforDB(emp_no_takedown) + ", "; mildew_query += "'" + Convert.ToDateTime(dt_takedown).ToString("yyyy-MM-dd HH:mm:ss") + "', "; mildew_query += "'" + NJS_Helper.FormatStringforDB(part_no) + "', "; mildew_query += "" + NJS_Helper.FormatStringforDB(qty_rejected) + ", "; mildew_query += "'" + NJS_Helper.FormatStringforDB(job_order_no) + "', "; if (eco_nmr_psr_di_no == "null") { mildew_query += "'" + NJS_Helper.FormatStringforDB(eco_nmr_psr_di_no) + "', "; } else { mildew_query += "'NMR (" + NJS_Helper.FormatStringforDB(eco_nmr_psr_di_no) + ")', "; } mildew_query += "'" + NJS_Helper.FormatStringforDB(sequence_no) + "', "; mildew_query += "'" + NJS_Helper.FormatStringforDB(color) + "', "; mildew_query += "" + NJS_Helper.FormatStringforDB(direct_cause_id) + ", "; mildew_query += "" + NJS_Helper.FormatStringforDB(first_why_id) + ", "; mildew_query += "'" + NJS_Helper.FormatStringforDB(direct_cause_detail) + "', "; mildew_query += "1, "; mildew_query += "'4326', "; mildew_query += "1, "; mildew_query += "'" + Convert.ToDateTime(dt_takedown).ToString("yyyy-MM-dd HH:mm:ss") + "', "; mildew_query += "'" + Convert.ToDateTime(dt_takedown).ToString("yyyy-MM-dd HH:mm:ss") + "', "; mildew_query += "1, "; mildew_query += "1, "; mildew_query += "" + NJS_Helper.FormatStringforDB(emp_no_takedown) + ", "; mildew_query += "'" + Convert.ToDateTime(DateTime.Now).ToString("yyyy-MM-dd HH:mm:ss") + "', "; mildew_query += "'" + GetAppName() + "', "; mildew_query += "" + NJS_Helper.FormatStringforDB(gFrom_app_auto_id) + " "; mildew_query += ")"; } else if (mildew_auto_id != "") { mildew_query = "UPDATE mildew_tracker SET "; mildew_query += "value_stream_id = " + NJS_Helper.FormatStringforDB(value_stream_id) + ", "; mildew_query += "requester_id = " + NJS_Helper.FormatStringforDB(emp_no_takedown) + ", "; mildew_query += "requested_date_time = '" + Convert.ToDateTime(dt_takedown).ToString("yyyy-MM-dd HH:mm:ss") + "', "; mildew_query += "part_no = '" + NJS_Helper.FormatStringforDB(part_no) + "', "; mildew_query += "quantity = " + NJS_Helper.FormatStringforDB(qty_rejected) + ", "; mildew_query += "job_order_no = '" + NJS_Helper.FormatStringforDB(job_order_no) + "', "; if (eco_nmr_psr_di_no == "null") { mildew_query += "eco_nmr_psr_di_no = '" + NJS_Helper.FormatStringforDB(eco_nmr_psr_di_no) + "', "; } else { mildew_query += "eco_nmr_psr_di_no = 'NMR (" + NJS_Helper.FormatStringforDB(eco_nmr_psr_di_no) + ")', "; } mildew_query += "sequence_no = '" + NJS_Helper.FormatStringforDB(sequence_no) + "', "; mildew_query += "color = '" + NJS_Helper.FormatStringforDB(color) + "', "; mildew_query += "direct_cause_id = " + NJS_Helper.FormatStringforDB(direct_cause_id) + ", "; mildew_query += "first_why_id = " + NJS_Helper.FormatStringforDB(first_why_id) + ", "; mildew_query += "direct_cause_detail = '" + NJS_Helper.FormatStringforDB(direct_cause_detail) + "', "; mildew_query += "supplier_id = 1, "; mildew_query += "location = '4327', "; mildew_query += "owner_id = 1, "; mildew_query += "promise_date_time = '" + Convert.ToDateTime(dt_takedown).ToString("yyyy-MM-dd HH:mm:ss") + "', "; mildew_query += "actual_date_time = '" + Convert.ToDateTime(dt_takedown).ToString("yyyy-MM-dd HH:mm:ss") + "', "; mildew_query += "root_cause_department_id = 1, "; mildew_query += "root_cause_team_id= 1, "; mildew_query += "closed_by_id = " + NJS_Helper.FormatStringforDB(emp_no_takedown) + ", "; mildew_query += "last_updated_date_time = '" + Convert.ToDateTime(DateTime.Now).ToString("yyyy-MM-dd HH:mm:ss") + "', "; mildew_query += "from_app = '" + GetAppName() + "', "; mildew_query += "from_app_auto_id = " + NJS_Helper.FormatStringforDB(gFrom_app_auto_id) + " "; mildew_query += "WHERE (auto_id = " + mildew_auto_id + ");"; } MySqlCommand mildew_cmd = new MySqlCommand(mildew_query, mildew_conn); int rows = mildew_cmd.ExecuteNonQuery(); } } } } catch (Exception ex) { //this.error.InnerHtml = ex.Message; } }