public async Task <IHttpActionResult> GetBinsByManager(string Id)
{
if (!ModelState.IsValid)
{
return(BadRequest(ModelState));
}
DataTable dt = Utils.ExecuteTable(SQLCommands.GetBinsByManager(Id));
List <BinModel> bin = Utils.DataTableToList <BinModel>(dt);
foreach (BinModel b in bin)
{
DataTable dtc = Utils.ExecuteTable(SQLCommands.GetCollectorsByBin(b.Id));
b.Collectors = Utils.DataTableToList <CollectorModel>(dtc);
}
if (dt.Rows.Count == 0)
{
return(NotFound());
}
else
{
return(Ok(bin));
}
}
public async Task <JsonResult> AddManager(ManagerViewModel model)
{
if (ModelState.IsValid)
{
var user = new ApplicationUser {
UserName = model.Email, Email = model.Email, Name = model.Name, Status = -1
};
var result = await UserManager.CreateAsync(user, model.Password);
if (result.Succeeded)
{
await SignInManager.SignInAsync(user, isPersistent : false, rememberBrowser : false);
var createdUser = Utils.ExecuteTable(SQLCommands.GetUserEmail(model.Email));
var list = Utils.DataTableToList <AspNetUser>(createdUser);
Utils.ExecuteNonQuery(SQLCommands.AddZone(model.Zone));
var createdZone = Utils.ExecuteTable(SQLCommands.GetZoneName(model.Zone));
var list2 = Utils.DataTableToList <ZoneModel>(createdZone);
Utils.ExecuteNonQuery(SQLCommands.AddManagerZoneAssociation(list.ElementAt(0).Id, list2.ElementAt(0).ZoneId));
return(Json(1));
}
AddErrors(result);
}
// If we got this far, something failed, redisplay form
return(Json(-1));
}
public async Task <IHttpActionResult> GetManagers()
{
if (!ModelState.IsValid)
{
return(BadRequest(ModelState));
}
DataTable dt = Utils.ExecuteTable(SQLCommands.GetManagers());
List <ManagerModel> managers = Utils.DataTableToList <ManagerModel>(dt);
foreach (ManagerModel m in managers)
{
DataTable dtc = Utils.ExecuteTable(SQLCommands.GetCollectorsByManager(m.Id));
m.Collectors = Utils.DataTableToList <CollectorModel>(dtc);
}
if (dt.Rows.Count == 0)
{
return(NotFound());
}
else
{
return(Ok(managers));
}
}
private void CreateButton_Click(object sender, EventArgs e)
{
username = usernameTextBox.Text;
password = passwordTextBox.Text;
reenterpassword = reenterPasswordTextBox.Text;
HDClasses.SQLCommands Com = new SQLCommands();
create = Com.UserDataSave(username, password);
if (create == true)
{
MessageBox.Show("An account already exists for this username. Please log in.");
usernameTextBox.Clear();
passwordTextBox.Clear();
reenterPasswordTextBox.Clear();
}
else
{
// validates for insufficient fields
if (usernameTextBox.Text == "")
{
MessageBox.Show("Enter a username ");
}
else if (username.Length < 5)
{
MessageBox.Show("Enter up to 5 characters as a username ");
}
if (passwordTextBox.Text == "")
{
MessageBox.Show("Enter a password that contains up to 8 characters with an uppercase letter and a number ");
}
else if (password.Length < 8)
{
MessageBox.Show("Enter up to 8 characters with an uppercase letter and a number for the password ");
}
else if (reenterPasswordTextBox.Text != passwordTextBox.Text)
{
MessageBox.Show("Passwords must match and meet requirements ");
}
else
{
SqlConnection con;
SqlDataAdapter da;
con = new SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=C:\\Users\\Lioness\\Documents\\GitHub\\HRHelpdesk\\HRHelpdeskApp\\HRHelpdeskApp\\HRHelpdeskApp\\HDDatabase.mdf");
con.Open();
System.Data.SqlClient.SqlCommand cmmd = new SqlCommand("INSERT INTO Login (Username, Password) VALUES (@Username, @Password)", con);
cmmd.Parameters.AddWithValue("@Username", usernameTextBox.Text);
cmmd.Parameters.AddWithValue("@Password", passwordTextBox.Text);
cmmd.ExecuteNonQuery();
MessageBox.Show("Your account has been created. Please sign in.");
Login loginform = new Login();
this.Hide();
loginform.ShowDialog();
}
}
}
public CaseList()
{
InitializeComponent();
SQLCommands com = new SQLCommands();
var names = com.RetrieveNameList();
activeCaseListBox.DataSource = names;
}
public void insertPemenang(string _norek, string _nocif, string _noundian, string _nama, string _status, string _hadiah, int level)
{
//MySqlCommand cmd;
MySqlDataReader reader = null;
try
{
mysql = new SQLCommands();
//MySqlConnection conn = connectionstring.getKoneksi();
//MySqlCommand command = conn.CreateCommand();
//conn.Open();
//MySqlCommand cmd = new MySqlCommand(cmdtext2, conn);
//cmd.ExecuteNonQuery();
string cmdtext2 = "select * from transaksi where no_cif = '" + _nocif.ToString() + "'";
reader = mysql.SQLSelect(cmdtext2);
if (reader.Read())
{
if (Convert.ToInt32(reader["level"]) < level)
{
reader.Close();
DialogResult dialogResult = MessageBox.Show("NO CIF sudah ada, gantikan hadiahnya ???", "Peringatan", MessageBoxButtons.YesNo);
if (dialogResult == DialogResult.Yes)
{
string update = "Update transaksi set hadiah='" + _hadiah.ToString() + "', level = '" + level.ToString() + "',no_undian = '" + _noundian.ToString() + "' where no_cif = '" + _nocif.ToString() + "'";
//MySqlCommand cmd2 = new MySqlCommand(update, conn);
//cmd2.ExecuteNonQuery();
mysql.SQLStringCmd(update);
}
else if (dialogResult == DialogResult.No)
{
//do something else
}
}
}
else
{
reader.Close();
string cmdText = "INSERT INTO transaksi(no_rek,no_cif,no_undian,nama,status,hadiah,level) VALUES ('" + _norek.ToString() + "', '" + _nocif.ToString() + "', '" + _noundian.ToString() + "','" + _nama.ToString() + "','" + _status.ToString() + "','" + _hadiah.ToString() + "','" + level + "')";
//MySqlCommand cmd3 = new MySqlCommand(cmdText, conn);
//cmd3.ExecuteNonQuery();
mysql.SQLStringCmd(cmdText);
}
//conn.Close();
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
finally {
mysql.SQLConnectionClose();
}
}
public void UpdateCompleteScenario(int Scenario, string TableName)
{
using (OleDbConnection conn = GetConnection.BIPSConn)
{
using (OleDbCommand cmd = new OleDbCommand(SQLCommands.UpdateScenarioTableComplete(tableSelection.ScenarioTable, scenario), conn))
{
cmd.ExecuteNonQuery();
}
}
}
private void BuildInputData()
{
i = 0;
using (OleDbConnection conn = GetConnection.BIPSConn)
{
using (OleDbCommand cmd = new OleDbCommand(SQLCommands.GetInputSQL(inputTable, scenario), conn))
{
using (OleDbDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
OutputData outputData = new OutputData();
outputData.OHUB = reader.IsDBNull((int)InputColumns.OHUB) ? "" : reader.GetString((int)InputColumns.OHUB);
outputData.HubMiles = reader.IsDBNull((int)InputColumns.HUB_MILES) ? 0 : reader.GetInt32((int)InputColumns.HUB_MILES);
outputData.LaneEmptyVol = reader.IsDBNull((int)InputColumns.MT_VOLUME) ? 0 : reader.GetInt32((int)InputColumns.MT_VOLUME);
outputData.AvailableBidVol = reader.IsDBNull((int)InputColumns.AVL_BID_VOL) ? 0 : reader.GetInt32((int)InputColumns.AVL_BID_VOL);
outputData.IMLaneRank = reader.IsDBNull((int)InputColumns.IMD_RANK) ? 0 : reader.GetInt32((int)InputColumns.IMD_RANK);
outputData.LaneRevenue = reader.IsDBNull((int)InputColumns.LANE_REVENUE) ? 0 : (double)reader.GetDecimal((int)InputColumns.LANE_REVENUE);
outputData.LaneCost = reader.IsDBNull((int)InputColumns.LANE_COST) ? 0 : (double)reader.GetDecimal((int)InputColumns.LANE_COST);
outputData.LaneProfit = outputData.LaneRevenue - outputData.LaneCost;
outputData.LaneWeeklyTrailerTurns = reader.IsDBNull((int)InputColumns.WEEKLY_TURNS) ? 0 : (double)reader.GetDecimal((int)InputColumns.WEEKLY_TURNS);
outputData.LaneMinVol = reader.IsDBNull((int)InputColumns.MODEL_MIN) ? 0 : reader.GetInt32((int)InputColumns.MODEL_MIN);
outputData.LaneMaxVol = reader.IsDBNull((int)InputColumns.MODEL_MIN) ? 0 : reader.GetInt32((int)InputColumns.MODEL_MIN);
outputData.TotalBidVol = reader.IsDBNull((int)InputColumns.TTL_BID_VOL) ? 0 : reader.GetInt32((int)InputColumns.TTL_BID_VOL);
outputData.LaneID = i;
outputData.ScenarioID = scenario;
outputData.DHUB = reader.IsDBNull((int)InputColumns.DHUB) ? "" : reader.GetString((int)InputColumns.DHUB);
LoadedMoves[i] = theModel.NumVar(0, outputData.AvailableBidVol, NumVarType.Float, "LM_" + outputData.Lane);
outputData.LoadedMoves = LoadedMoves[i];
EmptyMoves[i] = theModel.NumVar(0, outputData.LaneEmptyVol, NumVarType.Float, "EM_" + outputData.Lane);
outputData.EmptyMoves = EmptyMoves[i];
theData.AddModelData(i, outputData);
LaneRevenue[i] = outputData.LaneRevenue;
LaneCost[i] = outputData.LaneCost;
TrailerTurns[i] = outputData.LaneWeeklyTrailerTurns;
LaneMargin[i] = LaneRevenue[i] - LaneCost[i];
i++;
}
}
}
using (OleDbCommand cmd = new OleDbCommand(SQLCommands.GetHubListSQL(inputTable, scenario), conn))
{
using (OleDbDataReader reader = cmd.ExecuteReader())
{
i = 0;
while (reader.Read())
{
HubList.Add(i, reader.IsDBNull(0) ? "" : reader.GetString(0));
i++;
}
}
}
}
}
public async Task <IHttpActionResult> AddBin(BinModel model)
{
if (!ModelState.IsValid)
{
return(BadRequest(ModelState));
}
model.Id = Guid.NewGuid();
Utils.ExecuteNonQuery(SQLCommands.AddBin(model));
return(Ok());
}
public async Task <IHttpActionResult> UpdateBin(Guid Id, float Capacity)
{
if (!ModelState.IsValid)
{
return(BadRequest(ModelState));
}
Utils.ExecuteNonQuery(SQLCommands.UpdateBin(Id, Capacity));
return(Ok());
}
private void GetRecordCount()
{
using (OleDbConnection conn = GetConnection.BIPSConn)
{
using (OleDbCommand cmd = new OleDbCommand(SQLCommands.GetInputCount(inputTable, scenario), conn))
{
using (OleDbDataReader reader = cmd.ExecuteReader())
{
reader.Read();
inputrecs = reader.IsDBNull(0) ? 0 : reader.GetInt32(0);
}
}
}
}
private int GetLPInputRecordCount()
{
int thecount;
using (OleDbConnection conn = GetConnection.BIPSConn)
{
using (OleDbCommand cmd = new OleDbCommand(SQLCommands.GetNewLPRecordCount(tableSelection.ScenarioSolutionTable), conn))
{
using (OleDbDataReader reader = cmd.ExecuteReader())
{
reader.Read();
thecount = reader.GetInt32(0);
}
}
}
return(thecount);
}
private void viewCaseButton_Click(object sender, EventArgs e)
{
if (activeCaseListBox.SelectedIndex != -1)
{
SQLCommands com = new SQLCommands();
int index = activeCaseListBox.SelectedIndex;
string fullName = activeCaseListBox.Items[index].ToString();
string[] names = fullName.Split(' ');
string firstName = names[0];
string lastName = names[1];
var indiv = com.RetrieveIndivData(lastName);
CaseView view = new CaseView(lastName.ToString());
view.Show();
}
else
{
MessageBox.Show("No case was selected.");
}
}
public async Task <IHttpActionResult> GetBin(Guid Id)
{
if (!ModelState.IsValid)
{
return(BadRequest(ModelState));
}
DataTable dt = Utils.ExecuteTable(SQLCommands.GetBin(Id));
BinModel bin = Utils.DataTableToList <BinModel>(dt)[0];
if (dt.Rows.Count == 0)
{
return(NotFound());
}
else
{
return(Ok(bin));
}
}
private void loginButton_Click(object sender, EventArgs e)
{
userName = usernameTextBox.Text;
password = passwordTextBox.Text;
int count = 0;
CTClasses.SQLCommands com = new SQLCommands();
login = com.LogInRetrieval(userName, password);
if (login == true)
{
CaseList select = new CaseList();
select.ShowDialog();
usernameTextBox.Clear();
passwordTextBox.Clear();
}
else
{
MessageBox.Show("The information you provided does not match our records.");
usernameTextBox.Focus();
usernameTextBox.Clear();
passwordTextBox.Clear();
}
}
public void PopulateSolution(string TheSolutionTable)
{
//ModelInputData modelInputData = new ModelInputData(scenario, tableSelection);
using (OleDbConnection conn = GetConnection.BIPSConn)
{
using (OleDbCommand cmd = new OleDbCommand(SQLCommands.WriteToInterimSolutionTable(TheSolutionTable), conn))
{
//using (OleDbDataReader reader = cmd.ExecuteReader())
{
cmd.Parameters.Add("@OHUB", OleDbType.VarChar, 10); //0
cmd.Parameters.Add("@DHUB", OleDbType.VarChar, 10); //1
cmd.Parameters.Add("@LOADED_MOVE", OleDbType.Integer, 4); //2
cmd.Parameters.Add("@EMPTY_MOVE", OleDbType.Integer, 4); //3
cmd.Parameters.Add("@LOADED_REV", OleDbType.Decimal); //4
cmd.Parameters[4].Precision = 15;
cmd.Parameters[4].Scale = 4;
cmd.Parameters.Add("@LOADED_CST", OleDbType.Decimal); //5
cmd.Parameters[5].Precision = 15;
cmd.Parameters[5].Scale = 4;
cmd.Parameters.Add("@RATE_SENSITIVITY", OleDbType.Double); //6
cmd.Parameters.Add("@EMPTY_CST", OleDbType.Decimal); //7
cmd.Parameters[7].Precision = 15;
cmd.Parameters[7].Scale = 4;
cmd.Parameters.Add("@AVL_BID_VOL", OleDbType.Integer, 4); //8
cmd.Parameters.Add("@TRANSIT_TIME", OleDbType.Decimal); //9
cmd.Parameters[9].Precision = 15;
cmd.Parameters[9].Scale = 4;
cmd.Parameters.Add("@RATE", OleDbType.Decimal); //10
cmd.Parameters[10].Precision = 15;
cmd.Parameters[10].Scale = 4;
cmd.Parameters.Add("@LOAD_MILES", OleDbType.Integer, 4); //11
cmd.Parameters.Add("@RED_COST", OleDbType.Decimal); //12
cmd.Parameters[12].Precision = 15;
cmd.Parameters[12].Scale = 4;
cmd.Parameters.Add("@SOL_INDEX", OleDbType.Integer, 4); //13
cmd.Parameters.Add("@LOAD_PROFIT", OleDbType.Decimal); //14
cmd.Parameters[14].Precision = 15;
cmd.Parameters[14].Scale = 4;
cmd.Parameters.Add("@MODEL_MIN_VOLUME", OleDbType.Integer, 4); //15
cmd.Parameters.Add("@MODEL_MAX_VOLUME", OleDbType.Integer, 4); //16
cmd.Parameters.Add("@OBJECT_COEF_LOWER", OleDbType.Double); //17
cmd.Parameters.Add("@OBJECT_COEF_UPPER", OleDbType.Double); //18
cmd.Parameters.Add("@SCENARIO_ID", OleDbType.Integer, 4); //19
cmd.Prepare();
foreach (var lane in theData.ModelData)
{
cmd.Parameters[0].Value = lane.Value.OHUB;
cmd.Parameters[1].Value = lane.Value.DHUB;
cmd.Parameters[2].Value = theModel.GetValue(lane.Value.LoadedMoves);
cmd.Parameters[3].Value = theModel.GetValue(lane.Value.EmptyMoves);
cmd.Parameters[4].Value = lane.Value.LaneRevenue;
cmd.Parameters[5].Value = lane.Value.LaneCost;
cmd.Parameters[6].Value = lane.Value.ReducedCost;
cmd.Parameters[7].Value = lane.Value.LaneCost;
cmd.Parameters[8].Value = lane.Value.AvailableBidVol;
cmd.Parameters[9].Value = lane.Value.LaneWeeklyTrailerTurns;
cmd.Parameters[10].Value = lane.Value.WinPct;
cmd.Parameters[11].Value = lane.Value.HubMiles;
cmd.Parameters[12].Value = lane.Value.ReducedCost;
cmd.Parameters[13].Value = lane.Value.SolutionIndex;
cmd.Parameters[14].Value = lane.Value.LaneProfit;
cmd.Parameters[15].Value = lane.Value.LaneMinVol;
cmd.Parameters[16].Value = lane.Value.LaneMaxVol;
cmd.Parameters[17].Value = lane.Value.LowerObjectCoef;
cmd.Parameters[18].Value = lane.Value.UpperObjectCoef;
cmd.Parameters[19].Value = lane.Value.ScenarioID;
cmd.ExecuteNonQuery();
}
}
}
}
}
