public FIDetailEdit(FIHeader fiHeader, FIDetail fIDetail) //update
{
InitializeComponent();
Init();
ArrangeHeaderFields(fiHeader);
isInsert = false;
currentHeader = fiHeader;
txtDescription.Text = fIDetail.Description;
txtActionReq.Text = fIDetail.ActionReq;
txtActionCode.Text = fIDetail.ActionCode;
dtpActionDate.Value = fIDetail.ActionDt;
fIDetail.Owners = fIDetail.getOwners(fIDetail.Id, fIDetail.RevNo);
foreach (Users thisOwner in fIDetail.Owners)
{
dgvOwners.Rows.Add(new object[] { thisOwner.Id, thisOwner.FullName, thisOwner.RoleName });
}
oldFIDetailRecord = fIDetail;
}
private void ownersToolStripMenuItem_Click(object sender, EventArgs e)
{
if (dgvDetails.SelectedRows.Count > 0)
{
if (Convert.ToInt32(dgvDetails.SelectedRows[0].Cells["OwnersCnt"].Value.ToString()) > 0)
{
int detailId = Convert.ToInt32(dgvDetails.SelectedRows[0].Cells["DetailDetailRevId"].Value.ToString());
int revNo = Convert.ToInt32(dgvDetails.SelectedRows[0].Cells["DetailRevNo"].Value.ToString());
FIDetailRev detRev = DetailRevList.Where(i => i.FIDetailId == detailId && i.RevNo == revNo).First();
FIDetail det = new FIDetail(detRev);
FIDetailEdit frmShowDetails = new FIDetailEdit(glHeader, det);
frmShowDetails.txtDescription.ReadOnly = true;
frmShowDetails.txtActionReq.ReadOnly = true;
frmShowDetails.txtActionCode.Enabled = false;
frmShowDetails.dtpActionDate.Enabled = false;
frmShowDetails.dgvOwners.Enabled = false;
frmShowDetails.btnSave.Enabled = false;
frmShowDetails.ShowDialog();
}
else
{
MessageBox.Show("No Owners found!"); //will never be here!
}
}
}
private void MIeditDetail_Click(object sender, EventArgs e)
{
if (dgvDetails.SelectedRows.Count > 0)
{
int DetailId = Convert.ToInt32(dgvDetails.SelectedRows[0].Cells["DetailId"].Value.ToString());
FIDetail selectedDetail = Header_Details.Where(i => i.Id == DetailId).First();
int HeaderId = Convert.ToInt32(dgvHeaders.SelectedRows[0].Cells["HeaderId"].Value.ToString());
FIHeader selectedHeader = Audit_Headers.Where(i => i.Id == HeaderId).First();
FIDetailEdit frmDetailEdit = new FIDetailEdit(selectedHeader, selectedDetail);
frmDetailEdit.ShowDialog();
if (frmDetailEdit.success)
{
Header_Details = SelectDetails(selectedHeader.Id);
if (AdminAccess == false)
{
Header_Details = Header_Details.Where(i => i.IsDeleted == false).ToList();
}
FillDetailsDataGridView(dgvDetails, Header_Details);
}
}
}
private bool InsertInto_FIDetail_and_FIDetailOwners(FIDetail fiDetail)
{
if (InsertIntoTable_FIDetail(fiDetail) && InsertIntoTable_FIDetailOwners(fiDetail))
{
return(true);
}
else
{
return(false);
}
}
private bool InsertIntoTable_FIDetail(FIDetail fiDetail) //INSERT [dbo].[FIDetail]
{
bool ret = false;
SqlConnection sqlConn = new SqlConnection(SqlDBInfo.connectionString);
string InsSt = "INSERT INTO [dbo].[FIDetail] ([FIHeaderId],[Description],[ActionReq], [ActionDt], [ActionCode], [InsUserId], [InsDt],[UpdUserId], [UpdDt], [RevNo]) " +
"OUTPUT INSERTED.Id " +
"VALUES " +
"(@HeaderId,encryptByPassPhrase(@passPhrase, convert(varchar(500), @Description)), encryptByPassPhrase(@passPhrase, convert(varchar(500), @ActionReq))," +
"@ActionDt, @ActionCode, @InsUserId, getDate(), @InsUserId, getDate(), 1 ) ";
try
{
sqlConn.Open();
SqlCommand cmd = new SqlCommand(InsSt, sqlConn);
cmd.Parameters.AddWithValue("@passPhrase", SqlDBInfo.passPhrase);
cmd.Parameters.AddWithValue("@HeaderId", fiDetail.FIHeaderId);
cmd.Parameters.AddWithValue("@Description", fiDetail.Description);
cmd.Parameters.AddWithValue("@ActionReq", fiDetail.ActionReq);
cmd.Parameters.AddWithValue("@ActionDt", fiDetail.ActionDt);
cmd.Parameters.AddWithValue("@ActionCode", fiDetail.ActionCode);
cmd.Parameters.AddWithValue("@InsUserId", UserInfo.userDetails.Id);
cmd.CommandType = CommandType.Text;
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
fiDetail.Id = Convert.ToInt32(reader["Id"].ToString());
}
reader.Close();
//int rowsAffected = cmd.ExecuteNonQuery();
//if (rowsAffected > 0)
if (fiDetail.Id > 0)
{
ret = true;
}
}
catch (Exception ex)
{
MessageBox.Show("The following error occurred: " + ex.Message);
}
sqlConn.Close();
return(ret);
}
private void MIDetailRevisions_Click(object sender, EventArgs e)
{
if (dgvDetails.SelectedRows.Count > 0)
{
int HeaderId = Convert.ToInt32(dgvHeaders.SelectedRows[0].Cells["HeaderId"].Value.ToString());
FIHeader selectedHeader = Audit_Headers.Where(i => i.Id == HeaderId).First();
int DetailId = Convert.ToInt32(dgvDetails.SelectedRows[0].Cells["DetailId"].Value.ToString());
FIDetail selectedDetail = Header_Details.Where(i => i.Id == DetailId).First();
FIDetail_Revisions frmFIDetailRev = new FIDetail_Revisions(glAudit, selectedHeader, selectedDetail.Id);
frmFIDetailRev.ShowDialog();
}
}
private bool UpdateTable_Details(FIDetail detail)
{
bool ret = false;
SqlConnection sqlConn = new SqlConnection(SqlDBInfo.connectionString);
string InsSt = "UPDATE [dbo].[FIDetail] SET [Description] = encryptByPassPhrase(@passPhrase, convert(varchar(500), @Description)), " +
"[ActionReq] = encryptByPassPhrase(@passPhrase, convert(varchar(500), @ActionReq)), [ActionDt] = @ActionDt, [ActionCode] = @ActionCode, " +
"[UpdUserId] = @UpdUserId, [UpdDt] = getDate(), [RevNo] = RevNo+1, [UseUpdTrigger] = 1 " +
"WHERE id=@id";
try
{
sqlConn.Open();
SqlCommand cmd = new SqlCommand(InsSt, sqlConn);
cmd.Parameters.AddWithValue("@passPhrase", SqlDBInfo.passPhrase);
cmd.Parameters.AddWithValue("@id", detail.Id);
cmd.Parameters.AddWithValue("@ActionDt", detail.ActionDt);
cmd.Parameters.AddWithValue("@Description", detail.Description);
cmd.Parameters.AddWithValue("@ActionReq", detail.ActionReq);
cmd.Parameters.AddWithValue("@ActionCode", detail.ActionCode);
cmd.Parameters.AddWithValue("@UpdUserId", UserInfo.userDetails.Id);
cmd.CommandType = CommandType.Text;
int rowsAffected = cmd.ExecuteNonQuery();
if (rowsAffected > 0)
{
ret = true;
}
}
catch (Exception ex)
{
MessageBox.Show("The following error occurred: " + ex.Message);
}
sqlConn.Close();
return(ret);
}
private bool InsertIntoTable_FIDetailOwners(FIDetail fiDetail) //INSERT [dbo].[FIDetail_Owners]
{
bool ret = false;
fiDetail.RevNo += 1;//==0:insert->1, >0:update->++
int cnt = 0;
foreach (Users thisOwner in fiDetail.Owners)
{
if (InsertIntoTable_FIDetailSingleOwner(fiDetail, thisOwner))
{
cnt = cnt + 1;
}
}
if (cnt == fiDetail.Owners.Count)
{
ret = true;
}
return(ret);
}
private bool InsertIntoTable_FIDetailSingleOwner(FIDetail fiDetail, Users SingleOwner) //INSERT [dbo].[FIDetail_Owners]
{
bool ret = false;
//dgvOwners.Rows.Add(new object[] { thisOwner.Id, thisOwner.FullName, thisOwner.RoleName });
SqlConnection sqlConn = new SqlConnection(SqlDBInfo.connectionString);
string InsSt = "INSERT INTO [dbo].[FIDetail_Owners] ([FIDetailId], [RevNo], [OwnerId], [UsersId], [InsDate]) " +
"VALUES (@DetailId, @RevNo, @OwnerId, @InsUserId, getDate()) ";
try
{
sqlConn.Open();
SqlCommand cmd = new SqlCommand(InsSt, sqlConn);
cmd.Parameters.AddWithValue("@DetailId", fiDetail.Id);
cmd.Parameters.AddWithValue("@RevNo", fiDetail.RevNo);
cmd.Parameters.AddWithValue("@OwnerId", SingleOwner.Id);
cmd.Parameters.AddWithValue("@InsUserId", UserInfo.userDetails.Id);
cmd.CommandType = CommandType.Text;
int rowsAffected = cmd.ExecuteNonQuery();
if (rowsAffected > 0)
{
ret = true;
}
}
catch (Exception ex)
{
MessageBox.Show("The following error occurred: " + ex.Message);
}
sqlConn.Close();
return(ret);
}
private void btnSave_Click(object sender, EventArgs e)
{
if (txtDescription.Text.Trim() == "")
{
MessageBox.Show("Please insert a Description!");
return;
}
if (txtActionReq.Text.Trim() == "")
{
MessageBox.Show("Please insert an Action Required!");
return;
}
List <Users> newOwners = new List <Users>();
for (int l = 0; l < dgvOwners.Rows.Count; l++)
{
if (dgvOwners.Rows[l].IsNewRow == false)
{
newOwners.Add(new Users()
{
Id = Convert.ToInt32(dgvOwners.Rows[l].Cells["Id"].Value.ToString()),
FullName = dgvOwners.Rows[l].Cells["FullName"].Value.ToString(),
RoleName = dgvOwners.Rows[l].Cells["Role"].Value.ToString()
});
}
}
if (newOwners.Count <= 0)
{
MessageBox.Show("Please insert at least one Owner!");
return;
}
newFIDetailRecord = new FIDetail()
{
Id = oldFIDetailRecord.Id,
Description = txtDescription.Text,
ActionReq = txtActionReq.Text,
ActionDt = dtpActionDate.Value,
ActionCode = txtActionCode.Text,
OwnersCnt = oldFIDetailRecord.OwnersCnt,
Owners = newOwners,
RevNo = oldFIDetailRecord.RevNo,
FIHeaderId = currentHeader.Id,
AttCnt = oldFIDetailRecord.AttCnt
};
if (isInsert) //insert
{
//if (InsertIntoTable_FIDetail(newFIDetailRecord))
if (InsertInto_FIDetail_and_FIDetailOwners(newFIDetailRecord))
{
MessageBox.Show("New F/I Detail inserted successfully!");
success = true;
Close();
}
else
{
MessageBox.Show("The New F/I Detail has not been inserted!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else //update
{
if (FIDetail.isEqual(oldFIDetailRecord, newFIDetailRecord) == false)
{
//if (UpdateTable_Details(newFIDetailRecord))
if (Update_FIDetail_and_FIDetailOwners(newFIDetailRecord))
{
bool successful = true;
newFIDetailRecord.RevNo = newFIDetailRecord.RevNo + 1;
success = true;
if (oldFIDetailRecord.AttCnt > 0)
{
if (InsertIntoTable_DetailAtt(newFIDetailRecord.Id, oldFIDetailRecord.RevNo, UserInfo.userDetails.Id) == false)
{
successful = false;
}
}
if (successful)
{
MessageBox.Show("Detail updated successfully!");
}
else
{
MessageBox.Show("Detail updated. Error while inserting attachments.");
}
Close();
}
else
{
MessageBox.Show("The F/I Detail has not been updated!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
Close();
}
}
}
public static void FillDetailsDataGridView(DataGridView dgv, FIDetail Detail, int dgvIndex)
{
//dgv.Rows.Clear();
List <dgvDictionary> dgvDictList = new List <dgvDictionary>();
dgvDictList.Add(new dgvDictionary()
{
dbfield = Detail.Id, dgvColumnHeader = "DetailId"
});
dgvDictList.Add(new dgvDictionary()
{
dbfield = Detail.Description, dgvColumnHeader = "DetailDescription"
});
dgvDictList.Add(new dgvDictionary()
{
dbfield = Detail.ActionReq, dgvColumnHeader = "DetailActionReq"
});
dgvDictList.Add(new dgvDictionary()
{
dbfield = Detail.ActionDt.ToString("dd.MM.yyyy"), dgvColumnHeader = "DetailActionDt"
});
dgvDictList.Add(new dgvDictionary()
{
dbfield = Detail.UpdUser.FullName, dgvColumnHeader = "DetailUpdUser"
});
dgvDictList.Add(new dgvDictionary()
{
dbfield = Detail.UpdDt.ToString("dd.MM.yyyy HH:mm:ss"), dgvColumnHeader = "DetailUpdDate"
});
dgvDictList.Add(new dgvDictionary()
{
dbfield = Detail.RevNo, dgvColumnHeader = "DetailRevNo"
});
dgvDictList.Add(new dgvDictionary()
{
dbfield = Detail.AttCnt, dgvColumnHeader = "AttCnt"
});
dgvDictList.Add(new dgvDictionary()
{
dbfield = Detail.ActionCode, dgvColumnHeader = "DetailActionCode"
});
dgvDictList.Add(new dgvDictionary()
{
dbfield = Detail.OwnersCnt, dgvColumnHeader = "DetailOwnersCnt"
});
dgvDictList.Add(new dgvDictionary()
{
dbfield = Detail.IsDeleted, dgvColumnHeader = "DetailIsDeleted"
});
object[] obj = new object[dgv.Columns.Count];
for (int i = 0; i < dgv.Columns.Count; i++)
{
obj[i] = dgvDictList.Where(z => z.dgvColumnHeader == dgv.Columns[i].Name).First().dbfield;
dgv.Rows[dgvIndex].Cells[i].Value = obj[i];
}
//set selected again???
dgv.Rows[dgvIndex].Selected = true;
}
