public bool UpdateReconTracking(Recon_Tracking model)
{
try
{
using (SimplyPaymentCardDataContext db = new SimplyPaymentCardDataContext(ConnectionStringIntranet))
{
var trackingData = db.Recon_Trackings.Where(t => t.TrackingNo == model.TrackingNo);
if (trackingData.Count() > 0)
{
foreach (var record in trackingData)
{
record.AddressLine1 = model.AddressLine1.Truncate(60);
record.AddressLine2 = model.AddressLine2.Truncate(60);
record.AddressLine3 = model.AddressLine3.Truncate(60);
record.Subub = model.Subub.Truncate(30);
record.State = model.State.Truncate(3);
record.Postcode = model.Postcode.Truncate(4);
record.FileName = model.FileName;
}
db.SubmitChanges();
}
}
return(true);
}
catch (Exception ex)
{
logger.Info("failed to update tracking data. Tracking number:" + model.TrackingNo + ex.Message);
return(false);
}
}
public bool UpdateReconTracking2(Recon_Tracking model)
{
try
{
using (SimplyPaymentCardDataContext db = new SimplyPaymentCardDataContext(ConnectionStringIntranet))
{
var trackingData = db.Recon_Trackings.Where(t => t.TrackingNo == model.TrackingNo);
if (trackingData.Count() > 1)
{
var data = trackingData.Where(s => s.FileName == model.FileName);
if (data.Count() == 0)
{
var query = trackingData.Where(s => s.Flag == false);
if (query.Count() > 0)
{
query.First().FileName = model.FileName;
}
else
{
trackingData.First().FileName = model.FileName;
}
db.SubmitChanges();
}
}
}
return(true);
}
catch (Exception ex)
{
logger.Info("failed to update tracking data. Tracking number:" + model.TrackingNo + ex.Message);
return(false);
}
}
public bool InsertDRData(VarClass.JobType jobType)
{
try
{
using (SimplyPaymentCardDataContext db = new SimplyPaymentCardDataContext(ConnectionStringIntranet))
{
int DocType = db.Recon_DocTypes.SingleOrDefault(t => t.DT_DocType.Equals(VarClass.doctypeDescription)).DT_UID;
var files = jobType.Equals(VarClass.JobType.PAYMENT_CARDS) ? Directory.GetFiles(VarClass.downloadPath1, "*.CSV") : Directory.GetFiles(VarClass.downloadPath1, "*.txt");
foreach (string file in files)
{
Recon_DataReceived data = new Recon_DataReceived
{
DR_ClientID = 1,
DR_JobID = jobType.Equals(VarClass.JobType.PAYMENT_CARDS) ? Int32.Parse(getInputString(0, file)) : Int32.Parse(DateTime.Now.ToString("yyyyMMdd")),
DR_Datetime = DateTime.Now,
DR_TotalReceived = jobType.Equals(VarClass.JobType.PAYMENT_CARDS) ? Int32.Parse(getInputString(3, file)) : GetEntPackInfo().Item1,
DR_DocType = DocType,
DR_FileName = jobType.Equals(VarClass.JobType.PAYMENT_CARDS) ? getInputString(2, file) : GetEntPackInfo().Item2,
};
db.Recon_DataReceiveds.InsertOnSubmit(data);
db.SubmitChanges();
}
return(true);
}
}
catch (Exception ex)
{
logger.Info(string.Format("InsertDRData Failed. {0}", ex.Message.ToString()));
return(false);
}
}
public Recon_Tracking GetReconTracking(string fileName, ref bool expired)
{
try
{
var result = new Recon_Tracking();
using (SimplyPaymentCardDataContext db = new SimplyPaymentCardDataContext(ConnectionStringIntranet))
{
result = db.Recon_Trackings.Where(s => s.FileName.Equals(fileName)).OrderByDescending(o => o.DR_UID).FirstOrDefault();
if (result == null)
{
return(null);
}
var dataReceived = db.Recon_DataReceiveds.Where(dr => dr.DR_UID == result.DR_UID).SingleOrDefault();
if (dataReceived == null)
{
logger.Info(string.Format("Could not find record in [Recon_DataReceiveds]. DR_UID: {0}, Tracking Number: {1}", result.DR_UID, result.TrackingNo));
return(null);
}
DateTime expiryDate = dataReceived.DR_Datetime.Value.AddMonths(3);
if (expiryDate < DateTime.Now)
{
expired = true;
}
}
return(result);
}
catch (Exception ex)
{
logger.Info(string.Format("FileName:{0}. Error:{1}", fileName, ex.Message));
return(null);
}
}
public bool UpdateReconDP(ref HashSet <int> druidUpdateList)
{
try
{
using (SimplyPaymentCardDataContext db = new SimplyPaymentCardDataContext(ConnectionStringIntranet))
{
foreach (var druid in druidUpdateList)
{
// Update data in Recon_DataProcessed
var dataProcessed = db.Recon_DataProcesseds.Where(dp => dp.DR_IDs == druid.ToString()).SingleOrDefault();
if (dataProcessed == null)
{
logger.Info("DR_ID:" + druid + " not exist in Recon_DataProcessed");
return(false);
}
dataProcessed.DP_ArchSets = db.Recon_Trackings.Where(t => t.DR_UID == druid && t.Flag == true).Count();
}
db.SubmitChanges();
}
return(true);
}
catch (Exception ex)
{
logger.Info(MethodBase.GetCurrentMethod().Name + ex.Message);
return(false);
}
}
public bool InsertReconDataReceived(Recon_DataReceived input)
{
try
{
using (SimplyPaymentCardDataContext db = new SimplyPaymentCardDataContext(ConnectionStringIntranet))
{
//Check if the FileName already exists
var fileNameQuery = db.Recon_DataReceiveds.Where(f => f.DR_FileName.Equals(input.DR_FileName));
if (fileNameQuery.Count() >= 1)
{
logger.Info(MethodBase.GetCurrentMethod().Name + "DR_FileName already exists");
return(false);
}
db.Recon_DataReceiveds.InsertOnSubmit(input);
db.SubmitChanges();
return(true);
}
}
catch (Exception ex)
{
logger.Info(MethodBase.GetCurrentMethod().Name + ex.Message);
return(false);
}
}
public int GetDocTypeByDescription(string description)
{
bool isSuccess = false;
Recon_DocType query = new Recon_DocType();
try
{
using (SimplyPaymentCardDataContext db = new SimplyPaymentCardDataContext(ConnectionStringIntranet))
{
query = db.Recon_DocTypes.Where(t => t.DT_DocType.Equals(description)).SingleOrDefault();
if (query != null)
{
isSuccess = true;
}
}
}
catch (Exception ex)
{
logger.Info(MethodBase.GetCurrentMethod().Name + ex.Message);
}
if (isSuccess)
{
return(query.DT_UID);
}
else
{
return(-1);
}
}
public int GetDRUIDByFileName(string fileName)
{
bool isSuccess = false;
Recon_DataReceived query = new Recon_DataReceived();
try
{
using (SimplyPaymentCardDataContext db = new SimplyPaymentCardDataContext(ConnectionStringIntranet))
{
query = db.Recon_DataReceiveds.Where(vrr => vrr.DR_FileName.Equals(fileName)).SingleOrDefault();
if (query != null)
{
isSuccess = true;
}
}
}
catch (Exception ex)
{
logger.Info(MethodBase.GetCurrentMethod().Name + ex.Message);
}
if (isSuccess)
{
return(query.DR_UID);
}
else
{
return(-1);
}
}
public bool UpdateReconWithConfirmCSVOld(Recon_Tracking model)
{
try
{
using (SimplyPaymentCardDataContext db = new SimplyPaymentCardDataContext(ConnectionStringIntranet))
{
var trackingData = db.Recon_Trackings.Where(t => t.TrackingNo == model.TrackingNo);
if (trackingData.Count() == 0)
{
logger.Info("Tracking No " + model.TrackingNo + " not exist in Recon_Tracking");
return(false);
}
if (trackingData.Count() > 1)
{
logger.Info("Tracking No " + model.TrackingNo + " has more than 1 record in Recon_Tracking");
return(false);
}
//check if the record is received within 3 month
//If within 3 months, update the Recon_Tracking and Recon_DataProcessed table
int druid = trackingData.SingleOrDefault().DR_UID;
var dataReceived = db.Recon_DataReceiveds.Where(dr => dr.DR_UID == druid).SingleOrDefault();
if (dataReceived == null)
{
return(false);
}
DateTime expiryDate = dataReceived.DR_Datetime.Value.AddMonths(3);
if (expiryDate > DateTime.Now)
{
// Update data in Recon_Tracking
trackingData.SingleOrDefault().Flag = model.Flag;
// Update data in Recon_DataProcessed
int dpuid = dataReceived.DR_DPUID.Value;
var dataProcessed = db.Recon_DataProcesseds.Where(dp => dp.DP_UID == dpuid).SingleOrDefault();
if (dataProcessed == null)
{
logger.Info("Tracking Record " + model.TrackingNo + " not exist in Recon_DataProcessed");
return(false);
}
//If the result for the tracking number is true, then increase DP_ArchSets by 1
if (model.Flag == true)
{
dataProcessed.DP_ArchSets = dataProcessed.DP_ArchSets + 1;
}
db.SubmitChanges();
}
}
return(true);
}
catch (Exception ex)
{
logger.Info(MethodBase.GetCurrentMethod().Name + ex.Message);
return(false);
}
}
/// <summary>
/// Update DP_DateProcessed one record at a time
/// </summary>
/// <param name="reportPath"></param>
/// <returns></returns>
public bool UpdateSingleDPData(string reportPath, ref string reportStatus)
{
try
{
using (SimplyPaymentCardDataContext db = new SimplyPaymentCardDataContext(ConnectionStringIntranet))
{
var lines = File.ReadLines(reportPath).ToArray();
if (lines == null)
{
logger.Info("Fail to Update DP_DateProcessed. No record in Placard Report.");
}
for (int i = 1; i < lines.Count(); i++)
{
if (lines[i].Contains(","))
{
var lineSplit = lines[i].Split(',');
var desc = lineSplit[3];
var dateReceived = lineSplit[4];
int jobId = Int32.Parse(lineSplit[1].Split('_')[0]);
int subtotal = Int32.Parse(lineSplit[7]);
// Update DP_TotalProcessed in [DP_DateProcessed] for a specific day
string fileName = String.Format("{0}{1}.txt", VarClass.logFilePrefix, Convert.ToDateTime(dateReceived).ToString("yyyyMMdd"));
var query = from dp in db.Recon_DataProcesseds
where dp.DP_FileName == fileName && dp.DP_DateProcessed.Value.Date == Convert.ToDateTime(dateReceived).Date
select dp;
if (query.Count() > 1)
{
logger.Info("Fail to Update DP_DateProcessed. There is more than one record exist");
}
if (query.Count() < 1)
{
logger.Info("Fail to Update DP_DateProcessed. There is no record exist");
}
// DP_TotalProcessed = initial DP_TotalProcessed value + total processed in the report
int iniTotalProcessed = query.SingleOrDefault().DP_TotalProcessed.Value;
int totalProcessed = iniTotalProcessed + subtotal;
query.SingleOrDefault().DP_TotalProcessed = totalProcessed;
db.SubmitChanges();
reportStatus += string.Format("{0} {1}: {2} {3}", dateReceived, desc, subtotal, Environment.NewLine);
}
}
return(true);
}
}
catch (Exception ex)
{
logger.Info(string.Format("Fail to Update DP_DateProcessed. {0}", ex.Message.ToString()));
return(false);
}
}
public string GetJobNumber(DateTime month, int divisionId, int clientId)
{
string strJobNo = null;
try
{
using (SimplyPaymentCardDataContext objDb = new SimplyPaymentCardDataContext(ConnectionStringIntranet))
{
Recon_JobNumber objRecnJN = (from jobNo in objDb.Recon_JobNumbers where jobNo.JN_Month == month && jobNo.JU_ClientID == clientId && jobNo.JU_Division == divisionId select jobNo).SingleOrDefault();
if (objRecnJN != null)
{
strJobNo = objRecnJN.JN_JobNumber.ToString();
}
}
}
catch (Exception ex)
{
}
return(strJobNo);
}
public bool UpdateReconTracking(Recon_Tracking model, ref HashSet <int> druidUpdateList)
{
try
{
using (SimplyPaymentCardDataContext db = new SimplyPaymentCardDataContext(ConnectionStringIntranet))
{
var trackingData = db.Recon_Trackings.Where(t => t.TrackingNo == model.TrackingNo);
if (trackingData.Count() > 0)
{
foreach (var record in trackingData)
{
//check if the record is received within 3 month
//If within 3 months, update the Recon_Tracking and Recon_DataProcessed table
int druid = record.DR_UID;
var dataReceived = db.Recon_DataReceiveds.Where(dr => dr.DR_UID == druid).SingleOrDefault();
if (dataReceived == null)
{
return(false);
}
DateTime expiryDate = dataReceived.DR_Datetime.Value.AddMonths(3);
if (expiryDate > DateTime.Now)
{
record.Flag = model.Flag;
}
druidUpdateList.Add(record.DR_UID);
}
db.SubmitChanges();
}
}
return(true);
}
catch (Exception ex)
{
logger.Info(MethodBase.GetCurrentMethod().Name + ex.Message);
return(false);
}
}
public bool UpdateDRData(int dpUID)
{
try
{
using (SimplyPaymentCardDataContext db = new SimplyPaymentCardDataContext(ConnectionStringIntranet))
{
int DocType = db.Recon_DocTypes.SingleOrDefault(t => t.DT_DocType.Equals(VarClass.doctypeDescription)).DT_UID;
var query = from dr in db.Recon_DataReceiveds
where dr.DR_DocType == DocType && dr.DR_Datetime.Value.Date == DateTime.Now.Date && dr.DR_DPUID == null
select dr;
foreach (Recon_DataReceived dr in query)
{
dr.DR_DPUID = dpUID;
}
db.SubmitChanges();
}
}
catch (Exception ex)
{
logger.Info(string.Format("UpdateDRData Failed. {0}", ex.Message.ToString()));
return(false);
}
return(true);
}
//public bool InsertReconDataReceived(DBModels.ReconDataReceivedModel model)
//{
// try
// {
// using (SimplyPaymentCardDataContext db = new SimplyPaymentCardDataContext(ConnectionString))
// {
// //Check if the FileName already exists
// var fileNameQuery = db.Recon_DataReceiveds.Where(f => f.DR_FileName.Equals(model.DR_FileName));
// if (fileNameQuery.Count() >= 1)
// {
// logger.Info(MethodBase.GetCurrentMethod().Name + "DR_FileName already exists");
// return false;
// }
// var query = db.Recon_DocTypes.SingleOrDefault(t => t.DT_DocType.Equals(model.DocTypeDesc));
// if (query == null) return false;
// int DocType = query.DT_UID;
// Recon_DataReceived data = new Recon_DataReceived
// {
// DR_ClientID = model.DR_ClientID,
// DR_JobID = model.DR_JobID,
// DR_Datetime = DateTime.Now,
// DR_TotalReceived = model.DR_TotalReceived,
// DR_DocType = DocType,
// DR_FileName = model.DR_FileName,
// };
// db.Recon_DataReceiveds.InsertOnSubmit(data);
// db.SubmitChanges();
// return true;
// }
// }
// catch (Exception ex)
// {
// logger.Info(string.Format("Fail to Insert data to ReconDataReceived. {0}", ex.Message.ToString()));
// return false;
// }
//}
public bool UpdateReconDataReceived(int dpUID, int docType)
{
try
{
using (SimplyPaymentCardDataContext db = new SimplyPaymentCardDataContext(ConnectionStringIntranet))
{
var query = from dr in db.Recon_DataReceiveds
where dr.DR_DocType == docType && dr.DR_Datetime.Value.Date == DateTime.Now.Date && dr.DR_DPUID == null
select dr;
foreach (Recon_DataReceived dr in query)
{
dr.DR_DPUID = dpUID;
}
db.SubmitChanges();
}
}
catch (Exception ex)
{
logger.Info(string.Format("Fail to Update ReconDataReceived. {0}", ex.Message.ToString()));
return(false);
}
return(true);
}
public bool InsertDPData()
{
bool isSuccess = true;
try
{
string[] lines = File.ReadAllLines(VarClass.logFile);
var targetLine = lines[2].ToString();
var archSets = targetLine.Split(new[] { "PAGES" }, StringSplitOptions.None)[0].Split('=')[2].Trim();
using (SimplyPaymentCardDataContext db = new SimplyPaymentCardDataContext(ConnectionStringIntranet))
{
int DocType = db.Recon_DocTypes.SingleOrDefault(t => t.DT_DocType.Equals(VarClass.doctypeDescription)).DT_UID;
int?Division = db.Recon_Divisions.SingleOrDefault(c => c.D_Comment.Equals(VarClass.doctypeDescription)).D_Division;
// Query Recon_DataProcessed to get DR_UID based on DocType and DateTime
var drUIDs = (from dr in db.Recon_DataReceiveds
where dr.DR_DocType == DocType && dr.DR_Datetime.Value.Date == DateTime.Now.Date
select dr.DR_UID).ToArray();
// Insert query results and log file data to Recon_DataProcessed
if (drUIDs == null || drUIDs.Length == 0)
{
logger.Info("Failed to Insert DPData. Could not find related DR_IDs in Recon_DataReceived.");
}
Recon_DataProcessed data = new Recon_DataProcessed
{
DP_FileName = VarClass.logFileName,
DP_DateProcessed = DateTime.Now,
DP_TotalProcessed = 0,
DP_MailedSets = 0,
DP_EmailedSets = 0,
DP_MailedPages = 0,
DP_MailedImages = 0,
DP_ArchSets = Int32.Parse(archSets),
DP_Reconcilled = 0,
DP_ReturnedSets = 0,
DP_MailedRecords = 0,
DP_MailedSetsC4 = 0,
DP_Division = Division,
DR_IDs = string.Join(",", drUIDs), //TODO: Not testing for null on linq select
DP_BUNCarryOverRecords = 0,
DP_EmailPages = 0,
DP_EmailedImages = 0,
DP_EmailedSets2 = 0,
DP_Ignored = 0,
DP_ArchiveOnly = 0,
DP_EmailedRecords = 0,
DP_ReturnedRecords = 0,
DP_PrioritySets = 0,
};
db.Recon_DataProcesseds.InsertOnSubmit(data);
db.SubmitChanges();
VarClass.dpArchSets = Int32.Parse(archSets);
// Update Recon_DataReceived with returned DP_UID
if (!UpdateDRData(data.DP_UID))
{
isSuccess = false;
}
}
}
catch (Exception ex)
{
logger.Info(string.Format("InsertDPData Failed. {0}", ex.Message.ToString()));
isSuccess = false;
}
return(isSuccess);
}
