public void InsertClientsInvoice(ClientsInvoice invoice)
{
MySqlConnection connection = new MySqlConnection(ConnectionString);
MySqlCommand command = new MySqlCommand("UpsertClientsInvoice", connection)
{
CommandType = System.Data.CommandType.StoredProcedure
};
command.Parameters.Add(new MySqlParameter("VarOrderNo", invoice.OrderNo));
command.Parameters.Add(new MySqlParameter("VarEmployeeID", invoice.EmployeeID));
command.Parameters.Add(new MySqlParameter("VarFirstName", invoice.FirstName));
command.Parameters.Add(new MySqlParameter("VarLastName", invoice.LastName));
command.Parameters.Add(new MySqlParameter("VarCatalogName", invoice.CatalogName));
command.Parameters.Add(new MySqlParameter("VarQuantity", invoice.Quantity));
command.Parameters.Add(new MySqlParameter("VarOrderDate", invoice.OrderDate));
command.Parameters.Add(new MySqlParameter("VarRedemptionPoints", invoice.RedemptionPoints));
command.Parameters.Add(new MySqlParameter("VarLocalCost", invoice.LocalCost));
command.Parameters.Add(new MySqlParameter("VarCountry", invoice.Country));
command.Parameters.Add(new MySqlParameter("VarReasonofReturn", invoice.ReasonofReturn));
connection.Open();
command.ExecuteNonQuery();
connection.Close();
}
protected void GVClientsInvoice_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
ClientsInvoiceDAL invoice = new ClientsInvoiceDAL
{
ConnectionString = ConfigurationManager.ConnectionStrings["MySQLTest"].ToString()
};
ClientsInvoice command = new ClientsInvoice();
//{
// OrderNo = (GVClientsInvoice.Rows[e.RowIndex].FindControl("LblInvoicesOrderNoEdit") as Label).Text,
// EmployeeID = (GVClientsInvoice.Rows[e.RowIndex].FindControl("TxtInvoicesEmpID") as TextBox).Text,
// FirstName = (GVClientsInvoice.Rows[e.RowIndex].FindControl("TxtInvoicesFName") as TextBox).Text,
// LastName = (GVClientsInvoice.Rows[e.RowIndex].FindControl("TxtInvoicesLName") as TextBox).Text,
// CatalogName = (GVClientsInvoice.Rows[e.RowIndex].FindControl("TxtInvoicesCName") as TextBox).Text,
// Quantity = Convert.ToInt32((GVClientsInvoice.Rows[e.RowIndex].FindControl("TxtInvoicesQuantity") as TextBox).Text),
// OrderDate = Convert.ToDateTime((GVClientsInvoice.Rows[e.RowIndex].FindControl("TxtInvoicesOrderDate") as TextBox).Text),
// LocalCost = Convert.ToDouble((GVClientsInvoice.Rows[e.RowIndex].FindControl("TxtInvoicesLocalCost") as TextBox).Text),
// //USDCost = Convert.ToDouble((GVClientsInvoice.Rows[e.RowIndex].FindControl("TxtInvoicesUSDCost") as TextBox).Text),
// TotalLocalCost = Convert.ToDouble(GVClientsInvoice.DataKeys[e.RowIndex].Values["TotalLocalCost"].ToString()),
// RedemptionPoints = (GVClientsInvoice.Rows[e.RowIndex].FindControl("TxtInvoicesRP") as TextBox).Text,
// ReasonofReturn = (GVClientsInvoice.Rows[e.RowIndex].FindControl("DDLInvoicesReason") as DropDownList).SelectedValue,
// Country = GVClientsInvoice.DataKeys[e.RowIndex].Values[2].ToString()
//};
command.OrderNo = (GVClientsInvoice.Rows[e.RowIndex].FindControl("LblInvoicesOrderNoEdit") as Label).Text;
command.EmployeeID = (GVClientsInvoice.Rows[e.RowIndex].FindControl("TxtInvoicesEmpID") as TextBox).Text;
command.FirstName = (GVClientsInvoice.Rows[e.RowIndex].FindControl("TxtInvoicesFName") as TextBox).Text;
command.LastName = (GVClientsInvoice.Rows[e.RowIndex].FindControl("TxtInvoicesLName") as TextBox).Text;
command.CatalogName = (GVClientsInvoice.Rows[e.RowIndex].FindControl("TxtInvoicesCName") as TextBox).Text;
command.Quantity = Convert.ToInt32((GVClientsInvoice.Rows[e.RowIndex].FindControl("TxtInvoicesQuantity") as TextBox).Text);
command.OrderDate = Convert.ToDateTime((GVClientsInvoice.Rows[e.RowIndex].FindControl("TxtInvoicesOrderDate") as TextBox).Text);
command.LocalCost = Convert.ToDouble((GVClientsInvoice.Rows[e.RowIndex].FindControl("TxtInvoicesLocalCost") as TextBox).Text);
//USDCost = Convert.ToDouble((GVClientsInvoice.Rows[e.RowIndex].FindControl("TxtInvoicesUSDCost") as TextBox).Text);
command.TotalLocalCost = Convert.ToDouble(GVClientsInvoice.DataKeys[e.RowIndex].Values["TotalLocalCost"].ToString());
command.RedemptionPoints = (GVClientsInvoice.Rows[e.RowIndex].FindControl("TxtInvoicesRP") as TextBox).Text;
command.ReasonofReturn = (GVClientsInvoice.Rows[e.RowIndex].FindControl("DDLInvoicesReason") as DropDownList).SelectedValue;
command.Country = GVClientsInvoice.DataKeys[e.RowIndex].Values[2].ToString();
invoice.InsertClientsInvoice(command);
GVClientsInvoice.EditIndex = -1;
FetchClientsInvoice(search, search, company, sdate, edate);
}
public List <ClientsInvoice> GetClientsInvoice(string OrderNo, string EmployeeID, DateTime StartDate, DateTime EndDate, int Company)
{
MySqlConnection connection = new MySqlConnection(ConnectionString);
try
{
MySqlCommand command = new MySqlCommand("GetClientsInvoices", connection)
{
CommandType = System.Data.CommandType.StoredProcedure
};
if (string.IsNullOrEmpty(OrderNo))
{
command.Parameters.Add(new MySqlParameter("VarOrderNo", null));
}
else
{
command.Parameters.Add(new MySqlParameter("VarOrderNo", OrderNo));
}
if (string.IsNullOrEmpty(EmployeeID))
{
command.Parameters.Add(new MySqlParameter("VarEmployeeID", null));
}
else
{
command.Parameters.Add(new MySqlParameter("VarEmployeeID", EmployeeID));
}
if (StartDate != DateTime.MinValue)
{
command.Parameters.Add(new MySqlParameter("VarStartDate", StartDate.Date));
}
else
{
command.Parameters.Add(new MySqlParameter("VarStartDate", null));
}
if (EndDate != DateTime.MinValue)
{
command.Parameters.Add(new MySqlParameter("VarEndDate", EndDate.Date));
}
else
{
command.Parameters.Add(new MySqlParameter("VarEndDate", null));
}
command.Parameters.Add(new MySqlParameter("VarCompany", Company));
connection.Open();
List <ClientsInvoice> clientsInvoiceList = new List <ClientsInvoice>();
ClientsInvoice clientsInvoice = new ClientsInvoice();
MySqlDataReader dataReader = command.ExecuteReader();
while (dataReader.Read())
{
clientsInvoice = new ClientsInvoice
{
OrderNo = dataReader["OrderNo"].ToString(),
EmployeeID = dataReader["EmployeeID"].ToString(),
FirstName = dataReader["FirstName"].ToString(),
LastName = dataReader["LastName"].ToString(),
CatalogName = dataReader["CatalogName"].ToString(),
Quantity = Convert.ToInt32(dataReader["Quantity"]),
OrderDate = Convert.ToDateTime(dataReader["OrderDate"]),
RedemptionPoints = dataReader["RedemptionPoints"].ToString(),
LocalCost = Convert.ToDouble(dataReader["LocalCost"]),
Country = dataReader["RedemptionPoints"].ToString(),
ReasonofReturn = dataReader["ReasonofReturn"].ToString(),
USDCost = Convert.ToDouble(dataReader["USDCost"])
};
DateTime?OrderDate = (dataReader["OrderDate"] == System.DBNull.Value) ? (DateTime?)null : Convert.ToDateTime(dataReader["OrderDate"]).Date;
clientsInvoice.OrderDate = OrderDate;
clientsInvoiceList.Add(clientsInvoice);
}
connection.Close();
return(clientsInvoiceList);
}
catch (Exception e)
{
connection.Close();
return(null);
}
}
public List <ClientsInvoice> GetInvoiceTotal(string OrderNo, string EmployeeID, DateTime StartDate, DateTime EndDate, int Company)
{
MySqlConnection connection = new MySqlConnection(ConnectionString);
try
{
MySqlCommand command = new MySqlCommand("GetInvoiceTotal", connection)
{
CommandType = System.Data.CommandType.StoredProcedure
};
command.Parameters.Add(new MySqlParameter("VarOrderNo", OrderNo));
command.Parameters.Add(new MySqlParameter("VarEmployeeID", EmployeeID));
if (StartDate != DateTime.MinValue)
{
command.Parameters.Add(new MySqlParameter("VarStartDate", StartDate.Date));
}
else
{
command.Parameters.Add(new MySqlParameter("VarStartDate", null));
}
if (EndDate != DateTime.MinValue)
{
command.Parameters.Add(new MySqlParameter("VarEndDate", EndDate.Date));
}
else
{
command.Parameters.Add(new MySqlParameter("VarEndDate", null));
}
command.Parameters.Add(new MySqlParameter("VarCompany", Company));
List <ClientsInvoice> TotalInvoicesList = new List <ClientsInvoice>();
connection.Open();
MySqlDataReader dataReader = command.ExecuteReader();
while (dataReader.Read())
{
ClientsInvoice clientsInvoices = new ClientsInvoice()
{
TotalLocalCost = Convert.ToDouble(dataReader["TotalLocalCost"]),
TotalUSDCost = Convert.ToDouble(dataReader["TotalUSDCost"]),
Country = dataReader["Country"].ToString()
};
TotalInvoicesList.Add(clientsInvoices);
}
connection.Close();
return(TotalInvoicesList);
}
catch
{
connection.Close();
return(null);
}
}
