//public void ReportThread(string result, string strRptFolder)
public void ReportThread(Object objCode)
{
try
{
string strCode = (string)objCode;
string[] code = strCode.Split(';');
string strRepCode = code[0];
string strUserName = code[1];
string strFromDate = code[2];
string strToDate = code[3];
string strRptFolder = ConfigurationManager.AppSettings["ReportFolder"].ToString();
//Insert Report log
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["BO"].ConnectionString);
conn.Open();
SqlCommand cmd = new SqlCommand("InsertReport_Log", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@p_ReportCode", SqlDbType.VarChar, 50).Value = strRepCode;
cmd.Parameters.Add("@p_UserName", SqlDbType.VarChar, 50).Value = strUserName;
cmd.Parameters.Add("@p_FileLocation", SqlDbType.VarChar, 50).Value = strRptFolder;
if (strFromDate != "" && strToDate != "")
{
cmd.Parameters.Add("@p_Duration", SqlDbType.VarChar, 50).Value = strFromDate + " to " + strToDate;
}
else
{
if (strFromDate != "")
{
cmd.Parameters.Add("@p_Duration", SqlDbType.VarChar, 50).Value = strFromDate;
}
else
{
cmd.Parameters.Add("@p_Duration", SqlDbType.VarChar, 50).Value = "";
}
}
string result = (System.String)cmd.ExecuteScalar();
string[] parts = result.Split(';');
string strQuery = parts[0];
string strOriFName = parts[1];
string strGenFileName = strRptFolder + parts[1] + ".csv";
string strReportLogID = parts[2];
for (int i = 2; i <= code.Length - 1; i++)
{
strQuery = strQuery.Replace("''", "'").Replace("@Param" + (i - 1).ToString(), code[i]);
}
//strQuery = strQuery.Replace("''", "'").Replace("@Param1",strFromDate).Replace("@Param2",strToDate).Replace("@dq","''");
DatabaseInfo rp = new DatabaseInfo();
DbUser objDB = rp.dbUser;
DataTable dt = new DataTable();
dt = objDB.ExecuteSQLReturnDataTable(strQuery);
if (dt != null)
{
if (dt.Rows.Count > 0)
{
string dilimiter = ConfigurationManager.AppSettings["Delimiter"].ToString();
CreateExcelFile.CreateCSVFile(dt, strGenFileName, dilimiter);
//cmd = new SqlCommand(strProcedure, conn);
//cmd.Parameters.Add("@p_DocTypeID", SqlDbType.VarChar, 20).Value = strRepCode;
//cmd.Parameters.Add("@p_FromDate", SqlDbType.VarChar, 50).Value = strFromDate;
//cmd.Parameters.Add("@p_ToDate", SqlDbType.VarChar, 50).Value = strToDate;
//cmd.CommandType = CommandType.StoredProcedure;
//SqlDataAdapter adp = new SqlDataAdapter(cmd);
//DataTable dt = new DataTable();
//adp.Fill(dt);
//DataSet ds = new DataSet("table");
//ds.Tables.Add(dt);
//Minh removes for testing
// CreateExcelFile.CreateExcelDocument(dt, strGenFileName);
//update status of file in Database
//Minh remove for testing
FileInfo f = new FileInfo(strGenFileName);
if (f.Length > 0)
{
cmd = new SqlCommand("UpdateREPORT_LOG", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@p_ReportLogID", SqlDbType.VarChar, 20));
cmd.Parameters["@p_ReportLogID"].Value = strReportLogID;
cmd.Parameters.Add(new SqlParameter("@p_Status", SqlDbType.VarChar, 20));
cmd.Parameters["@p_Status"].Value = "Completed";
cmd.Parameters.Add(new SqlParameter("@p_FileName", SqlDbType.VarChar, 100));
cmd.Parameters["@p_FileName"].Value = strOriFName;
cmd.ExecuteNonQuery();
}
}
else
{
cmd = new SqlCommand("UpdateREPORT_LOG", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@p_ReportLogID", SqlDbType.VarChar, 20));
cmd.Parameters["@p_ReportLogID"].Value = strReportLogID;
cmd.Parameters.Add(new SqlParameter("@p_Status", SqlDbType.VarChar, 100));
cmd.Parameters["@p_Status"].Value = "Completed with no rows";
cmd.Parameters.Add(new SqlParameter("@p_FileName", SqlDbType.VarChar, 100));
cmd.Parameters["@p_FileName"].Value = strOriFName;
cmd.ExecuteNonQuery();
}
}
else
{
cmd = new SqlCommand("UpdateREPORT_LOG", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@p_ReportLogID", SqlDbType.VarChar, 20));
cmd.Parameters["@p_ReportLogID"].Value = strReportLogID;
cmd.Parameters.Add(new SqlParameter("@p_Status", SqlDbType.VarChar, 100));
cmd.Parameters["@p_Status"].Value = "Failed";
cmd.Parameters.Add(new SqlParameter("@p_FileName", SqlDbType.VarChar, 100));
cmd.Parameters["@p_FileName"].Value = strOriFName;
cmd.ExecuteNonQuery();
}
conn.Close();
}
catch (Exception ex)
{
log.Info("Error: ", ex);
}
}