private void Btn_C_DoneAdd_Click(object sender, EventArgs e)
{
if (MessageBox.Show(new StringBuilder().Append("Are you sure you want to add contractor with Contractor Code: ").Append(txt_C_CCode.Text).Append("?").ToString(), "Confirmation", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
using (SqlConnection conn = DBUtils.GetDBConnection())
{
conn.Open();
try
{
using (SqlCommand sqlCommand = new SqlCommand("INSERT INTO Contractors VALUES (@CCode, @Name, @Surname, @EName, @EVN)", conn))
{
sqlCommand.Parameters.AddWithValue("@CCode", txt_C_CCode.Text.Trim());
sqlCommand.Parameters.AddWithValue("@Name", txt_C_Name.Text.Trim());
sqlCommand.Parameters.AddWithValue("@Surname", txt_C_Surname.Text.Trim());
sqlCommand.Parameters.AddWithValue("@EName", txt_C_EName.Text.Trim());
sqlCommand.Parameters.AddWithValue("@EVN", txt_C_EVN.Text.Trim());
sqlCommand.ExecuteNonQuery();
MessageBox.Show("New contractor successfully added.", "Successful", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}
LoadCon();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Hand);
}
finally
{
SetFieldsReadOnly();
ShowButtons();
btn_C_DoneAdd.Visible = false;
}
}
}
else
{
SetFieldsReadOnly();
ShowButtons();
btn_C_DoneAdd.Visible = false;
}
}
//================================================================================================================================================//
// LOAD HOURS OF CONTRACTOR //
//================================================================================================================================================//
private void LoadHours()
{
using (SqlConnection conn = DBUtils.GetDBConnection())
{
conn.Open();
SqlDataAdapter da = new SqlDataAdapter("SELECT Code, Date_Start, Date_End, Hours, Rate_Per_Hour, Total_$, Exchange_Rate, Total_R, "
+ "QTech_Cut, Final_Total, Remittance, Invoice_Received, Paid, Date_Paid FROM Contractor_Hours WHERE Contractor_Code = '" + CCODE + "'", conn);
dt = new DataTable();
da.Fill(dt);
}
decimal netTot = new Decimal();
decimal totHours = new Decimal();
foreach (DataRow dr in dt.Rows)
{
if (dr["Final_Total"].ToString() != "")
{
netTot += Convert.ToDecimal(dr["Final_Total"].ToString());
}
else
{
netTot += Decimal.Zero;
}
}
foreach (DataRow dr in dt.Rows)
{
if (dr["Hours"].ToString() != "")
{
totHours += Convert.ToDecimal(dr["Hours"].ToString());
}
else
{
totHours += Decimal.Zero;
}
}
txt_C_TotPaid.Text = netTot.ToString("c");
txt_C_TotHours.Text = totHours.ToString();
bs.DataSource = dt;
}
private void Btn_IIS_Filter_Click(object sender, EventArgs e)
{
bs.Filter = string.Empty;
bs.Sort = string.Empty;
isFiltered = true;
using (SqlConnection conn = DBUtils.GetDBConnection())
{
conn.Open();
SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Invoices_Send WHERE Client LIKE '" + clientName + "%' AND Date BETWEEN '" + dtp_IIS_From.Value + "' AND '" + dtp_IIS_To.Value + "'", conn);
dt = new DataTable();
da.Fill(dt);
}
bs.DataSource = dt;
btn_IIS_Filter.Visible = false;
btn_IIS_ClearFilter.Visible = true;
}
private void LoadQuotes()
{
using (SqlConnection conn = DBUtils.GetDBConnection())
{
conn.Open();
SqlDataAdapter da = new SqlDataAdapter("SELECT Quote_Number, Quote_Description FROM Quotes_Send WHERE Client = '" + clientName + "'", conn);
dt = new DataTable();
da.Fill(dt);
}
foreach (DataRow row in dt.Rows)
{
string item = row["Quote_Number"].ToString() + " - " + row["Quote_Description"].ToString();
ddb_OA_QuoteNum.AddItem(item);
}
ddb_OA_QuoteNum.selectedIndex = 0;
}
private void Btn_ISA_Done_Click(object sender, EventArgs e)
{
if (MessageBox.Show("Are you sure you want to add invoice with Invoice Number: " + txt_ISA_InvNum.Text + "?", "Confirmation", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
using (SqlConnection conn = DBUtils.GetDBConnection())
{
conn.Open();
try
{
using (SqlCommand cmd = new SqlCommand("INSERT INTO Invoices_Send VALUES (@Date, @InvNum, @Client, @Desc, @Amt, @VAT, @Paid, @DatePaid)", conn))
{
cmd.Parameters.AddWithValue("@Date", dtp_ISA_Date.Value);
cmd.Parameters.AddWithValue("@InvNum", txt_ISA_InvNum.Text.Trim());
cmd.Parameters.AddWithValue("@Client", txt_ISA_CName.Text.Trim());
cmd.Parameters.AddWithValue("@Desc", txt_ISA_Desc.Text.Trim());
cmd.Parameters.AddWithValue("@Amt", ddb_InvSendCur.selectedValue + " " + txt_ISA_Amt.Text);
cmd.Parameters.AddWithValue("@VAT", txt_ISA_VAT.Text);
if (cb_ISA_Paid.Checked)
{
cmd.Parameters.AddWithValue("@Paid", "Yes");
cmd.Parameters.AddWithValue("@DatePaid", dtp_ISA_DatePaid.Value);
}
else
{
cmd.Parameters.AddWithValue("@Paid", "No");
cmd.Parameters.AddWithValue("@DatePaid", DBNull.Value);
}
cmd.ExecuteNonQuery();
MessageBox.Show("New invoice successfully added.", "Successful", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
}
//================================================================================================================================================//
// CELL CLICK IN DATAGRIDVIEW //
//================================================================================================================================================//
private void DGV_CellClick(object sender, DataGridViewCellEventArgs e)
{
if (e.RowIndex >= 0)
{
string str = dgv_Clients.Rows[e.RowIndex].Cells["Code"].Value.ToString();
DataTable tempDT;
if (!localInt)
{
using (SqlConnection conn = DBUtils.GetDBConnection())
{
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT * FROM Clients WHERE Code = '" + str + "'";
tempDT = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(tempDT);
}
}
}
else
{
using (SqlConnection conn = DBUtils.GetDBConnection())
{
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT * FROM Int_Clients WHERE Code = '" + str + "'";
tempDT = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(tempDT);
}
}
}
foreach (DataRow row in tempDT.Rows)
{
txt_ClientCode.Text = row["Code"].ToString().Trim();
txt_ClientName.Text = row["Name"].ToString().Trim();
}
}
}
private void LoadQuotes()
{
using (SqlConnection conn = DBUtils.GetDBConnection())
{
conn.Open();
SqlDataAdapter da = new SqlDataAdapter("SELECT Quote_Number, Quote_Description FROM Quotes_Send WHERE Client = '" + clientName + "'", conn);
quoteDT = new DataTable();
da.Fill(quoteDT);
}
List <String> temp = new List <String>();
foreach (DataRow row in quoteDT.Rows)
{
string item = row["Quote_Number"].ToString() + " - " + row["Quote_Description"].ToString();
temp.Add(item);
}
ddb_OED_QuoteNum.Items = temp.ToArray();
}
private void Btn_QA_Done_Click(object sender, EventArgs e)
{
StringBuilder sb = new StringBuilder().Append("Are you sure you want to add quote with Quote Number: ").Append(txt_QA_QNum.Text).Append("?");
if (MessageBox.Show(sb.ToString(), "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
using (SqlConnection conn = DBUtils.GetDBConnection())
{
conn.Open();
try
{
using (SqlCommand cmd = new SqlCommand("INSERT INTO Quotes_Send VALUES (@QNum, @Date, @Client, @Desc, @OPlaced)", conn))
{
cmd.Parameters.AddWithValue("@QNum", txt_QA_QNum.Text.Trim());
cmd.Parameters.AddWithValue("@Date", dtp_QA_Date.Value);
cmd.Parameters.AddWithValue("@Client", txt_QA_CName.Text.Trim());
cmd.Parameters.AddWithValue("@Desc", txt_QA_Desc.Text.Trim());
if (cb_QA_OrderPlaced.Checked)
{
cmd.Parameters.AddWithValue("@OPlaced", "Yes");
}
else
{
cmd.Parameters.AddWithValue("@OPlaced", "No");
}
cmd.ExecuteNonQuery();
MessageBox.Show("New quote successfully added.", "Successful", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
}
private void Btn_QED_Done_Click(object sender, EventArgs e)
{
string qNum = txt_QED_QNum.Text;
if (MessageBox.Show("Are you sure you want to update quote?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
using (SqlConnection conn = DBUtils.GetDBConnection())
{
conn.Open();
try
{
using (SqlCommand sqlCommand = new SqlCommand("UPDATE Quotes_Send SET Date_Send = @Date, Quote_Description = @Desc, Order_Placed = @OPlaced WHERE Quote_Number = @QNum", conn))
{
sqlCommand.Parameters.AddWithValue("@Date", dtp_QED_Date.Value);
sqlCommand.Parameters.AddWithValue("@Desc", txt_QED_Desc.Text.Trim());
if (cb_QED_OrderPlaced.Checked)
{
sqlCommand.Parameters.AddWithValue("@OPlaced", "Yes");
}
else
{
sqlCommand.Parameters.AddWithValue("@OPlaced", "No");
}
sqlCommand.Parameters.AddWithValue("@QNum", qNum);
sqlCommand.ExecuteNonQuery();
MessageBox.Show("Quote successfully updated.", "Successful", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
}
//================================================================================================================================================//
// DONE CLICKED //
//================================================================================================================================================//
private void Btn_NE_Done_Click(object sender, EventArgs e)
{
if (MessageBox.Show(new StringBuilder().Append("Are you sure you want to add new expenses?").ToString(), "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
using (SqlConnection conn = DBUtils.GetDBConnection())
{
conn.Open();
try
{
using (SqlCommand cmd = new SqlCommand("INSERT INTO Project_Expenses VALUES (@ProjID, @Date, @Merc, @Desc, @TotPrice, @Hrs)", conn))
{
cmd.Parameters.AddWithValue("@ProjID", Proj_ID);
cmd.Parameters.AddWithValue("@Date", DateTime.Now);
cmd.Parameters.AddWithValue("@Merc", txt_NE_Merc.Text.Trim());
cmd.Parameters.AddWithValue("@Desc", ddb_NE_Desc.selectedValue);
cmd.Parameters.AddWithValue("@TotPrice", ddb_Cur.selectedValue + " " + txt_NE_Tot.Text.Trim());
if (!txt_NE_Hours.Text.Equals(string.Empty))
{
cmd.Parameters.AddWithValue("@Hrs", Decimal.Parse(txt_NE_Hours.Text.Trim()));
}
else
{
cmd.Parameters.AddWithValue("@Hrs", DBNull.Value);
}
cmd.ExecuteNonQuery();
MessageBox.Show("New line successfully added.", "Successful", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
}
//================================================================================================================================================//
// LOAD CLIENT DETAILS //
//================================================================================================================================================//
private void LoadClients()
{
dt = new DataTable();
using (SqlConnection conn = DBUtils.GetDBConnection())
{
conn.Open();
SqlDataAdapter lClientDA = new SqlDataAdapter("SELECT * FROM Clients", conn);
SqlDataAdapter iClientDA = new SqlDataAdapter("SELECT * FROM Int_Clients", conn);
lClientDA.Fill(dt);
iClientDA.Fill(dt);
}
foreach (DataRow dr in dt.Rows)
{
ddb_PA_CCode.AddItem(dr["Code"].ToString().Trim());
}
ddb_PA_CCode.selectedIndex = 0;
}
//================================================================================================================================================//
// DONE CLICKED //
//================================================================================================================================================//
private void Btn_PA_Done_Click(object sender, EventArgs e)
{
StringBuilder sb = new StringBuilder().Append("Are you sure you want to add project with project code: ").Append(txt_PA_ProjCode.Text).Append("?");
if (MessageBox.Show(sb.ToString(), "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
using (SqlConnection conn = DBUtils.GetDBConnection())
{
conn.Open();
try
{
using (SqlCommand cmd = new SqlCommand("INSERT INTO Projects VALUES (@ProjID, @Date, @ClientCode, @ClientName, @Desc, @QNum)", conn))
{
cmd.Parameters.AddWithValue("@ProjID", txt_PA_ProjCode.Text.Trim());
cmd.Parameters.AddWithValue("@Date", dtp_PA_Date.Value);
cmd.Parameters.AddWithValue("@ClientCode", ddb_PA_CCode.selectedValue.Trim());
cmd.Parameters.AddWithValue("@ClientName", txt_PA_CName.Text.Trim());
cmd.Parameters.AddWithValue("@Desc", txt_PA_Desc.Text.Trim());
cmd.Parameters.AddWithValue("@QNum", txt_PA_QNum.Text.Trim());
cmd.ExecuteNonQuery();
}
//using (SqlCommand cmd = new SqlCommand("INSERT INTO Quotes_Send(Quote_Number, Client) VALUES (@QNum, @Client)", conn))
//{
// cmd.Parameters.AddWithValue("@QNum", txt_PA_QNum.Text.Trim());
// cmd.Parameters.AddWithValue("@Client", txt_PA_CName.Text.Trim());
// cmd.ExecuteNonQuery();
//}
MessageBox.Show("New project successfully added.", "Successful", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
}
private void Btn_C_Filter_Click(object sender, EventArgs e)
{
btn_C_Filter.Visible = false;
btn_C_ClearFilter.Visible = true;
bs.Filter = string.Empty;
bs.Sort = string.Empty;
isFiltered = true;
using (SqlConnection conn = DBUtils.GetDBConnection())
{
conn.Open();
SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Contractor_Hours WHERE Contractor_Code = '" + CCODE + "' AND Date_Start BETWEEN '"
+ dtp_C_From.Value + "' AND '" + dtp_C_To.Value + "' OR Date_End BETWEEN '" + dtp_C_From.Value + "' AND '" + dtp_C_To.Value + "'", conn);
dt = new DataTable();
da.Fill(dt);
}
bs.DataSource = dt;
}
private void LoadIntClients()
{
using (SqlConnection conn = DBUtils.GetDBConnection())
{
conn.Open();
dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Int_Clients", conn);
da.Fill(dt);
}
bs.DataSource = dt;
totClients = dt.Rows.Count;
if (totClients == 0)
{
btn_ClientEdit.Enabled = false;
}
else if (totClients != 0 && !btn_ClientEdit.Enabled)
{
btn_ClientEdit.Enabled = true;
}
}
private void BackgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{
try
{
app = new Microsoft.Office.Interop.Word.Application();
doc = null;
backgroundWorker1.ReportProgress(10);
object fileName = @"\\192.168.8.121\Contractors\Remittances\Remittance_Template.docx";
missing = Type.Missing;
backgroundWorker1.ReportProgress(20);
doc = app.Documents.Open(fileName, missing, missing);
app.Selection.Find.ClearFormatting();
app.Selection.Find.Replacement.ClearFormatting();
backgroundWorker1.ReportProgress(30);
app.Selection.Find.Execute("<code>", missing, missing, missing, missing, missing, missing, missing, missing, txt_HA_Code.Text.Trim(), 2);
app.Selection.Find.Execute("<name>", missing, missing, missing, missing, missing, missing, missing, missing, txt_HA_Name.Text.Trim(), 2);
app.Selection.Find.Execute("<surname>", missing, missing, missing, missing, missing, missing, missing, missing, txt_HA_Surname.Text.Trim(), 2);
backgroundWorker1.ReportProgress(40);
app.Selection.Find.Execute("<date>", missing, missing, missing, missing, missing, missing, missing, missing, DateTime.Now.ToShortDateString(), 2);
app.Selection.Find.Execute("<desc>", missing, missing, missing, missing, missing, missing, missing, missing, "Week ending " + dtp_HA_To.Value.ToLongDateString(), 2);
app.Selection.Find.Execute("<dolvalue>", missing, missing, missing, missing, missing, missing, missing, missing, txt_HA_TotBE.Text.Trim(), 2);
app.Selection.Find.Execute("<excrate>", missing, missing, missing, missing, missing, missing, missing, missing, txt_HA_ExcRate.Text.Trim(), 2);
backgroundWorker1.ReportProgress(50);
app.Selection.Find.Execute("<total>", missing, missing, missing, missing, missing, missing, missing, missing, txt_HA_TotAE.Text.Trim(), 2);
app.Selection.Find.Execute("<subtotal>", missing, missing, missing, missing, missing, missing, missing, missing, txt_HA_TotAE.Text.Trim(), 2);
app.Selection.Find.Execute("<grandtotal>", missing, missing, missing, missing, missing, missing, missing, missing, txt_HA_TotAE.Text.Trim(), 2);
backgroundWorker1.ReportProgress(60);
object SaveAsFile = (object)@"\\192.168.8.121\Contractors\Remittances\Remittance_" + txt_HA_Code.Text.Trim() + ".docx";
doc.SaveAs2(SaveAsFile, missing, missing, missing);
using (SqlConnection conn = DBUtils.GetDBConnection())
{
backgroundWorker1.ReportProgress(70);
conn.Open();
string sql = "UPDATE Contractor_Hours SET Remittance=@Rem WHERE Code=@Code AND Contractor_Code=@CCode";
backgroundWorker1.ReportProgress(80);
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.AddWithValue("@Rem", "Yes");
cmd.Parameters.AddWithValue("@Code", txt_HA_Code.Text.Trim());
backgroundWorker1.ReportProgress(90);
cmd.Parameters.AddWithValue("@CCode", txt_HA_CCode.Text.Trim());
cmd.ExecuteNonQuery();
}
}
backgroundWorker1.ReportProgress(100);
}
catch (Exception ex)
{
error = ex.Message;
isError = true;
}
}
private void DoUpdate()
{
using (SqlConnection conn = DBUtils.GetDBConnection())
{
conn.Open();
try
{
using (SqlCommand cmd = new SqlCommand("UPDATE Contractor_Hours SET Date_Start = @DateS, Date_End = @DateE, Hours = @Hours, Rate_Per_Hour = @RPH, Total_$ = @TotBE, Exchange_Rate = @ER, Total_R = @TotAE, QTech_Cut = @QTC, Final_Total = @FTot, Paid = @P, Date_Paid = @DP WHERE Code = @Code", conn))
{
decimal excRate;
if (txt_HA_ExcRate.Text == "0,00000" || txt_HA_ExcRate.Text == "0.00000")
{
excRate = 0.00000m;
}
else
{
excRate = Decimal.Parse(txt_HA_ExcRate.Text.Replace(".", ","), CultureInfo.GetCultureInfo("en-ZA"));
}
decimal perHour;
if (txt_HA_DolPH.Text.Contains("$"))
{
if (txt_HA_DolPH.Text.Replace("$", string.Empty) == "0.00")
{
perHour = 0.00m;
}
else
{
perHour = Decimal.Parse(txt_HA_DolPH.Text.Replace("$", string.Empty), CultureInfo.GetCultureInfo("en-US"));
}
}
else
{
perHour = 0.00m;
}
decimal totBE;
if (txt_HA_TotBE.Text.Replace("$", string.Empty) == "0.00")
{
totBE = 0.00m;
}
else
{
totBE = Decimal.Parse(txt_HA_TotBE.Text.Replace("$", string.Empty), CultureInfo.GetCultureInfo("en-US"));
}
decimal qtCut;
if (txt_HA_QTCut.Text.Contains("R"))
{
if (txt_HA_QTCut.Text.Replace("R", string.Empty) == "0,00" || txt_HA_QTCut.Text.Replace("R", string.Empty) == "0.00")
{
qtCut = 0.00m;
}
else
{
qtCut = Decimal.Parse(txt_HA_QTCut.Text.Replace("R", string.Empty), CultureInfo.GetCultureInfo("en-ZA"));
}
}
else
{
qtCut = 0.00m;
}
decimal totAE;
if (txt_HA_TotAE.Text.Replace("R", string.Empty) == "0,00" || txt_HA_TotAE.Text.Replace("R", string.Empty) == "0.00")
{
totAE = 0.00m;
}
else
{
totAE = Decimal.Parse(txt_HA_TotAE.Text.Replace("R", string.Empty), CultureInfo.GetCultureInfo("en-ZA"));
}
decimal fTot;
if (txt_HA_FTotal.Text.Replace("R", string.Empty) == "0,00" || txt_HA_FTotal.Text.Replace("R", string.Empty) == "0.00")
{
fTot = 0.00m;
}
else
{
fTot = Decimal.Parse(txt_HA_FTotal.Text.Replace("R", string.Empty), CultureInfo.GetCultureInfo("en-ZA"));
}
decimal hours;
if (txt_HA_HW.Text == string.Empty)
{
hours = 0.00m;
}
else if (txt_HA_HW.Text.Contains("."))
{
hours = Decimal.Parse(txt_HA_HW.Text.Replace(".", ","));
}
else
{
hours = Decimal.Parse(txt_HA_HW.Text);
}
cmd.Parameters.AddWithValue("@Code", txt_HA_Code.Text.Trim());
cmd.Parameters.AddWithValue("@DateS", dtp_HA_From.Value);
cmd.Parameters.AddWithValue("@DateE", dtp_HA_To.Value);
cmd.Parameters.AddWithValue("@Hours", hours);
cmd.Parameters.AddWithValue("@RPH", perHour);
cmd.Parameters.AddWithValue("@TotBE", totBE);
cmd.Parameters.AddWithValue("@ER", excRate);
cmd.Parameters.AddWithValue("@TotAE", totAE);
cmd.Parameters.AddWithValue("@QTC", qtCut);
cmd.Parameters.AddWithValue("@FTot", fTot);
if (cb_HA_Paid.Checked)
{
cmd.Parameters.AddWithValue("@P", "Yes");
cmd.Parameters.AddWithValue("@DP", dtp_HA_DatePaid.Value);
}
else
{
cmd.Parameters.AddWithValue("@P", "No");
cmd.Parameters.AddWithValue("@DP", DBNull.Value);
}
cmd.ExecuteNonQuery();
MessageBox.Show("Work week successfully updated.", "Successful", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
private void Btn_HA_Done_Click(object sender, EventArgs e)
{
string code = txt_HA_Code.Text;
if (send is Button)
{
StringBuilder sb = new StringBuilder().Append("Are you sure you want to add work week with Code: ").Append(code).Append("?");
if (MessageBox.Show(sb.ToString(), "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
using (SqlConnection conn = DBUtils.GetDBConnection())
{
conn.Open();
try
{
using (SqlCommand cmd = new SqlCommand("INSERT INTO Contractor_Hours VALUES (@Code, @Date_Start, @Date_End, @Hours, @RPHour, @TotBE, @ERate, @TotAE, @QTCut, @FTot, @Rem, @Inv, @Paid, @DPaid, @CCode)", conn))
{
decimal excRate;
if (txt_HA_ExcRate.Text == "0.00000")
{
excRate = 0.00000m;
}
else
{
excRate = Decimal.Parse(txt_HA_ExcRate.Text);
}
decimal perHour;
if (txt_HA_DolPH.Text.Contains("$"))
{
if (txt_HA_DolPH.Text.Replace("$", string.Empty) == "0.00")
{
perHour = 0.00m;
}
else
{
perHour = Decimal.Parse(txt_HA_DolPH.Text.Replace("$", string.Empty), CultureInfo.GetCultureInfo("en-US"));
}
}
else
{
perHour = 0.00m;
}
decimal totBE;
if (txt_HA_TotBE.Text.Replace("$", string.Empty) == "0.00")
{
totBE = 0.00m;
}
else
{
totBE = Decimal.Parse(txt_HA_TotBE.Text.Replace("$", string.Empty), CultureInfo.GetCultureInfo("en-US"));
}
decimal qtCut;
if (txt_HA_QTCut.Text.Contains("R"))
{
if (txt_HA_QTCut.Text.Replace("R", string.Empty) == "0.00")
{
qtCut = 0.00m;
}
else
{
qtCut = Decimal.Parse(txt_HA_QTCut.Text.Replace("R", string.Empty), CultureInfo.GetCultureInfo("en-ZA"));
}
}
else
{
qtCut = 0.00m;
}
decimal totAE;
if (txt_HA_TotAE.Text.Replace("R", string.Empty) == "0.00")
{
totAE = 0.00m;
}
else
{
totAE = Decimal.Parse(txt_HA_TotAE.Text.Replace("R", string.Empty), CultureInfo.GetCultureInfo("en-ZA"));
}
decimal fTot;
if (txt_HA_FTotal.Text.Replace("R", string.Empty) == "0.00")
{
fTot = 0.00m;
}
else
{
fTot = Decimal.Parse(txt_HA_FTotal.Text.Replace("R", string.Empty), CultureInfo.GetCultureInfo("en-ZA"));
}
decimal hours;
if (txt_HA_HW.Text == string.Empty)
{
hours = 0.00m;
}
else if (txt_HA_HW.Text.Contains("."))
{
hours = Decimal.Parse(txt_HA_HW.Text.Replace(".", ","));
}
else
{
hours = Decimal.Parse(txt_HA_HW.Text);
}
cmd.Parameters.AddWithValue("@Code", txt_HA_Code.Text.Trim());
cmd.Parameters.AddWithValue("@Date_Start", dtp_HA_From.Value);
cmd.Parameters.AddWithValue("@Date_End", dtp_HA_To.Value);
cmd.Parameters.AddWithValue("@Hours", hours);
cmd.Parameters.AddWithValue("@RPHour", perHour);
cmd.Parameters.AddWithValue("@TotBE", totBE);
cmd.Parameters.AddWithValue("@ERate", excRate);
cmd.Parameters.AddWithValue("@TotAE", totAE);
cmd.Parameters.AddWithValue("@QTCut", qtCut);
cmd.Parameters.AddWithValue("@FTot", fTot);
cmd.Parameters.AddWithValue("@Rem", "No");
cmd.Parameters.AddWithValue("@Inv", "No");
if (cb_HA_Paid.Checked)
{
cmd.Parameters.AddWithValue("@Paid", "Yes");
cmd.Parameters.AddWithValue("@DPaid", dtp_HA_DatePaid.Value);
}
else
{
cmd.Parameters.AddWithValue("@Paid", "No");
cmd.Parameters.AddWithValue("@DPaid", DBNull.Value);
}
cmd.Parameters.AddWithValue("@CCode", txt_HA_CCode.Text.Trim());
cmd.ExecuteNonQuery();
MessageBox.Show("New work week successfully added.", "Successful", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
}
else
{
StringBuilder sb = new StringBuilder().Append("Are you sure you want to update work week with Code: ").Append(code).Append("?");
if (MessageBox.Show(sb.ToString(), "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
DoUpdate();
}
}
}
private void Cb_QED_OrderPlaced_OnChange(object sender, EventArgs e)
{
bool matchFound = false;
if (cb_QED_OrderPlaced.Checked)
{
using (SqlConnection conn = DBUtils.GetDBConnection())
{
conn.Open();
try
{
// Generates string array with QNum values from Projects
SqlDataAdapter da = new SqlDataAdapter("SELECT Quote_Number FROM Projects", conn);
DataTable dt = new DataTable();
da.Fill(dt);
List <string> keyValues = new List <string>();
// Checks if there exists a project with the same Quote Number
foreach (DataRow row in dt.Rows)
{
if (txt_QED_QNum.Text.Equals(row["Quote_Number"].ToString().Trim()))
{
matchFound = true;
}
}
// If there is no match found then create a blank project in Projects table
if (!matchFound)
{
// Obtains project IDs to obtain the last known entry for Projects to generate a new project ID
da = new SqlDataAdapter("SELECT * FROM Projects", conn);
projDT = new DataTable();
da.Fill(projDT);
int CCode = 0;
string projCode;
foreach (DataRow row in projDT.Rows)
{
string[] strArray1 = row["Project_ID"].ToString().Trim().Split('_');
int x = 0;
if (strArray1[1].Equals(txt_QED_CCode.Text))
{
x = Convert.ToInt32(strArray1[0].Remove(0, 1));
}
if (x > CCode)
{
CCode = x;
}
}
string[] strArray2 = txt_QED_QNum.Text.Trim().Split('_');
projCode = "P" + txt_QED_CCode.Text.Remove(0, 3) + "_" + strArray2[1];
string timeKeep = projCode + "_" + txt_QED_CName.Text.Trim() + "" + txt_QED_Desc.Text.Trim();
// Inserts the new project
using (SqlCommand cmd = new SqlCommand("INSERT INTO Projects VALUES (@ProjID, @Date, @ClientCode, @ClientName, @Desc, @QNum, @Timekeep)", conn))
{
cmd.Parameters.AddWithValue("@ProjID", projCode.Trim());
cmd.Parameters.AddWithValue("@Date", DBNull.Value);
cmd.Parameters.AddWithValue("@ClientCode", txt_QED_CCode.Text.Trim());
cmd.Parameters.AddWithValue("@ClientName", txt_QED_CName.Text.Trim());
cmd.Parameters.AddWithValue("@Desc", DBNull.Value);
cmd.Parameters.AddWithValue("@QNum", txt_QED_QNum.Text.Trim());
cmd.Parameters.AddWithValue("@Timekeep", timeKeep.Trim());
cmd.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
}
//================================================================================================================================================//
// DONE ADDING BUTTON //
//================================================================================================================================================//
private void Btn_DoneAdd_Click(object sender, EventArgs e)
{
string newClientCode = txt_ClientCode.Text.Trim();
StringBuilder sb = new StringBuilder().Append("Are you sure you want to add client with code: ").Append(newClientCode).Append("?");
if (MessageBox.Show(sb.ToString(), "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
using (SqlConnection conn = DBUtils.GetDBConnection())
{
conn.Open();
try
{
if (!localInt)
{
using (SqlCommand cmd = new SqlCommand("INSERT INTO Clients VALUES (@Code, @Name)", conn))
{
cmd.Parameters.AddWithValue("@Code", newClientCode);
cmd.Parameters.AddWithValue("@Name", txt_ClientName.Text.Trim());
cmd.ExecuteNonQuery();
MessageBox.Show("New client successfully added.", "Successful", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
LoadLocalClients();
}
else
{
using (SqlCommand cmd = new SqlCommand("INSERT INTO Int_Clients VALUES (@Code, @Name)", conn))
{
cmd.Parameters.AddWithValue("@Code", newClientCode);
cmd.Parameters.AddWithValue("@Name", txt_ClientName.Text.Trim());
cmd.ExecuteNonQuery();
MessageBox.Show("New client successfully added.", "Successful", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
LoadIntClients();
}
dgv_Clients.CurrentCell = dgv_Clients.Rows[dgv_Clients.Rows.Count - 1].Cells[0];
if (dgv_Clients.Rows.Count != 1)
{
dgv_Clients.ClearSelection();
int index = dgv_Clients.Rows.Count - 1;
dgv_Clients.Rows[index].Selected = true;
dgv_Clients.FirstDisplayedScrollingRowIndex = index;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
btn_ClientAdd.Visible = true;
btn_ClientEdit.Visible = true;
btn_DoneAdd.Visible = false;
btn_Cancel.Visible = false;
isReadOnly = true;
}
}
}
}
//================================================================================================================================================//
// CLIENT CODE SELECT //
//================================================================================================================================================//
private void Ddb_PA_CCode_onItemSelected(object sender, EventArgs e)
{
foreach (DataRow dr in dt.Rows)
{
if (dr["Code"].ToString().Trim().Equals(ddb_PA_CCode.selectedValue))
{
txt_PA_CName.Text = dr["Name"].ToString().Trim();
}
}
// Obtains project IDs to obtain the last known entry for Projects
Projects owner = (Projects)this.Owner;
projDT = owner.GetProjects();
int CCode = 0;
foreach (DataRow row in projDT.Rows)
{
string[] strArray = row["Project_ID"].ToString().Trim().Split('_');
int x = 0;
if (strArray[1].Equals(ddb_PA_CCode.selectedValue))
{
x = Convert.ToInt32(strArray[0].Remove(0, 1));
}
if (x > CCode)
{
CCode = x;
}
}
txt_PA_ProjCode.Text = "P" + (CCode + 1).ToString("000") + "_" + ddb_PA_CCode.selectedValue;
DataTable dataTable;
using (SqlConnection conn = DBUtils.GetDBConnection())
{
conn.Open();
SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Quotes_Send WHERE Client = '" + txt_PA_CName.Text.Trim() + "'", conn);
dataTable = new DataTable();
da.Fill(dataTable);
}
int QNum = 1;
foreach (DataRow dr in dataTable.Rows)
{
if (dr.RowState == DataRowState.Deleted)
{
string str = dr["Quote_Number", DataRowVersion.Original].ToString().Trim();
int pos = str.IndexOf("_");
int x = Convert.ToInt32(str.Remove(0, pos + 2));
if (x > QNum)
{
QNum = x;
}
}
else
{
string str = dr["Quote_Number"].ToString().Trim();
int pos = str.IndexOf("_");
int x = Convert.ToInt32(str.Remove(0, pos + 2));
if (x > QNum)
{
QNum = x;
}
}
}
txt_PA_QNum.Text = ddb_PA_CCode.selectedValue + "_Q" + (QNum + 1).ToString("000");
}
private void Btn_OED_Done_Click(object sender, EventArgs e)
{
if (txt_OED_CONum.Text != string.Empty)
{
if (MessageBox.Show("Are you sure you want to update order?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
if (txt_OED_CONum.Text.Trim() == oldCONum)
{
using (SqlConnection conn = DBUtils.GetDBConnection())
{
conn.Open();
try
{
string sql = "UPDATE Orders_Received SET Date = @Date, Description = @Desc, Amount = @Amt, Percentage_Invoiced = @PercInv, " +
"Percentage_Received = @PercRec, Quote_Number = @QNum WHERE Client_Order_Number = @CONum";
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.AddWithValue("@Date", dtp_OED_Date.Value);
cmd.Parameters.AddWithValue("@Desc", txt_OED_Desc.Text.Trim());
cmd.Parameters.AddWithValue("@Amt", ddb_OrdersCur.selectedValue + " " + txt_OED_Amt.Text);
cmd.Parameters.AddWithValue("@PercInv", pInv);
cmd.Parameters.AddWithValue("@PercRec", pRec);
string[] temp = ddb_OED_QuoteNum.selectedValue.Split('-');
cmd.Parameters.AddWithValue("@QNum", temp[0]);
cmd.Parameters.AddWithValue("@CONum", oldCONum);
cmd.ExecuteNonQuery();
MessageBox.Show("Order successfully updated.", "Successful", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
else if (txt_OED_CONum.Text.Trim() != oldCONum)
{
using (SqlConnection conn = DBUtils.GetDBConnection())
{
conn.Open();
try
{
string sql = "UPDATE Orders_Received SET Client_Order_Number = @CONum, Date = @Date, Description = @Desc, Amount = @Amt, Percentage_Invoiced = @PercInv, " +
"Percentage_Received = @PercRec, Quote_Number = @QNum WHERE Client_Order_Number = @oldCONum";
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.AddWithValue("@CONum", txt_OED_CONum.Text.Trim());
cmd.Parameters.AddWithValue("@Date", dtp_OED_Date.Value);
cmd.Parameters.AddWithValue("@Desc", txt_OED_Desc.Text.Trim());
cmd.Parameters.AddWithValue("@Amt", ddb_OrdersCur.selectedValue + " " + txt_OED_Amt.Text);
cmd.Parameters.AddWithValue("@PercInv", pInv);
cmd.Parameters.AddWithValue("@PercRec", pRec);
string[] temp = ddb_OED_QuoteNum.selectedValue.Split('-');
cmd.Parameters.AddWithValue("@QNum", temp[0]);
cmd.Parameters.AddWithValue("@oldCONum", oldCONum);
cmd.ExecuteNonQuery();
MessageBox.Show("Order successfully updated.", "Successful", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
}
}
else
{
MessageBox.Show("Please enter a Client Order Number to continue.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}