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 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();
}
}
}