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; }