private void Form2_Load(object sender, EventArgs e)
{
cmbPlace.Items.Clear();
cmbUser.Items.Clear();
cmbType.Items.Clear();
cmbState.Items.Clear();
DbProviderFactory df = DbProviderFactories.GetFactory("System.Data.SqlClient");
using (DbConnection cn = df.CreateConnection())
{
cn.ConnectionString = "Data Source=10.15.140.2;Initial Catalog=ITO;User ID=evgeny;Password=ywfaggzu";
cn.Open();
DbCommand cmd = df.CreateCommand();
cmd.Connection = cn;
cmd.CommandText = "select distinct Place from Invent";
using (DbDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
cmbPlace.Items.Add(dr[0].ToString());
}
}
cmd.CommandText = "select distinct Type from Invent";
using (DbDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
cmbType.Items.Add(dr[0].ToString());
}
}
cmd.CommandText = "select distinct State from Invent";
using (DbDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
cmbState.Items.Add(dr[0].ToString());
}
}
cmd.CommandText = "select UserID, LastName, Name, SecondName from Staff order by LastName";
using (DbDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
ListElement le = new ListElement(dr[0].ToString(), dr[1].ToString() + " " + dr[2].ToString() + " " + dr[3].ToString());
cmbUser.Items.Add(le);
cbMOL_name.Items.Add(le);
}
}
}
}
public void FilllbStaff(string DepID)
{
lbStaff.Items.Clear();
StaffTableAdapter dAdapt = new StaffTableAdapter();
ITO_DAL.dsITO.StaffDataTable staffTbl = new dsITO.StaffDataTable();
dAdapt.Fill(staffTbl);
string filterString = "DepartmentID=" + DepID;
DataRow[] staff = staffTbl.Select(filterString);
//Отбражаем организационные единицы верхнего уровня UpDepID NULL
for (int i = 0; i < staff.Length; i++)
{
ListElement le = new ListElement(staff[i]["UserID"].ToString(), staff[i]["LastName"].ToString() + " " +
staff[i]["Name"].ToString() + " " + staff[i]["SecondName"].ToString());
lbStaff.Items.Add(le);
//lbStaff.Items
// lvStaff.Items.Add(staff[i]["UserID"].ToString(), staff[i]["LastName"].ToString() + " " +
// staff[i]["Name"].ToString() + " " + staff[i]["SecondName"].ToString(),0);
}
}
private void button1_Click(object sender, EventArgs e)
{
cbBookkeping.Items.Clear();
clbInvent.Items.Clear();
clbDevices.Items.Clear();
DbProviderFactory df = DbProviderFactories.GetFactory("System.Data.SqlClient");
using (DbConnection cn = df.CreateConnection())
{
cn.ConnectionString = "Data Source=10.15.140.2;Initial Catalog=ITO;User ID=evgeny;Password=ywfaggzu";
cn.Open();
DbCommand cmd = df.CreateCommand();
cmd.Connection = cn;
//cmd.CommandText = "select ID, [Наименование номенклатуры]+'\t' +[Номер]+'\t'+SerialNo+'\tQ:'+CAST(Quntity as NVARCHAR(10))+'\tF:'+CAST(FactQuntity as NVARCHAR(10))+'\t'+CAST(rev as NVARCHAR(10))+'\t'+[Комментарий] from Bookkeeping where rev = '82015'";
cmd.CommandText = "select ID, [Наименование номенклатуры],[Тип],[Номенклатура],[Номер],SerialNo,Quntity,FactQuntity,rev,[Комментарий],MOL_name from Bookkeeping where [Наименование номенклатуры] like '" + txbName.Text + "'";
if (txbSerial.Text.Length > 0)
cmd.CommandText += " and SerialNo like '" + txbSerial.Text + "'";
if (txbInvNo.Text.Length>0)
cmd.CommandText += " and ( [Номер] like '" + txbInvNo.Text + "' or [Номенклатура] like '" + txbInvNo.Text + "')";
if (!cbAllRev.Checked)
cmd.CommandText += " and rev = '82015'";
using (DbDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
ListElement le = new ListElement(dr[0].ToString(), dr[0].ToString() + "\t" + dr[1].ToString() + "\tтип:" + dr[2].ToString() + "\t" + dr[3].ToString() + "\tИнв.№ " + dr[4].ToString() + "\ts/n: " + dr[5].ToString() + "\tКол:" + dr[6].ToString() + "\tФакт:" + dr[7].ToString() + "\trev:" + dr[8].ToString() + "\t" + dr[9].ToString() + "\t" + dr[10].ToString());
cbBookkeping.Items.Add(le);
}
label1.Text = cbBookkeping.Items.Count.ToString();
}
cmd.CommandText = "select * from Invent where Model like '" + txbName.Text + "'";
if (txbSerial.Text.Length > 0)
cmd.CommandText += " and SerialNo like '" + txbSerial.Text + "'";
if (txbInvNo.Text.Length > 0)
cmd.CommandText += " and InvNo like '" + txbInvNo.Text + "'";
using (DbDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
ListElement le = new ListElement(dr[0].ToString(), dr[0].ToString() + "\t" + dr[1].ToString() + "\t" + dr[2].ToString() + "\tp/n: " + dr[3].ToString() + "\ts/n: " + dr[4].ToString() + "\tInv:" + dr[5].ToString() + "\t" + dr[6].ToString() + "\t" + dr[7].ToString() + "\t" + dr[8].ToString() + "\t" + dr[9].ToString() + "\t" + dr[10].ToString() );
clbInvent.Items.Add(le);
}
label2.Text = clbInvent.Items.Count.ToString();
}
cmd.CommandText = "SELECT d.ID,m.BrandName,m.Model,m.ProductNo,d.Serial_Number,d.Inv_Number,dbo.GetUserFIOfromStaff(d.UserID),d.PlaceRoom,d.Comment,d.State,d.PlaceBuilding FROM Devices d, models m WHERE m.ID = d. ModelID and ((m.Model like '" + txbName.Text + "') or (m.ProductNo like '" + txbName.Text + "'))";
if (txbSerial.Text.Length > 0)
cmd.CommandText += " and d.Serial_Number like '" + txbSerial.Text + "'";
if (txbInvNo.Text.Length > 0)
cmd.CommandText += " and d.Inv_Number like '" + txbInvNo.Text + "'";
using (DbDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
ListElement le = new ListElement(dr[0].ToString(), dr[0].ToString() + "\t" + dr[1].ToString() + "\t" + dr[2].ToString() + "\tp/n: " + dr[3].ToString() + "\ts/n: " + dr[4].ToString() + "\tinv: " + dr[5].ToString() + "\t" + dr[6].ToString() + "\t" + dr[7].ToString() + "\t" + dr[8].ToString() + "\t" + dr[9].ToString() + "\t" + dr[10].ToString());
clbDevices.Items.Add(le);
}
label5.Text = clbDevices.Items.Count.ToString();
}
}
}
private void Form1_Load(object sender, EventArgs e)
{
BuildOrgStructureTree();
//ADMethodsAccountManagement ADMethods = new ADMethodsAccountManagement();
//UserPrincipal myUser = ADMethods.GetUser(@"druzhinin");
//MessageBox.Show(myUser.GivenName + " " + myUser.EmailAddress);
m_TableAdapterManager = new TableAdapterManager();
m_TableAdapterManager.StaffTableAdapter = new StaffTableAdapter();
m_TableAdapterManager.stfOrgStructureTableAdapter = new stfOrgStructureTableAdapter();
m_TableAdapterManager.NewUserTableAdapter = new NewUserTableAdapter();
m_CommonQuery = new QueriesTableAdapter();
m_StaffTbl = new dsITO.StaffDataTable();
m_NewUser = new dsITO.NewUserDataTable();
m_TableAdapterManager.NewUserTableAdapter.Fill(m_NewUser);
m_OrgStructure = new dsITO.stfOrgStructureDataTable();
m_TableAdapterManager.StaffTableAdapter.Fill(m_StaffTbl);
m_ITOSQLCommand = new ITODAL();
m_ITOSQLCommand.OpenConnection("Data Source=10.15.140.2;Initial Catalog=ITO;Persist Security Info=True;User ID=evgeny;Password=ywfaggzu");
//Заполняем ComboBox данными из базы
//Должности
DataTable dt = m_ITOSQLCommand.ExecuteSQLCommand("Select PositionID, Name from stfPositions");
for (int curRow = 0; curRow < dt.Rows.Count; curRow++)
{
ListElement le = new ListElement(dt.Rows[curRow][0].ToString(), dt.Rows[curRow][1].ToString());
cbPosition.Items.Add(le);
}
//Подразделения
dt = m_ITOSQLCommand.ExecuteSQLCommand("Select DepartmentID, ShortName, Name from stfOrgStructure");
for (int curRow = 0; curRow < dt.Rows.Count; curRow++)
{
ListElement le = new ListElement(dt.Rows[curRow][0].ToString(), dt.Rows[curRow][1].ToString() + " " + dt.Rows[curRow][2].ToString());
cbDepartment.Items.Add(le);
}
//Здания
dt = m_ITOSQLCommand.ExecuteSQLCommand("Select BuildingID, Name from stfBuildings");
for (int curRow = 0; curRow < dt.Rows.Count; curRow++)
{
ListElement le = new ListElement(dt.Rows[curRow][0].ToString(), dt.Rows[curRow][1].ToString());
cbWorkPlace.Items.Add(le);
}
dt = m_ITOSQLCommand.ExecuteSQLCommand("Select UserID,LastName,Name,SecondName from Staff");
for (int curRow = 0; curRow < dt.Rows.Count; curRow++)
{
ListElement le = new ListElement(dt.Rows[curRow][0].ToString(), dt.Rows[curRow][1].ToString()+" "+dt.Rows[curRow][2].ToString()+" "+dt.Rows[curRow][3].ToString());
cmbUser.Items.Add(le);
}
dt = m_ITOSQLCommand.ExecuteSQLCommand("Select distinct Place from Invent");
for (int curRow = 0; curRow < dt.Rows.Count; curRow++)
{
cbPlace.Items.Add(dt.Rows[curRow][0].ToString());
}
dt = m_ITOSQLCommand.ExecuteSQLCommand("Select distinct Room from Invent");
for (int curRow = 0; curRow < dt.Rows.Count; curRow++)
{
cbRoom.Items.Add(dt.Rows[curRow][0].ToString());
}
}
private void FillPhones(string _UserID)
{
lbPhones.Items.Clear();
DataTable dt = m_ITOSQLCommand.ExecuteSQLCommand("Select PhoneID,dbo.GetPhoneType(Type),PhoneNumber from Phones where UserID=" + _UserID);
for (int curRow = 0; curRow < dt.Rows.Count; curRow++)
{
ListElement le = new ListElement(dt.Rows[curRow][0].ToString(),
dt.Rows[curRow][1].ToString() + ": " + dt.Rows[curRow][2].ToString());
lbPhones.Items.Add(le);
}
}
private void FillPasswords(string _UserID)
{
cbPassType.Items.Clear();
//Типы Телефонов
foreach (Enum value in Enum.GetValues(typeof(ePasswordTypes)))
{
ListElement le = new ListElement(Convert.ToString((int)(ePasswordTypes)value), value.ToString());
cbPassType.Items.Add(le);
}
lbPasswords.Items.Clear();
DataTable dt = m_ITOSQLCommand.ExecuteSQLCommand("Select PasswordID, dbo.GetPassType(Type),Password, Type from stfPasswords where UserID=" + _UserID);
for (int curRow = 0; curRow < dt.Rows.Count; curRow++)
{
ListElement le = new ListElement(dt.Rows[curRow][0].ToString(),
dt.Rows[curRow][1].ToString() + ": " + dt.Rows[curRow][2].ToString(),
dt.Rows[curRow][3].ToString(),
dt.Rows[curRow][2].ToString());
lbPasswords.Items.Add(le);
}
}
private void FillEmails(string _UserID)
{
lbEmails.Items.Clear();
DataTable dt = m_ITOSQLCommand.ExecuteSQLCommand("Select EmailID, email from Emails where UserID=" + _UserID);
for (int curRow = 0; curRow < dt.Rows.Count; curRow++)
{
ListElement le = new ListElement(dt.Rows[curRow][0].ToString(),
dt.Rows[curRow][1].ToString());
lbEmails.Items.Add(le);
}
}
public ChangeUserForm()
{
InitializeComponent();
/* m_TableAdapterManager = new TableAdapterManager();
m_TableAdapterManager.StaffTableAdapter = new StaffTableAdapter();
m_TableAdapterManager.stfOrgStructureTableAdapter = new stfOrgStructureTableAdapter();
m_TableAdapterManager.NewUserTableAdapter = new NewUserTableAdapter();
m_StaffTbl = new dsITO.StaffDataTable();
m_NewUser = new dsITO.NewUserDataTable();
m_TableAdapterManager.NewUserTableAdapter.Fill(m_NewUser);
m_OrgStructure = new dsITO.stfOrgStructureDataTable();
m_TableAdapterManager.StaffTableAdapter.Fill(m_StaffTbl);*/
m_ITOSQLCommand = new ITODAL();
m_ADcon = new ADMethodsAccountManagement();
m_ITOSQLCommand.OpenConnection("Data Source=10.15.140.2;Initial Catalog=ITO;Persist Security Info=True;User ID=evgeny;Password=ywfaggzu");
//Заполняем ComboBox данными из базы
//Должности
DataTable dt = m_ITOSQLCommand.ExecuteSQLCommand("Select PositionID, Name from stfPositions");
for (int curRow = 0; curRow < dt.Rows.Count; curRow++)
{
ListElement le = new ListElement(dt.Rows[curRow][0].ToString(),dt.Rows[curRow][1].ToString());
cbPosition.Items.Add(le);
}
//Подразделения
dt = m_ITOSQLCommand.ExecuteSQLCommand("Select ShortName, Name, DepartmentID from stfOrgStructure");
for (int curRow = 0; curRow < dt.Rows.Count; curRow++)
{
ListElement le = new ListElement(dt.Rows[curRow][2].ToString(), dt.Rows[curRow][0].ToString() + " " + dt.Rows[curRow][1].ToString());
cbDepartment.Items.Add(le);
}
//Здания
dt = m_ITOSQLCommand.ExecuteSQLCommand("Select BuildingID, Name from stfBuildings");
for (int curRow = 0; curRow < dt.Rows.Count; curRow++)
{
ListElement le = new ListElement(dt.Rows[curRow][0].ToString(), dt.Rows[curRow][1].ToString());
cbWorkPlace.Items.Add(le);
}
//Типы Телефонов
foreach ( Enum value in Enum.GetValues(typeof(ePhoneTypes)))
{
ListElement le = new ListElement(Convert.ToString((int)(ePhoneTypes)value), ITO_StringConverter.PhoneTypeToStr((ePhoneTypes)value));
cbPhoneType.Items.Add(le);
}
}
