//----------------------------------------------- private bool IsValid_IDENTIFIER(iLocalDB db, string id) { if (string.IsNullOrEmpty(id)) { return(Error("학번이 null이거나 빈문자열입니다.")); } if (id.Length != 8) { return(Error("학번의 길이는 8문자이어야 합니다")); } if (IsZero(id.Substring(5, 3)) == false) { return(Error("학번의 일련번호 000은 허용되지 않습니다.")); } if (IsNumericString(id.Substring(0, 2)) == false) { return(Error("학번의 처음 두 문자는 년도를 나타내야 합니다.(예: 19)")); } if (IsValidDeptCode(db, id.Substring(2, 3)) == false) { return(Error("학번의 3,4,5번째 문자에 일치하는 학과코드가 없습니다.")); } if (IsNumericString(id.Substring(5, 3)) == false) { return(Error("학번의 마지막 세 문자는 일련번호여야 합니다.(예: 001)")); } return(true); }
//--------------------------------------------------- static Int32 makeNewPitcher(iLocalDB db, string yymmdd, string choice) { string query = "SELECT Max(xNumber) AS MaxNum FROM PITCHER"; DB_Query(db, query); if (db.HasRows == false) return 1; db.Read(); string maxNumStr = db.GetData("MaxNum").ToString(); Int32 maxNumber; if (string.IsNullOrEmpty(maxNumStr)) { maxNumber = 1; } else { maxNumber = Convert.ToInt32(db.GetData("MaxNum").ToString()) + 1; } double recipeAmount = getRecipeAmount(db, choice); query = string.Format("INSERT INTO PITCHER VALUES({0},'{1}','{2}',{3})", maxNumber, yymmdd, choice, recipeAmount); DB_Query(db, query); Console.WriteLine("new Pitcher {0}: '{1}' made", maxNumber, choice); return maxNumber; }
//---------------------------------------------- //---------------------------------------------- private bool IsValid_IDENTIFIER(iLocalDB db, string 학번) { if (string.IsNullOrEmpty(학번)) { return(Error("학번이 null이거나 빈문자열입니다.")); } if (학번.Length != 8) { return(Error("학번의 길이는 8문자이어야 합니다")); } if (IsNumericString(학번.Substring(0, 2)) == false) { return(Error("학번의 처음 두 문자는 년도를 나타내야 합니다.(예: 19)")); } if (학번.Substring(5, 3).Equals("000")) { return(Error("000은 올바른 학번의 일련번호가 아닙니다")); } if (IsNumericString(학번.Substring(5, 3)) == false) { return(Error("학번의 마지막 세 문자는 일련번호여야 합니다.(예: 001)")); } if (IsValidDeptCode(db, 학번.Substring(2, 3)) == false) { return(Error("학번의 3,4,5번째 문자에 일치하는 학과코드가 없습니다.")); } if (IsSameDeptCode(db, 학번.Substring(2, 3)) == false) { return(Error("학번과 학과가 일치하지 않습니다.")); } return(true); }
//---------------------------------------------- //---------------------------------------------- private bool IsSameDeptCode(iLocalDB db, string code) { db.Open(); string query = string.Format("SELECT xDept_name FROM xDEPARTMENT WHERE xDept_code='{0}'", code); // {0}에는 code 값이 들어감 string err_msg = db.Query(query); string title = ""; if (err_msg != null) { MessageBox.Show(query + "\n\n" + err_msg, "SQL Error"); return(false); } db.ExecuteReader(query); while (db.Read()) { title = db.GetData("xDept_name").ToString(); } if (title != cbx_학과입력.SelectedValue.ToString()) //입력 학과랑 학번이랑 틀림 { return(false); } else { return(true); } }
//----------------------------------------------- public int insertStudent(iLocalDB db, string hakbun, string name, string pass, string pass_Confirm, string dept, int status, string addr, string email, string phone) { // 학번 유효성검사 if (IsValid_IDENTIFIER(db, hakbun) == false) { return(0); } if (IsNewHakbun(db, hakbun) == false) { return(0); } // 이름 유효성검사 if (IsValid_PEOPLE_NAME(name) == false) { return(0); } // 비밀번호 유효성검사 if (IsValid_PASSWORD(pass, pass_Confirm) == false) { return(0); } // 학과 유효성검사 if (IsValid_CLASS(dept) == false) { return(0); } // 상태 유효성검사 if (IsValid_STATUS(status) == false) { return(0); } // 주소 유효성검사 if (IsValid_HOUSE_ADDRESS(addr) == false) { return(0); } // 이메일 유효성검사 if (IsValid_EMAIL_ADDRESS(email) == false) { return(0); } // 전화번호 유효성검사 if (IsValid_PHONE_NUMBER(phone) == false) { return(0); } string str = string.Format("'{0}', '{1}', '{2}', '{3}', {4}, '{5}', '{6}', '{7}'", hakbun, name, pass, dept, status, addr, email, phone); return(insertRow(db, "xSTUDENTS", str)); }
}//Main //-------------------------------------------------- static bool? tableExists(iLocalDB db, string tableName) { string result = db.Query(string.Format("select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME='{0}'", tableName)); if (result != null) return null; return db.HasRows; }
//----------------------------------------------- static string getSalesPerson(iLocalDB db, string hhmm) { Int32 hh = Convert.ToInt32(hhmm.Substring(0, 2)); if (hh < 11) return "Sally"; if (hh < 12) return "Hulk"; if (hh < 13) return "Spiderman"; if (hh < 14) return "Black Widow"; return "Sally"; }
//---------------------------------------------- //---------------------------------------------- static bool DB_Query(iLocalDB db, string query) { string err_msg = db.Query(query); if (err_msg != null) { MessageBox.Show("Error\n" + err_msg + "\n" + query); return(false); } return(true); }
//------------------------------------------------------ static int insertRow(iLocalDB db, string tableName, string dataStr) { string queryStr = string.Format("INSERT INTO {0} VALUES({1})", tableName, dataStr); if (!DB_Query(db, queryStr)) return 0; Console.WriteLine("row inserted into '{0}'.", tableName); return 1; //OK }
//--------------------------------------------------- static double getPitcherAmount(iLocalDB db, Int32 pitcherNumber) { string query = string.Format("SELECT xAmount From PITCHER WHERE xNumber={0}", pitcherNumber); DB_Query(db, query); db.Read(); double xAmount = Convert.ToDouble(db.GetData("xAmount").ToString()); return xAmount; }
//------------------------------------------------ static bool DB_Query(iLocalDB db, string query) { string err_msg = db.Query(query); if (err_msg != null) { Console.WriteLine("Error\n" + err_msg + "\n" + query); return false; } return true; }
//----------------------------------------------- static bool IsValid_IDENTIFIER(iLocalDB db, string id) { if (string.IsNullOrEmpty(id)) return Error("학번이 null이거나 빈문자열입니다."); if (id.Length != 8) return Error("학번의 길이는 8문자이어야 합니다"); if (IsNumericString(id.Substring(0, 2)) == false) return Error("학번의 처음 두 문자는 년도를 나타내야 합니다.(예: 19)"); if (id.Substring(5, 3).Equals("000")) return Error("'000'은 올바른 학번의 일련번호가 아닙니다"); if (IsNumericString(id.Substring(5, 3)) == false) return Error("학번의 마지막 세 문자는 일련번호여야 합니다.(예: 001)"); if (IsValidDeptCode(db, id.Substring(2, 3)) == false) return Error("학번의 3,4,5번째 문자에 일치하는 학과코드가 없습니다."); return true; }
//--------------------------------------------------- static Int32 getPitcher(iLocalDB db, string choice) { string query = string.Format("SELECT xNumber FROM PITCHER WHERE Recipe_used='{0}' AND xAmount > 0", choice); DB_Query(db, query); if (db.HasRows == false) return 0; db.Read(); Int32 xNumber = Convert.ToInt32(db.GetData("xNumber").ToString()); return xNumber; }
//------------------------------------------------------ private int insertRow(iLocalDB db, string tableName, string dataStr) { string queryStr = string.Format("INSERT INTO {0} VALUES({1})", tableName, dataStr); if (!DB_Query(db, queryStr)) { return(0); } //MessageBox.Show("완료되었습니다."); return(1); //OK }
//------------------------------------------------ static void insertOrder(iLocalDB db, string fName, string lName, string choice, string yymmdd, string hhmm, int pitcher, double serviceAmount) { //--Insert order into ORDER table ---------------- string salesPerson = getSalesPerson(db, hhmm); string query = string.Format("INSERT INTO xORDER VALUES('{0}','{1}',{2},'{3}','{4}','{5}',{6})", fName, lName, pitcher, salesPerson, yymmdd, hhmm, serviceAmount); DB_Query(db, query); Console.WriteLine("Order Inserted: ('{0}','{1}',{2},'{3}','{4}','{5}',{6})", fName, lName, pitcher, salesPerson, yymmdd, hhmm, serviceAmount); }
//----------------------------------------------- static bool IsValidDeptCode(iLocalDB db, string code) { string query = string.Format("SELECT * FROM xDEPARTMENT WHERE xDept_code='{0}'", code); string res = db.Query(query); if(res != null) { Console.WriteLine(res); return false; } if (db.HasRows) return true; return false; }
//--------------------------------------------------- static bool IsNewHakbun(iLocalDB db, string hakbun) { string query = string.Format("SELECT xHakbun FROM xSTUDENTS WHERE xHakbun='{0}'", hakbun); string res = db.Query(query); if (res != null) { Console.WriteLine(res); return false; } if (db.HasRows) return Error("같은 학번이 이미 존재합니다." + hakbun); return true; }
//----------------------------------------------- static int insertStudent(iLocalDB db, string hakbun, string name, string pass, string dept, int status, string addr, string email, string phone) { hakbun = hakbun.Trim(); if (IsValid_IDENTIFIER(db, hakbun) == false) return 0; if (IsNewHakbun(db, hakbun) == false) return 0; //새 학생 추가인 경우에만 실행 if (IsValid_PEOPLE_NAME(name) == false) return 0; if (IsValid_PASSWORD(pass) == false) return 0; string str = string.Format("'{0}', '{1}', '{2}', '{3}', {4}, '{5}', '{6}', '{7}'", hakbun, name, pass, dept, status, addr, email, phone); return insertRow(db, "xSTUDENTS", str); }
//----------------------------------------------- private bool IsNewHakbun(iLocalDB db, string hakbun) { string query = string.Format("select xHakbun from xSTUDENTS where xHakbun='{0}'", hakbun); string res = db.Query(query); if (res != null) { return(false); } if (db.HasRows) { return(Error("같은 학번이 이미 존재합니다." + hakbun)); } return(true); }
//----------------------------------------------------- static double getRecipeAmount(iLocalDB db, string recipeName) { string query = string.Format("SELECT Water From RECIPE WHERE Name='{0}'", recipeName); DB_Query(db, query); if (db.HasRows == false) { Console.WriteLine("Receipe name error"); Environment.Exit(1); } db.Read(); double waterAmount = Convert.ToDouble(db.GetData("Water").ToString()); return waterAmount; }
//------------------------------------------------ static void Add_if_New_Customer(iLocalDB db, string fName, string lName) { string query = string.Format("SELECT * FROM CUSTOMER WHERE F_name='{0}' AND L_name='{1}'", fName, lName); DB_Query(db, query); if (db.HasRows == false) { query = string.Format("INSERT INTO CUSTOMER VALUES('{0}','{1}')", fName, lName); DB_Query(db, query); Console.WriteLine("Customer '{0}', '{1}' added.", fName, lName); } }
//------------------------------------------------------ static void dropTables(iLocalDB db) { int cnt = 0; cnt += dropTable(db, "xSTUDENTS"); cnt += dropTable(db, "xDEPARTMENT"); cnt += dropTable(db, "xSTUDENT_STATUS"); cnt += dropTable(db, "xTEACHERS"); cnt += dropTable(db, "xENROLL_CLASS"); cnt += dropTable(db, "xOPEN_CLASS"); cnt += dropTable(db, "xCURRICULUM"); cnt += dropTable(db, "xCLASSES"); Console.WriteLine("{0} tables dropped.\n", cnt); }
//----------------------------------------------- private bool IsValidDeptCode(iLocalDB db, string code) { string query = string.Format("SELECT * FROM xDEPARTMENT WHERE xDept_code='{0}'", code); string res = db.Query(query); if (res != null) { MessageBox.Show(res); return(false); } if (db.HasRows) { return(true); } return(false); }
//---------------------------------------------- //---------------------------------------------- private bool IsNewHackbun(iLocalDB db, string 학번) { string query = string.Format("SELECT xHakbun FROM xSTUDENTS WHERE xHakbun='{0}'", 학번); // {0}에는 code 값이 들어감 string res = db.Query(query); if (res == "") { MessageBox.Show(res); return(false); } else if (db.HasRows) { return(Error("같은 학번이 이미 존재합니다" + 학번)); } else { return(true); } }
//---------------------------------------------- //---------------------------------------------- private int insertStudent(iLocalDB db, string 학번, string 이름, string 비밀번호, string 학과, int 상태코드, string 주소, string 이메일, string 전화) { if (IsValid_IDENTIFIER(db, 학번) == false) { return(0); } if (IsNewHackbun(db, 학번) == false) { return(0); } if (IsValid_PEOPLE_NAME(이름) == false) { return(0); } if (IsValid_PASSWORD(비밀번호) == false) { return(0); } if (IsValid_HOUSE_ADDRESS(주소) == false) { return(0); } if (IsValid_EMAIL_ADDRESS(이메일) == false) { return(0); } if (IsValid_PHONE_NUMBER(전화) == false) { return(0); } string str = string.Format("'{0}', '{1}', '{2}', '{3}', {4}, '{5}', '{6}', '{7}'", 학번, 이름, 비밀번호, 학과, 상태코드, 주소, 이메일, 전화); return(insertRow(db, "xSTUDENTS", str)); }
//---------------------------------------------- //---------------------------------------------- private int insertRow(iLocalDB db, string tableName, string dataStr) { db.Open(); string queryStr = string.Format("INSERT INTO {0} VALUES({1})", tableName, dataStr); if (!DB_Query(db, queryStr)) { return(0); } MessageBox.Show("정상적으로 데이터가 입력되었습니다."); psBox_비밀번호.Visibility = Visibility.Hidden; reloadInfo(); All_text_clear(); db.Close(); return(1); //OK }
//------------------------------------------------------ static void dropTables(iLocalDB db) { int cnt = 0; cnt += dropTable(db, "PITCHER"); cnt += dropTable(db, "RECIPE"); //cnt += dropTable(db, "PITCHER"); cnt += dropTable(db, "xORDER"); cnt += dropTable(db, "SALESPERSON"); //cnt += dropTable(db, "xORDER"); cnt += dropTable(db, "NICKNAME"); cnt += dropTable(db, "CUST_SPORT"); cnt += dropTable(db, "CLASS_ATTRIBUTE"); cnt += dropTable(db, "CUSTOMER"); Console.WriteLine("{0} tables dropped.\n", cnt); }
//--------------------------------------------------- static void order(iLocalDB db, string fName, string lName, string choice, string yymmdd, string hhmm, double orderAmount) { Add_if_New_Customer(db, fName, lName); Int32 current_Pitcher = getPitcher(db, choice); Console.WriteLine("ordered PitcherNumber: {0}", current_Pitcher); //해당하는 Pitcher가 없으면 새로 제조 if (current_Pitcher == 0) current_Pitcher = makeNewPitcher(db, yymmdd, choice); //Pitcher가 있으면 주문양이 충분한지 체크하고 충분치 않으면 새로 제조 double currentAmount = getPitcherAmount(db, current_Pitcher); if (currentAmount < orderAmount) { Int32 new_Pitcher; double newAmount; new_Pitcher = makeNewPitcher(db, yymmdd, choice); newAmount = getPitcherAmount(db, new_Pitcher); updatePitcherAmount(db, current_Pitcher, 0.0); updatePitcherAmount(db, new_Pitcher, newAmount - (orderAmount - currentAmount)); insertOrder(db, fName, lName, choice, yymmdd, hhmm, current_Pitcher, currentAmount); insertOrder(db, fName, lName, choice, yymmdd, hhmm, new_Pitcher, (orderAmount - currentAmount)); } else { updatePitcherAmount(db, current_Pitcher, currentAmount - orderAmount); insertOrder(db, fName, lName, choice, yymmdd, hhmm, current_Pitcher, orderAmount); } }
//---------------------------------------------------- static int dropTable(iLocalDB db, string tableName) { //bool? res = tableExists(db, tableName); //if (res == null) //{ // Console.WriteLine("### Query Error in func dropTable.tableExists()..."); // return 0; //} //else if (res == false) //{ // Console.WriteLine("### Table '{0}'은 존재하지 않습니다.", tableName); // return 0; //} string queryStr = string.Format("DROP TABLE {0}", tableName); if (!DB_Query(db, queryStr)) return 0; Console.WriteLine("Table '{0}' dropped.", tableName); return 1; }
//---------------------------------------------------- static int createTable(iLocalDB db, string tableName, string tableDesign) { //bool? res = tableExists(db, tableName); //if (res == null) //{ // Console.WriteLine("### Query Error in func createTable.tableExists()..."); // return 0; //} //else if (res == true) //{ // Console.WriteLine("### Table '{0}'은 이미 존재합니다.", tableName); // return 0; //} string queryStr = string.Format("CREATE TABLE {0} ({1})", tableName, tableDesign); if (DB_Query(db, queryStr) == false) return 0; Console.WriteLine("Table '{0}' Created.", tableName); return 1; }