private void Form_handlers_Load(object sender, EventArgs e)
{
DataSet ds = localInterface.Select("SELECT * FROM handlers ORDER BY name");
dataGridViewHandlers.DataSource = ds.Tables[0];
dataGridViewHandlers.Columns[0].Width = dataGridViewHandlers.Width - 3;
data = ds.Tables[0];
}
private void Form_Expenses_Load(object sender, EventArgs e)
{
comboBoxSuppliers.DataSource = localInterface.Select("SELECT * FROM suppliers WHERE active='1' ORDER BY name").Tables[0];
comboBoxSuppliers.DisplayMember = "name";
comboBoxSuppliers.ValueMember = "id";
comboBoxSuppliers.DropDownHeight = 1000;
showExpenses();
comboBoxDebitDuration.SelectedIndex = 0;
comboBoxDebitInterval.SelectedIndex = 0;
}
private void Form_SelectCustomer_Load(object sender, EventArgs e)
{
DataSet ds = localInterface.Select("SELECT idCustomer,name FROM customers ORDER BY name");
dataGridViewCustomers.DataSource = ds.Tables[0];
dataGridViewCustomers.Columns[0].Visible = false;
dataGridViewCustomers.Columns[1].Width = dataGridViewCustomers.Width - 3;
data = ds.Tables[0];
textBoxSearch.Text = "";
}
public Form_ApproveExpense(Form1 form, DBinterface dbInterface, List <string> id)
{
InitializeComponent();
this.StartPosition = FormStartPosition.CenterScreen;
InputLanguage.CurrentInputLanguage = InputLanguage.FromCulture(new System.Globalization.CultureInfo("en-US"));
localInterface = dbInterface;
mainForm = form;
this.ids = id;
DataTable dt = localInterface.Select("SELECT approved,date,amount FROM expenses WHERE id=" + ids[0]).Tables[0];
approved = (bool)dt.Rows[0][0];
comboBox1.SelectedIndex = 0;
if (approved)
{
comboBox1.SelectedIndex = 1;
}
date = MyUtills.dateFromSQL(dt.Rows[0][1].ToString());
dateTimePicker1.Value = date;
if (this.ids.Count > 1)
{
checkBoxDate.Checked = false;
textBox1.Visible = false;
label1.Visible = false;
}
else
{
textBox1.Text = String.Format("{0:0,0.00}", double.Parse(dt.Rows[0][2].ToString()));
}
}
private void buttonUserEnter_Click(object sender, EventArgs e)
{
string username = textBoxUsername.Text;
string pass = textBoxPassword.Text;
if (username == String.Empty || pass == String.Empty)
{
return;
}
pass = MyUtills.ConvertToMd5(pass);
string adminUsername = "******";
string adminPassword = MyUtills.ConvertToMd5("bnrbnr");
DataTable userTable = localInterface.Select("SELECT type FROM users WHERE username='******' AND password='******'").Tables[0];
int res = userTable.Rows.Count;
if (username == adminUsername && pass == adminPassword)
{
mainForm.UserEnter(1);
this.Close();
}
else if (res > 0)
{
mainForm.UserEnter((int)userTable.Rows[0][0]);
this.Close();
}
else
{
labelStatus.Text = "שם משתמש או סיסמא לא נכונים";
labelStatus.ForeColor = Color.Red;
}
}
private void Form_SelectProject_Load(object sender, EventArgs e)
{
string query = "";
query = "SELECT idProjects,projectNumber,projectName,amountInfo FROM projects";
if (projectIds.Count > 0)
{
string query1 = "SELECT projectNumber FROM projects WHERE idProjects='" + this.projectIds[0].ToString() + "'";
string result1 = ((int)Double.Parse(localInterface.Select(query1).Tables[0].Rows[0][0].ToString())).ToString();
query += " WHERE projectNumber LIKE '%" + result1 + ".%' OR projectNumber='" + result1 + "'";
}
query += " ORDER BY projectNumber";
DataTable dt = localInterface.Select(query).Tables[0];
dataGridViewProjects.DataSource = dt;
DataGridViewCheckBoxColumn column = new DataGridViewCheckBoxColumn();
{
column.HeaderText = "Selected";
column.Name = "Selected";
}
dataGridViewProjects.Columns.Insert(0, column);
dataGridViewProjects.Columns[1].Visible = false;
dataGridViewProjects.Columns[4].Visible = false;
dataGridViewProjects.Columns[0].Width = 50;
dataGridViewProjects.Columns[2].Width = 60;
dataGridViewProjects.Columns[3].Width = dataGridViewProjects.Width - dataGridViewProjects.Columns[0].Width
- dataGridViewProjects.Columns[2].Width - 3;
data = dt;
foreach (int id in projectIds)
{
foreach (DataGridViewRow dr in dataGridViewProjects.Rows)
{
if ((int)dr.Cells[1].Value == id)
{
dr.Cells[0].Value = true;
projectsSelected++;
}
}
}
toolStripStatusLabelProjectsSelected.Text = projectsSelected + " פרויקטים נבחרו ";
}
public Form_EditIncome(Form1 form, DBinterface dbInterface, string id, bool def)
{
InitializeComponent();
this.StartPosition = FormStartPosition.CenterScreen;
InputLanguage.CurrentInputLanguage = InputLanguage.FromCulture(new System.Globalization.CultureInfo("en-US"));
localInterface = dbInterface;
mainForm = form;
this.id = id;
this.definite = def;
if (definite)
{
dateTimePicker1.Value = MyUtills.dateFromSQL(localInterface.Select("SELECT payDate FROM bills WHERE id=" + this.id).Tables[0].Rows[0][0].ToString());
}
else
{
dateTimePicker1.Value = MyUtills.dateFromSQL(localInterface.Select("SELECT toPayDate FROM bills WHERE id=" + this.id).Tables[0].Rows[0][0].ToString());
}
}
private void labelUpdateVat_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
double newVat = 0;
string inputNewVat = textBoxNewVat.Text;
if (inputNewVat == "")
{
inputNewVat = "0";
}
if (!Double.TryParse(inputNewVat, out newVat))
{
MessageBox.Show("מע\"מ לא חוקי", "שגיאה", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.RtlReading);
return;
}
String newDateFormatted = MyUtills.dateToSQL(dateTimePickerNewVat.Value);
//String newDate = dateTimePickerNewVat.Value.ToShortDateString();
//String newDateFormatted = newDate.Substring(6,4) + "-" + newDate.Substring(3,2) + "-" + newDate.Substring(0,2);
dt = localInterface.Select("SELECT id FROM vat WHERE date='" + newDateFormatted + "'").Tables[0];
if (dt.Rows.Count > 0)
{
if (inputNewVat != "0")
{
localInterface.Update("UPDATE vat SET vat='" + inputNewVat + "' WHERE date='" + newDateFormatted + "'");
}
else
{
localInterface.Update("DELETE FROM vat WHERE date='" + newDateFormatted + "'");
}
}
else
{
if (inputNewVat != "0")
{
localInterface.Insert("vat", "date,vat", newDateFormatted + "','" + newVat);
}
}
showVatDataGridView();
}
private void showSuppliers(string search)
{
string sqlCmd = "SELECT id,name FROM suppliers WHERE name LIKE '%" + search + "%' AND active=1 ORDER BY name ASC";
DataTable dt = localInterface.Select(sqlCmd).Tables[0];
dataGridViewSuppliers.DataSource = dt;
dataGridViewSuppliers.Columns[0].Visible = false;
if (dt.Rows.Count > 0)
{
textBoxEdit.Text = dataGridViewSuppliers.SelectedRows[0].Cells[1].Value.ToString();
}
}
private void ShowResults(string year)
{
double Amount = 0, SumAmount = 0;
double Paid = 0, SumPaid = 0;
double Collect = 0, SumCollect = 0;
DataTable dt = new DataTable();
if (year == "בחירה")
{
return;
}
createDataTable();
for (int i = 1; i <= 12; i++)
{
string startDate = year + "-" + i + "-01";
string endtDate = year + "-" + i + "-31";
string query = "SELECT " +
"SUM(projects.amount * bills.curencyRate * bills_projects.percent / 100) AS amount, SUM(projects.amount * bills.curencyRate * bills_projects.paid / 100) AS paid " +
"FROM bills " +
"INNER JOIN bills_projects ON bills.id = bills_projects.idBill " +
"INNER JOIN projects ON projects.idProjects = bills_projects.idProject " +
"WHERE bills.billDate>='" + startDate + "' AND bills.billDate<='" + endtDate + "'";
/*string query = "SELECT " +
* "SUM(amount),SUM(paid) " +
* "FROM bills " +
* "WHERE billDate>='" + startDate + "' AND billDate<='" + endtDate + "'";*/
dt = localInterface.Select(query).Tables[0];
try
{
Amount = Convert.ToDouble(dt.Rows[0][0]);
}
catch
{
Amount = 0;
}
try
{
Paid = Convert.ToDouble(dt.Rows[0][1]);
}
catch
{
Paid = 0;
}
if (Amount == 0)
{
Collect = 0;
}
else
{
Collect = (Paid / Amount) * 100;
}
SumAmount += Amount;
SumPaid += Paid;
ds.Tables["MonthlySums"].Rows.Add(i.ToString().PadLeft(2, '0'),
Amount,
Paid.ToString(),
(Amount - Paid).ToString(),
Collect.ToString());
}
if (SumAmount == 0)
{
SumCollect = 0;
}
else
{
SumCollect = (SumPaid / SumAmount) * 100;
}
ds.Tables["MonthlySums"].Rows.Add("סיכום",
SumAmount,
SumPaid.ToString(),
(SumAmount - SumPaid).ToString(),
SumCollect.ToString());
dataGridViewSummery.DataSource = null;
dataGridViewSummery.DataSource = ds.Tables["MonthlySums"];
dataGridViewSummery.Columns[0].HeaderText = "חודש";
dataGridViewSummery.Columns[1].HeaderText = "חשבון";
dataGridViewSummery.Columns[2].HeaderText = "נפרע";
dataGridViewSummery.Columns[3].HeaderText = "יתרה";
dataGridViewSummery.Columns[4].HeaderText = "גביה ב-%";
dataGridViewSummery.Rows[12].DefaultCellStyle.BackColor = System.Drawing.Color.LightGreen;
}
private void Form_NewProject_Load(object sender, EventArgs e)
{
formSelectCustomer = new Form_SelectCustomer(localInterface, this);
comboBoxCurency.SelectedIndex = 0;
comboBoxDetailsProjectlinking.SelectedIndex = 0;
textBoxDetailsProjectAmount.Text = "0";
textBoxDetailsProjectPriceIndex.Text = "0";
DataTable dt = localInterface.Select("Select * FROM engineers").Tables[0];
comboBoxDetailsProjectHandler1.Items.Add("");
comboBoxDetailsProjectHandler2.Items.Add("");
foreach (DataRow dr in dt.Rows)
{
comboBoxDetailsProjectHandler1.Items.Add(dr[0]);
comboBoxDetailsProjectHandler2.Items.Add(dr[0]);
}
dt = localInterface.Select("Select projectNumber FROM projects").Tables[0];
double temp = 0;
foreach (DataRow row in dt.Rows)
{
if (double.Parse(row[0].ToString()) > temp)
{
if (double.Parse(row[0].ToString()) != 9999)
{
temp = double.Parse(row[0].ToString());
}
}
}
textBoxDetailsProjectNumber.Text = ((int)temp + 1).ToString();
textBoxCustomerId.Text = localProject.CustomerId.ToString();
textBoxDetailsProjectAmount.Text = localProject.Amount.ToString();
comboBoxCurency.SelectedIndex = localProject.Curency;
textBoxDetailsProjectAmountInfo.Text = localProject.AmountNotes;
if (localProject.Type == 1)
{
radioButtonProjectTypeElectricity.Checked = true;
}
else
{
radioButtonProjectTypeInstilation.Checked = false;
}
try
{
comboBoxDetailsProjectHandler1.SelectedIndex = int.Parse(localProject.Handler1);
}
catch
{
comboBoxDetailsProjectHandler1.SelectedIndex = 0;
}
try
{
comboBoxDetailsProjectHandler2.SelectedIndex = int.Parse(localProject.Handler2);
}
catch
{
comboBoxDetailsProjectlinking.SelectedIndex = 0;
}
textBoxDetailsProjectPriceIndex.Text = localProject.PriceIndex.ToString();
if (localProject.PriceIndexDateExists == 1)
{
dateTimePickerDetailsProjectPriceIndexDate.Checked = true;
dateTimePickerDetailsProjectPriceIndexDate.Value = localProject.PriceIndexDate;
}
if (localProject.ContractExists == 1)
{
checkBoxDetailsProjectContract.Checked = true;
}
textBoxDetailsProjectContractNotes.Text = localProject.ContractNotes;
textBoxDetailsProjectNotes.Text = localProject.ProjectNotes;
textBoxDetailsProjectMileStones.Text = localProject.MileStonesNotes;
//Payer
textBoxPayerName.Text = localProject.PayerName;
textBoxPayerPhone.Text = localProject.PayerPhone;
textBoxPayerEmail.Text = localProject.PayerEmail;
textBoxPayerFax.Text = localProject.PayerFax;
textBoxPayerAddress.Text = localProject.PayerAddress;
//Approver
textBoxApproverName.Text = localProject.ApproverName;
textBoxApproverPhone.Text = localProject.ApproverPhone;
textBoxApproverEmail.Text = localProject.ApproverEmail;
textBoxApproverFax.Text = localProject.ApproverFax;
textBoxApproverAddress.Text = localProject.PayerAddress;
}
private void Form_NewBill_Load(object sender, EventArgs e)
{
//textBoxDetailsBillAmount.Text = amount;
textBoxDetailsBillPaid.Text = "0";
comboBoxDetailsBillPayMethod.SelectedIndex = 0;
textBoxCurencyRate.Text = "0";
textBoxBillIndex.Text = "0";
textBoxBillIncrease.Text = "0";
DataTable dt = localInterface.Select("Select * FROM handlers").Tables[0];
comboBoxDetailsBillHandlers.Items.Add("");
foreach (DataRow dr in dt.Rows)
{
comboBoxDetailsBillHandlers.Items.Add(dr[0]);
}
comboBoxDetailsBillHandlers.SelectedItem = "מזכירה";
DataTable dt2 = localInterface.Select("SELECT vat FROM vat WHERE date<='" + MyUtills.dateToSQL(DateTime.Now) + "' ORDER BY date DESC").Tables[0];
textBoxBillVat.Text = "% " + dt2.Rows[0][0].ToString();
}