private void Lvl1DeleteModule()
{
userId = Convert.ToInt32(textBox1.Text.Trim());
int lvl1modId = Convert.ToInt32(dataGridView1.CurrentRow.Cells[0].Value.ToString());
obj.WMS_MSTR_LVL1M.RemoveRange(from lvl1 in obj.WMS_MSTR_LVL1M
where lvl1.mod_id == lvl1modId && lvl1.usr_id == userId
select lvl1);
obj.WMS_MSTR_LVL2M.RemoveRange(from lvl2 in obj.WMS_MSTR_LVL2M
join lvl2View in obj.WMS_LVL2M_VIEW on lvl2.s1mod_id equals lvl2View.s1mod_id
where lvl2View.mod_id == lvl1modId && lvl2.usr_id == userId
select lvl2);
obj.WMS_MSTR_LVL3M.RemoveRange(from lvl3 in obj.WMS_MSTR_LVL3M
join lvl3View in obj.WMS_LVL3M_VIEW on lvl3.s2mod_id equals lvl3View.s2mod_id
join lvl2 in obj.WMS_LVL2M_VIEW on lvl3View.s1mod_id equals lvl2.s1mod_id
where lvl2.mod_id == lvl1modId && lvl3.usr_id == userId
select lvl3);
obj.SaveChanges();
MessageBox.Show("Successfully removed " + dataGridView1.CurrentRow.Cells[1].Value.ToString() + ".", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
var userId = US_Authorization_Form.userId;
if (e.ColumnIndex == 3)
{
DialogResult dialog = MessageBox.Show("Are you sure you want to add " + dataGridView1.CurrentRow.Cells[1].Value.ToString() + "?", "Message", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dialog == DialogResult.Yes)
{
var dateQuery = obj.Database.SqlQuery <DateTime>("SELECT getdate()");
DateTime serverDate = dateQuery.AsEnumerable().First();
var customer = obj.Set <WMS_MSTR_LVL1M>();
customer.Add(new WMS_MSTR_LVL1M
{
usr_id = userId,
mod_id = Convert.ToInt32(dataGridView1.CurrentRow.Cells[0].Value.ToString()),
date_added = serverDate,
added_by = loggedin_user.userId
});
obj.SaveChanges();
MessageBox.Show("Successfully saved " + dataGridView1.CurrentRow.Cells[1].Value.ToString() + ".", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
getModule();
var uaf = Application.OpenForms.OfType <US_Authorization_Form>().Single();
uaf.ViewAccessModule();
}
else
{
}
}
}
private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
{
if (e.ColumnIndex == 3)
{
var xugrp = obj.Set <WMS_MSTR_UGRPLVL2>();
xugrp.Add(new WMS_MSTR_UGRPLVL2
{
grp_id = Convert.ToInt32(US_Groups_Form.groupid),
s1mod_id = Convert.ToInt32(dataGridView1.CurrentRow.Cells[0].Value.ToString())
});
obj.SaveChanges();
var mm = Application.OpenForms.OfType <US_Groups_Form>().Single();
mm.GroupListLevel2();
//this.Close();
if (textBox1.Text.Trim() == "")
{
ModuleList();
}
else
{
SearchMod();
}
}
else
{
}
}
private void saveBtn_Click_1(object sender, EventArgs e)
{
if (textBox4.Text == "")
{
MessageBox.Show("Input Quantity", "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else if (textBox4.Text == "0")
{
MessageBox.Show("Cannot input 0 quantity", "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
DialogResult dialog = MessageBox.Show("Are you sure you want to update the quantity of " + textBox2.Text + "\n " + textBox3.Text + "?", "Message", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
if (dialog == DialogResult.Yes)
{
var result = obj.WMS_MSTR_DVMR.SingleOrDefault(b => b.dvmr_billdoc == DVMR_Data.BillDoc && b.invty_id == DVMR_Data.Invty);
if (result != null)
{
result.dvmr_qty = Convert.ToInt32(textBox4.Text);
result.uom_id = Convert.ToInt32(comboBox1.SelectedValue);
obj.SaveChanges();
var uaf = Application.OpenForms.OfType <DVMR_Data>().Single();
uaf.searchItem();
}
}
}
}
private void SaveToHeader(string billdoc)
{
bool checker = CheckIfRecordExists(billdoc);
if (checker.Equals(false))
{
var rcvd = DateTime.Now;
var header = obj.Set <WMS_INC_HEADER>();
header.Add(new WMS_INC_HEADER {
billdoc = billdoc, date_rcvd = rcvd.ToString("yyyy/MM/dd"), stat_id = 1
});
obj.SaveChanges();
statusLabel.Text = "Open";
MessageBox.Show("Billdoc Recorded to Table.");
}
}
private void DeleteInvty()
{
string invty = dataGridView1.CurrentRow.Cells[0].Value.ToString();
string billdoc = textBox2.Text.ToString();
obj.WMS_MSTR_DVMR.RemoveRange(from dvmr in obj.WMS_MSTR_DVMR
where dvmr.invty_id == invty && dvmr.dvmr_billdoc == billdoc
select dvmr);
obj.SaveChanges();
MessageBox.Show("Successfully removed " + dataGridView1.CurrentRow.Cells[0].Value.ToString() + ".", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
if (e.ColumnIndex == 6)
{
var userId = US_Authorization_Form.userId;
int S1modId = Convert.ToInt32(dataGridView1.CurrentRow.Cells[1].Value.ToString());
var UserValidation1 = (from c in obj.WMS_MSTR_LVL2M
where c.usr_id == userId &&
c.s1mod_id == S1modId
select new { c.usr_id, c.lvl2mod_id }).FirstOrDefault();
if (UserValidation1 != null)
{
int s2mod_id = Convert.ToInt32(dataGridView1.CurrentRow.Cells[0].Value.ToString());
DialogResult dialog = MessageBox.Show("Are you sure you want to add " + dataGridView1.CurrentRow.Cells[3].Value.ToString() + "?", "Message", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dialog == DialogResult.Yes)
{
var dateQuery = obj.Database.SqlQuery <DateTime>("SELECT getdate()");
DateTime serverDate = dateQuery.AsEnumerable().First();
var users = obj.Set <WMS_MSTR_LVL3M>();
users.Add(new WMS_MSTR_LVL3M
{
usr_id = userId,
s2mod_id = Convert.ToInt32(dataGridView1.CurrentRow.Cells[0].Value.ToString()),
date_added = serverDate,
added_by = loggedin_user.userId,
//s1mod_id = Convert.ToInt32(dataGridView1.CurrentRow.Cells[1].Value.ToString()),
});
obj.SaveChanges();
MessageBox.Show("Successfully saved " + dataGridView1.CurrentRow.Cells[3].Value.ToString() + ".", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
uaf.ViewAccessModule();
getS3Module();
}
}
else
{
MessageBox.Show("" + dataGridView1.CurrentRow.Cells[3].Value.ToString() + " cannot be added .Register First the " + dataGridView1.CurrentRow.Cells[2].Value.ToString() + " in Access-Module Level 2", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
}
}
private void button4_Click(object sender, EventArgs e)
{
if (button4.Text == "Save")
{
if (textBox3.Text != "")
{
if (textBox4.Text != "")
{
if (textBox5.Text != "")
{
if (textBox6.Text != "")
{
if (comboBox2.Text != "")
{
if (comboBox3.Text != "")
{
if (comboBox4.Text != "")
{
DialogResult dialog = MessageBox.Show("Are you sure you want to save User?", "Message", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dialog == DialogResult.Yes)
{
isUpdate = false;
string xstatdesc = comboBox4.Text;
int? xstatid = null;
var statid = (from c in obj.WMS_TYPE_STAT
where c.stat_desc == xstatdesc
select c.stat_id).FirstOrDefault();
xstatid = statid;
string xusertypedesc = comboBox2.Text;
int? xusertypeid = null;
var xxusertypeid = (from c in obj.WMS_TYPE_USRS
where c.usr_type_name == xusertypedesc
select c.usr_type_id).FirstOrDefault();
xusertypeid = xxusertypeid;
string xusergroup = comboBox3.Text;
int? xusergroupid = null;
var xxusergroupid = (from c in obj.WMS_MSTR_UGRP
where c.grp_name == xusergroup
select c.grp_id).FirstOrDefault();
xusergroupid = xxusergroupid;
var dateQuery = obj.Database.SqlQuery <DateTime>("SELECT getdate()");
DateTime serverDate = dateQuery.AsEnumerable().First();
var user = (from c in obj.WMS_USRS_VIEW
where c.usr_username == textBox3.Text.Trim()
select c.usr_username).FirstOrDefault();
if (user == null)
{
group_id = Convert.ToInt32(xusergroupid);
var customer = obj.Set <WMS_MSTR_USRS>();
customer.Add(new WMS_MSTR_USRS
{
usr_username = textBox3.Text.Trim(),
usr_password = textBox4.Text.Trim(),
usr_fname = textBox5.Text.Trim(),
usr_lname = textBox6.Text.Trim(),
usr_type_id = Convert.ToInt32(xusertypeid),
grp_id = Convert.ToInt32(xusergroupid),
stat_id = Convert.ToInt32(xstatid),
usr_datecrtd = serverDate,
usr_crtdby = loggedin_user.userId
});
obj.SaveChanges();
AddDefaultModuleLvl1();
MessageBox.Show("Successfully saved User.", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
comboBox1.Text = "Username";
textBox1.Text = "";
textBox1.Text = textBox3.Text.Trim();
disableAddControls();
}
else
{
MessageBox.Show("'Customer ID' already exist.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
comboBox1.Text = "Customer ID";
textBox1.Text = textBox2.Text.Trim();
}
}
else if (dialog == DialogResult.No)
{
}
}
else
{
MessageBox.Show("Please select 'Status'.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
comboBox4.Focus();
}
}
else
{
MessageBox.Show("Please select 'User Group'.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
comboBox3.Focus();
}
}
else
{
MessageBox.Show("Please select 'User Type'.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
comboBox2.Focus();
}
}
else
{
MessageBox.Show("Please fill up 'Last Name'.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
textBox6.Focus();
}
}
else
{
MessageBox.Show("Please fill up 'First Name'.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
textBox5.Focus();
}
}
else
{
MessageBox.Show("Please fill up 'Password'.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
textBox4.Focus();
}
}
else
{
MessageBox.Show("Please fill up 'Username'.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
textBox3.Focus();
}
}
else
{
if (textBox3.Text != "")
{
if (textBox4.Text != "")
{
if (textBox5.Text != "")
{
if (textBox6.Text != "")
{
if (comboBox2.Text != "")
{
if (comboBox3.Text != "")
{
if (comboBox4.Text != "")
{
DialogResult dialog = MessageBox.Show("Are you sure you want to update User?", "Message", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dialog == DialogResult.Yes)
{
isUpdate = true;
string xstatdesc = comboBox4.Text;
int? xstatid = null;
var statid = (from c in obj.WMS_TYPE_STAT
where c.stat_desc == xstatdesc
select c.stat_id).FirstOrDefault();
xstatid = statid;
string xusertypedesc = comboBox2.Text;
int? xusertypeid = null;
var xxusertypeid = (from c in obj.WMS_TYPE_USRS
where c.usr_type_name == xusertypedesc
select c.usr_type_id).FirstOrDefault();
xusertypeid = xxusertypeid;
string xusergroup = comboBox3.Text;
int? xusergroupid = null;
var xxusergroupid = (from c in obj.WMS_MSTR_UGRP
where c.grp_name == xusergroup
select c.grp_id).FirstOrDefault();
xusergroupid = xxusergroupid;
group_id = Convert.ToInt32(xusergroupid);
var dateQuery = obj.Database.SqlQuery <DateTime>("SELECT getdate()");
DateTime serverDate = dateQuery.AsEnumerable().First();
var userid = Convert.ToInt32(textBox2.Text.Trim());
obj.WMS_MSTR_USRS.Where(c => c.usr_id == userid).ToList().ForEach(x =>
{
x.usr_username = textBox3.Text.Trim();
x.usr_password = textBox4.Text.Trim();
x.usr_fname = textBox5.Text.Trim();
x.usr_lname = textBox6.Text.Trim();
x.usr_type_id = Convert.ToInt32(xusertypeid);
x.grp_id = Convert.ToInt32(xusergroupid);
x.stat_id = Convert.ToInt32(xstatid);
x.usr_dateuptd = serverDate;
x.usr_uptdby = loggedin_user.userId;
});
obj.SaveChanges();
AddDefaultModuleLvl1();
MessageBox.Show("Successfully updated User.", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
comboBox1.Text = "User ID";
textBox1.Text = "";
textBox1.Text = textBox2.Text.Trim();
disableAddControls();
}
else if (dialog == DialogResult.No)
{
}
}
else
{
MessageBox.Show("Please select 'Status'.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
comboBox4.Focus();
}
}
else
{
MessageBox.Show("Please select 'User Group'.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
comboBox3.Focus();
}
}
else
{
MessageBox.Show("Please select 'User Type'.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
comboBox2.Focus();
}
}
else
{
MessageBox.Show("Please fill up 'Last Name'.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
textBox6.Focus();
}
}
else
{
MessageBox.Show("Please fill up 'First Name'.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
textBox5.Focus();
}
}
else
{
MessageBox.Show("Please fill up 'Password'.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
textBox4.Focus();
}
}
else
{
MessageBox.Show("Please fill up 'Username'.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
textBox3.Focus();
}
}
}
private void backgroundWorker2_DoWork(object sender, DoWorkEventArgs e)
{
inserted = 0;
updated = 0;
con1.Open();
cmd1.CommandText = "select DISTINCT * from [Sheet1$] WHERE [Salesman Code]<>'' ORDER BY [Salesman Name]";
cmd1.Connection = con1;
OleDbDataReader dr1 = cmd1.ExecuteReader();
if (dr1.Read() == true)
{
dt1.Reset();
dr1.Close();
adapt1.SelectCommand = cmd1;
adapt1.Fill(dt1);
foreach (DataRow i in dt1.Rows)
{
var xxslsmancode = i["Salesman Code"].ToString();
var xslsmancode = (from c in obj.WMS_MSTR_SLSMAN
where c.salesman_id == xxslsmancode
select c.salesman_id).FirstOrDefault();
if (xslsmancode == null)
{
var dateQuery = obj.Database.SqlQuery <DateTime>("SELECT getdate()");
DateTime serverDate = dateQuery.AsEnumerable().First();
var slsman = obj.Set <WMS_MSTR_SLSMAN>();
slsman.Add(new WMS_MSTR_SLSMAN
{
salesman_id = i["Salesman Code"].ToString().ToUpper(),
salesman_name = i["Salesman Name"].ToString().ToUpper(),
salesman_type_id = 1,
site_id = i["Site"].ToString().ToUpper(),
salesman_datecrtd = serverDate,
salesman_crtdby = loggedin_user.userId
});
if (obj.SaveChanges() > 0)
{
}
else
{
}
inserted = inserted + 1;
}
else
{
var dateQuery = obj.Database.SqlQuery <DateTime>("SELECT getdate()");
DateTime serverDate = dateQuery.AsEnumerable().First();
var xxsalesmancode = i["Salesman Code"].ToString();
obj.WMS_MSTR_SLSMAN.Where(c => c.salesman_id == xxsalesmancode).ToList().ForEach(x =>
{
x.salesman_name = i["Salesman Name"].ToString().ToUpper();
x.salesman_type_id = 1;
x.site_id = i["Site"].ToString().ToUpper();
x.salesman_dateuptd = serverDate;
x.salesman_uptdby = loggedin_user.userId;
});
if (obj.SaveChanges() > 0)
{
}
else
{
}
updated = updated + 1;
}
TranCounter2 = TranCounter2 + 1;
backgroundWorker2.ReportProgress(Convert.ToInt32(100 * TranCounter2 / max), "Uploading " + TranCounter2.ToString() + " out of " + max.ToString() + " Salesman(s)");
if (backgroundWorker2.CancellationPending)
{
backgroundWorker2.ReportProgress(Convert.ToInt32(100 * TranCounter2 / max), "Cancelling...");
// e.Cancel = true;
backgroundWorker2.ReportProgress(100, "Cancelled!");
break;
}
}
}
con1.Close();
}
private void saveBtn_Click(object sender, EventArgs e)
{
if (textBox1.Text.Trim() == "")
{
MessageBox.Show("Please fill up 'Module Name'.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
textBox1.Focus();
}
else
{
if (comboBox4.Text == "")
{
MessageBox.Show("Please select 'Module Status'.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
comboBox4.Focus();
}
else
{
if (saveBtn.Text == "Save")
{
DialogResult dialog = MessageBox.Show("Are you sure you want to save Module (Level 1)?", "Message", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dialog == DialogResult.Yes)
{
string xstatdesc = comboBox4.Text;
int xstatid = 1;
var statid = (from c in obj.WMS_TYPE_STAT
where c.stat_desc == xstatdesc
select c.stat_id).FirstOrDefault();
xstatid = statid;
var dateQuery = obj.Database.SqlQuery <DateTime>("SELECT getdate()");
DateTime serverDate = dateQuery.AsEnumerable().First();
var xmodule = obj.Set <WMS_MSTR_MODULE>();
xmodule.Add(new WMS_MSTR_MODULE
{
mod_name = textBox1.Text.Trim(),
mod_datecrtd = serverDate,
stat_id = xstatid,
mod_crtdby = loggedin_user.userId
});
obj.SaveChanges();
MessageBox.Show("Successfully saved Module.", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
clearFieldslvl1();
panelvl1.Height = 0;
ModuleList();
Main_Form.GetInstance().AddItemsToModule();
}
else if (dialog == DialogResult.No)
{
}
}
else
{
DialogResult dialog = MessageBox.Show("Are you sure you want to update Module (Level 1)?", "Message", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dialog == DialogResult.Yes)
{
string xstatdesc = comboBox4.Text;
int xstatid = 1;
var statid = (from c in obj.WMS_TYPE_STAT
where c.stat_desc == xstatdesc
select c.stat_id).FirstOrDefault();
xstatid = statid;
var dateQuery = obj.Database.SqlQuery <DateTime>("SELECT getdate()");
DateTime serverDate = dateQuery.AsEnumerable().First();
int xmodid = Convert.ToInt32(dataGridView1.CurrentRow.Cells[0].Value);
obj.WMS_MSTR_MODULE.Where(c => c.mod_id == xmodid).ToList().ForEach(x =>
{
x.mod_name = textBox1.Text.ToString().Replace("'", "''");
x.mod_dateuptd = serverDate;
x.mod_uptdby = loggedin_user.userId;
x.stat_id = xstatid;
});
obj.SaveChanges();
MessageBox.Show("Successfully updated Module.", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
clearFieldslvl1();
panelvl1.Height = 0;
ModuleList();
Main_Form.GetInstance().AddItemsToModule();
}
else if (dialog == DialogResult.No)
{
}
}
}
}
}
private void button4_Click(object sender, EventArgs e)
{
if (button4.Text == "Save")
{
if (textBox2.Text.Trim() != "")
{
if (textBox3.Text.Trim() != "")
{
if (comboBox2.Text.Trim() != "")
{
if (comboBox3.Text.Trim() != "")
{
DialogResult dialog = MessageBox.Show("Are you sure you want to save Salesman?", "Message", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dialog == DialogResult.Yes)
{
var dateQuery = obj.Database.SqlQuery <DateTime>("SELECT getdate()");
DateTime serverDate = dateQuery.AsEnumerable().First();
string xslsmantype = comboBox2.Text;
int xslsmantypeid = 1;
var slsmantypeid = (from c in obj.WMS_TYPE_SLSMAN
where c.salesman_type_name == xslsmantype
select c.salesman_type_id).FirstOrDefault();
xslsmantypeid = slsmantypeid;
var salesmanid = (from c in obj.WMS_SLSMAN_VIEW
where c.salesman_id == textBox2.Text.Trim()
select c.salesman_id).FirstOrDefault();
if (salesmanid == null)
{
var salesman = obj.Set <WMS_MSTR_SLSMAN>();
salesman.Add(new WMS_MSTR_SLSMAN
{
salesman_id = textBox2.Text.Trim(),
salesman_name = textBox3.Text.Trim(),
salesman_type_id = xslsmantypeid,
site_id = comboBox3.Text,
salesman_datecrtd = serverDate,
salesman_crtdby = loggedin_user.userId
});
obj.SaveChanges();
MessageBox.Show("Successfully saved Salesman.", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
comboBox1.Text = "Salesman ID";
textBox1.Text = textBox2.Text.Trim();
disableAddControls();
}
else
{
MessageBox.Show("'Salesman ID' already exist.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
comboBox1.Text = "Salesman ID";
textBox1.Text = textBox2.Text.Trim();
}
}
else if (dialog == DialogResult.No)
{
}
}
else
{
MessageBox.Show("Please select 'Salesman Site'.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
comboBox3.Focus();
}
}
else
{
MessageBox.Show("Please select 'Salesman Type'.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
comboBox2.Focus();
}
}
else
{
MessageBox.Show("Please fill up 'Salesman Name'.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
textBox3.Focus();
}
}
else
{
MessageBox.Show("Please fill up 'Salesman Code'.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
textBox2.Focus();
}
}
else if (button4.Text == "Update")
{
if (textBox3.Text.Trim() != "")
{
if (comboBox2.Text.Trim() != "")
{
if (comboBox3.Text.Trim() != "")
{
DialogResult dialog = MessageBox.Show("Are you sure you want to update Salesman?", "Message", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dialog == DialogResult.Yes)
{
var dateQuery = obj.Database.SqlQuery <DateTime>("SELECT getdate()");
DateTime serverDate = dateQuery.AsEnumerable().First();
string xslsmantype = comboBox2.Text;
int xslsmantypeid = 1;
var slsmantypeid = (from c in obj.WMS_TYPE_SLSMAN
where c.salesman_type_name == xslsmantype
select c.salesman_type_id).FirstOrDefault();
xslsmantypeid = slsmantypeid;
obj.WMS_MSTR_SLSMAN.Where(c => c.salesman_id == textBox2.Text.Trim()).ToList().ForEach(x =>
{
x.salesman_name = textBox3.Text.Trim();
x.salesman_type_id = xslsmantypeid;
x.site_id = comboBox3.Text;
x.salesman_dateuptd = serverDate;
x.salesman_uptdby = loggedin_user.userId;
});
obj.SaveChanges();
MessageBox.Show("Successfully updated Salesman.", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
comboBox1.Text = "Salesman ID";
textBox1.Text = "";
textBox1.Text = textBox2.Text.Trim();
disableAddControls();
}
else if (dialog == DialogResult.No)
{
}
}
else
{
MessageBox.Show("Please select 'Salesman Site'.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
comboBox3.Focus();
}
}
else
{
MessageBox.Show("Please select 'Salesman Type'.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
comboBox2.Focus();
}
}
else
{
MessageBox.Show("Please fill up 'Salesman Name'.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
textBox3.Focus();
}
}
}
private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{
TranCounter1 = 0;
inserted = 0;
updated = 0;
con1.Open();
cmd1.CommandText = "select DISTINCT * from [Sheet1$] WHERE [Username]<>'' AND [Password]<>'' AND [First Name]<>'' AND [Last Name]<>'' AND [User Type]<>'' AND [User Group]<>'' AND [Status]<>'' ORDER BY [Username]";
cmd1.Connection = con1;
OleDbDataReader dr1 = cmd1.ExecuteReader();
if (dr1.Read() == true)
{
dt1.Reset();
dr1.Close();
adapt1.SelectCommand = cmd1;
adapt1.Fill(dt1);
foreach (DataRow i in dt1.Rows)
{
var xxusername = i["Username"].ToString();
var xusrid = (from c in obj.WMS_MSTR_USRS
where c.usr_username == xxusername
select c.usr_id).ToList();
string xusrtypedesc = i["User Type"].ToString();
int? xusrtypeid = null;
var usrtypeid = (from c in obj.WMS_TYPE_USRS
where c.usr_type_name == xusrtypedesc
select c.usr_type_id).FirstOrDefault();
xusrtypeid = usrtypeid;
string xusrgroupdesc = i["User Group"].ToString();
int? xusrgroupid = null;
var usrgroupid = (from c in obj.WMS_MSTR_UGRP
where c.grp_name == xusrgroupdesc
select c.grp_id).FirstOrDefault();
xusrgroupid = usrgroupid;
string xstatdesc = i["Status"].ToString();
int? xstatid = null;
var statid = (from c in obj.WMS_TYPE_STAT
where c.stat_desc == xstatdesc
select c.stat_id).FirstOrDefault();
xstatid = statid;
var dateQuery = obj.Database.SqlQuery <DateTime>("SELECT getdate()");
DateTime serverDate = dateQuery.AsEnumerable().First();
if (xusrid.Count > 0)
{
obj.WMS_MSTR_USRS.Where(c => c.usr_username == xxusername).ToList().ForEach(x =>
{
x.usr_password = i["Password"].ToString();
x.usr_fname = i["First Name"].ToString().ToUpper();
x.usr_lname = i["First Name"].ToString().ToUpper();
x.usr_type_id = Convert.ToInt32(xusrtypeid);
x.grp_id = Convert.ToInt32(xusrgroupid);
x.stat_id = Convert.ToInt32(xstatid);
x.usr_dateuptd = serverDate;
x.usr_uptdby = loggedin_user.userId;
});
obj.SaveChanges();
updated = updated + 1;
}
else
{
var xuser = obj.Set <WMS_MSTR_USRS>();
xuser.Add(new WMS_MSTR_USRS
{
usr_username = i["Username"].ToString(),
usr_password = i["Password"].ToString(),
usr_fname = i["First Name"].ToString().ToUpper(),
usr_lname = i["Last Name"].ToString().ToUpper(),
usr_type_id = Convert.ToInt32(xusrtypeid),
grp_id = Convert.ToInt32(xusrgroupid),
stat_id = Convert.ToInt32(xstatid),
usr_datecrtd = serverDate,
usr_crtdby = loggedin_user.userId
});
obj.SaveChanges();
inserted = inserted + 1;
}
TranCounter1 = TranCounter1 + 1;
backgroundWorker1.ReportProgress(Convert.ToInt32(100 * TranCounter1 / max), "Uploading " + TranCounter1.ToString() + " out of " + max.ToString() + " User(s)");
if (backgroundWorker1.CancellationPending)
{
backgroundWorker1.ReportProgress(Convert.ToInt32(100 * TranCounter1 / max), "Cancelling...");
// e.Cancel = true;
backgroundWorker1.ReportProgress(100, "Cancelled!");
break;
}
}
}
con1.Close();
}
private void saveBtn_Click(object sender, EventArgs e)
{
if (saveBtn.Text == "Save")
{
if (textBox2.Text.Trim() != "")
{
if (textBox3.Text.Trim() != "")
{
if (textBox6.Text.Trim() != "")
{
if (textBox7.Text.Trim() != "")
{
if (comboBox4.Text.Trim() != "")
{
DialogResult dialog = MessageBox.Show("Are you sure you want to save Item?", "Message", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dialog == DialogResult.Yes)
{
string xstatdesc = comboBox4.Text;
int xstatid = 1;
var statid = (from c in obj.WMS_TYPE_STAT
where c.stat_desc == xstatdesc
select c.stat_id).FirstOrDefault();
xstatid = statid;
var item = (from c in obj.WMS_INVTY_VIEW
where c.invty_id == textBox2.Text.Trim()
select c.invty_id).FirstOrDefault();
var dateQuery = obj.Database.SqlQuery <DateTime>("SELECT getdate()");
DateTime serverDate = dateQuery.AsEnumerable().First();
if (item == null)
{
var xitem = obj.Set <WMS_MSTR_INVTY>();
xitem.Add(new WMS_MSTR_INVTY
{
invty_id = textBox2.Text.Trim(),
invty_desc = textBox3.Text.Trim(),
invty_barcode = textBox4.Text.Trim(),
invty_casecode = textBox5.Text.Trim(),
invty_ppu = Convert.ToInt32(textBox6.Text.Trim()),
invty_cat1 = textBox7.Text.Trim(),
invty_cat2 = textBox8.Text.Trim(),
invty_cat3 = textBox9.Text.Trim(),
invty_cat4 = textBox10.Text.Trim(),
invty_brand = textBox11.Text.Trim(),
stat_id = xstatid,
invty_datecrtd = serverDate,
invty_crtdby = loggedin_user.userId
});
obj.SaveChanges();
MessageBox.Show("Successfully saved Item.", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
searchComboBox.Text = "Itemcode";
searchTxtBox.Text = textBox2.Text.Trim();
disableAddControls();
}
else
{
MessageBox.Show("'Itemcode' already exist.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
searchComboBox.Text = "Itemcode";
searchTxtBox.Text = textBox2.Text.Trim();
}
}
else if (dialog == DialogResult.No)
{
}
}
else
{
MessageBox.Show("Please select 'Item Status'.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
comboBox4.Focus();
}
}
else
{
MessageBox.Show("Please fill up 'Item Category1'.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
textBox7.Focus();
}
}
else
{
MessageBox.Show("Please fill up 'Item PPU'.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
textBox6.Focus();
}
}
else
{
MessageBox.Show("Please fill up 'Item Description'.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
textBox3.Focus();
}
}
else
{
MessageBox.Show("Please fill up 'Itemcode'.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
textBox2.Focus();
}
}
else if (saveBtn.Text == "Update")
{
if (textBox3.Text.Trim() != "")
{
if (textBox6.Text.Trim() != "")
{
if (textBox7.Text.Trim() != "")
{
if (comboBox4.Text.Trim() != "")
{
DialogResult dialog = MessageBox.Show("Are you sure you want to update Item?", "Message", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dialog == DialogResult.Yes)
{
string bcode = "";
string ccode = "";
string cat2 = "";
string cat3 = "";
string cat4 = "";
string brand = "";
if (textBox4.Text.Trim() == "")
{
bcode = null;
}
else
{
bcode = textBox4.Text.Trim();
}
if (textBox5.Text.Trim() == "")
{
ccode = null;
}
else
{
ccode = textBox5.Text.Trim();
}
if (textBox8.Text.Trim() == "")
{
cat2 = null;
}
else
{
cat2 = textBox8.Text.Trim();
}
if (textBox9.Text.Trim() == "")
{
cat3 = null;
}
else
{
cat3 = textBox9.Text.Trim();
}
if (textBox10.Text.Trim() == "")
{
cat4 = null;
}
else
{
cat4 = textBox10.Text.Trim();
}
if (textBox11.Text.Trim() == "")
{
brand = null;
}
else
{
brand = textBox11.Text.Trim();
}
string xstatdesc = comboBox4.Text;
int xstatid = 1;
var statid = (from c in obj.WMS_TYPE_STAT
where c.stat_desc == xstatdesc
select c.stat_id).FirstOrDefault();
xstatid = statid;
var dateQuery = obj.Database.SqlQuery <DateTime>("SELECT getdate()");
DateTime serverDate = dateQuery.AsEnumerable().First();
obj.WMS_MSTR_INVTY.Where(c => c.invty_id == textBox2.Text.Trim()).ToList().ForEach(x =>
{
x.invty_desc = textBox3.Text.Trim();
x.invty_barcode = bcode;
x.invty_casecode = ccode;
x.invty_ppu = Convert.ToInt32(textBox6.Text.Trim());
x.invty_cat1 = textBox7.Text.Trim();
x.invty_cat2 = cat2;
x.invty_cat3 = cat3;
x.invty_cat4 = cat4;
x.invty_brand = brand;
x.stat_id = xstatid;
x.invty_dateuptd = serverDate;
x.invty_uptdby = loggedin_user.userId;
});
obj.SaveChanges();
MessageBox.Show("Successfully updated Item.", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
searchComboBox.Text = "Itemcode";
searchTxtBox.Text = "";
searchTxtBox.Text = textBox2.Text.Trim();
disableAddControls();
}
else if (dialog == DialogResult.No)
{
}
}
else
{
MessageBox.Show("Please select 'Item Status'.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
comboBox4.Focus();
}
}
else
{
MessageBox.Show("Please fill up 'Item Category1'.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
textBox7.Focus();
}
}
else
{
MessageBox.Show("Please fill up 'Item PPU'.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
textBox6.Focus();
}
}
else
{
MessageBox.Show("Please fill up 'Item Description'.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
textBox3.Focus();
}
}
}
private void backgroundWorker3_DoWork(object sender, DoWorkEventArgs e)
{
TranCounter3 = 0;
con1.Open();
cmd1.CommandText = "select * from [Sheet1$] WHERE [Salesman]<>'' ORDER BY [Name1]";
cmd1.Connection = con1;
OleDbDataReader dr1 = cmd1.ExecuteReader();
if (dr1.Read() == true)
{
dt1.Reset();
dr1.Close();
adapt1.SelectCommand = cmd1;
adapt1.Fill(dt1);
foreach (DataRow i in dt1.Rows)
{
int stat = 0;
if (i["Status"].ToString() == "Active")
{
stat = 1;
}
else
{
stat = 2;
}
string xcustomer = i["Outlet Code"].ToString();
var customer = (from c in obj.WMS_CUST_VIEW
where c.cust_id == xcustomer
select c.cust_id).FirstOrDefault();
if (customer == null)
{
var dateQuery = obj.Database.SqlQuery <DateTime>("SELECT getdate()");
DateTime serverDate = dateQuery.AsEnumerable().First();
var customers = obj.Set <WMS_MSTR_CUST>();
customers.Add(new WMS_MSTR_CUST
{
cust_id = i["Outlet Code"].ToString().ToUpper(),
cust_name = i["Name1"].ToString().ToUpper(),
cust_address = i["Street"].ToString().ToUpper(),
salesman_id = i["Salesman"].ToString().Replace("/", "").ToUpper(),
site_id = i["Delivering Site"].ToString().ToUpper(),
cust_latitude = Convert.ToDouble(i["Latitude"]),
cust_longitude = Convert.ToDouble(i["Longitude"]),
stat_id = stat,
cust_datecrtd = serverDate,
cust_crtdby = loggedin_user.userId
});
obj.SaveChanges();
}
else
{
var dateQuery = obj.Database.SqlQuery <DateTime>("SELECT getdate()");
DateTime serverDate = dateQuery.AsEnumerable().First();
obj.WMS_MSTR_CUST.Where(c => c.cust_id == xcustomer).ToList().ForEach(x =>
{
x.cust_name = i["Name1"].ToString().ToUpper();
x.cust_address = i["Street"].ToString().ToUpper();
x.salesman_id = i["Salesman"].ToString().Replace("/", "").ToUpper();
x.site_id = i["Delivering Site"].ToString().ToUpper();
x.cust_latitude = Convert.ToDouble(i["Latitude"]);
x.cust_longitude = Convert.ToDouble(i["Longitude"]);
x.stat_id = stat;
x.cust_dateuptd = serverDate;
x.cust_uptdby = loggedin_user.userId;
});
obj.SaveChanges();
}
TranCounter3 = TranCounter3 + 1;
backgroundWorker3.ReportProgress(Convert.ToInt32(100 * TranCounter3 / max), "Uploading " + TranCounter3.ToString() + " out of " + max.ToString() + " Customer(s)");
if (backgroundWorker3.CancellationPending)
{
backgroundWorker3.ReportProgress(Convert.ToInt32(100 * TranCounter3 / max), "Cancelling...");
// e.Cancel = true;
backgroundWorker3.ReportProgress(100, "Cancelled!");
break;
}
}
}
con1.Close();
}
private void saveBtn_Click(object sender, EventArgs e)
{
if (textBox2.Text.Trim() == "")
{
}
else
{
if (comboBox4.Text == "")
{
}
else
{
if (saveBtn.Text == "Save")
{
DialogResult dialog = MessageBox.Show("Are you sure you want to save User Group?", "Message", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dialog == DialogResult.Yes)
{
string xstatdesc = comboBox4.Text;
int xstatid = 1;
var statid = (from c in obj.WMS_TYPE_STAT
where c.stat_desc == xstatdesc
select c.stat_id).FirstOrDefault();
xstatid = statid;
var dateQuery = obj.Database.SqlQuery <DateTime>("SELECT getdate()");
DateTime serverDate = dateQuery.AsEnumerable().First();
var xugrp = obj.Set <WMS_MSTR_UGRP>();
xugrp.Add(new WMS_MSTR_UGRP
{
grp_name = textBox2.Text.Trim(),
grp_datecrtd = serverDate,
grp_crtdby = loggedin_user.userId,
stat_id = xstatid
});
obj.SaveChanges();
MessageBox.Show("Successfully saved User Group.", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
clearFieldslvl1();
panel3.Height = 0;
GroupList();
}
else if (dialog == DialogResult.No)
{
}
}
else
{
DialogResult dialog = MessageBox.Show("Are you sure you want to update User Group?", "Message", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dialog == DialogResult.Yes)
{
string xstatdesc = comboBox4.Text;
int xstatid = 1;
var statid = (from c in obj.WMS_TYPE_STAT
where c.stat_desc == xstatdesc
select c.stat_id).FirstOrDefault();
xstatid = statid;
var dateQuery = obj.Database.SqlQuery <DateTime>("SELECT getdate()");
DateTime serverDate = dateQuery.AsEnumerable().First();
int xgrpid = Convert.ToInt32(dataGridView1.CurrentRow.Cells[0].Value);
obj.WMS_MSTR_UGRP.Where(c => c.grp_id == xgrpid).ToList().ForEach(x =>
{
x.grp_name = textBox2.Text.ToString().Replace("'", "''");
x.grp_dateuptd = serverDate;
x.grp_uptdby = loggedin_user.userId;
x.stat_id = xstatid;
});
obj.SaveChanges();
MessageBox.Show("Successfully updated User Group.", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
clearFieldslvl1();
panel3.Height = 0;
GroupList();
}
else if (dialog == DialogResult.No)
{
}
}
}
}
}
private void saveBtn_Click(object sender, EventArgs e)
{
if (textBox13.Text == "" || textBox9.Text == "")
{
MessageBox.Show("Please fill in the empty box", "Message", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else if (textBox9.Text == "0")
{
MessageBox.Show("Cannot Insert 0 Quantity", "Message", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
var invtyID = textBox13.Text.Trim();
var invty1 = (from c in obj.WMS_MSTR_DVMR
where c.dvmr_billdoc == DVMR_Data.BillDoc && c.invty_id == invtyID
select c).ToList();
if (invty1.Count > 0)
{
MessageBox.Show("Item Already Existed ! ", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
DialogResult dialog = MessageBox.Show("Are you sure you want to Add " + textBox13.Text + "?", "Message", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
if (dialog == DialogResult.Yes)
{
var dateQuery = obj.Database.SqlQuery <DateTime>("SELECT getdate()");
DateTime serverDate = dateQuery.AsEnumerable().First();
var dvmr = obj.Set <WMS_MSTR_DVMR>();
dvmr.Add(new WMS_MSTR_DVMR
{
dvmr_load_date = Convert.ToDateTime(DVMR_Data.LoadDate),
site_code = DVMR_Data.SiteCode,
dvmr_customer = DVMR_Data.Customer,
dvmr_rdd = Convert.ToDateTime(DVMR_Data.RDD),
dvmr_shipment = DVMR_Data.Shipment,
dvmr_shipping_line = DVMR_Data.ShippingLine,
dvmr_truck_no = DVMR_Data.TruckNo,
dvmr_cvan = DVMR_Data.Cvan,
dvmr_salesdoc = DVMR_Data.SalesDoc,
dvmr_po_number = DVMR_Data.PoNo,
dvmr_billdoc = DVMR_Data.BillDoc,
dvmr_category = textBox5.Text,
invty_id = textBox13.Text,
dvmr_qty = Convert.ToInt32(textBox9.Text),
dvmr_date_added = serverDate,
dvmr_schedule_date = DVMR_Data.scheduleDate,
uom_id = Convert.ToInt32(comboBox2.SelectedValue),
});
obj.SaveChanges();
MessageBox.Show("Successfully saved Inventory :" + textBox13.Text + "\n" + textBox2.Text + ".", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
var uaf = Application.OpenForms.OfType <DVMR_Data>().Single();
uaf.searchItem();
}
else
{
}
}
}
}
private void button4_Click(object sender, EventArgs e)
{
if (button4.Text == "Save")
{
if (textBox2.Text.Trim() != "")
{
if (textBox3.Text.Trim() != "")
{
if (textBox4.Text.Trim() != "")
{
DialogResult dialog = MessageBox.Show("Are you sure you want to save Site?", "Message", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dialog == DialogResult.Yes)
{
var dateQuery = obj.Database.SqlQuery <DateTime>("SELECT getdate()");
DateTime serverDate = dateQuery.AsEnumerable().First();
var xsite = (from c in obj.WMS_MSTR_SITE
where c.site_id == textBox2.Text.Trim()
select c.site_id).ToList();
if (xsite.Count == 0)
{
var salesman = obj.Set <WMS_MSTR_SITE>();
salesman.Add(new WMS_MSTR_SITE
{
site_id = textBox2.Text.Trim(),
site_name = textBox3.Text.Trim(),
site_code = textBox4.Text.Trim(),
site_datecrtd = serverDate,
site_crtdby = loggedin_user.userId
});
obj.SaveChanges();
MessageBox.Show("Successfully saved Site.", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
comboBox1.Text = "Site ID";
textBox1.Text = textBox2.Text.Trim();
disableAddControls();
}
else
{
MessageBox.Show("'Site ID' already exist.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
comboBox1.Text = "Site ID";
textBox1.Text = textBox2.Text.Trim();
}
}
else if (dialog == DialogResult.No)
{
}
}
else
{
MessageBox.Show("Please fill up 'Site Code'.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
textBox4.Focus();
}
}
else
{
MessageBox.Show("Please fill up 'Site Name'.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
textBox3.Focus();
}
}
else
{
MessageBox.Show("Please fill up 'Site ID'.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
textBox2.Focus();
}
}
else
{
if (textBox2.Text.Trim() != "")
{
if (textBox3.Text.Trim() != "")
{
DialogResult dialog = MessageBox.Show("Are you sure you want to update Site?", "Message", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dialog == DialogResult.Yes)
{
var dateQuery = obj.Database.SqlQuery <DateTime>("SELECT getdate()");
DateTime serverDate = dateQuery.AsEnumerable().First();
obj.WMS_MSTR_SITE.Where(c => c.site_id == textBox2.Text.Trim()).ToList().ForEach(x =>
{
x.site_name = textBox3.Text.Trim();
x.site_code = textBox4.Text.Trim();
x.site_dateuptd = serverDate;
x.site_uptdby = loggedin_user.userId;
});
obj.SaveChanges();
MessageBox.Show("Successfully updated Site.", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
comboBox1.Text = "Site ID";
textBox1.Text = "";
textBox1.Text = textBox2.Text.Trim();
disableAddControls();
}
else if (dialog == DialogResult.No)
{
}
}
else
{
MessageBox.Show("Please fill up 'Site Name'.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
textBox3.Focus();
}
}
else
{
MessageBox.Show("Please fill up 'Site ID'.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
textBox2.Focus();
}
}
}
private void backgroundWorker_DoWork(object sender, DoWorkEventArgs e)
{
TranCounter1 = 0;
con1.Open();
cmd1.CommandText = "select DISTINCT * from [Sheet1$] WHERE [Article Code]<>'' AND [Status]<>'' AND [Unit Conversion2]<>0 ORDER BY [Article Description]";
cmd1.Connection = con1;
OleDbDataReader dr1 = cmd1.ExecuteReader();
if (dr1.Read() == true)
{
dt1.Reset();
dr1.Close();
adapt1.SelectCommand = cmd1;
adapt1.Fill(dt1);
foreach (DataRow i in dt1.Rows)
{
var xxitem = i["Article Code"].ToString();
var xitem = (from c in obj.WMS_INVTY_VIEW
where c.invty_id == xxitem
select c.invty_id).FirstOrDefault();
if (xitem == null)
{
string xstatdesc = i["Status"].ToString();
int xstatid = 1;
var statid = (from c in obj.WMS_TYPE_STAT
where c.stat_desc == xstatdesc
select c.stat_id).FirstOrDefault();
xstatid = statid;
var dateQuery = obj.Database.SqlQuery <DateTime>("SELECT getdate()");
DateTime serverDate = dateQuery.AsEnumerable().First();
string xbcode = "";
if (i["Barcode"].ToString().ToUpper() == "")
{
xbcode = null;
}
else
{
xbcode = i["Barcode"].ToString().ToUpper();
}
string xccode = "";
if (i["Casecode"].ToString().ToUpper() == "")
{
xccode = null;
}
else
{
xccode = i["Casecode"].ToString().ToUpper();
}
string xcat1 = "";
if (i["Assortment No"].ToString().ToUpper() == "")
{
xcat1 = null;
}
else
{
xcat1 = i["Assortment No"].ToString().ToUpper();
}
string xcat2 = "";
if (i["Division"].ToString().ToUpper() == "")
{
xcat2 = null;
}
else
{
xcat2 = i["Division"].ToString().ToUpper();
}
string xcat3 = "";
if (i["Category"].ToString().ToUpper() == "")
{
xcat3 = null;
}
else
{
xcat3 = i["Category"].ToString().ToUpper();
}
string xbrand = "";
if (i["Brand"].ToString().ToUpper() == "")
{
xbrand = null;
}
else
{
xbrand = i["Brand"].ToString().ToUpper();
}
var item = obj.Set <WMS_MSTR_INVTY>();
item.Add(new WMS_MSTR_INVTY
{
invty_id = i["Article Code"].ToString().ToUpper(),
invty_desc = i["Article Description"].ToString().ToUpper(),
invty_barcode = xbcode,
invty_casecode = xccode,
invty_ppu = Convert.ToInt32(i["Unit Conversion2"].ToString().ToUpper()),
invty_cat1 = xcat1,
invty_cat2 = xcat2,
invty_cat3 = xcat3,
invty_cat4 = xcat3,
invty_brand = xbrand,
stat_id = statid,
invty_datecrtd = serverDate,
invty_crtdby = loggedin_user.userId
});
if (obj.SaveChanges() > 0)
{
}
else
{
}
}
else
{
string xstatdesc = i["Status"].ToString();
int xstatid = 1;
var statid = (from c in obj.WMS_TYPE_STAT
where c.stat_desc == xstatdesc
select c.stat_id).FirstOrDefault();
xstatid = statid;
var dateQuery = obj.Database.SqlQuery <DateTime>("SELECT getdate()");
DateTime serverDate = dateQuery.AsEnumerable().First();
var xxitem2 = i["Article Code"].ToString();
string xbcode = "";
if (i["Barcode"].ToString().ToUpper() == "")
{
xbcode = null;
}
else
{
xbcode = i["Barcode"].ToString().ToUpper();
}
string xccode = "";
if (i["Casecode"].ToString().ToUpper() == "")
{
xccode = null;
}
else
{
xccode = i["Casecode"].ToString().ToUpper();
}
string xcat1 = "";
if (i["Assortment No"].ToString().ToUpper() == "")
{
xcat1 = null;
}
else
{
xcat1 = i["Assortment No"].ToString().ToUpper();
}
string xcat2 = "";
if (i["Division"].ToString().ToUpper() == "")
{
xcat2 = null;
}
else
{
xcat2 = i["Division"].ToString().ToUpper();
}
string xcat3 = "";
if (i["Category"].ToString().ToUpper() == "")
{
xcat3 = null;
}
else
{
xcat3 = i["Category"].ToString().ToUpper();
}
string xbrand = "";
if (i["Brand"].ToString().ToUpper() == "")
{
xbrand = null;
}
else
{
xbrand = i["Brand"].ToString().ToUpper();
}
obj.WMS_MSTR_INVTY.Where(c => c.invty_id == xxitem2).ToList().ForEach(x =>
{
x.invty_desc = i["Article Description"].ToString().ToUpper();
x.invty_barcode = xbcode;
x.invty_casecode = xccode;
x.invty_ppu = Convert.ToInt32(i["Unit Conversion2"].ToString().ToUpper());
x.invty_cat1 = xcat1;
x.invty_cat2 = xcat2;
x.invty_cat3 = xcat3;
x.invty_cat4 = xcat3;
x.invty_brand = xbrand;
x.stat_id = statid;
x.invty_dateuptd = serverDate;
x.invty_uptdby = loggedin_user.userId;
});
}
TranCounter1 = TranCounter1 + 1;
backgroundWorker.ReportProgress(Convert.ToInt32(100 * TranCounter1 / max), "Uploading " + TranCounter1.ToString() + " out of " + max.ToString() + " Item(s)");
if (backgroundWorker.CancellationPending)
{
backgroundWorker.ReportProgress(Convert.ToInt32(100 * TranCounter1 / max), "Cancelling...");
// e.Cancel = true;
backgroundWorker.ReportProgress(100, "Cancelled!");
break;
}
}
}
con1.Close();
}
private void backgroundWorker3_DoWork(object sender, DoWorkEventArgs e)
{
TranCounter3 = 0;
inserted = 0;
updated = 0;
con1.Open();
cmd1.CommandText = "select * from [Sheet1$] WHERE [Material]<>NULL ";
cmd1.Connection = con1;
OleDbDataReader dr1 = cmd1.ExecuteReader();
if (dr1.Read() == true)
{
dt1.Reset();
dr1.Close();
adapt1.SelectCommand = cmd1;
adapt1.Fill(dt1);
foreach (DataRow i in dt1.Rows)
{
string xItem = i["Material"].ToString();
string xBillDoc = i["Bill#Doc#"].ToString();
var Item = (from c in obj.WMS_MSTR_DVMR
where c.dvmr_billdoc == xBillDoc &&
c.invty_id == xItem
select c.invty_id).FirstOrDefault();
if (Item == null)
{
var dateQuery = obj.Database.SqlQuery <DateTime>("SELECT getdate()");
DateTime serverDate = dateQuery.AsEnumerable().First();
var dvmr = obj.Set <WMS_MSTR_DVMR>();
dvmr.Add(new WMS_MSTR_DVMR
{
dvmr_load_date = Convert.ToDateTime(i["Load Date"]),
site_code = i["Ship-to"].ToString().ToUpper(),
dvmr_customer = i["Customer Name"].ToString().ToUpper(),
dvmr_rdd = Convert.ToDateTime(i["RDD"].ToString()),
dvmr_shipment = i["Shipment"].ToString().ToUpper(),
dvmr_shipping_line = i["Shipping Line"].ToString(),
dvmr_truck_no = i["Truck No"].ToString(),
dvmr_cvan = i["CVAN"].ToString(),
dvmr_salesdoc = i["Sales Doc#"].ToString(),
dvmr_po_number = i["PO number"].ToString(),
dvmr_billdoc = i["Bill#Doc#"].ToString(),
dvmr_category = i["Category"].ToString(),
invty_id = i["Material"].ToString(),
dvmr_qty = Convert.ToInt32(i["Qty"]),
dvmr_date_added = serverDate,
uom_id = 1
});
obj.SaveChanges();
inserted = inserted + 1;
}
else
{
string xItem2 = i["Material"].ToString();
string xBillDoc2 = i["Bill#Doc#"].ToString();
var Item2 = (from c in obj.WMS_MSTR_DVMR
where c.dvmr_billdoc == xBillDoc2 &&
c.invty_id == xItem2
select c.invty_id).FirstOrDefault();
var dateQuery = obj.Database.SqlQuery <DateTime>("SELECT getdate()");
DateTime serverDate = dateQuery.AsEnumerable().First();
obj.WMS_MSTR_DVMR.Where(c => c.invty_id == Item2 && c.dvmr_schedule_date == Convert.ToDateTime("")).ToList().ForEach(x =>
{
x.dvmr_load_date = Convert.ToDateTime(i["Load Date"]);
x.site_code = i["Ship-to"].ToString().ToUpper();
x.dvmr_customer = i["Customer Name"].ToString().ToUpper();
x.dvmr_rdd = Convert.ToDateTime(i["RDD"].ToString());
x.dvmr_shipment = i["Shipment"].ToString().ToUpper();
x.dvmr_shipping_line = i["Shipping Line"].ToString();
x.dvmr_truck_no = i["Truck No"].ToString();
x.dvmr_cvan = i["CVAN"].ToString();
x.dvmr_salesdoc = i["Sales Doc#"].ToString();
x.dvmr_po_number = i["PO number"].ToString();
x.dvmr_billdoc = i["Bill#Doc#"].ToString();
x.dvmr_category = i["Category"].ToString();
x.invty_id = i["Material"].ToString();
x.dvmr_qty = Convert.ToInt32(i["Qty"]);
x.dvmr_date_added = serverDate;
x.uom_id = 1;
});
updated = updated + 1;
}
TranCounter3 = TranCounter3 + 1;
backgroundWorker3.ReportProgress(Convert.ToInt32(100 * TranCounter3 / max), "Uploading " + TranCounter3.ToString() + " out of " + max.ToString() + " Dvmr(s)");
if (backgroundWorker3.CancellationPending)
{
backgroundWorker3.ReportProgress(Convert.ToInt32(100 * TranCounter3 / max), "Cancelling...");
// e.Cancel = true;
backgroundWorker3.ReportProgress(100, "Cancelled!");
break;
}
}
}
con1.Close();
}
private void button4_Click(object sender, EventArgs e)
{
if (button4.Text == "Save")
{
if (textBox2.Text.Trim() != "")
{
if (textBox3.Text.Trim() != "")
{
if (textBox4.Text.Trim() != "")
{
if (comboBox2.Text != "")
{
if (comboBox3.Text != "")
{
if (comboBox4.Text != "")
{
DialogResult dialog = MessageBox.Show("Are you sure you want to save Customer?", "Message", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dialog == DialogResult.Yes)
{
Double xlat = 0;
Double xlong = 0;
if (textBox7.Text.Trim() != "")
{
xlat = Convert.ToDouble(textBox7.Text);
}
if (textBox8.Text.Trim() != "")
{
xlong = Convert.ToDouble(textBox8.Text);
}
string xstatdesc = comboBox4.Text;
int xstatid = 1;
var statid = (from c in obj.WMS_TYPE_STAT
where c.stat_desc == xstatdesc
select c.stat_id).FirstOrDefault();
xstatid = statid;
var dateQuery = obj.Database.SqlQuery <DateTime>("SELECT getdate()");
DateTime serverDate = dateQuery.AsEnumerable().First();
var cust = (from c in obj.WMS_CUST_VIEW
where c.cust_id == textBox2.Text.Trim()
select c.cust_id).FirstOrDefault();
if (cust == null)
{
var customer = obj.Set <WMS_MSTR_CUST>();
customer.Add(new WMS_MSTR_CUST
{
cust_id = textBox2.Text.Trim(),
cust_name = textBox3.Text.Trim(),
cust_address = textBox4.Text.Trim(),
salesman_id = comboBox2.Text,
site_id = comboBox3.Text,
cust_latitude = xlat,
cust_longitude = xlong,
stat_id = xstatid,
cust_datecrtd = serverDate,
cust_crtdby = loggedin_user.userId
});
obj.SaveChanges();
MessageBox.Show("Successfully saved Customer.", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
comboBox1.Text = "Customer ID";
textBox1.Text = textBox2.Text.Trim();
disableAddControls();
}
else
{
MessageBox.Show("'Customer ID' already exist.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
comboBox1.Text = "Customer ID";
textBox1.Text = "";
textBox1.Text = textBox2.Text.Trim();
}
}
else if (dialog == DialogResult.No)
{
}
}
else
{
MessageBox.Show("Please select 'Customer Status'.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
comboBox4.Focus();
}
}
else
{
MessageBox.Show("Please select 'Customer Site'.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
comboBox3.Focus();
}
}
else
{
MessageBox.Show("Please select 'Customer Salesman'.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
comboBox2.Focus();
}
}
else
{
MessageBox.Show("Please fill up 'Customer Address'.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
textBox4.Focus();
}
}
else
{
MessageBox.Show("Please fill up 'Customer Name'.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
textBox3.Focus();
}
}
else
{
MessageBox.Show("Please fill up 'Customer ID'.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
textBox2.Focus();
}
}
else if (button4.Text == "Update")
{
if (textBox3.Text.Trim() != "")
{
if (textBox4.Text.Trim() != "")
{
DialogResult dialog = MessageBox.Show("Are you sure you want to update Customer?", "Message", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dialog == DialogResult.Yes)
{
Double xlat = 0;
Double xlong = 0;
if (textBox7.Text.Trim() != "")
{
xlat = Convert.ToDouble(textBox7.Text);
}
if (textBox8.Text.Trim() != "")
{
xlong = Convert.ToDouble(textBox8.Text);
}
string xstatdesc = comboBox4.Text;
int xstatid = 1;
var statid = (from c in obj.WMS_TYPE_STAT
where c.stat_desc == xstatdesc
select c.stat_id).FirstOrDefault();
xstatid = statid;
var dateQuery = obj.Database.SqlQuery <DateTime>("SELECT getdate()");
DateTime serverDate = dateQuery.AsEnumerable().First();
obj.WMS_MSTR_CUST.Where(c => c.cust_id == textBox2.Text.Trim()).ToList().ForEach(x =>
{
x.cust_name = textBox3.Text.Trim();
x.cust_address = textBox4.Text.Trim();
x.salesman_id = comboBox2.Text;
x.site_id = comboBox3.Text;
x.cust_latitude = xlat;
x.cust_longitude = xlong;
x.stat_id = xstatid;
x.cust_dateuptd = serverDate;
x.cust_uptdby = loggedin_user.userId;
});
obj.SaveChanges();
MessageBox.Show("Successfully updated Customer.", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
comboBox1.Text = "Customer ID";
textBox1.Text = "";
textBox1.Text = textBox2.Text.Trim();
disableAddControls();
}
else if (dialog == DialogResult.No)
{
}
}
else
{
MessageBox.Show("Please fill up 'Customer Address'.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
textBox4.Focus();
}
}
else
{
MessageBox.Show("Please fill up 'Customer Name'.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
textBox3.Focus();
}
}
}