/// <summary>
/// Select a report
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void comboBox_reports_SelectedIndexChanged(object sender, EventArgs e)
{
advancedDataGridView_main.CleanFilterAndSort();
_mainBindingSource.DataSource = null;
_datatableReportsParameters.Clear();
textBox_info.Text = "";
if (comboBox_reports.SelectedIndex != -1 && comboBox_reports.SelectedIndex != 0)
{
int reports_id = Convert.ToInt32(comboBox_reports.SelectedValue);
reports report = _dentnedModel.Reports.Find(reports_id);
if (!String.IsNullOrEmpty(report.reports_infotext))
{
textBox_info.Text = report.reports_infotext;
}
string query = report.reports_query;
List <string> pl = Regex.Matches(query, @"(?<!\@)\@\w+").Cast <Match>().Select(m => m.Value).ToList();
List <string> pinserted = new List <string>();
foreach (string p in pl)
{
if (!pinserted.Contains(p.ToString()) && !p.StartsWith("@NOT"))
{
pinserted.Add(p.ToString());
DataRow dr = _datatableReportsParameters.NewRow();
dr["Name"] = p.ToString();
_datatableReportsParameters.Rows.Add(dr);
}
}
}
}
private async Task LoadReport()
{
try
{
int count = 0;
var fullyPaid = chkFullyPaid.Checked;
var year = dtDate.Value.Year;
var month = dtDate.Value.Month;
var supplierName = cboSuppliers.Text;
_purchases = await _purchaseService.GetAllPurchasePayablesAsync(year, month, supplierName);
var ds = new reports();
DataTable t = ds.Tables["Payables"];
DataRow r;
foreach (var item in _purchases)
{
count++;
r = t.NewRow();
//r["Id"] = count.ToString();
r["Date"] = item.DatePurchase.ToShortDateString();
r["SupplierName"] = item.SupplierName;
r["SIDR"] = item.SIDR;
r["TotalPurchaseAmount"] = item.TotalPurchaseAmount.ToCurrencyFormat();
r["TotalCashAmount"] = item.TotalCashAmount.ToCurrencyFormat();
r["TotalPayableAmount"] = item.TotalPayableAmount.ToCurrencyFormat();
r["CheckDate"] = item.CheckDate;
r["CheckNumber"] = item.CheckNumber;
r["TotalCheckAmount"] = item.TotalCheckAmount.ToCurrencyFormat();
t.Rows.Add(r);
}
// for total of cash and purchases
r = t.NewRow();
r["SupplierName"] = "Total";
r["TotalPurchaseAmount"] = _purchases.Sum(x => x.TotalPurchaseAmount).ToCurrencyFormat();
r["TotalCashAmount"] = _purchases.Sum(x => x.TotalCashAmount).ToCurrencyFormat();
r["TotalPayableAmount"] = _purchases.Sum(x => x.TotalPayableAmount).ToCurrencyFormat();
t.Rows.Add(r);
var __dateRange = new ReportParameter("MonthYear", string.Format("{0}", dtDate.Value.ToString("MMMM yyyy")));
reportViewer1.LocalReport.SetParameters(new ReportParameter[] { __dateRange });
reportViewer1.LocalReport.DataSources.Clear();
ReportDataSource rds = new ReportDataSource("Payables", t);
reportViewer1.LocalReport.DataSources.Add(rds);
reportViewer1.SetDisplayMode(DisplayMode.PrintLayout);
reportViewer1.ZoomMode = ZoomMode.PageWidth;
this.reportViewer1.RefreshReport();
}
catch (CustomBaseException ex)
{
MetroMessageBox.Show(this, ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
catch (Exception ex)
{
MetroMessageBox.Show(this, ex.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private DataTable GetDataTable(IList <CustomerReceivableModel> customerReceivables, string reportTable)
{
var ds = new reports();
DataTable t = ds.Tables[reportTable];
DataRow r;
foreach (var item in customerReceivables)
{
r = t.NewRow();
r["Date"] = item.Date.ToShortDateString();
r["FullName"] = item.FullName;
r["SI"] = item.SI;
r["Debit"] = item.Debit;
r["Credit"] = item.Credit;
r["Balance"] = item.Balance;
t.Rows.Add(r);
}
r = t.NewRow();
r["SI"] = "Total";
r["Debit"] = customerReceivables.Sum(x => x.Debit);
r["Credit"] = customerReceivables.Sum(x => x.Credit);
r["Balance"] = customerReceivables.Sum(x => x.Balance);
t.Rows.Add(r);
return(t);
}
public void SetUp()
{
_context = ShimsContext.Create();
_page = new reports();
InitializePage(_page);
SetupFakes();
}
private async Task LoadSalesMonthlyReport()
{
try
{
var month = dtDate.Value.Month;
var year = dtDate.Value.Year;
var salesMonthlyTotal = await _salesService.GetSalesMonthlyTotal(month, year);
int count = 0;
var ds = new reports();
DataTable t = ds.Tables["MonthlyTotalSales"];
DataRow r;
foreach (var item in salesMonthlyTotal.SalesDailyTotals)
{
count++;
r = t.NewRow();
//r["Id"] = count.ToString();
r["Date"] = item.Date.ToShortDateString();
r["TotalDailyCashPayment"] = item.TotalDailyCashPayment.ToCurrencyFormat();
r["TotalDailyReceivablesAmount"] = item.TotalDailyReceivablesAmount.ToCurrencyFormat();
r["TotalDailySalesAmount"] = item.TotalDailySalesAmount.ToCurrencyFormat();
t.Rows.Add(r);
}
// for total of cash and purchases
r = t.NewRow();
r["Description"] = "Total";
r["TotalDailyCashPayment"] = salesMonthlyTotal.TotalCashMonthlyPayment.ToCurrencyFormat();
r["TotalDailyReceivablesAmount"] = salesMonthlyTotal.TotalMonthlyReceivablesAmount.ToCurrencyFormat();
r["TotalDailySalesAmount"] = salesMonthlyTotal.TotalMonthlySalesAmount.ToCurrencyFormat();
t.Rows.Add(r);
reportViewer1.LocalReport.DataSources.Clear();
var __totalPayablePayment = new ReportParameter("MonthYear", string.Format("{0} {1}", CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(salesMonthlyTotal.Month), salesMonthlyTotal.Year));
reportViewer1.LocalReport.SetParameters(new ReportParameter[] { __totalPayablePayment });
ReportDataSource rds = new ReportDataSource("MonthlyTotalSales", t);
reportViewer1.LocalReport.DataSources.Add(rds);
reportViewer1.SetDisplayMode(DisplayMode.PrintLayout);
reportViewer1.ZoomMode = ZoomMode.PageWidth;
this.reportViewer1.RefreshReport();
}
catch (CustomBaseException ex)
{
MetroMessageBox.Show(this, ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
catch (Exception ex)
{
MetroMessageBox.Show(this, ex.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public List <reports> custreports(string phoneno, string restcode)
{
try
{
GetConnectionString getConnectionString = new GetConnectionString();
string ConnectionString = getConnectionString.CustomizeConnectionString(restcode);
SqlConnection Conn = new SqlConnection(ConnectionString);
Conn.Open();
List <reports> custreports = new List <reports>();
if (Conn.State != System.Data.ConnectionState.Open)
{
Conn.Open();
}
SqlCommand cmd = new SqlCommand("spviewcustordersforreport", Conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@custphone", phoneno);
SqlDataReader _Reader = cmd.ExecuteReader();
while (_Reader.Read())
{
reports objCust = new reports();
objCust.OrderId = _Reader["OrderId"].ToString();
objCust.OrderDate = _Reader["OrderDate"].ToString();
objCust.restcode = _Reader["restcode"].ToString();
objCust.OrderType = _Reader["OrderType"].ToString();
objCust.amountPaid = _Reader["AmountPaid"].ToString();
objCust.TotalPrice = _Reader["TotalAmount"].ToString();
objCust.OrderType = _Reader["OrderType"].ToString();
custreports.Add(objCust);
}
return(custreports);
}
catch (Exception ex)
{
throw ex;
}
finally
{
//if (Conn != null)
//{
// //if (Conn.State == ConnectionState.Open)
// //{
// // Conn.Close();
// // Conn.Dispose();
// //}
//}
}
}
public void Reports_Test1()
{
string[] errors = new string[] { };
reports t_reports = null;
_dentnedModel.Reports.Remove(_dentnedModel.Reports.List(r => r.reports_name == "XX1").ToArray());
_dentnedModel.Reports.Remove(_dentnedModel.Reports.List(r => r.reports_name == "XX2").ToArray());
t_reports = new reports()
{
//reports_name = "XX1",
reports_query = "SELECT * FROM reports"
};
Assert.IsFalse(_dentnedModel.Reports.CanAdd(t_reports));
t_reports = new reports()
{
reports_name = "XX1",
//reports_query = "SELECT * FROM reports"
};
Assert.IsFalse(_dentnedModel.Reports.CanAdd(t_reports));
t_reports = new reports()
{
reports_name = "XX1",
reports_query = "SELECT * FROM reports"
};
Assert.IsTrue(_dentnedModel.Reports.CanAdd(t_reports));
_dentnedModel.Reports.Add(t_reports);
t_reports = new reports()
{
reports_name = "XX1",
reports_query = "SELECT * FROM reports"
};
Assert.IsFalse(_dentnedModel.Reports.CanAdd(t_reports));
t_reports = new reports()
{
reports_name = "XX2",
reports_query = "SELECT * FROM reports"
};
_dentnedModel.Reports.Add(t_reports);
t_reports = _dentnedModel.Reports.FirstOrDefault(r => r.reports_name == "XX1");
t_reports.reports_name = "XX2";
Assert.IsFalse(_dentnedModel.Reports.CanUpdate(t_reports));
t_reports.reports_name = "XX3";
Assert.IsTrue(_dentnedModel.Reports.CanUpdate(t_reports));
_dentnedModel.Reports.Remove(_dentnedModel.Reports.List(r => r.reports_name == "XX1").ToArray());
_dentnedModel.Reports.Remove(_dentnedModel.Reports.List(r => r.reports_name == "XX2").ToArray());
}
private void LoadReport(string duration, decimal finalAmount, IList <SaleProductInventoryModel> productInventories)
{
try
{
int count = 0;
var ds = new reports();
DataTable t = ds.Tables["ProductInventory"];
DataRow r;
foreach (var item in productInventories)
{
count++;
r = t.NewRow();
//r["Id"] = count.ToString();
r["Category"] = item.CategoryName;
r["Description"] = item.Name;
r["Unit"] = item.Unit;
r["Qty"] = item.Quantity;
r["UCost"] = item.Price.ToCurrencyFormat();
r["Amount"] = item.TotalAmount.ToCurrencyFormat();
t.Rows.Add(r);
}
// for total of cash and purchases
r = t.NewRow();
r["Description"] = "Total";
r["Amount"] = finalAmount.ToCurrencyFormat();
t.Rows.Add(r);
reportViewer1.LocalReport.DataSources.Clear();
var __duration = new ReportParameter("Duration", duration);
reportViewer1.LocalReport.SetParameters(new ReportParameter[] { __duration });
ReportDataSource rds = new ReportDataSource("ProductInventory", t);
reportViewer1.LocalReport.DataSources.Add(rds);
reportViewer1.SetDisplayMode(DisplayMode.PrintLayout);
reportViewer1.ZoomMode = ZoomMode.PageWidth;
this.reportViewer1.RefreshReport();
}
catch (Exception ex)
{
MetroMessageBox.Show(this, ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public ActionResult Report(reports obj)
{
try
{
if (ModelState.IsValid)
{
db.reports.Add(obj);
db.SaveChanges();
ModelState.Clear();
return(View());
}
return(View(obj));
}
catch (Exception ex)
{
return(View(obj));
}
}
public JsonResult repoets_add(reports ss)
{
string result = string.Empty;
try
{
if (dblayer.report_add(ss) > 0)
{
result = "Report successsfully!";
}
else
{
result = " Failed !!";
}
}
catch (Exception e)
{
Console.WriteLine("Exception here" + e);
result = "failed";
}
return(Json(result, JsonRequestBehavior.AllowGet));
}
//report savw view
public int report_add(reports re)
{
int sqlExecutionRes;
SqlCommand cmd = new SqlCommand("sp_reports_add", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@report_name", re.report_name);
cmd.Parameters.AddWithValue("@supplier_id", re.supplier_id);
cmd.Parameters.AddWithValue("@supplier_name", re.supplier_name);
cmd.Parameters.AddWithValue("@account_number", re.account_number);
cmd.Parameters.AddWithValue("@email", re.email);
cmd.Parameters.AddWithValue("@address", re.address);
cmd.Parameters.AddWithValue("@document_types", re.document_types);
cmd.Parameters.AddWithValue("@comments", re.comments);
cmd.Parameters.AddWithValue("@rules_type", re.rules_type);
cmd.Parameters.AddWithValue("@rules", re.rules);
con.Open();
sqlExecutionRes = cmd.ExecuteNonQuery();
con.Close();
return(sqlExecutionRes);
}
private async Task LoadSales()
{
try
{
// delay to load the ui
await Task.Delay(100);
var date = dtDate.Value;
var criteria = txtCriteria.Text;
var sales = await _salesService.GetSales(date, criteria);
var recievablePaymentAmount = await _salePaymentService.GetReceivablePaymentAmount(date.Date);
var ds = new reports();
DataTable t = ds.Tables["SalesReport"];
DataRow r;
int count = 0;
foreach (var sale in sales)
{
count++;
r = t.NewRow();
r["Date"] = sale.SaleDate.ToShortDateString();
r["Cost"] = sale.Cost;
r["SIDR"] = sale.SIDR;
r["Quantity"] = "";
r["ProductName"] = "";
r["SalesDebit"] = "";
r["SalesCredit"] = "";
r["CashDebit"] = "";
r["CashCredit"] = "";
r["ReceivablesDebit"] = "";
r["ReceivablesCredit"] = "";
r["Customer"] = sale.CustomerFullName;
t.Rows.Add(r);
foreach (var item in sale.SalesProducts)
{
r = t.NewRow();
r["Date"] = "";
r["Cost"] = "";
r["SIDR"] = "";
r["Quantity"] = item.Quantity + " " + item.ProductUnit;
r["ProductName"] =
//IsReplace and IsCancel = true then show productName [replace] [cancelled]
//IsCancel = true then show productName [cancelled]
item.IsCancel ? string.Format("{0} [{1} {2}]", item.ProductName, item.QuantityToCancel.ToString(), "cancelled")
//show product name only
: item.ProductName;
r["SalesDebit"] = item.SaleDebit.ToCurrencyFormat();
r["SalesCredit"] = item.SaleCredit.ToCurrencyFormat();
r["CashDebit"] = item.CashDebit.ToCurrencyFormat();
r["CashCredit"] = item.CashCredit.ToCurrencyFormat();
r["ReceivablesDebit"] = item.ReceivableDebit.ToCurrencyFormat();
r["ReceivablesCredit"] = item.ReceivablesCredit.ToCurrencyFormat();
r["Customer"] = "";
t.Rows.Add(r);
}
//total
r = t.NewRow();
r["Date"] = "";
r["Cost"] = "";
r["SIDR"] = "";
r["Quantity"] = "";
r["ProductName"] =
"Total";
r["SalesDebit"] = sale.TotalSaleDebit.ToCurrencyFormat();
r["SalesCredit"] = sale.TotalSaleCredit.ToCurrencyFormat();
r["CashDebit"] = sale.TotalCashDebit.ToCurrencyFormat();
r["CashCredit"] = sale.TotalCashCredit.ToCurrencyFormat();
r["ReceivablesDebit"] = sale.TotalReceivableDebit.ToCurrencyFormat();
r["ReceivablesCredit"] = sale.TotalReceivableCredit.ToCurrencyFormat();
r["Customer"] = "";
t.Rows.Add(r);
}
var cashAmount = sales.Sum(x => x.TotalCashDebit);
var saleAmount = sales.Sum(x => x.TotalSaleCredit);
var recievableSale = sales.Sum(x => x.TotalReceivableDebit);
var totalCash = (recievablePaymentAmount + cashAmount);
var totalSale = totalCash + recievableSale;
var __dateRange = new ReportParameter("DateRange", string.Format("Date: {0}", date.ToString("MMMM dd,yyyy")));
// total purchase by customer per day
var __totalPurchaseSale = new ReportParameter("TotalPurchaseSale", saleAmount.ToCurrencyFormat());
// total cash payment paid bay customer per day
var __cash = new ReportParameter("CashPayment", cashAmount.ToCurrencyFormat());
// total receivable payment by customer by that day
var __receivablePayment = new ReportParameter("ReceivablePayment", recievablePaymentAmount.ToCurrencyFormat());
// cash + receivablePayment
var __totalCash = new ReportParameter("TotalCash", totalCash.ToCurrencyFormat());
// unpaid by customer
var __receivable = new ReportParameter("ReceivableSale", recievableSale.ToCurrencyFormat());
// totalcash + receivable sale
var __totalSale = new ReportParameter("TotalSale", totalSale.ToCurrencyFormat());
reportViewer1.LocalReport.SetParameters(new ReportParameter[] { __dateRange,
__totalPurchaseSale,
__cash,
__receivablePayment,
__receivable,
__totalSale });
reportViewer1.LocalReport.DataSources.Clear();
ReportDataSource rds = new ReportDataSource("SalesReport", t);
reportViewer1.LocalReport.DataSources.Add(rds);
reportViewer1.SetDisplayMode(DisplayMode.PrintLayout);
reportViewer1.ZoomMode = ZoomMode.PageWidth;
this.reportViewer1.RefreshReport();
}
catch (Exception ex)
{
MetroMessageBox.Show(this, ex.ToString());
}
}
public List <dot> getDots(List <user> users, int fromYear, int toYear)
{
reports reports = new reports();
return(reports.getDots(users, fromYear, toYear));
}
/// <summary>
/// Run a report
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button_execute_Click(object sender, EventArgs e)
{
if (comboBox_reports.SelectedIndex != -1 && comboBox_reports.SelectedIndex != 0)
{
DataTable dt = new DataTable();
int reports_id = Convert.ToInt32(comboBox_reports.SelectedValue);
reports report = _dentnedModel.Reports.Find(reports_id);
if (report.reports_ispasswordprotected && !_isPasswordLogged)
{
string input = null;
if (InputBox.ShowPassword(language.reportsPasswordInputMessage, language.reportsPasswordInputTitle, ref input) == DialogResult.OK)
{
if (PasswordHelper.CheckPassword(input, ConfigurationManager.AppSettings["passwordProtectedReports"]))
{
_isPasswordLogged = true;
}
else
{
MessageBox.Show(language.reportsPasswordErrorMessage, language.reportsPasswordErrorTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
else
{
return;
}
}
string query = report.reports_query;
if (!OpenSQLConnection(_sqlConnection))
{
return;
}
try
{
//check for user in group
SqlCommand sql_cm1 = null;
SqlDataReader sql_rd1 = null;
sql_cm1 = new SqlCommand();
sql_cm1.Connection = _sqlConnection;
sql_cm1.CommandType = CommandType.Text;
sql_cm1.CommandText = query;
foreach (DataRow dr in _datatableReportsParameters.Rows)
{
SqlParameter param = new SqlParameter();
param.ParameterName = dr["Name"].ToString();
param.Value = dr["Value"].ToString();
sql_cm1.Parameters.Add(param);
}
sql_rd1 = sql_cm1.ExecuteReader();
sql_rd1.Read();
if (sql_rd1.HasRows)
{
for (int i = 0; i < sql_rd1.FieldCount; i++)
{
Type dtType = Type.GetType("System.String");
try
{
dtType = sql_rd1[sql_rd1.GetName(i)].GetType();
if (dtType == Type.GetType("System.DBNull"))
{
dtType = Type.GetType("System.String");
}
}
catch { }
dt.Columns.Add(sql_rd1.GetName(i), dtType);
}
}
else
{
for (int i = 0; i < sql_rd1.FieldCount; i++)
{
Type dtType = Type.GetType("System.String");
dt.Columns.Add(sql_rd1.GetName(i), dtType);
}
}
sql_rd1.Close();
sql_rd1 = sql_cm1.ExecuteReader();
while (sql_rd1.Read())
{
DataRow dr = dt.NewRow();
for (int i = 0; i < sql_rd1.FieldCount; i++)
{
dr[sql_rd1.GetName(i)] = sql_rd1[sql_rd1.GetName(i)];
}
dt.Rows.Add(dr);
}
sql_rd1.Close();
sql_rd1.Dispose();
sql_cm1.Dispose();
}
catch (Exception ex)
{
dt.Columns.Clear();
dt.Columns.Add("Errors", Type.GetType("System.String"));
DataRow dr = dt.NewRow();
dr["Errors"] = ex.Message;
dt.Rows.Add(dr);
}
finally
{
_sqlConnection.Close();
}
advancedDataGridView_main.CleanFilterAndSort();
_mainBindingSource.DataSource = dt;
for (int i = 0; i < advancedDataGridView_main.Columns.Count; i++)
{
advancedDataGridView_main.Columns[i].AutoSizeMode = System.Windows.Forms.DataGridViewAutoSizeColumnMode.Fill;
}
}
}
public List <reports> Weeklyreports(DateTime DeliverTime, string restcode)
{
try
{
GetConnectionString getConnectionString = new GetConnectionString();
string ConnectionString = getConnectionString.CustomizeConnectionString(restcode);
SqlConnection Conn = new SqlConnection(ConnectionString);
Conn.Open();
DateTime DeliverTime1 = Convert.ToDateTime(DeliverTime);
List <reports> Monthlyreports = new List <reports>();
if (Conn.State != System.Data.ConnectionState.Open)
{
Conn.Open();
}
SqlCommand cmd = new SqlCommand("rptGetWeeklyReportDT", Conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@from_date", DeliverTime1);
SqlDataReader _Reader = cmd.ExecuteReader();
while (_Reader.Read())
{
reports objCust = new reports();
objCust.OrderId = _Reader["OrderId"].ToString();
objCust.OrderDate = _Reader["OrderDate"].ToString();
objCust.restcode = _Reader["restcode"].ToString();
objCust.amountPaid = _Reader["amountPaid"].ToString();
objCust.statusdiscription = _Reader["statusdiscription"].ToString();
objCust.customerphone = _Reader["customerphone"].ToString();
objCust.customerfname = _Reader["customerfname"].ToString();
objCust.customerlname = _Reader["customerlname"].ToString();
objCust.Quantity = _Reader["Quantity"].ToString();
objCust.TotalPrice = _Reader["TotalPrice"].ToString();
objCust.Deliverycharges = _Reader["Deliverycharges"].ToString();
objCust.cgstcharges = _Reader["cgstcharges"].ToString();
objCust.sgstcharges = _Reader["sgstcharges"].ToString();
objCust.Discount = _Reader["Discount"].ToString();
objCust.Tip = _Reader["Tip"].ToString();
objCust.Remarks = _Reader["Remarks"].ToString();
objCust.OrderType = _Reader["OrderType"].ToString();
objCust.TransactionId = _Reader["TransactionId"].ToString();
objCust.status = _Reader["statusid"].ToString();
objCust.Orderstatus = _Reader["statusdiscription"].ToString();
objCust.Delivery_Addresss = _Reader["Delivery_Addresss"].ToString();
objCust.empcode = _Reader["empcode"].ToString();
objCust.TotalPrice = _Reader["TotalPrice"].ToString();
objCust.Billing_Address = _Reader["Billing_Address"].ToString();
objCust.TransactionStatus = _Reader["TransactionStatus"].ToString();
Monthlyreports.Add(objCust);
}
return(Monthlyreports);
}
catch (Exception ex)
{
throw ex;
}
//finally
//{
// if (Conn != null)
// {
// //if (Conn.State == ConnectionState.Open)
// //{
// // Conn.Close();
// // Conn.Dispose();
// //}
// }
//}
}