public DataTable GetAllProject()
{
SqlCmdBuilder cmd = DataBaseHelpers.CreateADPPipelineCommand();
cmd.Query = @"SELECT id,nama,kota,alamat,startdate,no_kontrak nokontrak,no_spk nospk,telp_spk telpspk
FROM adp_project";
return(cmd.GetTable());
}
public static DataTable RetrieveWebLoginUserByUsername(string uName)
{
SqlCmdBuilder cmd = DataBaseHelpers.CreateADPPipelineCommand();
cmd.Query = @"SELECT USERNAME, PASSWORD
FROM ADP_USER
WHERE UPPER(Username)=@uName AND allow_login = 1";
cmd.AddParameter("uName", SqlCmdParameterDirection.Input, uName.ToUpper());
return(cmd.GetTable());
}
public DataTable RetrieveRoleById(string idRole)
{
SqlCmdBuilder cmd = DataBaseHelpers.CreateADPPipelineCommand();
cmd.Query = @"SELECT *
FROM ADP_ROLE
WHERE ID_ROLE=@idRole";
cmd.AddParameter("idRole", SqlCmdParameterDirection.Input, idRole);
return(cmd.GetTable());
}
public DataTable RetrieveRoleByName(string name)
{
SqlCmdBuilder cmd = DataBaseHelpers.CreateCommand();
cmd.Query = @"SELECT *
FROM ADP_ROLE
WHERE NAMA_ROLE=@name";
cmd.AddParameter("name", SqlCmdParameterDirection.Input, name);
return(cmd.GetTable());
}
public DataTable RetrieveUser(string uName, string pass)
{
SqlCmdBuilder cmd = DataBaseHelpers.CreateADPPipelineCommand();
cmd.Query = @"SELECT *
FROM ADP_USER
WHERE UPPER(USERNAME)=@uName AND PASSWORD=@pass";
cmd.AddParameter("uName", SqlCmdParameterDirection.Input, uName.ToUpper());
cmd.AddParameter("pass", SqlCmdParameterDirection.Input, ADP.Encryption.CryptoTools.ActionEncrypt(pass.ToUpper()));
return(cmd.GetTable());
}
public DataTable RetrieveUser(string uName)
{
SqlCmdBuilder cmd = DataBaseHelpers.CreateADPPipelineCommand();
cmd.Query = @"SELECT *
FROM ADP_USER
WHERE UPPER(USERNAME)=@uName";
cmd.AddParameter("uName", SqlCmdParameterDirection.Input, uName.ToUpper());
return(cmd.GetTable());
}
public bool CreateUser(string Username, string Password, string IdRole)
{
SqlCmdBuilder cmd = DataBaseHelpers.CreateADPPipelineCommand();
cmd.Query = "INSERT INTO ADP_USER (USERNAME, PASSWORD, ID_ROLE)" +
"VALUES (@user, @pass, @idrole)";
cmd.AddParameter("user", SqlCmdParameterDirection.Input, Username);
cmd.AddParameter("pass", SqlCmdParameterDirection.Input, Password);
cmd.AddParameter("idrole", SqlCmdParameterDirection.Input, IdRole);
return(cmd.ExecuteNonQuery() > 0);
}
private bool TesKoneksi(string Server, string Database, string UserId, string Password, out string pesanError)
{
pesanError = string.Empty;
bool isSuccess = false;
SqlCmdBuilder cmd = new SqlCmdBuilder(tbServer.Text, tbDB.Text, tbUser.Text, tbPassword.Text);
cmd.TestConnection(out pesanError);
if (string.IsNullOrEmpty(pesanError))
{
isSuccess = true;
}
return(isSuccess);
}
public void UpdateStatus(string email, Data_AppUserFile.eUserStatus newStatus, IMyLog log)
{
SqlCmdBuilder cmd = new SqlCmdBuilder(TABLE_NAME);
cmd.Add("[AccountStatus]", newStatus.ToIntString());
string strCmd = cmd.GetSql_Update(string.Format("WHERE [Email] like '{0}'", email));
using (SqlDisposable s = new SqlDisposable(Db, strCmd))
{
if (s.Reader.RecordsAffected != 1)
{
log.Error("UpdateAll: *** unexpected RecordsAffected != 1");
}
}
}
public DataTable RetrieveUserRoles(string username)
{
SqlCmdBuilder cmd = DataBaseHelpers.CreateADPPipelineCommand();
cmd.Query = @"SELECT *
FROM ADP_USER usr INNER JOIN ADP_ROLE uro " +
@"ON usr.id_role = uro.id_role";
if (!string.IsNullOrEmpty(username))
{
cmd.Query += "WHERE UPPER(usr.username)";
cmd.AddParameter("username", SqlCmdParameterDirection.Input, username.ToUpper());
}
return(cmd.GetTable());
}
public bool CreateEmployee(string nama, string tempLahir, DateTime tglLahir, string noTlp, string email, string jabatan)
{
SqlCmdBuilder cmd = DataBaseHelpers.CreateADPPipelineCommand();
cmd.Query = "INSERT INTO ADP_EMPLOYEE (ID_EMPLOYEE, NAMA, TEMPAT_LAHIR, TGL_LAHIR, NO_TELP, MAIL_ADDRESS, JABATAN) " +
"VALUES (@idemp, @nm, @tempLahir, @tglLahir, @notlp, @email, @jabatan)";
cmd.AddParameter("idemp", SqlCmdParameterDirection.Input, Guid.NewGuid().ToString());
cmd.AddParameter("nm", SqlCmdParameterDirection.Input, nama);
cmd.AddParameter("tempLahir", SqlCmdParameterDirection.Input, tempLahir);
cmd.AddParameter("tglLahir", SqlCmdParameterDirection.Input, tglLahir);
cmd.AddParameter("notlp", SqlCmdParameterDirection.Input, noTlp);
cmd.AddParameter("email", SqlCmdParameterDirection.Input, email);
cmd.AddParameter("jabatan", SqlCmdParameterDirection.Input, jabatan);
return(cmd.ExecuteNonQuery() > 0);
}
public bool UpdateProject(string id, string nama, string kota, string alamat, DateTime startDate, string no_kontrak, string no_spk, string telp_spk)
{
SqlCmdBuilder cmd = DataBaseHelpers.CreateADPPipelineCommand();
cmd.Query = @"UPDATE adp_project SET nama=@nama,kota=@kota,alamat=@alamat,startDate=@startDate,no_kontrak=@noKontrak,no_spk=@noSpk,telp_spk=@telp
WHERE id=@id";
cmd.AddParameter("nama", SqlCmdParameterDirection.Input, nama);
cmd.AddParameter("kota", SqlCmdParameterDirection.Input, kota);
cmd.AddParameter("alamat", SqlCmdParameterDirection.Input, alamat);
cmd.AddParameter("startDate", SqlCmdParameterDirection.Input, startDate);
cmd.AddParameter("noKontrak", SqlCmdParameterDirection.Input, no_kontrak);
cmd.AddParameter("noSpk", SqlCmdParameterDirection.Input, no_spk);
cmd.AddParameter("telp", SqlCmdParameterDirection.Input, telp_spk);
cmd.AddParameter("id", SqlCmdParameterDirection.Input, id);
return(cmd.ExecuteNonQuery() > 0);
}
public bool InsertProject(string id, string nama, string kota, string alamat, DateTime startDate, string no_kontrak, string no_spk, string telp_spk)
{
SqlCmdBuilder cmd = DataBaseHelpers.CreateADPPipelineCommand();
cmd.Query = @"INSERT INTO adp_project (id,nama,kota,alamat,startdate,no_kontrak,no_spk,telp_spk)
VALUES(@id,@nama,@kota,@alamat,@startdate,@nokontrak,@nospk,@telpspk)";
cmd.AddParameter("id", SqlCmdParameterDirection.Input, id);
cmd.AddParameter("nama", SqlCmdParameterDirection.Input, nama);
cmd.AddParameter("kota", SqlCmdParameterDirection.Input, kota);
cmd.AddParameter("alamat", SqlCmdParameterDirection.Input, alamat);
cmd.AddParameter("startdate", SqlCmdParameterDirection.Input, startDate);
cmd.AddParameter("nokontrak", SqlCmdParameterDirection.Input, no_kontrak);
cmd.AddParameter("nospk", SqlCmdParameterDirection.Input, no_spk);
cmd.AddParameter("telpspk", SqlCmdParameterDirection.Input, telp_spk);
return(cmd.ExecuteNonQuery() > 0);
}
public void UpdateCounters_Queued(string email, long timeNow, int howMany, IMyLog log)
{
SqlCmdBuilder cmd = new SqlCmdBuilder(TABLE_NAME);
cmd.Add("[LastMsgQueuedUtc]", SqlExtensions.SqlDateUtcNow().ToString());
cmd.AddInc("[NoOfQueuedMessages]", howMany);
cmd.AddDec("[RemainingMessages]", howMany);
cmd.Add("[AccountStatus]", String.Format("CASE WHEN {0}={1} THEN {2} ELSE {0} END",
"[AccountStatus]",
Data_AppUserFile.eUserStatus.verified_welcome_No_sent.ToIntString(),
Data_AppUserFile.eUserStatus.verified_welcome_queued.ToIntString()));
string strCmd = cmd.GetSql_Update(string.Format("WHERE [Email] like '{0}'", email));
using (SqlDisposable s = new SqlDisposable(Db, strCmd))
{
if (s.Reader.RecordsAffected != 1)
{
log.Error("StoreNew: *** unexpected RecordsAffected != 1");
}
}
}
public void UpdateAll(
string email,
string userName,
string password,
string creationIp,
string telNumbers,
Data_AppUserFile.eUserStatus status,
Int32 minDelayInSeconds,
Int64 noOfQueuedMessages,
Int64 noOfSentMessages,
Int64 noOfBytesSent,
Int64 remainingMsg,
IMyLog log)
{
SqlCmdBuilder cmd = new SqlCmdBuilder(TABLE_NAME);
cmd.Add("[UserName]", userName.Quote());
cmd.Add("[Password]", password.Quote());
cmd.Add("[CreationIp]", creationIp.Quote());
cmd.Add("[MobileNumbers]", telNumbers.Quote());
cmd.Add("[AccountStatus]", status.ToIntString());
cmd.Add("[MinDelayInSeconds]", minDelayInSeconds.ToString());
cmd.Add("[NoOfQueuedMessages]", noOfQueuedMessages.ToString());
cmd.Add("[NoOfSentMessages]", noOfSentMessages.ToString());
cmd.Add("[NoOfBytesSent]", noOfBytesSent.ToString());
cmd.Add("[RemainingMessages]", remainingMsg.ToString());
string strCmd = cmd.GetSql_Update(string.Format("WHERE [Email] like '{0}'", email));
using (SqlDisposable s = new SqlDisposable(Db, strCmd))
{
if (s.Reader.RecordsAffected != 1)
{
log.Error("UpdateAll: *** unexpected RecordsAffected != 1");
}
}
}
public void StoreNew(Data_AppUserFile data, out bool fileArleadyUsed, IMyLog log)
{
fileArleadyUsed = false;
if (HasAccount(data.Email, log))
{
fileArleadyUsed = true;
return;
}
SqlCmdBuilder cmd = new SqlCmdBuilder(TABLE_NAME);
cmd.Add("[UserName]", data.UserName.Quote());
cmd.Add("[MobileNumbers]", data.MoblieAllNumbers.Quote());
cmd.Add("[Email]", data.Email.Quote());
cmd.Add("[Password]", data.Password.Quote());
cmd.Add("[CreationDateUtc]", data.CreationDate.SqlDate().ToString());
cmd.Add("[CreationIp]", data.CreationIp.Quote());
cmd.Add("[ApiGuId]", data.ApiGuId.Quote());
cmd.Add("[NoOfQueuedMessages]", data.NoOfQueuedMessages.ToString());
cmd.Add("[NoOfSentMessages]", data.NoOfSentMessages.ToString());
cmd.Add("[NoOfBytesSent]", data.NoOfBytesSent.ToString());
cmd.Add("[RemainingMessages]", data.RemainingMessages.ToString());
cmd.Add("[MinDelayInSeconds]", data.MinDelayInSeconds.ToString());
cmd.Add("[AccountStatus]", data.AccountStatus.ToIntString());
string strCmd = cmd.GetSql_InsertSelect();
//System.Diagnostics.Debug.WriteLine(strCmd);//mg remove
using (SqlDisposable s = new SqlDisposable(Db, strCmd))
{
if (s.Reader.RecordsAffected != 1)
{
log.Error("StoreNew: *** unexpected RecordsAffected != 1");
}
}
}
