public bool ValidateConnection(XlsFileConnection conn, string fileNamePath, WaitDialogForm wait)
{
if (conn.Open() == null)
{
if (wait != null) wait.Close();
if (conn.ConnectionError == XlsFileErrorCode.NOT_EXCEL_FORMATED)
{
HelpMsgBox.ShowNotificationMessage("Tập tin \"" + fileNamePath + "\" không phải định dạng excel!");
}
else if (conn.ConnectionError == XlsFileErrorCode.PASSWORD_PROTECTED)
{
HelpMsgBox.ShowNotificationMessage("Tập tin \"" + new FileInfo(fileNamePath).Name + "\" đã được bảo vệ bằng mật khẩu!");
}
else
{
HelpMsgBox.ShowNotificationMessage("Truy cập vào tập tin \"" + fileNamePath + "\" không thành công!");
}
return false;
}
return true;
}
private TNSSaveResult ReadContent()
{
var result = TNSSaveResult.VALID_EXCEL;
try
{
var f = new XlsFileConnection(_filePath, _excelVersion);
f.Open();
var ds = f.LoadDataSet(_sheeName, null, false);
f.Close();
if (ds == null || ds.Tables.Count == 0)
result = TNSSaveResult.INVALID_EXCEL;
else
{
var dt = ds.Tables[0];
dt.DefaultView.RowFilter = string.Format("{0} is not null and {0} <> ''",
TNS_SALE_REV_SCR_CT.KENH_PHAT_NAME);
var dtKenhPhat = dt.DefaultView.ToTable(true, TNS_SALE_REV_SCR_CT.KENH_PHAT_NAME);
var channelNames = (from DataRow rKp in dtKenhPhat.Rows select rKp[0].ToString()).ToList();
_notExistsChannel = TNSHelper.GetNotExistsChannel(channelNames);
if (_notExistsChannel != "")
{
return TNSSaveResult.NOT_EXIST_CHANNEL;
}
#region danh mục
var allRowsInFile = dt.AsEnumerable();
FWDBService db = HelpDB.getDBService();
var dbTrans = FWTransaction.BeginTrans(db);
FWTransaction fwTrans = FWTransaction.I(db, dbTrans);
try
{
foreach (var dmObj in _dmObjs)
{
Application.DoEvents();
var colName = dmObj.ColNameInFile;
if (dt.Columns.Contains(dmObj.ColIDInFile))
{
dt.Columns.Remove(dmObj.ColIDInFile);
}
dt.Columns.Add(dmObj.ColIDInFile, typeof (Int64));
var dsDmFromDb = AppUtil.GetDataset(fwTrans, dmObj.TableName);
var dtDmFromDb = dsDmFromDb.Tables[0];
var namesFromDb = dtDmFromDb.AsEnumerable().Select(r => r["NAME"].ToString());
var namesFromFile =
allRowsInFile.Select(r => r[colName].ToString()).Where(
a => a != "" && !a.StartsWith("..") && a != "-").Distinct(
StringComparer.CurrentCultureIgnoreCase);
var namesNew = namesFromFile.Except(namesFromDb,
StringComparer.CurrentCultureIgnoreCase).ToList();
var count = namesNew.Count;
if (count > 0)
{
var dtForSave = dtDmFromDb.Clone();
dtForSave.Columns["VISIBLE_BIT"].DefaultValue = "Y";
if (dmObj.TableName == DMDaiTruyenHinh.TABLE_MAP)
{
dtForSave.Columns["IS_DID"].DefaultValue = "N";
}
var id = fwTrans.GetNewID(AppConst.G_FW_DM_ID);
var maxId = id + count;
AppUtil.SetValueGenerator(fwTrans, AppConst.G_FW_DM_ID, maxId);
for (int i = 0; i < count; i++)
{
dtForSave.Rows.Add(id, namesNew[i]);
id++;
}
var dsDmForSave = new DataSet();
dsDmForSave.Tables.Add(dtForSave);
if (!db.UpdateDataSet(dsDmForSave, dbTrans))
{
FWTransaction.Rollback(dbTrans);
return TNSSaveResult.INVALID_EXCEL;
}
dtDmFromDb.Merge(dtForSave, false, MissingSchemaAction.Ignore);
}
dmObj.DataSet = dsDmFromDb;
UpdateDatasetForComboxPivotField(dmObj);
var colname = dmObj.ColNameInFile;
var rowsInDb = dtDmFromDb.AsEnumerable();
foreach (var name in namesFromFile.ToList())
{
var nameLower = name.ToLower();
Application.DoEvents();
var rid = rowsInDb.FirstOrDefault(r => r["NAME"].ToString().ToLower() == nameLower);
if (rid != null&&rid["ID"].ToString()!="")
{
var rowsInFile =
allRowsInFile.Where(r => r[colname].ToString().ToLower() == nameLower);
rowsInFile.ToList().ForEach(r => r.SetField(dmObj.ColIDInFile, rid["ID"]));
}
}
}
FWTransaction.Commit(dbTrans);
}
catch (Exception)
{
FWTransaction.Rollback(dbTrans);
return TNSSaveResult.VALID_EXCEL;
}
//TODO: begin refactor here, check why it's slow
#endregion
dt.Columns.Add(TNS_SALE_REV_SCR_CT.TIME_START);
dt.Columns.Add(TNS_SALE_REV_SCR_CT.TIME_END);
dt.Columns.Add(TNS_SALE_REV_SCR_CT.MINS_START, typeof(decimal));
dt.Columns.Add(TNS_SALE_REV_SCR_CT.MINS_END, typeof(decimal));
object preChannel = DBNull.Value;
var preTimeband = "";
TNSTimeLine preTimes = null;
object preAdvertiser = DBNull.Value;
foreach (DataRow row in dt.Rows)
{
Application.DoEvents();
var channel = row[TNS_SALE_REV_SCR_CT.KENH_PHAT];
if (channel is DBNull)
{
var timeband = row[TNS_SALE_REV_SCR_CT.TIME_BAND].ToString();
if (timeband == "")
{
row[TNS_SALE_REV_SCR_CT.TIME_BAND] = preTimeband;
var advertiser = row[TNS_SALE_REV_SCR_CT.ADVERTISER];
if (advertiser is DBNull)
{
row[TNS_SALE_REV_SCR_CT.ADVERTISER] = preAdvertiser;
}
else
{
preAdvertiser = advertiser;
}
}
else
{
preTimeband = timeband;
preTimes = TNSTimeLine.Parse(preTimeband);
if (preTimes == null || preTimes.End.TotalMinute - preTimes.Start.TotalMinute != 60)
{
return TNSSaveResult.INVALID_TIMEBAND;
}
preAdvertiser = "";
}
if (preTimes != null)
{
row[TNS_SALE_REV_SCR_CT.TIME_START] = preTimes.Start.TimeText;
row[TNS_SALE_REV_SCR_CT.MINS_START] = preTimes.Start.TotalMinute;
row[TNS_SALE_REV_SCR_CT.TIME_END] = preTimes.End.TimeText;
row[TNS_SALE_REV_SCR_CT.MINS_END] = preTimes.End.TotalMinute;
}
row[TNS_SALE_REV_SCR_CT.KENH_PHAT] = preChannel;
}
else
{
preChannel = channel;
preTimeband = "";
preTimes = null;
}
}
dt.DefaultView.RowFilter = string.Format("{0} is not null and {0} <> ''",
TNS_SALE_REV_SCR_CT.TIME_BAND);
DataSet dsSource = DATNSSaleRevScr.I.LoadDetailDataSet(-1);
DataTable dtSource = dsSource.Tables[0];
_savedDatatables = new List<DataTable>();
foreach (DateTime? date in _listDates)
{
Application.DoEvents();
if (date == null) continue;
var subfix = TNSHelper.GetSubFix(date);
var newDt = dt.DefaultView.ToTable(TNS_SALE_REV_SCR_CT.TABLE_NAME, false,
TNS_SALE_REV_SCR_CT.KENH_PHAT,
TNS_SALE_REV_SCR_CT.ADVERTISER,
TNS_SALE_REV_SCR_CT.BRAND,
TNS_SALE_REV_SCR_CT.SECTOR,
TNS_SALE_REV_SCR_CT.GROUP,
TNS_SALE_REV_SCR_CT.AGENCY,
TNS_SALE_REV_SCR_CT.TIME_START,
TNS_SALE_REV_SCR_CT.TIME_END,
TNS_SALE_REV_SCR_CT.MINS_START,
TNS_SALE_REV_SCR_CT.MINS_END,
TNS_SALE_REV_SCR_CT.TIME_BAND,
TNS_SALE_REV_SCR_CT.ALTERNATIVE_COST + subfix,
TNS_SALE_REV_SCR_CT.GRP + subfix,
TNS_SALE_REV_SCR_CT.DISCOUNT + subfix,
TNS_SALE_REV_SCR_CT.CPP + subfix,
TNS_SALE_REV_SCR_CT.GRP_PERCENT + subfix);
TNSHelper.RenameColumn(newDt, subfix,
TNS_SALE_REV_SCR_CT.ALTERNATIVE_COST,
TNS_SALE_REV_SCR_CT.GRP,
TNS_SALE_REV_SCR_CT.DISCOUNT,
TNS_SALE_REV_SCR_CT.CPP,
TNS_SALE_REV_SCR_CT.GRP_PERCENT);
TNSHelper.AddDefaultColumn(newDt, TNS_SALE_REV_SCR_CT.NGAY_PHAT, typeof(DateTime), date.Value);
_savedDatatables.Add(newDt);
dtSource.Merge(newDt, true, MissingSchemaAction.Ignore);
}
_detailRowCount = dtSource.Rows.Count;
pivotGridControl1.DataSource = dtSource;
return result;
}
}
catch (ArgumentException)
{
result = TNSSaveResult.INVALID_DATA_FORMAT;
}
catch (Exception)
{
result = TNSSaveResult.INVALID_EXCEL;
}
return result;
}