internal static PGDatabase GetDatabase()
{
PGDatabase database = new PGDatabase();
Collection <PGDatabase.PGDatabaseSetting> settings = new Collection <PGDatabase.PGDatabaseSetting>();
string sql = FileHelper.ReadSqlResource("db.sql");
using (NpgsqlCommand command = new NpgsqlCommand(sql))
{
using (DataTable table = DbOperation.GetDataTable(command))
{
if (table.Rows.Count > 0)
{
foreach (DataRow row in table.Rows)
{
PGDatabase.PGDatabaseSetting setting = new PGDatabase.PGDatabaseSetting
{
Name = Conversion.TryCastString(row["name"]),
Setting = Conversion.TryCastString(row["setting"]),
Description = Conversion.TryCastString(row["description"])
};
settings.Add(setting);
}
database.Comment = GetDatabaseComment();
database.Settings = settings;
}
}
}
return(database);
}
private static void BuildDocumentation(string content, IEnumerable <string> matches)
{
PGDatabase database = DbProcessor.GetDatabase();
content = content.Replace("[DBName]", Program.Database.ToUpperInvariant());
content = content.Replace("[DBComment]", database.Comment);
foreach (string match in matches)
{
string comment = HtmlHelper.RemoveComment(match);
if (!comment.StartsWith("PostgreSQLDatabase"))
{
continue;
}
comment = comment.Replace("PostgreSQLDatabase", "");
StringBuilder items = new StringBuilder();
foreach (PGDatabase.PGDatabaseSetting setting in database.Settings)
{
items.Append(comment.Replace("[Name]", setting.Name)
.Replace("[Setting]", setting.Setting)
.Replace("[Description]", setting.Description));
}
content = content.Replace(match, items.ToString());
}
FileHelper.WriteFile(content, OutputPath);
Console.WriteLine("Writing schemas.");
SchemasRunner.Run();
}
public List <Customers> RandevulariGetirIptaller()
{
List <Customers> cstm = new List <Customers>();
using (PGDatabase dbd = new PGDatabase())
{
string command = "select *,rooms.roomnumber as roomnumbernum from customers inner join rooms on customers.roomnumber = rooms.roomid inner join odatype on rooms.odatur = odatype.id and customers.rezervasyoniptal =true ";
NpgsqlDataReader reader = dbd.Reader(command);
while (reader.Read())
{
Customers customer = new Customers();
customer.ad = reader["ad"].ToString();
customer.soyad = reader["soyad"].ToString();
customer.email = reader["email"].ToString();
customer.odaturu = (reader["odaturadi"]).ToString();
customer.odanumarasi = Convert.ToInt32(reader["roomnumbernum"]);
customer.id = Convert.ToInt32(reader["id"]);
customer.r_baslama = Convert.ToDateTime(reader["r_baslama"]);
customer.r_bitis = Convert.ToDateTime(reader["r_bitis"]);
customer.cocukvarmi = Convert.ToBoolean(reader["haschildren"]);
customer.cocuksayisi = (reader["cocuksayisi"]).ToString();
customer.notlar = (reader["notlar"]).ToString();
cstm.Add(customer);
}
reader.Close();
dbd.connClose();
}
return(cstm);
}
public SysUser GetUserByID(string id)
{
string sql = @"select a.id, a.inspur_id, a.code, a.full_name, a.name_global, a.mobile, a.email,
c.id org_id, c.name org_name, c.full_name org_full_name, c.full_path,c.parent_id,a.serial_number
from users a inner join r_org_user b on a.id = b.user_id inner JOIN organizations c on b.org_id = c.id where a.id ={0}";
SysUser user = default(SysUser);
PGDatabase db = PGDatabase.GetDatabase("sysdb");
using (IDataReader reader = db.ExcuteDataReader(sql, id))
{
if (reader.Read())
{
user = new SysUser();
user.ID = reader.GetString(0);
user.InspurID = reader.GetString(1);
user.Code = reader.GetString(2);
user.Name = reader.GetString(3);
user.GlobalName = reader.GetString(4);
user.Phone = reader.GetString(5);
user.Mail = reader.GetString(6);
user.SerialNumber = reader.GetString(12);
Organization org = new Organization()
{
ID = reader.GetString(7),
Name = reader.GetString(8),
FullName = reader.GetString(9),
FullPath = reader.GetString(10),
ParentID = reader.GetString(11)
};
}
}
return(user);
}
public void MusteriKaydet(string girisGun, string cikisGun, string musteriAd, string musteriSoyad, string ucretToplam, string gunToplam, string odaNumber, string email, string cocukSayisi, string not, bool chckMi)
{
int kayitIDsi = 0;
PGDatabase pgdb = new PGDatabase();
if (chckMi = false)
{
cocukSayisi = "0";
}
using (pgdb)
{
string commandsql = "";
commandsql = ("insert into customers (haschildren,rezervasyoniptal,rezervasyonbitti,ucret,ad,soyad,cocuksayisi,kisiSayisi,roomNumber,r_baslama,r_bitis,kalicakgunsayisi,notlar,email) values("
+ chckMi + ","
+ false + ","
+ false + ","
+ Convert.ToInt32(ucretToplam) + ",'"
+ musteriAd + "','"
+ musteriSoyad + "',"
+ Convert.ToInt32(cocukSayisi) + ","
+ Convert.ToInt32(cocukSayisi) + ","
+ Convert.ToInt32(odaNumber) + ",'"
+ girisGun + "','"
+ cikisGun + "',"
+ Convert.ToInt32(gunToplam) + ",'"
+ not + "','"
+ email +
"')returning id");
string token = pgdb.neSayisi(commandsql);
int.TryParse(token, out kayitIDsi);
pgdb.connClose();
}
}
private static List <string> GetInspurID(string[] users)
{
string qu = string.Empty;
List <string> list = new List <string>();
for (int i = 0; i < users.Length; i++)
{
qu += string.Format("'{0}',", users[i]);
}
qu = qu.TrimEnd(',');
string sql = "select inspur_id from users where inspur_id<>'' and id in (" + qu + ")";
PGDatabase db = PGDatabase.GetDatabase("sysdb");
using (IDataReader reader = db.ExcuteDataReader(sql))
{
while (reader.Read())
{
list.Add(reader.GetString(0));
}
}
return(list);
}
/// <summary>
/// 获取身份证号
/// </summary>
/// <param name="userID"></param>
/// <returns></returns>
public static string GetUserIdCardNmb(string userID)
{
string sql = @"select id_nmb from users where id={0}";
string nmb = string.Empty;
PGDatabase db = PGDatabase.GetDatabase("sysdb");
using (IDataReader reader = db.ExcuteDataReader(sql, userID))
{
if (reader.Read())
{
nmb = reader.GetString(0);
}
}
return(nmb);
}
public List <string> GetUserIDsByOrgID(string orgID)
{
List <string> userIDList = new List <string>();
string sql = @"select user_id from r_org_user where org_id ={0}";
PGDatabase db = PGDatabase.GetDatabase("sysdb");
using (IDataReader reader = db.ExcuteDataReader(sql, orgID))
{
while (reader.Read())
{
userIDList.Add(reader.GetString(0));
}
}
return(userIDList);
}
public static Stream GetPassportPic(string userId)
{
string sql = @"select picture from user_passport where user_id={0}";
Stream sm = default(Stream);
PGDatabase db = PGDatabase.GetDatabase("sysdb");
using (IDataReader reader = db.ExcuteDataReader(sql, userId))
{
if (reader.Read())
{
object obj = reader.GetValue(0);
sm = ToStream(obj);
}
}
return(sm);
}
public bool RezervasyonIptal(int id)
{
bool success = false;
using (PGDatabase dbd = new PGDatabase())
{
string command = "update customers set rezervasyoniptal = true where id = " + id;
int rowsAffected = dbd.Cmd(command);
if (rowsAffected > 0)
{
success = true;
}
dbd.connClose();
}
return(success);
}
/// <summary>
///
/// </summary>
/// <param name="userName"></param>
/// <returns></returns>
public List <SysUser> QueryByUserName(string userName, string tenant_id)
{
List <SysUser> list = new List <SysUser>();
if (userName.Length < 2) //至少要输入两个字符。
{
return(list);
}
string sql = @"select a.id, a.inspur_id, a.code, a.full_name, a.name_global, a.mobile, a.email,
c.id org_id, c.name org_name, c.full_name org_full_name, c.full_path,c.parent_id,a.serial_number
from users a inner join r_org_user b on a.id = b.user_id inner JOIN organizations c on b.org_id = c.id where a.full_name like {0} and b.tenant_id={1}";
SysUser user = default(SysUser);
PGDatabase db = PGDatabase.GetDatabase("sysdb");
using (IDataReader reader = db.ExcuteDataReader(sql, string.Format("%{0}%", userName), tenant_id))
{
while (reader.Read())
{
user = new SysUser();
user.ID = Convert.ToString(reader["id"]);
user.InspurID = Convert.ToString(reader["inspur_id"]);
user.Code = Convert.ToString(reader["code"]);
user.Name = Convert.ToString(reader["full_name"]);
user.GlobalName = Convert.ToString(reader["name_global"]);
user.Phone = Convert.ToString(reader["mobile"]);
user.Mail = Convert.ToString(reader["email"]);
user.SerialNumber = Convert.ToString(reader["serial_number"]);
Organization org = new Organization()
{
ID = Convert.ToString(reader["org_id"]),
Name = Convert.ToString(reader["org_name"]),
FullName = Convert.ToString(reader["org_full_name"]),
FullPath = Convert.ToString(reader["full_path"]),
ParentID = Convert.ToString(reader["parent_id"])
};
user.org = org;
list.Add(user);
}
}
return(list);
}
/// <summary>
///
/// </summary>
/// <param name="userName"></param>
/// <returns></returns>
public List <SysUser> QueryByUserName(string userName, string tenant_id, int page, int total)
{
List <SysUser> list = new List <SysUser>();
if (string.IsNullOrEmpty(userName)) //至少要输入两个字符。
{
throw new Exception("名称不能为空。");
}
string sql = @"select a.id, a.inspur_id, a.code, a.full_name, a.name_global, a.mobile, a.email,
c.id org_id, c.name org_name, c.full_name org_full_name, c.full_path,c.parent_id,a.serial_number
from users a inner join r_org_user b on a.id = b.user_id inner JOIN organizations c on b.org_id = c.id where a.full_name like {0} and b.tenant_id={1}";
SysUser user = default(SysUser);
PGDatabase db = PGDatabase.GetDatabase("sysdb");
using (IDataReader reader = db.ExcuteDataReader(sql, string.Format("%{0}%", userName), tenant_id))
{
while (reader.Read())
{
user = new SysUser();
user.ID = reader.GetString(0);
user.InspurID = reader.GetString(1);
user.Code = reader.GetString(2);
user.Name = reader.GetString(3);
user.GlobalName = reader.GetString(4);
user.Phone = reader.GetString(5);
user.Mail = reader.GetString(6);
user.SerialNumber = reader.GetString(12);
Organization org = new Organization()
{
ID = reader.GetString(7),
Name = reader.GetString(8),
FullName = reader.GetString(9),
FullPath = reader.GetString(10),
ParentID = reader.GetString(11)
};
list.Add(user);
}
}
return(list);
}
public List <RoomTypeModel> OdaTurlerComboBoxDoldur()
{
List <RoomTypeModel> roomTypeModels = new List <RoomTypeModel>();
using (PGDatabase dbd = new PGDatabase())
{
string command = "select * from odatype ";
NpgsqlDataReader reader = dbd.Reader(command);
while (reader.Read())
{
RoomTypeModel roomType = new RoomTypeModel();
roomType.id = Convert.ToInt32(reader["id"]);
roomType.odaTurAdi = (reader["odaturadi"]).ToString();
roomTypeModels.Add(roomType);
}
reader.Close();
dbd.connClose();
}
return(roomTypeModels);
}
private static List <Organization> GetOrgs(string sql, params object[] objParams)
{
List <Organization> orgs = new List <Organization>();
PGDatabase db = PGDatabase.GetDatabase("sysdb");
using (var reader = db.ExcuteDataReader(sql, objParams))
{
while (reader.Read())
{
orgs.Add(new Organization()
{
ID = Convert.ToString(reader["id"]),
Name = Convert.ToString(reader["name"]),
FullName = Convert.ToString(reader["full_name"]),
FullPath = Convert.ToString(reader["full_path"]),
ParentID = Convert.ToString(reader["parent_id"])
});
}
}
return(orgs);
}
private static Organization GetOrg(string sql, params object[] objParams)
{
Organization org = default(Organization);
PGDatabase db = PGDatabase.GetDatabase("sysdb");
using (IDataReader reader = db.ExcuteDataReader(sql, objParams))
{
if (reader.Read())
{
org = new Organization()
{
ID = reader.GetString(0),
Name = reader.GetString(1),
FullName = reader.GetString(2),
FullPath = reader.GetString(3),
ParentID = reader.GetString(4)
};
}
}
return(org);
}
private static Organization GetOrg(string sql, params object[] objParams)
{
Organization org = default(Organization);
PGDatabase db = PGDatabase.GetDatabase("sysdb");
using (IDataReader reader = db.ExcuteDataReader(sql, objParams))
{
if (reader.Read())
{
org = new Organization()
{
ID = Convert.ToString(reader["id"]),
Name = Convert.ToString(reader["name"]),
FullName = Convert.ToString(reader["full_name"]),
FullPath = Convert.ToString(reader["full_path"]),
ParentID = Convert.ToString(reader["parent_id"])
};
}
}
return(org);
}
public List <Customers> RezervasyonlariGetirIdle(int roomId)
{
List <Customers> customers = new List <Customers>();
string command = "select r_baslama,r_bitis,roomnumber from customers where roomnumber =" + roomId + "";
using (PGDatabase dbd = new PGDatabase())
{
NpgsqlDataReader reader = dbd.Reader(command);
while (reader.Read())
{
Customers customer = new Customers();
customer.roomId = Convert.ToInt32(reader["roomnumber"]);
customer.r_baslama = Convert.ToDateTime(reader["r_baslama"]);
customer.r_bitis = Convert.ToDateTime(reader["r_bitis"]);
customers.Add(customer);
}
reader.Close();
dbd.connClose();
}
return(customers);
}
public List <RoomModel> odalarDonmeId(int id)
{
List <RoomModel> roomModels = new List <RoomModel>();
using (PGDatabase dbd = new PGDatabase())
{
string command = "select rooms.* , odatype.odaturadi as odaturu from rooms inner join odatype on rooms.odatur = odatype.id where odatype.id = " + id;
NpgsqlDataReader reader = dbd.Reader(command);
while (reader.Read())
{
RoomModel roomModel = new RoomModel();
roomModel.RoomId = Convert.ToInt32(reader["roomid"]);
roomModel.kat = Convert.ToInt32(reader["kat"]);
roomModel.roomNumber = Convert.ToInt32(reader["roomnumber"]);
roomModel.odaTurAdi = (reader["odaturu"]).ToString();
roomModels.Add(roomModel);
}
reader.Close();
dbd.connClose();
}
return(roomModels);
}
public List <string> GetInspurIdByUserId(List <string> users, string tenant_id)
{
if (users == null || users.Count < 1)
{
throw new Exception("用户标识列表为空");
}
string id = string.Join("','", users.ToArray());
string sql = $@"select a.inspur_id from users a inner join r_org_user b on a.id = b.user_id where b.tenant_id='{tenant_id}' and a.id in('{id}')";
PGDatabase db = PGDatabase.GetDatabase("sysdb");
List <string> list = new List <string>();
using (IDataReader reader = db.ExcuteDataReader(sql))
{
while (reader.Read())
{
string inspurID = reader.GetString(0);
list.Add(inspurID);
}
}
return(list);
}
/// <summary>
/// 根据用户ID获取护照信息
/// </summary>
/// <param name="userId"></param>
/// <returns></returns>
public static Passport GetPassport(string userId)
{
string sql = @"select id,user_id, number, picture, effect_date, type from user_passport where user_id={0}";
Passport ppt = default(Passport);
PGDatabase db = PGDatabase.GetDatabase("sysdb");
using (IDataReader reader = db.ExcuteDataReader(sql, userId))
{
if (reader.Read())
{
ppt = new Passport()
{
ID = reader.GetString(0),
UserID = reader.GetString(1),
Number = reader.GetString(2),
Picture = ToStream(reader.GetValue(3)),
EffectDate = reader.GetDateTime(4),
Type = reader.GetInt32(5)
};
}
}
return(ppt);
}
