User-1104215994 posted
Here is how I did it;
class Program
{
static readonly log4net.ILog log =
log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
static void Main(string[] args)
{
try
{
//Declarations
DateTime runday = DateTime.Today;
string status = null;
string myFileName = String.Format("{0}__{1}", DateTime.Now.ToString("ddMMyyyy"), "RegionalReconFile.txt");
//DEV
string myFullPath = Path.Combine("T:\\ReconLogFiles\\", myFileName);
if (args.Length > 0)
{
Console.WriteLine(args[0].ToString());
runday = DateTime.Parse(args[0].ToString());
}
using (var conn = new SqlConnection())
{
// Create the connectionString
//DEV
conn.ConnectionString =
"Server=(localdb)\\MSSQLLocalDB; Initial Catalog=TestAPI; Integrated Security=True; MultipleActiveResultSets=True; Trusted_Connection=yes;";
conn.Open();
var command = new SqlCommand(
"SELECT cf.referenceid, ISNULL(cc.shopno,gr.shopNo) AS shopno, ISNULL(cc.safeno,gr.safeNo) AS safeno,ISNULL(cc.cashierno, gr.cashierNo) AS cashierno, cf.purchaseStatusDate, cc.confirmcanceldatetime, cf.productcode, cf.productdescription, cf.quantity, cf.unitprice, cf.totalprice,ISNULL(cc.status, 0) As status, substring(ISNULL([cc].[shopNo], [gr].[shopNo]), 3, 2) as shop FROM[gameconfirmresponses] cf LEFT JOIN(SELECT *, Row_number() OVER(partition BY referenceid ORDER BY confirmcanceldatetime) AS row_num FROM[confirmcancels]) AS cc ON cf.referenceid = cc.referenceid AND cc.row_num = 1 JOIN[GameRequests] AS gr ON gr.referenceId = cf.referenceId WHERE cf.purchasestatusdate >= @Today AND cf.purchasestatusdate < DATEADD(day,1,@Today) order by substring(ISNULL([cc].[shopNo], [gr].[shopNo]),3,2) asc", conn);
// Add the parameters.
command.Parameters.Add(new SqlParameter("Today", runday));
var shop = "";
var total = 0.0;
var count = 0;
var grandTotal = 0.0;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine(
$"{reader[0]} \t | {reader[1]} \t | {reader[2]} \t | {reader[3]} \t | {reader[4]} \t | {reader[5]} \t | {reader[6]} \t | {reader[7]} \t | {reader[8]} \t | {reader[9]} \t | {reader[10]} \t | {reader[11]} \t | {reader[12]}");
if (reader.GetInt32(11) == 0)
{
status = "Uncertain";
}
else if (reader.GetInt32(11) == 1)
{
status = "Confirm";
}
else
{
status = "Cancel";
}
log.Info(
$"{reader[0]};{reader[1]};{reader[2]};{reader[3]};{reader[4]};{reader[5]};{reader[6]};{reader[7]};{reader[8]};{reader[9]};{reader[10]}" +
";" + status);
// Append text to an existing file.
using (StreamWriter outputFile = new StreamWriter(myFullPath, true))
{
if (shop != reader.GetString(12))
{
shop = reader.GetString(12);
if (count > 0 )
{
outputFile.WriteLine(
$"Subtotal: " + total);
grandTotal += total;
total = 0.0;
outputFile.WriteLine(
$"{reader[0]};{reader[1]};{reader[2]};{reader[3]};{reader[4]};{reader[5]};{reader[6]};{reader[7]};{reader[8]};{reader[9]};{reader[10]}" +
";" + status);
}
else
{
outputFile.WriteLine(
$"{reader[0]};{reader[1]};{reader[2]};{reader[3]};{reader[4]};{reader[5]};{reader[6]};{reader[7]};{reader[8]};{reader[9]};{reader[10]}" +
";" + status);
}
}
else
{
outputFile.WriteLine(
$"{reader[0]};{reader[1]};{reader[2]};{reader[3]};{reader[4]};{reader[5]};{reader[6]};{reader[7]};{reader[8]};{reader[9]};{reader[10]}" +
";" + status);
}
}
//Sum
total = total + reader.GetDouble(10);
count++;
Console.WriteLine("Done! Press enter to move to the next step");
}
//Last subtotal and grand total
using (StreamWriter sw = File.AppendText(myFullPath))
{
sw.WriteLine($"Subtotal: " + total);
grandTotal += total;
sw.WriteLine($"Grand total: " + grandTotal);
}
//No Purchase - empty file
if (!reader.HasRows)
{
using (StreamWriter outputFile = new StreamWriter(myFullPath, false))
{
}
}
}
Console.WriteLine("Data displayed! Now press enter to move to the next section!");
}
}
catch (Exception ex)
{
log.Error("Error Message: " + ex.Message.ToString(), ex);
}
}
}
