protected void btnDownloadInventoryReport_Click(object sender, EventArgs e)
{
DateTime dateTo;
DateTime dateFrom;
dateFrom = Convert.ToDateTime("1/1/1753");
if (txtToDate.Text != "")
{
dateTo = Convert.ToDateTime(txtToDate.Text);
}
else
{
dateTo = DateTime.Now;
}
//DataTable dtSO = Trans_History.RetrieveDataPURReport(oCon, dateFrom, dateTo);
//dvDateRange.Visible = false;
////using (XLWorkbook wb = new XLWorkbook())
////{
//// wb.Worksheets.Add(dtSO, "Inventory Report");
//// Response.Clear();
//// Response.Buffer = true;
//// Response.Charset = "";
//// Response.ContentType = "application/vnd.ms-excel";
//// Response.AddHeader("content-disposition", "attachment;filename=Inventory Report (PUR)" + DateTime.Now.ToString("MMMM") + " " + DateTime.Now.Year.ToString() + ".xlsx");
//// using (MemoryStream MyMemoryStream = new MemoryStream())
//// {
//// wb.SaveAs(MyMemoryStream);
//// MyMemoryStream.WriteTo(Response.OutputStream);
//// Response.Flush();
//// Response.End();
//// }
////}
//ExporttoExcel(dtSO);
string request_mode = Session["ReportType"].ToString();
Report_Request_Model report_Request_Model = new Report_Request_Model
{
Request_Type = request_mode,
DateFrom = dateFrom,
DateTo = dateTo,
SendToEmail = Session["User_Email"].ToString(),
isFinished = "N",
TimeStamp = DateTime.Now,
CreatedBy = Session["User_Domain"].ToString(),
};
bool result = Report_Request.Save(oCon, report_Request_Model);
if (result == true)
{
HttpContext.Current.Response.Write("<script>alert(" + ControlChars.Quote + "Success! Kindly wait for the report to be sent in your email. Thank you." + ControlChars.Quote + ");</script>");
}
else
{
HttpContext.Current.Response.Write("<script>alert(" + ControlChars.Quote + "There is an error in processing your report. Please contact helpdesk." + ControlChars.Quote + ");</script>");
}
}
public static bool Update(SqlConnection connection, Report_Request_Model model)
{
bool returnValue = true;
StringBuilder sQuery = new StringBuilder();
try
{
sQuery.Append(@"UPDATE a_Report_Request SET
Request_Type = @Request_Type
,DateFrom = @DateFrom
,DateTo = @DateTo
,SendToEmail = @SendToEmail
,isFinished = @isFinished
,TimeStamp = @TimeStamp
,CreatedBy = @CreatedBy
WHERE idReportRequest = @idReportRequest ");
connection.Open();
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = connection;
cmd.CommandText = sQuery.ToString();
cmd.CommandType = CommandType.Text;
SqlParameter parm1 = new SqlParameter
{
ParameterName = "@idReportRequest",
SqlDbType = SqlDbType.Int,
Value = model.idReportRequest
};
cmd.Parameters.Add(parm1);
SqlParameter parm2 = new SqlParameter
{
ParameterName = "@Request_Type",
SqlDbType = SqlDbType.NVarChar,
Value = model.Request_Type
};
cmd.Parameters.Add(parm2);
SqlParameter parm3 = new SqlParameter
{
ParameterName = "@DateFrom",
SqlDbType = SqlDbType.DateTime,
Value = model.DateFrom
};
cmd.Parameters.Add(parm3);
SqlParameter parm4 = new SqlParameter
{
ParameterName = "@DateTo",
SqlDbType = SqlDbType.DateTime,
Value = model.DateTo
};
cmd.Parameters.Add(parm4);
SqlParameter parm5 = new SqlParameter
{
ParameterName = "@SendToEmail",
SqlDbType = SqlDbType.NVarChar,
Value = model.SendToEmail
};
cmd.Parameters.Add(parm5);
SqlParameter parm6 = new SqlParameter
{
ParameterName = "@isFinished",
SqlDbType = SqlDbType.NVarChar,
Value = model.isFinished
};
cmd.Parameters.Add(parm6);
SqlParameter parm7 = new SqlParameter
{
ParameterName = "@TimeStamp",
SqlDbType = SqlDbType.DateTime,
Value = model.TimeStamp
};
cmd.Parameters.Add(parm7);
SqlParameter parm8 = new SqlParameter
{
ParameterName = "@CreatedBy",
SqlDbType = SqlDbType.NVarChar,
Value = model.CreatedBy
};
cmd.Parameters.Add(parm8);
if (cmd.ExecuteNonQuery() >= 1)
{
returnValue = true;
}
cmd.Dispose();
cmd.Parameters.Clear();
}
connection.Close();
}
catch (Exception ex)
{
throw new ArgumentException(ex.Message);
}
return(returnValue);
}
public static bool Save(SqlConnection connection, Report_Request_Model model)
{
bool returnValue = false;
StringBuilder sQuery = new StringBuilder();
sQuery.Append(@"INSERT INTO a_Report_Request
(Request_Type
,DateFrom
,DateTo
,SendToEmail
,isFinished
,TimeStamp
,CreatedBy
)
VALUES
(@Request_Type
,@DateFrom
,@DateTo
,@SendToEmail
,@isFinished
,@TimeStamp
,@CreatedBy
)
");
// SELECT SCOPE_IDENTITY() as 'ID'
var GUID = SQL_Transact.GenerateGUID();
SQL_Transact.BeginTransaction(connection, GUID);
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = connection;
cmd.CommandText = sQuery.ToString();
cmd.CommandType = CommandType.Text;
SqlParameter parm2 = new SqlParameter
{
ParameterName = "@Request_Type",
SqlDbType = SqlDbType.NVarChar,
Value = model.Request_Type
};
cmd.Parameters.Add(parm2);
SqlParameter parm3 = new SqlParameter
{
ParameterName = "@DateFrom",
SqlDbType = SqlDbType.DateTime,
Value = model.DateFrom
};
cmd.Parameters.Add(parm3);
SqlParameter parm4 = new SqlParameter
{
ParameterName = "@DateTo",
SqlDbType = SqlDbType.DateTime,
Value = model.DateTo
};
cmd.Parameters.Add(parm4);
SqlParameter parm5 = new SqlParameter
{
ParameterName = "@SendToEmail",
SqlDbType = SqlDbType.NVarChar,
Value = model.SendToEmail
};
cmd.Parameters.Add(parm5);
SqlParameter parm6 = new SqlParameter
{
ParameterName = "@isFinished",
SqlDbType = SqlDbType.NVarChar,
Value = model.isFinished
};
cmd.Parameters.Add(parm6);
SqlParameter parm7 = new SqlParameter
{
ParameterName = "@TimeStamp",
SqlDbType = SqlDbType.DateTime,
Value = model.TimeStamp
};
cmd.Parameters.Add(parm7);
SqlParameter parm8 = new SqlParameter
{
ParameterName = "@CreatedBy",
SqlDbType = SqlDbType.NVarChar,
Value = model.CreatedBy
};
cmd.Parameters.Add(parm8);
// var oreader = cmd.ExecuteReader();
try
{
if (cmd.ExecuteNonQuery() >= 1)
{
returnValue = true;
}
else
{
returnValue = false;
}
//while (oreader.Read())
//{
// returnValue = Convert.ToInt32(oreader["ID"].ToString());
//}
//oreader.Close();
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.CommitTransaction(connection, GUID);
}
catch
{
returnValue = false;
// oreader.Close();
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.RollbackTransaction(connection, GUID);
}
}
return(returnValue);
}
public static List <Report_Request_Model> RetrieveData(SqlConnection connection, string DateFrom, string Request_Type)
{
StringBuilder sQuery = new StringBuilder();
sQuery.Append(@"
SELECT idReportRequest
,Request_Type
,DateFrom
,DateTo
,SendToEmail
,isFinished
,TimeStamp
,CreatedBy
FROM a_Report_Request
WHERE idReportRequest <> 0
");
if (Request_Type != "")
{
sQuery.Append("AND Request_Type = @Request_Type");
}
if (DateFrom != "")
{
sQuery.Append("AND DateFrom = @DateFrom");
}
var lmodel = new List <Report_Request_Model>();
connection.Open();
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = connection;
cmd.CommandText = sQuery.ToString();
cmd.CommandType = CommandType.Text;
if (DateFrom != "")
{
SqlParameter parm2 = new SqlParameter
{
ParameterName = "@DateFrom",
SqlDbType = SqlDbType.NVarChar,
Value = DateFrom
};
cmd.Parameters.Add(parm2);
}
if (Request_Type != "")
{
SqlParameter parm2 = new SqlParameter
{
ParameterName = "@Request_Type",
SqlDbType = SqlDbType.NVarChar,
Value = Request_Type
};
cmd.Parameters.Add(parm2);
}
var oreader = cmd.ExecuteReader();
while (oreader.Read())
{
Report_Request_Model oModel = new Report_Request_Model
{
idReportRequest = (int)oreader["idReportRequest"],
Request_Type = (string)oreader["Request_Type"],
DateFrom = (DateTime)oreader["DateFrom"],
DateTo = (DateTime)oreader["DateTo"],
SendToEmail = (string)oreader["SendToEmail"],
isFinished = (string)oreader["isFinished"],
TimeStamp = (DateTime)oreader["TimeStamp"],
CreatedBy = ((int)oreader["CreatedBy"]).ToString()
};
lmodel.Add(oModel);
}
oreader.Close();
cmd.Dispose();
}
connection.Close();
return(lmodel);
}