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