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