1770 lines
84 KiB
C#
1770 lines
84 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data.SQLite;
|
|
using System.Globalization;
|
|
using System.IO;
|
|
using System.Linq;
|
|
using System.Windows.Forms;
|
|
|
|
namespace HSUCO_Cargo_Garage_Operation_Program
|
|
{
|
|
public static class Database
|
|
{
|
|
private static readonly string DBLocation = "database.sqlite";
|
|
private static SQLiteConnection _sqLiteConnection;
|
|
|
|
private static readonly string CONNECTION_STRING =
|
|
$"Data Source={DBLocation}; Version=3; Integrated Security=true;";
|
|
|
|
/// <summary>
|
|
/// 최초 호출시 Database 연결
|
|
/// </summary>
|
|
static Database()
|
|
{
|
|
CreateDatabaseIfNotExist();
|
|
}
|
|
|
|
/// <summary>
|
|
/// Database 연결시 파일이 없으면 Database Table 생성
|
|
/// </summary>
|
|
private static void CreateDatabaseIfNotExist()
|
|
{
|
|
if (!File.Exists(DBLocation))
|
|
{
|
|
SQLiteConnection.CreateFile(DBLocation);
|
|
_sqLiteConnection = OpenConnection();
|
|
|
|
var createKeyTable = @"
|
|
CREATE TABLE ""ApplicantList"" (
|
|
""No"" TEXT NOT NULL,
|
|
""ApplicantType"" INTEGER NOT NULL,
|
|
""UID"" INTEGER NOT NULL,
|
|
""Date"" TEXT NOT NULL,
|
|
""RegistrationStatus"" INTEGER NOT NULL,
|
|
""DropDate"" TEXT,
|
|
""DropOrder"" INTEGER,
|
|
FOREIGN KEY(""UID"") REFERENCES ""UserInformation""(""UID""),
|
|
PRIMARY KEY(""No"")
|
|
);
|
|
|
|
CREATE TABLE ""LedgerCertificate"" (
|
|
""No"" TEXT NOT NULL UNIQUE,
|
|
""UserNo"" TEXT NOT NULL,
|
|
""IssueDate"" TEXT NOT NULL,
|
|
""StartDate"" TEXT NOT NULL,
|
|
""EndDate"" TEXT NOT NULL,
|
|
""Amount"" INTEGER NOT NULL,
|
|
""PaymentStatus"" INTEGER NOT NULL,
|
|
FOREIGN KEY(""UserNo"") REFERENCES ""UserList"",
|
|
PRIMARY KEY(""No"")
|
|
);
|
|
|
|
CREATE TABLE ""LedgerProceeds"" (
|
|
""UserNo"" TEXT NOT NULL,
|
|
""Type"" INTEGER NOT NULL,
|
|
""Amount"" INTEGER NOT NULL,
|
|
""Date"" TEXT NOT NULL,
|
|
FOREIGN KEY(""UserNo"") REFERENCES ""UserList""(""No"")
|
|
);
|
|
|
|
CREATE TABLE ""Settings"" (
|
|
""SettingName"" TEXT NOT NULL,
|
|
""SettingValue"" TEXT NOT NULL,
|
|
""SettingDate"" TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE ""AreaSetting"" (
|
|
""No"" INTEGER NOT NULL,
|
|
""AreaName"" TEXT NOT NULL,
|
|
""AreaType"" INTEGER NOT NULL,
|
|
PRIMARY KEY(""No"" AUTOINCREMENT)
|
|
);
|
|
|
|
CREATE TABLE ""UserInformation"" (
|
|
""UID"" INTEGER NOT NULL,
|
|
""Owner"" INTEGER NOT NULL,
|
|
""Name"" TEXT NOT NULL,
|
|
""PersonalNumber"" TEXT NOT NULL,
|
|
""Phone"" TEXT NOT NULL,
|
|
""Address"" TEXT NOT NULL,
|
|
""VehicleType"" INTEGER NOT NULL,
|
|
""CargoVehicleNumber"" TEXT NOT NULL,
|
|
""CargoVehicleName"" TEXT NOT NULL,
|
|
""PassengerVehicleNumber"" TEXT NOT NULL,
|
|
""PassengerVehicleName"" TEXT NOT NULL,
|
|
PRIMARY KEY(""UID"" AUTOINCREMENT)
|
|
);
|
|
|
|
CREATE TABLE ""UserList"" (
|
|
""No"" TEXT NOT NULL UNIQUE,
|
|
""Area"" TEXT NOT NULL,
|
|
""ApplicantNo"" TEXT NOT NULL,
|
|
""ApplicantDate"" TEXT NOT NULL,
|
|
""UID"" INTEGER NOT NULL,
|
|
""Date"" TEXT NOT NULL,
|
|
""DateStart"" TEXT NOT NULL,
|
|
""DateEnd"" TEXT NOT NULL,
|
|
""ExtensionStatus"" INTEGER NOT NULL,
|
|
""ExtensionStart"" TEXT,
|
|
""PaymentStatus"" INTEGER NOT NULL,
|
|
PRIMARY KEY(""No""),
|
|
FOREIGN KEY(""UID"") REFERENCES ""UserInformation""(""UID"")
|
|
);
|
|
|
|
CREATE TABLE ""UserListHistory"" (
|
|
""UserNo"" TEXT NOT NULL,
|
|
""HistoryCode"" INTEGER NOT NULL,
|
|
""HistoryDate"" TEXT NOT NULL,
|
|
""HistoryAmount"" INTEGER NOT NULL,
|
|
FOREIGN KEY(""UserNo"") REFERENCES ""UserList""(""No"")
|
|
);
|
|
|
|
CREATE VIEW ViewApplicantList AS SELECT AL.No,
|
|
AL.ApplicantType,
|
|
AL.UID,
|
|
UI.Owner,
|
|
UI.Name,
|
|
UI.PersonalNumber,
|
|
UI.Phone,
|
|
UI.Address,
|
|
UI.VehicleType,
|
|
UI.CargoVehicleNumber,
|
|
UI.CargoVehicleName,
|
|
UI.PassengerVehicleNumber,
|
|
UI.PassengerVehicleName,
|
|
AL.Date,
|
|
AL.RegistrationStatus,
|
|
AL.DropDate,
|
|
AL.DropOrder
|
|
FROM main.ApplicantList AL INNER JOIN main.UserInformation UI ON AL.UID = UI.UID;
|
|
|
|
CREATE VIEW ViewUserList AS SELECT
|
|
UL.No,
|
|
UL.Area,
|
|
UL.ApplicantNo,
|
|
UL.ApplicantDate,
|
|
UL.UID,
|
|
UI.Owner,
|
|
UI.Name,
|
|
UI.PersonalNumber,
|
|
UI.Phone,
|
|
UI.Address,
|
|
UI.VehicleType,
|
|
UI.CargoVehicleNumber,
|
|
UI.CargoVehicleName,
|
|
UI.PassengerVehicleNumber,
|
|
UI.PassengerVehicleName,
|
|
UL.Date,
|
|
UL.DateStart,
|
|
UL.DateEnd,
|
|
UL.ExtensionStatus,
|
|
UL.ExtensionStart,
|
|
UL.PaymentStatus
|
|
FROM main.UserList UL INNER JOIN main.UserInformation UI ON UL.UID = UI.UID;
|
|
";
|
|
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = createKeyTable;
|
|
command.ExecuteNonQuery();
|
|
}
|
|
}
|
|
else
|
|
{
|
|
OpenConnection();
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// Dtabase 연결
|
|
/// </summary>
|
|
/// <returns>SQLiteConnection 정보</returns>
|
|
private static SQLiteConnection OpenConnection()
|
|
{
|
|
_sqLiteConnection = new SQLiteConnection(CONNECTION_STRING);
|
|
_sqLiteConnection.Open();
|
|
|
|
return _sqLiteConnection;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 차량번호로 이미 신청한 차량인지 조회
|
|
/// </summary>
|
|
/// <param name="CargoVehicleNumber">화물차 번호</param>
|
|
/// <returns>중복 여부</returns>
|
|
public static bool ApplicantCheckAlready(string cargoVehicleNumber)
|
|
{
|
|
var count = 0;
|
|
var query = $"SELECT COUNT(*) FROM ViewApplicantList WHERE CargoVehicleNumber = '@cargoVehicleNumbe' AND RegistrationStatus=@registrationStatus;";
|
|
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
command.Parameters.AddWithValue("@cargoVehicleNumber", cargoVehicleNumber);
|
|
command.Parameters.AddWithValue("@registrationStatus", (int)ERegistrationStatus.Wait);
|
|
|
|
using (var reader = command.ExecuteReader())
|
|
{
|
|
while (reader.Read())
|
|
count = int.Parse(reader[0].ToString());
|
|
}
|
|
}
|
|
|
|
return count > 0;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 사용자 리스트에서 사용자 찾기
|
|
/// </summary>
|
|
/// <param name="CargoVehicleNumber">화물차 번호</param>
|
|
/// <returns>중복 여부</returns>
|
|
public static bool UserListCheckAlready(string CargoVehicleNumber)
|
|
{
|
|
var count = 0;
|
|
var query = "SELECT COUNT(*) FROM ViewUserList WHERE DateEnd >= \"" + DateTime.Now +
|
|
"\" AND CargoVehicleNumber = \"" + CargoVehicleNumber + "\"";
|
|
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
|
|
using (var reader = command.ExecuteReader())
|
|
{
|
|
while (reader.Read())
|
|
count = int.Parse(reader[0].ToString());
|
|
}
|
|
}
|
|
|
|
return count > 0;
|
|
}
|
|
|
|
public static EPaymentStatus CheckPaymentStatus(string no)
|
|
{
|
|
EPaymentStatus paymentStatus = new EPaymentStatus();
|
|
var query = $"SELECT PaymentStatus From UserList Where No='{no}'";
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
|
|
using (var reader = command.ExecuteReader())
|
|
{
|
|
while (reader.Read())
|
|
paymentStatus = (EPaymentStatus)int.Parse(reader["PaymentStatus"].ToString());
|
|
}
|
|
}
|
|
return paymentStatus;
|
|
}
|
|
|
|
public static string SetApplicant(ApplicantInformation applicant)
|
|
{
|
|
int year = DateTime.Now.Year;
|
|
string suffix;
|
|
EGetLastNumber eGetLastNumber;
|
|
if (applicant.ApplicantType == EApplicantType.Lots)
|
|
{
|
|
suffix = Global.SuffixApplicantLot;
|
|
eGetLastNumber = EGetLastNumber.ApplicantListLots;
|
|
}
|
|
else
|
|
{
|
|
suffix = Global.SuffixApplicantWait;
|
|
eGetLastNumber = EGetLastNumber.ApplicantListWait;
|
|
}
|
|
|
|
int lastNo = GetLastNo(eGetLastNumber, year);
|
|
lastNo++;
|
|
string no = $"{year}-{suffix}{lastNo}";
|
|
|
|
var query = $"INSERT INTO UserInformation(Owner, Name, PersonalNumber, Phone, Address, VehicleType, CargoVehicleNumber, CargoVehicleName, PassengerVehicleNumber, PassengerVehicleName) " +
|
|
$"VALUES({(int)applicant.Owenr},'{applicant.Name}','{applicant.PersonalNumber}','{applicant.Phone}','{applicant.Address}','{(int)applicant.VehicleType}','{applicant.CargoVehicleNumber}','{applicant.CargoVehicleName}','{applicant.PassengerVehicleNumber}','{applicant.PassengerVehicleName}');" +
|
|
$"select last_insert_rowid();";
|
|
|
|
int lastId = 0;
|
|
int insertResult;
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
lastId = int.Parse(command.ExecuteScalar().ToString());
|
|
}
|
|
if (lastId < 1)
|
|
{
|
|
return string.Empty;
|
|
}
|
|
query = $"INSERT INTO ApplicantList(No, ApplicantType, UID, Date, RegistrationStatus) VALUES('{no}', '{(int)applicant.ApplicantType}',{lastId},'{applicant.Date.DateTimeDatabase()}', {(int)ERegistrationStatus.Wait})";
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
insertResult = command.ExecuteNonQuery();
|
|
}
|
|
|
|
if (insertResult > 0)
|
|
{
|
|
return no;
|
|
}
|
|
else
|
|
{
|
|
return string.Empty;
|
|
}
|
|
}
|
|
|
|
public static PrintFilingReceiptInformation GetApplicantData(string no)
|
|
{
|
|
PrintFilingReceiptInformation printFilingReceiptInformation = new PrintFilingReceiptInformation();
|
|
|
|
var query = $"SELECT * From ViewApplicantList Where No='{no}'";
|
|
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
|
|
using (var reader = command.ExecuteReader())
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
printFilingReceiptInformation.No = reader["No"].ToString();
|
|
printFilingReceiptInformation.Name = reader["Name"].ToString();
|
|
printFilingReceiptInformation.PersonalNumber = reader["PersonalNumber"].ToString();
|
|
|
|
printFilingReceiptInformation.Address = reader["Address"].ToString();
|
|
printFilingReceiptInformation.CargoVehicleNumber = reader["CargoVehicleNumber"].ToString();
|
|
printFilingReceiptInformation.PassengerVehicleNumber = reader["PassengerVehicleNumber"].ToString();
|
|
printFilingReceiptInformation.DateReception = DateTime.Parse(reader["Date"].ToString());
|
|
}
|
|
}
|
|
}
|
|
|
|
return printFilingReceiptInformation;
|
|
}
|
|
|
|
public static List<ApplicantInformation> GetApplicant()
|
|
{
|
|
var applicants = new List<ApplicantInformation>();
|
|
var query = $"SELECT * From ViewApplicantList Where ApplicantType={(int)EApplicantType.Lots} And RegistrationStatus={(int)ERegistrationStatus.Wait}";
|
|
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
|
|
using (var reader = command.ExecuteReader())
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
var applicant = new ApplicantInformation();
|
|
applicant.No = reader["No"].ToString();
|
|
applicant.ApplicantType = (EApplicantType)int.Parse(reader["ApplicantType"].ToString());
|
|
applicant.Owenr = (EOwner)int.Parse(reader["Owner"].ToString());
|
|
applicant.Name = reader["Name"].ToString();
|
|
applicant.PersonalNumber = reader["PersonalNumber"].ToString();
|
|
applicant.Phone = reader["Phone"].ToString();
|
|
applicant.Address = reader["Address"].ToString();
|
|
applicant.VehicleType = (EVehicleType)int.Parse(reader["VehicleType"].ToString());
|
|
applicant.CargoVehicleNumber = reader["CargoVehicleNumber"].ToString();
|
|
applicant.CargoVehicleName = reader["CargoVehicleName"].ToString();
|
|
applicant.PassengerVehicleNumber = reader["PassengerVehicleNumber"].ToString();
|
|
applicant.PassengerVehicleName = reader["PassengerVehicleName"].ToString();
|
|
applicant.Date = DateTime.Parse(reader["Date"].ToString());
|
|
applicants.Add(applicant);
|
|
}
|
|
}
|
|
}
|
|
|
|
return applicants;
|
|
}
|
|
|
|
public static List<ApplicantInformation> GetApplicantWait()
|
|
{
|
|
string[] querys = new string[4];
|
|
querys[0] = $"SELECT * From ViewApplicantList Where ApplicantType={(int)EApplicantType.LotsOut} And RegistrationStatus={(int)ERegistrationStatus.Wait} AND Address LIKE '%화성시%' Order By DropDate, DropOrder, Date ASC";
|
|
querys[1] = $"SELECT * From ViewApplicantList Where ApplicantType={(int)EApplicantType.Wait} And RegistrationStatus={(int)ERegistrationStatus.Wait} AND Address LIKE '%화성시%' Order By Date ASC";
|
|
querys[2] = $"SELECT * From ViewApplicantList Where ApplicantType={(int)EApplicantType.LotsOut} And RegistrationStatus={(int)ERegistrationStatus.Wait} AND Address NOT LIKE '%화성시%' Order By DropDate, DropOrder, Date ASC";
|
|
querys[3] = $"SELECT * From ViewApplicantList Where ApplicantType={(int)EApplicantType.Wait} And RegistrationStatus={(int)ERegistrationStatus.Wait} AND Address NOT LIKE '%화성시%' Order By Date ASC";
|
|
var applicants = new List<ApplicantInformation>();
|
|
foreach (var query in querys)
|
|
{
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
|
|
using (var reader = command.ExecuteReader())
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
var applicant = new ApplicantInformation();
|
|
applicant.No = reader["No"].ToString();
|
|
applicant.ApplicantType = (EApplicantType)int.Parse(reader["ApplicantType"].ToString());
|
|
applicant.Owenr = (EOwner)int.Parse(reader["Owner"].ToString());
|
|
applicant.Name = reader["Name"].ToString();
|
|
applicant.PersonalNumber = reader["PersonalNumber"].ToString();
|
|
applicant.Phone = reader["Phone"].ToString();
|
|
applicant.Address = reader["Address"].ToString();
|
|
applicant.VehicleType = (EVehicleType)int.Parse(reader["VehicleType"].ToString());
|
|
applicant.CargoVehicleNumber = reader["CargoVehicleNumber"].ToString();
|
|
applicant.CargoVehicleName = reader["CargoVehicleName"].ToString();
|
|
applicant.PassengerVehicleNumber = reader["PassengerVehicleNumber"].ToString();
|
|
applicant.PassengerVehicleName = reader["PassengerVehicleName"].ToString();
|
|
|
|
applicant.Date = DateTime.Parse(reader["Date"].ToString());
|
|
|
|
applicants.Add(applicant);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
return applicants;
|
|
}
|
|
|
|
public static bool DeleteApplicantList(string no)
|
|
{
|
|
var query = $"UPDATE ApplicantList SET RegistrationStatus={(int)ERegistrationStatus.Delete} Where No='{no}'";
|
|
using (var updateCommand = _sqLiteConnection.CreateCommand())
|
|
{
|
|
updateCommand.CommandText = query;
|
|
var updateResult = updateCommand.ExecuteNonQuery();
|
|
return (updateResult > 0);
|
|
}
|
|
}
|
|
|
|
public static UpdateUserInformation GetUserData(EUserInformationType eUserInformationType, string no)
|
|
{
|
|
UpdateUserInformation updateUserInformation = new UpdateUserInformation();
|
|
var query = $"SELECT * From View{eUserInformationType.ToString()} Where No='{no}'";
|
|
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
|
|
using (var reader = command.ExecuteReader())
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
updateUserInformation.UserInformationType = eUserInformationType;
|
|
updateUserInformation.No = no;
|
|
updateUserInformation.Owner = (EOwner)int.Parse(reader["Owner"].ToString());
|
|
updateUserInformation.Name = reader["Name"].ToString();
|
|
updateUserInformation.PersonalNumber = reader["PersonalNumber"].ToString();
|
|
updateUserInformation.Phone = reader["Phone"].ToString();
|
|
updateUserInformation.Address = reader["Address"].ToString();
|
|
updateUserInformation.VehicleType = (EVehicleType)int.Parse(reader["VehicleType"].ToString());
|
|
updateUserInformation.CargoVehicleNumber = reader["CargoVehicleNumber"].ToString();
|
|
updateUserInformation.CargoVehicleName = reader["CargoVehicleName"].ToString();
|
|
updateUserInformation.PassengerVehicleNumber = reader["PassengerVehicleNumber"].ToString();
|
|
updateUserInformation.PassengerVehicleName = reader["PassengerVehicleName"].ToString();
|
|
}
|
|
}
|
|
}
|
|
|
|
return updateUserInformation;
|
|
}
|
|
|
|
public static bool UpdateUserData(UpdateUserInformation updateUserInformation)
|
|
{
|
|
var query = $"SELECT UID From {updateUserInformation.UserInformationType.ToString()} Where No='{updateUserInformation.No}'";
|
|
int id;
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
id = int.Parse(command.ExecuteScalar().ToString());
|
|
}
|
|
query = $"UPDATE UserInformation Set Owner={(int)updateUserInformation.Owner}, Name='{updateUserInformation.Name}', PersonalNumber='{updateUserInformation.PersonalNumber}', Phone='{updateUserInformation.Phone}', Address='{updateUserInformation.Address}', VehicleType={(int)updateUserInformation.VehicleType}, CargoVehicleNumber='{updateUserInformation.CargoVehicleNumber}', CargoVehicleName='{updateUserInformation.CargoVehicleName}', PassengerVehicleNumber='{updateUserInformation.PassengerVehicleNumber}', PassengerVehicleName='{updateUserInformation.PassengerVehicleName}' Where UID={id}";
|
|
using (var updateCommand = _sqLiteConnection.CreateCommand())
|
|
{
|
|
updateCommand.CommandText = query;
|
|
var updateResult = updateCommand.ExecuteNonQuery();
|
|
return (updateResult > 0);
|
|
}
|
|
}
|
|
public static UserInformation GetUser(string no)
|
|
{
|
|
UserInformation user = new UserInformation();
|
|
string query = "SELECT * From ViewUserList WHERE No=@no";
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
command.Parameters.AddWithValue("@no", no);
|
|
using (var reader = command.ExecuteReader())
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
user.No = (reader["No"].ToString());
|
|
int areaNo = Convert.ToInt32(reader["Area"].ToString());
|
|
user.Area = GetAreaName(areaNo);
|
|
user.ApplicantNo = reader["ApplicantNo"].ToString();
|
|
user.ApplicantDate = DateTime.Parse(reader["ApplicantDate"].ToString(), new CultureInfo("ko-KR"));
|
|
user.Owner = (EOwner)int.Parse(reader["Owner"].ToString());
|
|
user.Name = reader["Name"].ToString();
|
|
user.PersonalNumber = reader["PersonalNumber"].ToString();
|
|
user.Phone = reader["Phone"].ToString();
|
|
user.Address = reader["Address"].ToString();
|
|
user.VehicleType = (EVehicleType)int.Parse(reader["VehicleType"].ToString());
|
|
user.CargoVehicleNumber = reader["CargoVehicleNumber"].ToString();
|
|
user.CargoVehicleName = reader["CargoVehicleName"].ToString();
|
|
user.PassengerVehicleNumber = reader["PassengerVehicleNumber"].ToString();
|
|
user.PassengerVehicleName = reader["PassengerVehicleName"].ToString();
|
|
user.Date = DateTime.Parse(reader["Date"].ToString(), new CultureInfo("ko-KR"));
|
|
user.DateStart = DateTime.Parse(reader["DateStart"].ToString(), new CultureInfo("ko-KR"));
|
|
user.DateEnd = DateTime.Parse(reader["DateEnd"].ToString(), new CultureInfo("ko-KR"));
|
|
user.ExtensionStatus = reader["ExtensionStatus"].ToString() == "1";
|
|
user.ExtensionStart = reader["ExtensionStatus"].ToString() == "1" ? DateTime.Parse(reader["ExtensionStart"].ToString(), new CultureInfo("ko-KR")) : DateTime.MinValue;
|
|
user.PaymentStatus = (EPaymentStatus)int.Parse(reader["PaymentStatus"].ToString());
|
|
user.HistoryInformations = new List<HistoryInformation>();
|
|
}
|
|
}
|
|
}
|
|
return user;
|
|
}
|
|
|
|
public static List<UserInformation> GetUserList()
|
|
{
|
|
var users = new List<UserInformation>();
|
|
string query = "SELECT * From ViewUserList";
|
|
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
|
|
using (var reader = command.ExecuteReader())
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
var user = new UserInformation();
|
|
|
|
user.No = (reader["No"].ToString());
|
|
int areaNo = Convert.ToInt32(reader["Area"].ToString());
|
|
user.Area = GetAreaName(areaNo);
|
|
user.ApplicantNo = reader["ApplicantNo"].ToString();
|
|
user.ApplicantDate = DateTime.Parse(reader["ApplicantDate"].ToString(), new CultureInfo("ko-KR"));
|
|
user.Owner = (EOwner)int.Parse(reader["Owner"].ToString());
|
|
user.Name = reader["Name"].ToString();
|
|
user.PersonalNumber = reader["PersonalNumber"].ToString();
|
|
user.Phone = reader["Phone"].ToString();
|
|
user.Address = reader["Address"].ToString();
|
|
user.VehicleType = (EVehicleType)int.Parse(reader["VehicleType"].ToString());
|
|
user.CargoVehicleNumber = reader["CargoVehicleNumber"].ToString();
|
|
user.CargoVehicleName = reader["CargoVehicleName"].ToString();
|
|
user.PassengerVehicleNumber = reader["PassengerVehicleNumber"].ToString();
|
|
user.PassengerVehicleName = reader["PassengerVehicleName"].ToString();
|
|
user.Date = DateTime.Parse(reader["Date"].ToString(), new CultureInfo("ko-KR"));
|
|
user.DateStart = DateTime.Parse(reader["DateStart"].ToString(), new CultureInfo("ko-KR"));
|
|
user.DateEnd = DateTime.Parse(reader["DateEnd"].ToString(), new CultureInfo("ko-KR"));
|
|
user.ExtensionStatus = reader["ExtensionStatus"].ToString() == "1";
|
|
user.ExtensionStart = reader["ExtensionStatus"].ToString() == "1" ? DateTime.Parse(reader["ExtensionStart"].ToString(), new CultureInfo("ko-KR")) : DateTime.MinValue;
|
|
user.PaymentStatus = (EPaymentStatus)int.Parse(reader["PaymentStatus"].ToString());
|
|
user.HistoryInformations = new List<HistoryInformation>();
|
|
|
|
users.Add(user);
|
|
}
|
|
}
|
|
}
|
|
|
|
for (int i = 0; i < users.Count; i++)
|
|
{
|
|
query = $"SELECT * From UserListHistory Where UserNo='{users[i].No}' Order By HistoryCode,HistoryDate ASC";
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
using (var reader = command.ExecuteReader())
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
HistoryInformation historyInformation = new HistoryInformation
|
|
{
|
|
HistoryCode = (EHistoryCode)int.Parse(reader["HistoryCode"].ToString()),
|
|
HistoryDate = DateTime.Parse(reader["HistoryDate"].ToString(), new CultureInfo("ko-KR")),
|
|
HistoryAmount = int.Parse(reader["HistoryAmount"].ToString())
|
|
};
|
|
users[i].HistoryInformations.Add(historyInformation);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
return users;
|
|
}
|
|
|
|
public static GetPaymentBaseInformation GetPaymentBase(string no)
|
|
{
|
|
GetPaymentBaseInformation getPaymentBaseInformation = new GetPaymentBaseInformation();
|
|
var query = $"SELECT DateStart, DateEnd, VehicleType FROM ViewUserList Where No='{no}'";
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
using (var reader = command.ExecuteReader())
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
DateTime start = DateTime.Parse(reader["DateStart"].ToString(), new CultureInfo("ko-KR"));
|
|
DateTime end = DateTime.Parse(reader["DateEnd"].ToString(), new CultureInfo("ko-KR"));
|
|
getPaymentBaseInformation.Month = Extends.GetProgressDays(start, end).Month;
|
|
getPaymentBaseInformation.VehicleType = (EVehicleType)int.Parse(reader["VehicleType"].ToString());
|
|
}
|
|
}
|
|
}
|
|
|
|
return getPaymentBaseInformation;
|
|
}
|
|
|
|
public static void AcceptUser(string no, int pay)
|
|
{
|
|
var query = $"UPDATE UserList Set PaymentStatus={(int)EPaymentStatus.Use} Where No='{no}'";
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
command.ExecuteNonQuery();
|
|
}
|
|
query = $"INSERT INTO LedgerProceeds(UserNo, Type, Amount, Date) VALUES('{no}',{(int)EProceedsType.Fees},{pay},'{DateTime.Now.DateTimeDatabase()}')";
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
command.ExecuteNonQuery();
|
|
}
|
|
return;
|
|
}
|
|
|
|
public static void DeleteUser(string no)
|
|
{
|
|
string query = $"UPDATE UserList Set PaymentStatus={(int)EPaymentStatus.Delete} Where No='{no}'";
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
command.ExecuteNonQuery();
|
|
}
|
|
}
|
|
|
|
public static bool DeleteUserUndo(string no)
|
|
{
|
|
bool result = false;
|
|
string area = string.Empty;
|
|
DateTime startDateTime = DateTime.Now;
|
|
DateTime endDateTime = DateTime.Now;
|
|
string query = $"SELECT Area, DateStart, DateEnd FROM UserList Where No='{no}'";
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
using (var reader = command.ExecuteReader())
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
area = reader["Area"].ToString();
|
|
startDateTime = Convert.ToDateTime(reader["DateStart"]);
|
|
endDateTime = Convert.ToDateTime(reader["DateEnd"]);
|
|
}
|
|
}
|
|
}
|
|
query = $"SELECT COUNT(*) FROM UserList Where Area={area} AND ('{startDateTime.DateOnly()}' < DateEnd OR '{endDateTime.DateOnly()}' > DateStart) And (PaymentStatus={(int)EPaymentStatus.StandBy} OR PaymentStatus={(int)EPaymentStatus.Use})";
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
using (var reader = command.ExecuteReader())
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
result = reader[0].ToString() == "0";
|
|
}
|
|
}
|
|
}
|
|
if (!result)
|
|
return false;
|
|
|
|
query = $"UPDATE UserList Set PaymentStatus={(int)EPaymentStatus.StandBy} Where No='{no}'";
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
command.ExecuteNonQuery();
|
|
}
|
|
return true;
|
|
}
|
|
|
|
/// <summary>
|
|
/// UserList 에서 연장을 했는지 여부
|
|
/// </summary>
|
|
/// <param name="no"></param>
|
|
/// <returns>Y=연장,N=연장안함</returns>
|
|
public static bool CheckExtensionStatus(string no)
|
|
{
|
|
bool result = false;
|
|
string query = $"SELECT ExtensionStatus From ViewUserList Where No='{no}'";
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
using (var reader = command.ExecuteReader())
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
result = reader["ExtensionStatus"].ToString() == "1";
|
|
}
|
|
}
|
|
}
|
|
return result;
|
|
}
|
|
|
|
public static bool UpdateExtended(string no, int amount)
|
|
{
|
|
var query = $"SELECT DateEnd From UserList Where No='{no}'";
|
|
DateTime endDateTime = DateTime.Now;
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
using (var reader = command.ExecuteReader())
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
endDateTime = DateTime.Parse(reader["DateEnd"].ToString());
|
|
}
|
|
}
|
|
}
|
|
DateTime extensionStart = endDateTime.AddDays(1);
|
|
endDateTime = endDateTime.AddYears(1);
|
|
|
|
query = $"INSERT INTO UserListHistory(UserNo, HistoryCode, HistoryDate, HistoryAmount) VALUES('{no}',{(int)EHistoryCode.Extension},'{DateTime.Now.DateTimeDatabase()}',{amount})";
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
int result = command.ExecuteNonQuery();
|
|
if (result < 1)
|
|
{
|
|
return false;
|
|
}
|
|
}
|
|
|
|
query = $"INSERT INTO LedgerProceeds(UserNo, Type, Amount, Date) VALUES('{no}',{(int)EProceedsType.ExtensionFees},{amount},'{DateTime.Now.DateTimeDatabase()}')";
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
int result = command.ExecuteNonQuery();
|
|
if (result < 1)
|
|
{
|
|
return false;
|
|
}
|
|
}
|
|
query = $"UPDATE UserList Set DateEnd='{endDateTime.EndDateTime()}', ExtensionStatus={true.BoolToInt()}, ExtensionStart='{extensionStart.StartDateTime()}' Where No='{no}'";
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
int result = command.ExecuteNonQuery();
|
|
if (result < 1)
|
|
{
|
|
return false;
|
|
}
|
|
}
|
|
return true;
|
|
}
|
|
|
|
public static GetRefundInformation GetRefundData(string no)
|
|
{
|
|
GetRefundInformation getRefundInformation = new GetRefundInformation();
|
|
var query = $"SELECT PaymentStatus, DateStart, DateEnd, ExtensionStatus, ExtensionStart From UserList Where No='{no}'";
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
using (var reader = command.ExecuteReader())
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
getRefundInformation.PaymentStatus = (EPaymentStatus)int.Parse(reader["PaymentStatus"].ToString());
|
|
getRefundInformation.DateStart = DateTime.Parse(reader["DateStart"].ToString());
|
|
getRefundInformation.DateEnd = DateTime.Parse(reader["DateEnd"].ToString());
|
|
getRefundInformation.ExtensionStatus = reader["ExtensionStatus"].ToString() == "1";
|
|
if (getRefundInformation.ExtensionStatus)
|
|
{
|
|
getRefundInformation.ExtensionStart = DateTime.Parse(reader["ExtensionStart"].ToString());
|
|
}
|
|
}
|
|
}
|
|
}
|
|
query = $"SELECT Type, Amount From LedgerProceeds Where UserNo='{no}'";
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
using (var reader = command.ExecuteReader())
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
EProceedsType eProceedsType = (EProceedsType)int.Parse(reader["Type"].ToString());
|
|
int amount = int.Parse(reader["Amount"].ToString());
|
|
if (eProceedsType == EProceedsType.Fees)
|
|
{
|
|
getRefundInformation.BaseFee = amount;
|
|
}
|
|
if (eProceedsType == EProceedsType.ExtensionFees)
|
|
{
|
|
getRefundInformation.ExtensionFee = amount;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
return getRefundInformation;
|
|
}
|
|
|
|
public static bool SetRefund(string no, DateTime refundDate, int amount)
|
|
{
|
|
var query = $"UPDATE UserList Set DateEnd='{refundDate.AddDays(-1).EndDateTime()}', PaymentStatus={(int)EPaymentStatus.Refund} Where No='{no}'";
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
int result = command.ExecuteNonQuery();
|
|
if (result < 1)
|
|
{
|
|
return false;
|
|
}
|
|
}
|
|
|
|
query = $"INSERT INTO UserListHistory(UserNo, HistoryCode, HistoryDate, HistoryAmount) VALUES('{no}',{(int)EHistoryCode.Refund},'{DateTime.Now.DateTimeDatabase()}',{-amount})";
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
int result = command.ExecuteNonQuery();
|
|
if (result < 1)
|
|
{
|
|
return false;
|
|
}
|
|
}
|
|
query = $"INSERT INTO LedgerProceeds(UserNo, Type, Amount, Date) Values('{no}',{(int)EProceedsType.Refunds},{-amount},'{DateTime.Now.DateTimeDatabase()}')";
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
int result = command.ExecuteNonQuery();
|
|
if (result < 1)
|
|
{
|
|
return false;
|
|
}
|
|
}
|
|
|
|
return true;
|
|
}
|
|
|
|
public static List<ResultSetUserListInformation> SetUserListForApplicant(LeftAreaInformation leftAreaInformation, DateTime endDate)
|
|
{
|
|
List<SetUserListInformation> tempUserList = new List<SetUserListInformation>();
|
|
|
|
var query = $"SELECT * From ViewApplicantList Where ApplicantType={(int)EApplicantType.Lots} And RegistrationStatus={(int)ERegistrationStatus.Wait}";
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
|
|
using (var reader = command.ExecuteReader())
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
SetUserListInformation setUserListInformation = new SetUserListInformation();
|
|
setUserListInformation.ApplicantNo = reader["No"].ToString();
|
|
setUserListInformation.UID = int.Parse(reader["UID"].ToString());
|
|
setUserListInformation.Date = DateTime.Parse(reader["Date"].ToString());
|
|
setUserListInformation.VehicleType = (EVehicleType)int.Parse(reader["VehicleType"].ToString());
|
|
setUserListInformation.CargoVehicleNumber = reader["CargoVehicleNumber"].ToString();
|
|
string address = reader["Address"].ToString();
|
|
setUserListInformation.Inside = address.IndexOf("화성시") != -1;
|
|
tempUserList.Add(setUserListInformation);
|
|
}
|
|
}
|
|
}
|
|
|
|
// 사용자 랜덤
|
|
tempUserList.Shuffle();
|
|
|
|
int countInsideLarge = tempUserList.Count(x => x.Inside && (x.VehicleType == EVehicleType.Large_Up || x.VehicleType == EVehicleType.Large_Down));
|
|
int countInsideOversized = tempUserList.Count(x => x.Inside && (x.VehicleType == EVehicleType.Oversized_Up || x.VehicleType == EVehicleType.Oversized_Down));
|
|
|
|
List<SetUserListInformation> setUserListInformationLarge = new List<SetUserListInformation>();
|
|
List<SetUserListInformation> setUserListInformationOversized = new List<SetUserListInformation>();
|
|
// Large 차량들이 남은 자리보다 작을때
|
|
if (leftAreaInformation.Large.Count > countInsideLarge)
|
|
{
|
|
int tmepCount = leftAreaInformation.Large.Count - countInsideLarge;
|
|
|
|
setUserListInformationLarge.AddRange(tempUserList.Where(x => x.Inside && (x.VehicleType == EVehicleType.Large_Up || x.VehicleType == EVehicleType.Large_Down)));
|
|
setUserListInformationLarge.AddRange(tempUserList.Where(x => !x.Inside && (x.VehicleType == EVehicleType.Large_Up || x.VehicleType == EVehicleType.Large_Down)).Take(tmepCount));
|
|
}
|
|
else // 화성시만으로 자리가 꽉참
|
|
{
|
|
setUserListInformationLarge.AddRange(tempUserList.Where(x => x.Inside && (x.VehicleType == EVehicleType.Large_Up || x.VehicleType == EVehicleType.Large_Down)).Take(leftAreaInformation.Large.Count));
|
|
}
|
|
|
|
// OverSized 차량들이 남은 자리보다 적을때
|
|
if (leftAreaInformation.OverSized.Count > countInsideOversized)
|
|
{
|
|
int tmepCount = leftAreaInformation.OverSized.Count - countInsideOversized;
|
|
|
|
setUserListInformationOversized.AddRange(tempUserList.Where(x => x.Inside && (x.VehicleType == EVehicleType.Oversized_Up || x.VehicleType == EVehicleType.Oversized_Down)));
|
|
setUserListInformationOversized.AddRange(tempUserList.Where(x => !x.Inside && (x.VehicleType == EVehicleType.Oversized_Up || x.VehicleType == EVehicleType.Oversized_Down)).Take(tmepCount));
|
|
}
|
|
else // 화성
|
|
{
|
|
setUserListInformationOversized.AddRange(tempUserList.Where(x => x.Inside && (x.VehicleType == EVehicleType.Oversized_Up || x.VehicleType == EVehicleType.Oversized_Down)).Take(leftAreaInformation.OverSized.Count));
|
|
}
|
|
|
|
List<SetUserListInformation> setUserListInformationFinal = new List<SetUserListInformation>();
|
|
for (int i = 0; i < setUserListInformationLarge.Count; i++)
|
|
{
|
|
SetUserListInformation setUserListInformation = setUserListInformationLarge[i];
|
|
setUserListInformation.Area = leftAreaInformation.Large[i].No;
|
|
setUserListInformationFinal.Add(setUserListInformation);
|
|
}
|
|
for (int i = 0; i < setUserListInformationOversized.Count; i++)
|
|
{
|
|
SetUserListInformation setUserListInformation = setUserListInformationOversized[i];
|
|
setUserListInformation.Area = leftAreaInformation.OverSized[i].No;
|
|
setUserListInformationFinal.Add(setUserListInformation);
|
|
}
|
|
var result = SetUserList(setUserListInformationFinal, leftAreaInformation.StartDateTime, endDate);
|
|
|
|
List<string> oList = new List<string>();
|
|
query = $"SELECT * FROM ApplicantList Where ApplicantType={(int)EApplicantType.Lots} And RegistrationStatus={(int)ERegistrationStatus.Wait}";
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
|
|
using (var reader = command.ExecuteReader())
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
string no = reader["No"].ToString();
|
|
oList.Add(no);
|
|
}
|
|
}
|
|
}
|
|
oList.Shuffle();
|
|
// 랜덤
|
|
DateTime dropDate = DateTime.Now;
|
|
int lastDropOrder = GetLastDropOrder(dropDate);
|
|
lastDropOrder++;
|
|
for (int i = 0; i < oList.Count; i++)
|
|
{
|
|
query = $"UPDATE ApplicantList SET ApplicantType={(int)EApplicantType.LotsOut}, DropDate='{dropDate.DateTimeDatabase()}' , DropOrder={lastDropOrder + i} WHERE No='{oList[i]}'";
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
command.ExecuteNonQuery();
|
|
}
|
|
}
|
|
|
|
return result;
|
|
}
|
|
|
|
public static bool SetManualUserList(string no, int area, DateTime startDate, DateTime endDate)
|
|
{
|
|
SetUserListInformation setUserListInformation = new SetUserListInformation();
|
|
var query = $"SELECT * From ViewApplicantList Where No='{no}'";
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
|
|
using (var reader = command.ExecuteReader())
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
setUserListInformation.ApplicantNo = reader["No"].ToString();
|
|
setUserListInformation.UID = int.Parse(reader["UID"].ToString());
|
|
setUserListInformation.CargoVehicleNumber = reader["CargoVehicleNumber"].ToString();
|
|
setUserListInformation.Date = DateTime.Parse(reader["Date"].ToString());
|
|
setUserListInformation.VehicleType = (EVehicleType)int.Parse(reader["VehicleType"].ToString());
|
|
}
|
|
}
|
|
}
|
|
setUserListInformation.Area = area;
|
|
var result = SetUserList(new List<SetUserListInformation>() { setUserListInformation }, startDate, endDate);
|
|
return result.Count != 0;
|
|
}
|
|
|
|
public static bool UpdateUserArea(string userNo, int area)
|
|
{
|
|
bool result = false;
|
|
var query = "UPDATE UserList SET Area=@area WHERE No=@no";
|
|
using (var updateCommand = _sqLiteConnection.CreateCommand())
|
|
{
|
|
updateCommand.CommandText = query;
|
|
updateCommand.Parameters.AddWithValue("@area", area);
|
|
updateCommand.Parameters.AddWithValue("@no", userNo);
|
|
var updateResult = updateCommand.ExecuteNonQuery();
|
|
result = updateResult > 0;
|
|
}
|
|
return result;
|
|
}
|
|
public static List<ResultSetUserListInformation> SetUserListForApplicantWait(LeftAreaInformation leftAreaInformation, DateTime endDate)
|
|
{
|
|
List<SetUserListInformation> setUserListInformations = new List<SetUserListInformation>();
|
|
string[] querys = new string[4];
|
|
querys[0] = $"SELECT * From ViewApplicantList Where ApplicantType={(int)EApplicantType.LotsOut} And RegistrationStatus={(int)ERegistrationStatus.Wait} AND Address LIKE '%화성시%' Order By DropDate, DropOrder, Date ASC";
|
|
querys[1] = $"SELECT * From ViewApplicantList Where ApplicantType={(int)EApplicantType.Wait} And RegistrationStatus={(int)ERegistrationStatus.Wait} AND Address LIKE '%화성시%' Order By Date ASC";
|
|
querys[2] = $"SELECT * From ViewApplicantList Where ApplicantType={(int)EApplicantType.LotsOut} And RegistrationStatus={(int)ERegistrationStatus.Wait} AND Address NOT LIKE '%화성시%' Order By DropDate, DropOrder, Date ASC";
|
|
querys[3] = $"SELECT * From ViewApplicantList Where ApplicantType={(int)EApplicantType.Wait} And RegistrationStatus={(int)ERegistrationStatus.Wait} AND Address NOT LIKE '%화성시%' Order By Date ASC";
|
|
// 저번에 떨어진 사람들 리스트
|
|
// 순위 정립 1. 화성시 떨어짐, 2. 대기 화성시, 3. 그외 떨어짐, 4. 그외 대기
|
|
foreach (var query in querys)
|
|
{
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
|
|
using (var reader = command.ExecuteReader())
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
SetUserListInformation setUserListInformation = new SetUserListInformation();
|
|
setUserListInformation.ApplicantNo = reader["No"].ToString();
|
|
setUserListInformation.UID = int.Parse(reader["UID"].ToString());
|
|
setUserListInformation.Date = DateTime.Parse(reader["Date"].ToString());
|
|
setUserListInformation.VehicleType = (EVehicleType)int.Parse(reader["VehicleType"].ToString());
|
|
setUserListInformation.CargoVehicleNumber = reader["CargoVehicleNumber"].ToString();
|
|
string address = reader["Address"].ToString();
|
|
setUserListInformation.Inside = address.IndexOf("화성시") != -1;
|
|
setUserListInformations.Add(setUserListInformation);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
List<SetUserListInformation> setUserListInformationLarge = new List<SetUserListInformation>();
|
|
List<SetUserListInformation> setUserListInformationOversized = new List<SetUserListInformation>();
|
|
setUserListInformationLarge.AddRange(setUserListInformations.Where(x => x.VehicleType == EVehicleType.Large_Down || x.VehicleType == EVehicleType.Large_Up).Take(leftAreaInformation.Large.Count));
|
|
setUserListInformationOversized.AddRange(setUserListInformations.Where(x => x.VehicleType == EVehicleType.Oversized_Down || x.VehicleType == EVehicleType.Oversized_Up).Take(leftAreaInformation.OverSized.Count));
|
|
|
|
setUserListInformationLarge.Shuffle();
|
|
setUserListInformationOversized.Shuffle();
|
|
List<SetUserListInformation> setUserListInformationFinal = new List<SetUserListInformation>();
|
|
for (int i = 0; i < setUserListInformationLarge.Count; i++)
|
|
{
|
|
SetUserListInformation setUserListInformation = setUserListInformationLarge[i];
|
|
setUserListInformation.Area = leftAreaInformation.Large[i].No;
|
|
setUserListInformationFinal.Add(setUserListInformation);
|
|
}
|
|
for (int i = 0; i < setUserListInformationOversized.Count; i++)
|
|
{
|
|
SetUserListInformation setUserListInformation = setUserListInformationOversized[i];
|
|
setUserListInformation.Area = leftAreaInformation.OverSized[i].No;
|
|
setUserListInformationFinal.Add(setUserListInformation);
|
|
}
|
|
var result = SetUserList(setUserListInformationFinal, leftAreaInformation.StartDateTime, endDate);
|
|
return result;
|
|
}
|
|
|
|
public static List<ResultSetUserListInformation> SetUserList(List<SetUserListInformation> setUserListInformation, DateTime startDate, DateTime endDate)
|
|
{
|
|
List<ResultSetUserListInformation> ResultSetUserListInformations = new List<ResultSetUserListInformation>();
|
|
int lastNo = GetLastUser(startDate.Year);
|
|
string noPrefix = startDate.Year + "-U";
|
|
lastNo++;
|
|
for (int i = 0; i < setUserListInformation.Count; i++)
|
|
{
|
|
string query = $"INSERT INTO UserList(No, Area, ApplicantNo, ApplicantDate, UID, Date, DateStart, DateEnd, ExtensionStatus, PaymentStatus) " +
|
|
$"VALUES('{noPrefix + lastNo}',{setUserListInformation[i].Area},'{setUserListInformation[i].ApplicantNo}','{setUserListInformation[i].Date.DateTimeDatabase()}',{setUserListInformation[i].UID},'{DateTime.Now.DateTimeDatabase()}','{startDate.DateOnly()}','{endDate.DateOnly()}',0,{(int)EPaymentStatus.StandBy})";
|
|
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
command.ExecuteNonQuery();
|
|
}
|
|
query = $"UPDATE ApplicantList SET RegistrationStatus={(int)ERegistrationStatus.Registration} Where No='{setUserListInformation[i].ApplicantNo}'";
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
command.ExecuteNonQuery();
|
|
}
|
|
ResultSetUserListInformation ResultSetUserListInformation = new ResultSetUserListInformation();
|
|
ResultSetUserListInformation.Area = GetAreaName(setUserListInformation[i].Area);
|
|
ResultSetUserListInformation.No = noPrefix + lastNo;
|
|
ResultSetUserListInformation.ApplicantNo = setUserListInformation[i].ApplicantNo;
|
|
ResultSetUserListInformation.CargoVehicleNumber = setUserListInformation[i].CargoVehicleNumber;
|
|
ResultSetUserListInformations.Add(ResultSetUserListInformation);
|
|
lastNo++;
|
|
}
|
|
return ResultSetUserListInformations;
|
|
}
|
|
public static void DeleteUserPayment(string no)
|
|
{
|
|
bool result = false;
|
|
var query = "UPDATE UserList SET PaymentStatus=@payment WHERE No=@no";
|
|
using(var updateCommand = _sqLiteConnection.CreateCommand())
|
|
{
|
|
updateCommand.CommandText = query;
|
|
updateCommand.Parameters.AddWithValue("@payment", EPaymentStatus.StandBy);
|
|
updateCommand.Parameters.AddWithValue("@no", no);
|
|
updateCommand.ExecuteNonQuery();
|
|
}
|
|
|
|
query = "DELETE FROM LedgerProceeds WHERE UserNo=@no";
|
|
using(var deleteCommand = _sqLiteConnection.CreateCommand())
|
|
{
|
|
deleteCommand.CommandText = query;
|
|
deleteCommand.Parameters.AddWithValue("@no", no);
|
|
deleteCommand.ExecuteNonQuery();
|
|
}
|
|
query = "DELETE FROM LedgerCertificate WHERE UserNo=@no";
|
|
using (var deleteCommand = _sqLiteConnection.CreateCommand())
|
|
{
|
|
deleteCommand.CommandText = query;
|
|
deleteCommand.Parameters.AddWithValue("@no", no);
|
|
deleteCommand.ExecuteNonQuery();
|
|
}
|
|
}
|
|
public static List<ProceedsData> GetLedgerProceedUser(string no)
|
|
{
|
|
var proceedsDatas = new List<ProceedsData>();
|
|
var query = "SELECT U.CargoVehicleNumber, U.PassengerVehicleNumber, U.Name, P.Type, P.Amount, P.Date From LedgerProceeds P INNER JOIN ViewUserList U ON P.UserNo = U.No WHERE P.UserNo=@no";
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
command.Parameters.AddWithValue("@no", no);
|
|
using (var reader = command.ExecuteReader())
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
var proceedsData = new ProceedsData();
|
|
proceedsData.CargoVehicleNumber = reader["CargoVehicleNumber"].ToString();
|
|
proceedsData.PassengerVehicleNumber = reader["PassengerVehicleNumber"].ToString();
|
|
proceedsData.Name = reader["Name"].ToString();
|
|
proceedsData.Amount = int.Parse(reader["Amount"].ToString());
|
|
proceedsData.Date = DateTime.Parse(reader["Date"].ToString());
|
|
proceedsData.Type = (EProceedsType)int.Parse(reader["Type"].ToString());
|
|
proceedsDatas.Add(proceedsData);
|
|
}
|
|
}
|
|
}
|
|
return proceedsDatas;
|
|
}
|
|
public static List<ProceedsData> GetLedgerProceeds(DateTime startDateTime, DateTime endDateTime)
|
|
{
|
|
var proceedsDatas = new List<ProceedsData>();
|
|
|
|
var query =
|
|
$"SELECT U.CargoVehicleNumber, U.PassengerVehicleNumber, U.Name, P.Type, P.Amount, P.Date From LedgerProceeds P INNER JOIN ViewUserList U ON P.UserNo = U.No Where P.Date >='{startDateTime.StartDateTime()}' And P.Date <='{endDateTime.EndDateTime()}'";
|
|
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
|
|
using (var reader = command.ExecuteReader())
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
var proceedsData = new ProceedsData();
|
|
proceedsData.CargoVehicleNumber = reader["CargoVehicleNumber"].ToString();
|
|
proceedsData.PassengerVehicleNumber = reader["PassengerVehicleNumber"].ToString();
|
|
proceedsData.Name = reader["Name"].ToString();
|
|
proceedsData.Amount = int.Parse(reader["Amount"].ToString());
|
|
proceedsData.Date = DateTime.Parse(reader["Date"].ToString());
|
|
proceedsData.Type = (EProceedsType)int.Parse(reader["Type"].ToString());
|
|
proceedsDatas.Add(proceedsData);
|
|
}
|
|
}
|
|
}
|
|
|
|
return proceedsDatas;
|
|
}
|
|
|
|
public static int GetAmountByCertificate(string userNo)
|
|
{
|
|
int amount = 0;
|
|
var query = $"SELECT SUM(Amount) as Amount From LedgerProceeds Where Type >= 0 And Type <= 1 And UserNo='{userNo}'";
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
using (var reader = command.ExecuteReader())
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
amount = int.Parse(reader["Amount"].ToString());
|
|
}
|
|
}
|
|
}
|
|
return amount;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 증명서 발급 대장 조회용
|
|
/// </summary>
|
|
/// <param name="startDateTime"></param>
|
|
/// <param name="endDateTime"></param>
|
|
/// <returns></returns>
|
|
public static List<CertificateInformation> GetLedgerCertificate(DateTime startDateTime, DateTime endDateTime)
|
|
{
|
|
List<CertificateInformation> certificateInformations = new List<CertificateInformation>();
|
|
var query =
|
|
$"SELECT U.Name, U.PersonalNumber, U.Address, U.CargoVehicleNumber, C.No, C.IssueDate, C.StartDate, C.EndDate, C.Amount, C.PaymentStatus From LedgerCertificate C INNER JOIN ViewUserList U ON C.UserNo = U.No Where C.IssueDate >='{startDateTime.StartDateTime()}' And C.IssueDate <='{endDateTime.EndDateTime()}'";
|
|
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
|
|
using (var reader = command.ExecuteReader())
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
CertificateInformation certificateInformation = new CertificateInformation();
|
|
|
|
certificateInformation.Name = reader["Name"].ToString();
|
|
certificateInformation.PersonalNumber = reader["PersonalNumber"].ToString();
|
|
certificateInformation.Address = reader["Address"].ToString();
|
|
certificateInformation.CargoVehicleNumber = reader["CargoVehicleNumber"].ToString();
|
|
certificateInformation.IssueNumber = reader["No"].ToString();
|
|
certificateInformation.Amount = int.Parse(reader["Amount"].ToString());
|
|
certificateInformation.IssueDate = DateTime.Parse(reader["IssueDate"].ToString());
|
|
certificateInformation.StartDate = DateTime.Parse(reader["StartDate"].ToString());
|
|
certificateInformation.EndDate = DateTime.Parse(reader["EndDate"].ToString());
|
|
certificateInformation.PaymentStatus = (EPaymentStatus)Convert.ToInt32(reader["PaymentStatus"]);
|
|
certificateInformations.Add(certificateInformation);
|
|
}
|
|
}
|
|
}
|
|
|
|
return certificateInformations;
|
|
}
|
|
|
|
public static CertificateInformation GetUserCertificate(string no)
|
|
{
|
|
List<AreaSetting> areaSettings = GetAreaSettings();
|
|
CertificateInformation certificateInformation = new CertificateInformation();
|
|
var query = $"SELECT * FROM ViewUserList Where No='{no}'";
|
|
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
|
|
using (var reader = command.ExecuteReader())
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
certificateInformation.Name = reader["Name"].ToString();
|
|
certificateInformation.PersonalNumber = reader["PersonalNumber"].ToString();
|
|
certificateInformation.Address = reader["Address"].ToString();
|
|
certificateInformation.CargoVehicleNumber = reader["CargoVehicleNumber"].ToString();
|
|
int areaNo = Convert.ToInt32(reader["Area"].ToString());
|
|
certificateInformation.Area = GetAreaName(areaNo);
|
|
certificateInformation.StartDate = DateTime.Parse(reader["DateStart"].ToString());
|
|
certificateInformation.EndDate = DateTime.Parse(reader["DateEnd"].ToString());
|
|
}
|
|
}
|
|
}
|
|
/// Amount 의
|
|
query = $"SELECT SUM(AMOUNT) FROM LedgerProceeds Where UserNo='{no}' And (Type={(int)EProceedsType.Fees} Or Type={(int)EProceedsType.ExtensionFees})";
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
|
|
using (var reader = command.ExecuteReader())
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
string amountString = reader[0].ToString();
|
|
int amount = 0;
|
|
int.TryParse(amountString, out amount);
|
|
|
|
certificateInformation.Amount = amount;
|
|
}
|
|
}
|
|
}
|
|
|
|
return certificateInformation;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 발급 번호로 발급데이터 조회
|
|
/// </summary>
|
|
/// <param name="issueNumber"></param>
|
|
/// <returns></returns>
|
|
public static CertificateInformation GetCertificate(string issueNumber)
|
|
{
|
|
CertificateInformation certificateInformation = new CertificateInformation();
|
|
var query = $"SELECT C.UserNo, U.Name, U.PersonalNumber, U.Address, U.CargoVehicleNumber, C.Amount, U.Area, C.IssueDate, C.StartDate, C.EndDate, C.PaymentStatus From LedgerCertificate C INNER JOIN ViewUserList U ON (C.UserNo = U.No) Where C.No='{issueNumber}'";
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
|
|
using (var reader = command.ExecuteReader())
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
certificateInformation.IssueNumber = issueNumber;
|
|
certificateInformation.UserNo = reader["UserNo"].ToString();
|
|
certificateInformation.Name = reader["Name"].ToString();
|
|
certificateInformation.PersonalNumber = reader["PersonalNumber"].ToString();
|
|
certificateInformation.Address = reader["Address"].ToString();
|
|
certificateInformation.CargoVehicleNumber = reader["CargoVehicleNumber"].ToString();
|
|
certificateInformation.Amount = int.Parse(reader["Amount"].ToString());
|
|
certificateInformation.Area = reader["Area"].ToString();
|
|
certificateInformation.IssueDate = DateTime.Parse(reader["IssueDate"].ToString());
|
|
certificateInformation.StartDate = DateTime.Parse(reader["StartDate"].ToString());
|
|
certificateInformation.EndDate = DateTime.Parse(reader["EndDate"].ToString());
|
|
certificateInformation.PaymentStatus = (EPaymentStatus)Convert.ToInt32(reader["PaymentStatus"]);
|
|
}
|
|
}
|
|
}
|
|
|
|
return certificateInformation;
|
|
}
|
|
public static bool UpdatePaymentCertificate(string no, EPaymentStatus paymentStatus)
|
|
{
|
|
bool result = false;
|
|
var query = "UPDATE LedgerCertificate SET PaymentStatus=@payment WHERE No=@no";
|
|
using(var updateCommand = _sqLiteConnection.CreateCommand())
|
|
{
|
|
updateCommand.CommandText = query;
|
|
updateCommand.Parameters.AddWithValue("@payment", Convert.ToInt32(paymentStatus));
|
|
updateCommand.Parameters.AddWithValue("@no", no);
|
|
var updateResult = updateCommand.ExecuteNonQuery();
|
|
result = updateResult > 0;
|
|
}
|
|
return result;
|
|
}
|
|
public static int GetLastApplicantLots(int year)
|
|
{
|
|
return GetLastNo(EGetLastNumber.ApplicantListLots, year);
|
|
}
|
|
|
|
public static int GetLastApplicantWait(int year)
|
|
{
|
|
return GetLastNo(EGetLastNumber.ApplicantListWait, year);
|
|
}
|
|
|
|
public static int GetLastCertificate(int year)
|
|
{
|
|
return GetLastNo(EGetLastNumber.LedgerCertificate, year);
|
|
}
|
|
|
|
public static int GetLastUser(int year)
|
|
{
|
|
return GetLastNo(EGetLastNumber.UserList, year);
|
|
}
|
|
|
|
private static int GetLastNo(EGetLastNumber eGetLastNumber, int year)
|
|
{
|
|
string tableName = string.Empty;
|
|
string suffix = string.Empty;
|
|
switch (eGetLastNumber)
|
|
{
|
|
case EGetLastNumber.ApplicantListLots:
|
|
tableName = "ApplicantList";
|
|
suffix = "L";
|
|
break;
|
|
|
|
case EGetLastNumber.ApplicantListWait:
|
|
tableName = "ApplicantList";
|
|
suffix = "W";
|
|
break;
|
|
|
|
case EGetLastNumber.UserList:
|
|
tableName = "UserList";
|
|
suffix = "U";
|
|
break;
|
|
|
|
case EGetLastNumber.LedgerCertificate:
|
|
tableName = "LedgerCertificate";
|
|
suffix = "C";
|
|
break;
|
|
}
|
|
var query = $"SELECT No FROM {tableName} Where No Like '{year}-{suffix}%' Order By CAST(substr(No,7) as INTEGER) DESC LIMIT 1";
|
|
var lastNo = 0;
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
|
|
using (var reader = command.ExecuteReader())
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
var issueNumber = reader["No"].ToString();
|
|
issueNumber = issueNumber.Substring(6, issueNumber.Length - 6);
|
|
lastNo = int.Parse(issueNumber);
|
|
}
|
|
}
|
|
}
|
|
return lastNo;
|
|
}
|
|
|
|
public static int GetLastDropOrder(DateTime DropDate)
|
|
{
|
|
var lastDropOrder = 0;
|
|
var query = $"SELECT DropOrder FROM ApplicantList WHERE DropDate='{DropDate.DateTimeDatabase()}' ORDER BY DropOrder DESC LIMIT 1";
|
|
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
|
|
using (var reader = command.ExecuteReader())
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
var dropOrder = reader["DropOrder"].ToString();
|
|
lastDropOrder = Convert.ToInt32(dropOrder);
|
|
}
|
|
}
|
|
}
|
|
return lastDropOrder;
|
|
}
|
|
|
|
public static bool SetCertificate(SetCertificateInformation setCertificateInformation)
|
|
{
|
|
var query =
|
|
$"INSERT INTO LedgerCertificate(No, UserNo, IssueDate, StartDate, EndDate, Amount, PaymentStatus) VALUES(@no, @userNo, @issueDate, @startDate, @endDate, @amount, @paymentStatus) ";
|
|
|
|
using (var insertCommand = _sqLiteConnection.CreateCommand())
|
|
{
|
|
insertCommand.CommandText = query;
|
|
insertCommand.Parameters.AddWithValue("@no", setCertificateInformation.No);
|
|
insertCommand.Parameters.AddWithValue("@userNo", setCertificateInformation.UserNo);
|
|
insertCommand.Parameters.AddWithValue("@issueDate", setCertificateInformation.IssueDate.DateTimeDatabase());
|
|
insertCommand.Parameters.AddWithValue("@startDate", setCertificateInformation.StartDate.StartDateTime());
|
|
insertCommand.Parameters.AddWithValue("@endDate", setCertificateInformation.EndDate.StartDateTime());
|
|
insertCommand.Parameters.AddWithValue("@amount", setCertificateInformation.Amount);
|
|
insertCommand.Parameters.AddWithValue("@paymentStatus",Convert.ToInt32(EPaymentStatus.StandBy));
|
|
|
|
|
|
return insertCommand.ExecuteNonQuery() > 0;
|
|
}
|
|
}
|
|
|
|
public static bool SetProceeds(SetProceedsInfo setProceedsInfo)
|
|
{
|
|
var query =
|
|
$"INSERT INTO LedgerProceeds VALUES('{setProceedsInfo.No}','{(int)setProceedsInfo.Type}','{setProceedsInfo.Amount}','{setProceedsInfo.Date.DateTimeDatabase()}') ";
|
|
|
|
using (var insertCommand = _sqLiteConnection.CreateCommand())
|
|
{
|
|
insertCommand.CommandText = query;
|
|
return insertCommand.ExecuteNonQuery() > 0;
|
|
}
|
|
}
|
|
|
|
public static EVehicleType GetApplicantVehicleType(string no)
|
|
{
|
|
var query = $"SELECT VehicleType From ViewApplicantList Where No='{no}'";
|
|
int type = 0;
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
|
|
using (var reader = command.ExecuteReader())
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
type = int.Parse(reader["VehicleType"].ToString());
|
|
}
|
|
}
|
|
}
|
|
return (EVehicleType)type;
|
|
}
|
|
|
|
public static EVehicleType GetUserListVehicleType(string no)
|
|
{
|
|
var query = $"SELECT VehicleType From ViewUserList Where No='{no}'";
|
|
int type = 0;
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
|
|
using (var reader = command.ExecuteReader())
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
type = int.Parse(reader["VehicleType"].ToString());
|
|
}
|
|
}
|
|
}
|
|
return (EVehicleType)type;
|
|
}
|
|
|
|
public static LeftAreaInformation GetLeftArea(DateTime startDateTime)
|
|
{
|
|
LeftAreaInformation leftAreaInformation = new LeftAreaInformation();
|
|
leftAreaInformation.StartDateTime = startDateTime;
|
|
leftAreaInformation.Large = new List<AreaSetting>();
|
|
leftAreaInformation.OverSized = new List<AreaSetting>();
|
|
List<AreaSetting> areaSettings = GetAreaSettings();
|
|
|
|
var query = $"SELECT Area FROM UserList Where DateEnd > '{startDateTime.Date.DateTimeDatabase()}' And PaymentStatus < {(int)EPaymentStatus.Refund}";
|
|
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
|
|
using (var reader = command.ExecuteReader())
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
int area = Convert.ToInt32(reader["Area"].ToString());
|
|
for (int i = 0; i < areaSettings.Count; i++)
|
|
{
|
|
if (areaSettings[i].No == area)
|
|
{
|
|
areaSettings.RemoveAt(i);
|
|
break;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
foreach (var areaSetting in areaSettings)
|
|
{
|
|
switch(areaSetting.AreaType)
|
|
{
|
|
case EAreaType.Large:
|
|
leftAreaInformation.Large.Add(areaSetting);
|
|
break;
|
|
|
|
case EAreaType.OverSized:
|
|
leftAreaInformation.OverSized.Add(areaSetting);
|
|
break;
|
|
}
|
|
}
|
|
return leftAreaInformation;
|
|
}
|
|
|
|
public static LeftUserInformation GetLeftUserLots(DateTime startDateTime)
|
|
{
|
|
LeftUserInformation leftUserInformation = new LeftUserInformation();
|
|
leftUserInformation.Large = 0;
|
|
leftUserInformation.OverSized = 0;
|
|
var query = $"SELECT VehicleType , COUNT(VehicleType) as Count FROM ViewApplicantList Where ApplicantType={(int)EApplicantType.Lots} And RegistrationStatus={(int)ERegistrationStatus.Wait} Group By VehicleType";
|
|
leftUserInformation = GetLeftUser(startDateTime, leftUserInformation, query);
|
|
|
|
return leftUserInformation;
|
|
}
|
|
|
|
public static LeftUserInformation GetLeftUserWait(DateTime startDateTime)
|
|
{
|
|
LeftUserInformation leftUserInformation = new LeftUserInformation();
|
|
leftUserInformation.Large = 0;
|
|
leftUserInformation.OverSized = 0;
|
|
var query = $"SELECT VehicleType , COUNT(VehicleType) as Count From ViewApplicantList Where (ApplicantType={(int)EApplicantType.LotsOut} OR ApplicantType={(int)EApplicantType.Wait}) And RegistrationStatus={(int)ERegistrationStatus.Wait} Group By VehicleType";
|
|
leftUserInformation = GetLeftUser(startDateTime, leftUserInformation, query);
|
|
|
|
return leftUserInformation;
|
|
}
|
|
|
|
public static LeftUserInformation GetLeftUser(DateTime startDateTime, LeftUserInformation leftUserInformation, string query)
|
|
{
|
|
LeftUserInformation leftUser = leftUserInformation;
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
|
|
using (var reader = command.ExecuteReader())
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
int vehicleType = int.Parse(reader["VehicleType"].ToString());
|
|
int count = int.Parse(reader["Count"].ToString());
|
|
switch ((EVehicleType)vehicleType)
|
|
{
|
|
case EVehicleType.Large_Down:
|
|
case EVehicleType.Large_Up:
|
|
leftUser.Large += count;
|
|
break;
|
|
|
|
case EVehicleType.Oversized_Down:
|
|
case EVehicleType.Oversized_Up:
|
|
leftUser.OverSized += count;
|
|
break;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
return leftUser;
|
|
}
|
|
|
|
public static Settings LoadSettings()
|
|
{
|
|
var settings = new Settings();
|
|
settings.GarageName = string.Empty; // Null 방지;
|
|
var query =
|
|
"SELECT * FROM(SELECT * From Settings Where (SettingName, SettingDate) in (Select SettingName, MAX(SettingDate) as SettingDate From Settings Group by SettingName) Order by SettingDate desc) t Group By t.SettingName";
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
|
|
using (var reader = command.ExecuteReader())
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
var name = reader["SettingName"].ToString();
|
|
var value = reader["SettingValue"].ToString();
|
|
var date = reader["SettingDate"].ToString();
|
|
int intValue;
|
|
|
|
switch (name)
|
|
{
|
|
case "GarageName":
|
|
settings.GarageName = value;
|
|
break;
|
|
|
|
case "CargoLargeFee":
|
|
int.TryParse(value, out intValue);
|
|
settings.CargoLargeFee = intValue;
|
|
break;
|
|
|
|
case "CargoOversizedFee":
|
|
|
|
int.TryParse(value, out intValue);
|
|
settings.CargoOversizedFee = intValue;
|
|
break;
|
|
|
|
case "CargoLargeCertificateFee":
|
|
int.TryParse(value, out intValue);
|
|
settings.CargoLargeCertificateFee = intValue;
|
|
break;
|
|
|
|
case "CargoOversizedCertificateFee":
|
|
int.TryParse(value, out intValue);
|
|
settings.CargoOversizedCertificateFee = intValue;
|
|
break;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
return settings;
|
|
}
|
|
|
|
public static void SaveSettings(Settings settings)
|
|
{
|
|
var originalSettings = LoadSettings();
|
|
|
|
if (settings.GarageName != originalSettings.GarageName)
|
|
{
|
|
var query =
|
|
$"INSERT INTO Settings VALUES('GarageName','{settings.GarageName}','{DateTime.Now.DateTimeDatabase()}') ";
|
|
using (var insertCommand = _sqLiteConnection.CreateCommand())
|
|
{
|
|
insertCommand.CommandText = query;
|
|
insertCommand.ExecuteNonQuery();
|
|
}
|
|
}
|
|
|
|
if (settings.CargoLargeFee != originalSettings.CargoLargeFee)
|
|
{
|
|
var query =
|
|
$"INSERT INTO Settings VALUES('CargoLargeFee','{settings.CargoLargeFee}','{DateTime.Now.DateTimeDatabase()}') ";
|
|
using (var insertCommand = _sqLiteConnection.CreateCommand())
|
|
{
|
|
insertCommand.CommandText = query;
|
|
insertCommand.ExecuteNonQuery();
|
|
}
|
|
}
|
|
if (settings.CargoOversizedFee != originalSettings.CargoOversizedFee)
|
|
{
|
|
var query =
|
|
$"INSERT INTO Settings VALUES('CargoOversizedFee','{settings.CargoOversizedFee}','{DateTime.Now.DateTimeDatabase()}') ";
|
|
using (var insertCommand = _sqLiteConnection.CreateCommand())
|
|
{
|
|
insertCommand.CommandText = query;
|
|
insertCommand.ExecuteNonQuery();
|
|
}
|
|
}
|
|
|
|
if (settings.CargoLargeCertificateFee != originalSettings.CargoLargeCertificateFee)
|
|
{
|
|
var query =
|
|
$"INSERT INTO Settings VALUES('CargoLargeCertificateFee','{settings.CargoLargeCertificateFee}','{DateTime.Now.DateTimeDatabase()}') ";
|
|
using (var insertCommand = _sqLiteConnection.CreateCommand())
|
|
{
|
|
insertCommand.CommandText = query;
|
|
insertCommand.ExecuteNonQuery();
|
|
}
|
|
}
|
|
|
|
if (settings.CargoOversizedCertificateFee != originalSettings.CargoOversizedCertificateFee)
|
|
{
|
|
var query =
|
|
$"INSERT INTO Settings VALUES('CargoOversizedCertificateFee','{settings.CargoOversizedCertificateFee}','{DateTime.Now.DateTimeDatabase()}') ";
|
|
using (var insertCommand = _sqLiteConnection.CreateCommand())
|
|
{
|
|
insertCommand.CommandText = query;
|
|
insertCommand.ExecuteNonQuery();
|
|
}
|
|
}
|
|
}
|
|
|
|
#region AreaSetting
|
|
|
|
public static int SetAreaSetting(AreaSetting areaSetting)
|
|
{
|
|
int no = 0;
|
|
var query = $"INSERT INTO AreaSetting(AreaName, AreaType) VALUES(@name,@type);" +
|
|
$"select last_insert_rowid();";
|
|
using (var insertCommand = _sqLiteConnection.CreateCommand())
|
|
{
|
|
insertCommand.CommandText = query;
|
|
insertCommand.Parameters.AddWithValue("@name", areaSetting.AreaName);
|
|
insertCommand.Parameters.AddWithValue("@type", (int)areaSetting.AreaType);
|
|
|
|
no = Convert.ToInt32(insertCommand.ExecuteScalar().ToString());
|
|
}
|
|
return no;
|
|
}
|
|
|
|
public static bool CheckDuplicateAreaSettingName(string areaName)
|
|
{
|
|
bool result = true;
|
|
var query = $"SELECT COUNT(*) FROM AreaSetting WHERE AreaName=@name;";
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
command.Parameters.AddWithValue("@name", areaName);
|
|
using (var reader = command.ExecuteReader())
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
result = Convert.ToInt32(reader[0].ToString()) != 0;
|
|
}
|
|
}
|
|
}
|
|
return result;
|
|
}
|
|
|
|
public static bool UpdateAreaSetting(AreaSetting areaSetting)
|
|
{
|
|
bool result = false;
|
|
var query = "UPDATE AreaSetting Set AreaName=@name, AreaType=@type WHERE No=@no";
|
|
using (var updateCommand = _sqLiteConnection.CreateCommand())
|
|
{
|
|
updateCommand.CommandText = query;
|
|
updateCommand.Parameters.AddWithValue("@name", areaSetting.AreaName);
|
|
updateCommand.Parameters.AddWithValue("@type", areaSetting.AreaType);
|
|
updateCommand.Parameters.AddWithValue("@no", areaSetting.No);
|
|
var updateResult = updateCommand.ExecuteNonQuery();
|
|
result = updateResult > 0;
|
|
}
|
|
return result;
|
|
}
|
|
|
|
public static List<AreaSetting> GetAreaSettings()
|
|
{
|
|
List<AreaSetting> areaSettings = new List<AreaSetting>();
|
|
var query = $"SELECT * FROM AreaSetting;";
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
using (var reader = command.ExecuteReader())
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
AreaSetting areaSetting = new AreaSetting();
|
|
areaSetting.No = Convert.ToInt32(reader["No"].ToString());
|
|
areaSetting.AreaName = reader["AreaName"].ToString();
|
|
areaSetting.AreaType = (EAreaType)Convert.ToInt32(reader["AreaType"].ToString());
|
|
areaSettings.Add(areaSetting);
|
|
}
|
|
}
|
|
}
|
|
return areaSettings;
|
|
}
|
|
public static string GetAreaName(int no)
|
|
{
|
|
string areaName = string.Empty;
|
|
var query = "SELECT AreaName FROM AreaSetting WHERE No=@no";
|
|
using (var command = _sqLiteConnection.CreateCommand())
|
|
{
|
|
command.CommandText = query;
|
|
command.Parameters.AddWithValue("@no", no);
|
|
using (var reader = command.ExecuteReader())
|
|
{
|
|
while(reader.Read())
|
|
{
|
|
areaName = reader["AreaName"].ToString();
|
|
}
|
|
}
|
|
}
|
|
return areaName;
|
|
}
|
|
|
|
#endregion AreaSetting
|
|
}
|
|
} |