private void btnRELoad_Click(object sender, RoutedEventArgs e)
{
try
{
if (dpREFrom.Text == "" || dpRETo.Text == "")
{
MessageBox.Show("Please select date");
return;
}
string dtfrom = Convert.ToDateTime(dpREFrom.Text).ToSqlLiteDatetime();
string dtto = Convert.ToDateTime(dpRETo.Text + " 23:59:59").ToSqlLiteDatetime();
db = new SQLiteDatabase();
string expenseQuery = "Select * from EXPENSES where Created BETWEEN '" + dtfrom + "' AND '" + dtto + "' " + (string.IsNullOrEmpty(txtREName.Text) ? "" : "AND VendorName Like '%" + txtREName.Text + "%'") + " ORDER BY Created DESC";
DataTable dtd = db.GetDataTable(expenseQuery);
List <Expense> lidon = dtd.DataTableToList <Expense>();
grdRepExpenses.ItemsSource = lidon;
string countqry = "Select Count(*) as Count,SUM(Amount) as Total from EXPENSES where Created BETWEEN '" + dtfrom + "' AND '" + dtto + "' " + (string.IsNullOrEmpty(txtREName.Text) ? "" : "AND VendorName Like '%" + txtREName.Text + "%'");
DataTable ctd = db.GetDataTable(countqry);
if (ctd.Rows.Count > 0)
{
lblErows.Content = Convert.ToString(ctd.Rows[0]["Count"] != DBNull.Value ? ctd.Rows[0].Field <Int64>("Count") : 0);
lblETotal.Content = Convert.ToString(ctd.Rows[0]["Total"] != DBNull.Value ? ctd.Rows[0].Field <Int64>("Total") : 0);
}
else
{
lblErows.Content = lblETotal.Content = "0";
}
}
catch (Exception)
{
}
}
private void btnEExpExcel_Click(object sender, RoutedEventArgs e)
{
try
{
if (dpREFrom.Text == "" || dpRETo.Text == "")
{
MessageBox.Show("Please select date");
return;
}
string dtfrom = Convert.ToDateTime(dpREFrom.Text).ToSqlLiteDatetime();
string dtto = Convert.ToDateTime(dpRETo.Text + " 23:59:59").ToSqlLiteDatetime();
db = new SQLiteDatabase();
string expenseQuery = "Select * from EXPENSES where Created BETWEEN '" + dtfrom + "' AND '" + dtto + "' " + (string.IsNullOrEmpty(txtREName.Text) ? "" : "AND VendorName Like '%" + txtREName.Text + "%'") + " ORDER BY Created DESC";
DataTable dtd = db.GetDataTable(expenseQuery);
List <Expense> lidon = dtd.DataTableToList <Expense>();
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.FileName = "Expense" + (new Random(1000)).Next(0, 1000).ToString() + ".csv";
saveFileDialog.DefaultExt = ".csv";
if (saveFileDialog.ShowDialog() == true)
{
Utility.CreateCSVFromGenericList <Expense>(lidon, saveFileDialog.FileName);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
}
private void btnLogin_Click(object sender, RoutedEventArgs e)
{
SQLiteDatabase db = new SQLiteDatabase();
string expenseQuery = "Select * from Users where Uname='" + txtUname.Text + "' and Password='******'";
DataTable dtd = db.GetDataTable(expenseQuery);
if (dtd.Rows.Count > 0)
{
LoginUser.UName = dtd.Rows[0].Field <string>("Uname");
LoginUser.Role = dtd.Rows[0].Field <string>("Role");
LoginGrid.Visibility = Visibility.Collapsed;
HideStackPanels();
MainGrid.Visibility = Visibility.Visible;
stkAddDonations.Visibility = Visibility.Visible;
LoadData();
btnSqlSettings.IsEnabled = (LoginUser.Role == "Admin" ? true : false);
txtPassword.Password = "";
InitializeConsts();
var Logindet = @"INSERT INTO LoginActivity (LoginName,LoginDate)VALUES ('" + LoginUser.UName + "', datetime('now'));";
string ret = db.ExecuteScalar(Logindet);
}
else
{
MessageBox.Show("Please Enetr Valid User Name Password.");
}
}
private void LoadExpenses()
{
try
{
if (dtExpFrom.Text == "" || dtExpTo.Text == "")
{
MessageBox.Show("Please select date");
return;
}
string dtfrom = Convert.ToDateTime(dtExpFrom.Text).ToSqlLiteDatetime();
string dtto = Convert.ToDateTime(dtExpTo.Text + " 23:59:59").ToSqlLiteDatetime();
SQLiteDatabase db = new SQLiteDatabase();
string expenseQuery = "Select * from EXPENSES where Created BETWEEN '" + dtfrom + "' AND '" + dtto + "' " + (string.IsNullOrEmpty(txtExpSearch.Text) ? "" : "AND VendorName Like '%" + txtExpSearch.Text + "%'") + " ORDER BY Created DESC";
DataTable dtd = db.GetDataTable(expenseQuery);
List <Expense> liexp = dtd.DataTableToList <Expense>();
grdExpenses.ItemsSource = liexp;
// SQLiteDatabase db = new SQLiteDatabase();
//string expenseQuery = "Select * from Expenses ORDER BY Created DESC";
//DataTable dtd = db.GetDataTable(expenseQuery);
//List<Expense> liexp = dtd.DataTableToList<Expense>();
//grdExpenses.ItemsSource = liexp;
}
catch (Exception)
{
MessageBox.Show("Please enter Valid Values", "Error");
}
}
private void BtnChart_Click(object sender, RoutedEventArgs e)
{
if (dpRCFrom.Text == "" || dpRCTo.Text == "")
{
MessageBox.Show("Please select date");
return;
}
gdChart.Children.Clear();
string dtfrom = Convert.ToDateTime(dpRCFrom.Text).ToSqlLiteDatetime();
string dtto = Convert.ToDateTime(dpRCTo.Text + " 23:59:59").ToSqlLiteDatetime();
db = new SQLiteDatabase();
string Query = "Select ID, ExpDate as Date, AmountPaid as Amount from EXPENSES where Created BETWEEN '" + dtfrom + "' AND '" + dtto + "' ORDER BY Created DESC";
DataTable dte = db.GetDataTable(Query);
List <ChartData> liexp = dte.DataTableToList <ChartData>();
Query = "Select ReceiptNo, Ddate as Date, Amount from Donations where Created BETWEEN '" + dtfrom + "' AND '" + dtto + "' ORDER BY Created DESC";
DataTable dtd = db.GetDataTable(Query);
List <ChartData> lidon = dtd.DataTableToList <ChartData>();
Chart crt = new Chart();
crt.Margin = new Thickness(0, 0, 0, 0);
PieSeries ls = new PieSeries();
ls.IndependentValuePath = "Date";
ls.DependentValuePath = "Amount";
ls.ItemsSource = liexp;
crt.Series.Add(ls);
PieSeries lsd = new PieSeries();
lsd.IndependentValuePath = "Date";
lsd.DependentValuePath = "Amount";
lsd.ItemsSource = lidon;
crt.Series.Add(lsd);
gdChart.Children.Add(crt);
}
private void btnPrintExp_Click(object sender, RoutedEventArgs e)
{
try
{
wb = new WebBrowser();
string excdir = Directory.GetCurrentDirectory() + @"\Templates";
string tpath = excdir + @"\VocherTemplate.html";
string imgPath = excdir + @"\Vocher.png";
StreamReader sr = new StreamReader(tpath);
string html = sr.ReadToEnd();
html = html.Replace("@img@", imgPath);
Expense exp = grdExpenses.SelectedItem as Expense;
SQLiteDatabase db = new SQLiteDatabase();
string expenseQuery = "Select * from EXPENSES e inner join FundType f on e.FundType=f.Fundtype where e.ID=" + exp.ID;
DataTable dtd = db.GetDataTable(expenseQuery);
if (exp != null)
{
html = html.Replace("@ReceiptNo@", Convert.ToString(exp.ExpenseNo)).Replace("@date@", exp.ExpDate.Substring(0, 9)).Replace("@Name@", exp.Reason)
.Replace("@AmountWords@", NumberToWords((int)exp.AmountPaid) + " only").Replace("@DD@", exp.TxnRefNo).Replace("@Amount@", Convert.ToString(exp.AmountPaid) + "/-");
html = html.Replace("@FUNDTYPE@", GetField(dtd.Rows[0], "FundType"));
html = html.Replace("@Bank@", GetField(dtd.Rows[0], "BankName"));
html = html.Replace("@Dated@", exp.ExpDate.Substring(0, 10));
html = html.Replace("@Debit@", GetField(dtd.Rows[0], "AccountNo"));
wb.NavigateToString(html);
sr.Close();
wb.Navigated += Wb_Navigated;
}
else
{
wb = null;
MessageBox.Show("Pleases select item to print", "Print");
}
}
catch (Exception ex)
{
MessageBox.Show("Error Happened :" + ex.Message, "Print");
}
}
public string GenerateSeq(string tabName)
{
string nextnumber;
db = new SQLiteDatabase();
string qry = "select * from Sequence where TabName='" + tabName + "' LIMIT 1";
DataTable Seq = db.GetDataTable(qry);
DateTime dt = DateTime.Now;
string todayseq = dt.Year.ToString().Substring(2, 2) + dt.Month.ToString().PadLeft(2, '0') + dt.Day.ToString().PadLeft(2, '0');
if (Convert.ToString(Seq.Rows[0].Field <Int32>("Sequence")) == todayseq)
{
string s = Seq.Rows[0].Field <Int32>("NextNumber").ToString();
nextnumber = todayseq + s.PadLeft(3, '0');
UpdateSeqWithNextNumber(s, todayseq);
}
else
{
string s = "1";
nextnumber = todayseq + s.PadLeft(3, '0');
UpdateSeqWithNextNumber(s, todayseq);
}
return(nextnumber);
}