public List <Gift_Cards_Invoice> GetGCTotal(DateTime StartDate, DateTime EndDate)
{
MySqlConnection con = new MySqlConnection(ConnectionString);
try
{
MySqlCommand com = new MySqlCommand("GetGCTotal", con);
com.CommandType = System.Data.CommandType.StoredProcedure;
List <Gift_Cards_Invoice> GCTList = new List <Gift_Cards_Invoice>();
com.Parameters.Add(new MySqlParameter("VarStartDate", StartDate));
com.Parameters.Add(new MySqlParameter("VarEndDate", EndDate));
con.Open();
MySqlDataReader reader = com.ExecuteReader();
while (reader.Read())
{
Gift_Cards_Invoice GC_Invoice = new Gift_Cards_Invoice();
GC_Invoice.TotalLocalCost = Convert.ToDouble(reader["TotalLocalCost"]);
GC_Invoice.TotalUSDCost = Convert.ToDouble(reader["TotalUSDCost"]);
GCTList.Add(GC_Invoice);
}
con.Close();
return(GCTList);
}
catch
{
con.Close();
return(null);
}
}
public void InsertGiftCardsInvoice(Gift_Cards_Invoice GC_Invoice)
{
//Connection and Command objects.
MySqlConnection con = new MySqlConnection(ConnectionString);
MySqlCommand com = new MySqlCommand("UpsertGiftCardsInvoice", con);
//Procedure Parameters.
com.CommandType = System.Data.CommandType.StoredProcedure;
string RName = GC_Invoice.RewardName;
string sp = "/";
if (RName.Contains(sp))
{
com.Parameters.Add(new MySqlParameter("VarRewardName", RName.Split('/')[1]));
}
else
{
{ com.Parameters.Add(new MySqlParameter("VarRewardName", RName)); }
}
com.Parameters.Add(new MySqlParameter("VarOrderId", GC_Invoice.OrderId));
com.Parameters.Add(new MySqlParameter("VarEmployeeID", GC_Invoice.EmployeeID));
com.Parameters.Add(new MySqlParameter("VarLineNumber", GC_Invoice.LineNumber));
com.Parameters.Add(new MySqlParameter("VarOrderDate", GC_Invoice.OrderDate));
com.Parameters.Add(new MySqlParameter("VarLocalCost", GC_Invoice.LocalCost));
com.Parameters.Add(new MySqlParameter("VarQuantity", GC_Invoice.Quantity));
if (GC_Invoice.ReasonofReturen == "0")
{
com.Parameters.Add(new MySqlParameter("VarReasonofReturen", "Not Available"));
}
else if (GC_Invoice.ReasonofReturen == "1")
{
com.Parameters.Add(new MySqlParameter("VarReasonofReturen", "By Customer"));
}
else
{
com.Parameters.Add(new MySqlParameter("VarReasonofReturen", "None"));
}
com.Parameters.Add(new MySqlParameter("VarCountry", GC_Invoice.Country));
com.Parameters.Add(new MySqlParameter("VarSKU", GC_Invoice.SKU));
//
con.Open();
com.ExecuteNonQuery();
con.Close();
}
protected void gvGCI_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
GiftCardsInvoiceDAL GCIDAL = new GiftCardsInvoiceDAL();
GCIDAL.ConnectionString = ConfigurationManager.ConnectionStrings["MySQLConn"].ToString();
Gift_Cards_Invoice com = new Gift_Cards_Invoice();
com.OrderId = (gvGCI.Rows[e.RowIndex].FindControl("lblOrderId") as Label).Text;
com.EmployeeID = (gvGCI.Rows[e.RowIndex].FindControl("lblEmployeeID") as Label).Text;
com.LineNumber = (gvGCI.Rows[e.RowIndex].FindControl("tbLineNumber") as Label).Text;
com.RewardName = (gvGCI.Rows[e.RowIndex].FindControl("lblRewardName") as Label).Text;
com.OrderDate = Convert.ToDateTime((gvGCI.Rows[e.RowIndex].FindControl("lblOrderDate") as Label).Text);
com.LocalCost = (gvGCI.Rows[e.RowIndex].FindControl("lblLocalCost") as Label).Text;
com.Quantity = Convert.ToInt32((gvGCI.Rows[e.RowIndex].FindControl("lblQuantity") as Label).Text);
//com.ReasonofReturen = gvGCI.DataKeys[e.RowIndex].Values[1].ToString();
com.ReasonofReturen = (gvGCI.Rows[e.RowIndex].FindControl("ddlReasonofReturen") as DropDownList).SelectedValue;
com.Country = gvGCI.DataKeys[e.RowIndex].Values["Country"].ToString();
com.SKU = gvGCI.DataKeys[e.RowIndex].Values["SKU"].ToString();
GCIDAL.InsertGiftCardsInvoice(com);
gvGCI.EditIndex = -1;
GetGiftCardsInvoice(searcho, searche, sdate, edate);
}
public List <Gift_Cards_Invoice> GetGiftCardsInvoice(string OrderId, string EmployeeID, DateTime StartDate, DateTime EndDate)
{
MySqlConnection con = new MySqlConnection(ConnectionString);
try
{
MySqlCommand com = new MySqlCommand("GetGiftCardsInvoice", con);
com.CommandType = System.Data.CommandType.StoredProcedure;
List <Gift_Cards_Invoice> GC_InvoiceList = new List <Gift_Cards_Invoice>();
com.Parameters.Add(new MySqlParameter("VarOrderId", OrderId));
com.Parameters.Add(new MySqlParameter("VarEMPID", EmployeeID));
//com.Parameters.Add(new MySqlParameter("VarQuantity", Quantity));
if (StartDate != DateTime.MinValue)
{
com.Parameters.Add(new MySqlParameter("VarStartDate", StartDate.Date));
}
else
{
com.Parameters.Add(new MySqlParameter("VarStartDate", null));
}
if (EndDate != DateTime.MinValue)
{
com.Parameters.Add(new MySqlParameter("VarEndDate", EndDate.Date));
}
else
{
com.Parameters.Add(new MySqlParameter("VarEndDate", null));
}
con.Open();
MySqlDataReader reader = com.ExecuteReader();
while (reader.Read())
{
Gift_Cards_Invoice GC_Invoice = new Gift_Cards_Invoice();
GC_Invoice.OrderId = reader["OrderID"].ToString();
GC_Invoice.EmployeeID = reader["EmployeeID"].ToString();
GC_Invoice.LineNumber = (reader["OrderID"].ToString()).Split('-')[0];
GC_Invoice.RewardName = reader["RewardName"].ToString();
GC_Invoice.Quantity = Convert.ToInt32(reader["Quantity"]);
DateTime?OrderDate = (reader["OrderDate"] == System.DBNull.Value) ? (DateTime?)null : Convert.ToDateTime(reader["OrderDate"]).Date;
GC_Invoice.OrderDate = OrderDate;
GC_Invoice.LocalCost = (reader["LocalCost"].ToString());
GC_Invoice.ReasonofReturen = reader["ReasonofReturen"].ToString();
GC_Invoice.USDCost = Convert.ToDouble(reader["USDCost"]);
GC_Invoice.Country = reader["Country"].ToString();;
GC_Invoice.SKU = reader["SKU"].ToString();;
GC_InvoiceList.Add(GC_Invoice);
}
con.Close();
return(GC_InvoiceList);
}
catch
{
con.Close();
return(null);
}
}