public static void InitTabs()
{
C_Db.Exec("create table if not exists grims.entwell( " +
"id int auto_increment, " + //0
"tsOrSt varchar(255), " + //1
"entName varchar(255), " + //2
"unitCat varchar(255), " + //3
"loc varchar(255), " + //4
"lng varchar(255), " + //5
"lat varchar(255), " + //6
"usefor varchar(255), " + //7
"digTime date, " + //8
"fetchWaterId varchar(255), " + //9
"isPaid bool, " + //10
"wellDepth float, " + //11
"tubeMat varchar(255), " + //12
"tubeIr float, " + //13
"stanWaterDepth float, " + //14
"saltWaterFloorDepth float, " + //15
"filterLocLow float, " + //16
"filterLocHigh float, " + //17
"stillWaterLoc float, " + //18
"pumpMode varchar(255), " + //19
"pumpPower float, " + //20
"isWaterLevelOp bool, " + //21
"isMfInstalled bool, " + //22
"remark varchar(255), " + //23
"primary key(Id) " +
")default charset = utf8mb4; ");
}
public static void InitTabs()
{
C_Db.Exec("create table if not exists grims.well( " +
"id int auto_increment, " + //0
"tsOrSt varchar(255), " + //1
"village varchar(255), " + //2
"unitCat varchar(255), " + //3
"loc varchar(255), " + //4
"lng varchar(255), " + //5
"lat varchar(255), " + //6
"usefor varchar(255), " + //7
"digTime date, " + //8
"wellDepth float, " + //9
"tubeMat varchar(255), " + //10
"tubeIr float, " + //11
"stanWaterDepth float, " + //12
"saltWaterFloorDepth float, " + //13
"filterLocLow float, " + //14
"filterLocHigh float, " + //15
"stillWaterLoc float, " + //16
"pumpMode varchar(255), " + //17
"pumpPower float, " + //18
"coverArea float, " + //19
"supPeopleNum int, " + //20
"isWaterLevelOp bool, " + //21
"isMfInstalled bool, " + //22
"linkWellNo int, " + //23
"isSeepChnLinked bool, " + //24
"seepChnLength float, " + //25
"remark varchar(255), " + //26
"primary key(Id) " +
")default charset = utf8mb4; ");
}
public static Tuple <E_DbRState, Exception> Change(C_EntWell well)
{
return(C_Db.Exec("update grims.entwell set TsOrSt='" + well.TsOrSt +
"', EntName='" + well.EntName +
"', UnitCat='" + well.UnitCat +
"', Loc='" + well.Loc +
"', Lng='" + well.Lng +
"', Lat='" + well.Lat +
"', Usefor='" + well.Usefor +
"', DigTime='" + well.DigTime.ToString("yyyy-MM-dd") +
"', FetchWaterId='" + well.FetchWaterId +
"', IsPaid=" + well.IsPaid +
", WellDepth=" + well.WellDepth +
", TubeMat='" + well.TubeMat +
"', TubeIr=" + well.TubeIr +
", StanWaterDepth=" + well.StanWaterDepth +
", SaltWaterFloorDepth=" + well.SaltWaterFloorDepth +
", FilterLocLow=" + well.FilterLocLow +
", FilterLocHigh=" + well.FilterLocHigh +
", StillWaterLoc=" + well.StillWaterLoc +
", PumpMode='" + well.PumpMode +
"', PumpPower=" + well.PumpPower +
", IsWaterLevelOp=" + well.IsWaterLevelOp +
", IsMfInstalled=" + well.IsMfInstalled +
", Remark='" + well.Remark +
"' where Id = '" + well.Id + "';"
));
}
public static Tuple <E_DbRState, Exception> Change(C_Well well)
{
return(C_Db.Exec(" update grims.well set " +
"TsOrSt ='" + well.TsOrSt + "'," +
"Village ='" + well.Village + "'," +
"UnitCat ='" + well.UnitCat + "'," +
"Loc ='" + well.Loc + "'," +
"Lng ='" + well.Lng + "'," +
"Lat ='" + well.Lat + "'," +
"Usefor ='" + well.Usefor + "'," +
"DigTime ='" + well.DigTime.ToString("yyyy-MM-dd") + "'," +
"WellDepth =" + well.WellDepth + "," +
"TubeMat ='" + well.TubeMat + "'," +
"TubeIr =" + well.TubeIr + "," +
"StanWaterDepth =" + well.StanWaterDepth + "," +
"SaltWaterFloorDepth=" + well.SaltWaterFloorDepth + "," +
"FilterLocLow =" + well.FilterLocLow + "," +
"FilterLocHigh =" + well.FilterLocHigh + "," +
"StillWaterLoc =" + well.StillWaterLoc + "," +
"PumpMode ='" + well.PumpMode + "'," +
"PumpPower =" + well.PumpPower + "," +
"CoverArea =" + well.CoverArea + "," +
"SupPeopleNum =" + well.SupPeopleNum + "," +
"IsWaterLevelOp =" + well.IsWaterLevelOp + "," +
"IsMfInstalled =" + well.IsMfInstalled + "," +
"LinkWellNo =" + well.LinkWellNo + "," +
"IsSeepChnLinked =" + well.IsSeepChnLinked + "," +
"SeepChnLength =" + well.SeepChnLength + "," +
"Remark ='" + well.Remark + "'" +
" where Id = " + well.Id + "" +
";"
));
}
public static Tuple <E_DbRState, Exception> Add(C_User user)
{
string cmd = "insert into grims.user (name,pwd,deptId,tel,email) values('" + user.Name + "'" +
",'" + user.Pwd +
"','" + C_DbTabDept.GetIdByName(user.DeptName).Item2 +
"','" + user.Tel +
"','" + user.Email +
"');";
return(C_Db.Exec(cmd));
}
public static void InitTabs()
{
C_Db.Exec("create table if not exists grims.user(" +
"id int auto_increment," +
"name varchar(255) not null unique," +
"pwd varchar(255) not null," +
"deptId int not null," +
"tel varchar(255)," +
"email varchar(255)," +
"primary key(id),foreign key(deptId) references grims.dept(id)" +
") default charset=utf8mb4;");
}
public static void InitTabs()
{
Tuple <E_DbRState, MySqlDataReader, Exception> QRes;
C_Db.Exec("create table if not exists grims.adminPwd(pwd varchar(255) not null) default charset=utf8mb4;");
QRes = C_Db.Query("select * from grims.adminPwd;");
if (QRes.Item1 == E_DbRState.Success)
{
if (!QRes.Item2.HasRows)
{
C_Db.Exec("insert into grims.AdminPwd (pwd) values('" + C_Md5.GetHash("123456") + "');");
}
}
}
public static Tuple <E_DbRState, Exception> ResetPwd(string oldPwd, string newPwd)
{
Tuple <E_DbRState, MySqlDataReader, Exception> QRes = C_Db.Query("select * from grims.adminPwd;");
if (QRes.Item1 == E_DbRState.Failed)
{
return(new Tuple <E_DbRState, Exception>(QRes.Item1, QRes.Item3));
}
QRes.Item2.Read();
if (oldPwd == QRes.Item2.GetString("pwd"))
{
return(C_Db.Exec("update grims.adminPwd set pwd='" + newPwd + "';"));
}
else
{
return(new Tuple <E_DbRState, Exception>(E_DbRState.ErrorPwd, null));
}
}
public static Tuple <E_DbRState, Exception> Change(C_WellPara oldWp, C_WellPara newWp)
{
return(C_Db.Exec("update grims.wellpara set paraType='" + newWp.Type + "' , paraValue='" + newWp.Value + "' where paraType='" + oldWp.Type + "' and paraValue='" + oldWp.Value + "';"));
}
public static Tuple <E_DbRState, Exception> Delete(C_WellPara wellPara)
{
return(C_Db.Exec("delete from grims.wellpara where paraType='" + wellPara.Type + "' and paraValue='" + wellPara.Value + "';"));
}
public static Tuple <E_DbRState, Exception> Add(C_WellPara wellPara)
{
return(C_Db.Exec("insert into grims.wellpara (paraType,paraValue) values('" + wellPara.Type + "','" + wellPara.Value + "');"));
}
public static void InitTabs()
{
C_Db.Exec("create table if not exists grims.wellpara(paraType varchar(255),paraValue varchar(255) unique)default charset = utf8mb4;");
}
public static Tuple <E_DbRState, Exception> Delete(C_Well well)
{
return(C_Db.Exec("delete from grims.well where Id='" + well.Id.ToString() + "';"));
}
//OUT
public static Tuple <E_DbRState, Exception> Delete(string deptName)
{
return(C_Db.Exec("delete from grims.dept where deptName='" + deptName + "';"));
}
public static Tuple <E_DbRState, Exception> Add(List <C_EntWell> wells)
{
string cmd = "insert into grims.entwell ( TsOrSt," + //1
"EntName," + //2
"UnitCat," + //3
"Loc," + //4
"Lng," + //5
"Lat," + //6
"Usefor," + //7
"DigTime," + //8
"FetchWaterId," + //9
"IsPaid," + //10
"WellDepth," + //11
"TubeMat," + //12
"TubeIr," + //13
"StanWaterDepth," + //14
"SaltWaterFloorDepth," + //15
"FilterLocLow," + //16
"FilterLocHigh," + //17
"StillWaterLoc," + //18
"PumpMode," + //19
"PumpPower," + //20
"IsWaterLevelOp," + //21
"IsMfInstalled," + //22
"Remark" + //23
")values";
string tmp;
for (int i = 0; i < wells.Count; ++i)
{
tmp = "('" +
wells[i].TsOrSt + "','" + //1
wells[i].EntName + "','" + //2
wells[i].UnitCat + "','" + //3
wells[i].Loc + "','" + //4
wells[i].Lng + "','" + //5
wells[i].Lat + "','" + //6
wells[i].Usefor + "','" + //7
wells[i].DigTime.ToString("yyyy-MM-dd") + "','" + //8
wells[i].FetchWaterId + "'," + //9
wells[i].IsPaid + "," + //10
wells[i].WellDepth + ",'" + //11
wells[i].TubeMat + "'," + //12
wells[i].TubeIr + "," + //13
wells[i].StanWaterDepth + "," + //14
wells[i].SaltWaterFloorDepth + "," + //15
wells[i].FilterLocLow + "," + //16
wells[i].FilterLocHigh + "," + //17
wells[i].StillWaterLoc + ",'" + //18
wells[i].PumpMode + "'," + //19
wells[i].PumpPower + "," + //20
wells[i].IsWaterLevelOp + "," + //21
wells[i].IsMfInstalled + ",'" + //22
wells[i].Remark + //23
"')";
if (i == wells.Count - 1)
{
tmp += ";";
}
else
{
tmp += ",";
}
cmd += tmp;
}
return(C_Db.Exec(cmd));
}
public static void InitTabs()
{
C_Db.Exec("create table if not exists grims.dept(id int auto_increment,deptName varchar(255) not null unique,primary key(id)) default charset=utf8mb4;");
}
public static Tuple <E_DbRState, Exception> Change(C_User user)
{
return(C_Db.Exec("update grims.user set name='" + user.Name + "',pwd='" + user.Pwd + "',deptId='" + C_DbTabDept.GetIdByName(user.DeptName).Item2 + "',tel='" + user.Tel + "',email='" + user.Email + "' where id='" + user.Id + "';"));
}
public static Tuple <E_DbRState, Exception> Delete(C_User user)
{
return(C_Db.Exec("delete from grims.user where id='" + user.Id.ToString() + "'"));
}
public static Tuple <E_DbRState, Exception> Add(List <C_Well> wells)
{
string cmd = "insert into grims.well ( TsOrSt," + //1
"Village," + //2
"UnitCat," + //3
"Loc," + //4
"Lng," + //5
"Lat," + //6
"Usefor," + //7
"DigTime," + //8
"WellDepth," + //9
"TubeMat," + //10
"TubeIr," + //11
"StanWaterDepth," + //12
"SaltWaterFloorDepth," + //13
"FilterLocLow," + //14
"FilterLocHigh," + //15
"StillWaterLoc," + //16
"PumpMode," + //17
"PumpPower," + //18
"CoverArea," + //19
"SupPeopleNum," + //20
"IsWaterLevelOp," + //21
"IsMfInstalled," + //22
"LinkWellNo," + //23
"IsSeepChnLinked," + //24
"SeepChnLength," + //25
"Remark" + //26
")values";
string tmp;
for (int i = 0; i < wells.Count; ++i)
{
tmp = "('" +
wells[i].TsOrSt + "','" + //1
wells[i].Village + "','" + //2
wells[i].UnitCat + "','" + //3
wells[i].Loc + "','" + //4
wells[i].Lng + "','" + //5
wells[i].Lat + "','" + //6
wells[i].Usefor + "','" + //7
wells[i].DigTime.ToString("yyyy-MM-dd") + "'," + //8
wells[i].WellDepth + ",'" + //9
wells[i].TubeMat + "'," + //10
wells[i].TubeIr + "," + //11
wells[i].StanWaterDepth + "," + //12
wells[i].SaltWaterFloorDepth + "," + //13
wells[i].FilterLocLow + "," + //14
wells[i].FilterLocHigh + "," + //15
wells[i].StillWaterLoc + ",'" + //16
wells[i].PumpMode + "'," + //17
wells[i].PumpPower + "," + //18
wells[i].CoverArea + "," + //19
wells[i].SupPeopleNum + "," + //20
wells[i].IsWaterLevelOp + "," + //21
wells[i].IsMfInstalled + "," + //22
wells[i].LinkWellNo + "," + //23
wells[i].IsSeepChnLinked + "," + //24
wells[i].SeepChnLength + ",'" + //25
wells[i].Remark + //26
"')";
if (i == wells.Count - 1)
{
tmp += ";";
}
else
{
tmp += ",";
}
cmd += tmp;
}
return(C_Db.Exec(cmd));
}
//OUT
public static Tuple <E_DbRState, Exception> Add(string deptName)
{
return(C_Db.Exec("insert into grims.dept (deptName) values('" + deptName + "');"));
}
//OUT
public static Tuple <E_DbRState, Exception> Change(string oldDeptName, string newDeptName)
{
return(C_Db.Exec("update grims.dept set deptName='" + newDeptName + "' where deptName='" + oldDeptName + "';"));
}