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;
}
private void btnImportCode_Click(object sender, EventArgs e)
{
WaitDialogForm wait = null;
try
{
OpenFileDialog oP = new OpenFileDialog();
oP.InitialDirectory = "My Documents://";
oP.Filter = HelpExcel.FILTER_FILE_OPEN;
oP.Title = HelpApplication.getTitleForm("Chọn bảng giá kế toán");
if (oP.ShowDialog() == DialogResult.Cancel) return;
System.Windows.Forms.Application.DoEvents();
string filenamepath = oP.FileName;
wait = new WaitDialogForm("Đang xử lý..", "Import code chương trình từ excel!", new Size(250, 50));
string tempFilePath = AppUtil.GetTempFile(Path.GetExtension(filenamepath));
File.Copy(filenamepath, tempFilePath, true);
FileInfo inf = new FileInfo(tempFilePath);
if (inf.IsReadOnly)
{
inf.IsReadOnly = false;
}
wait.SetCaption("Đang kết nối tập tin excel...");
XlsFileConnection conn = new XlsFileConnection(tempFilePath);
if (ValidateConnection(conn, filenamepath, wait) == false)
{
if (wait != null) wait.Close();
return;
}
DataSet ds = new DataSet();
ds = conn.LoadDataSet(CHUONG_TRINH.TABLE_NAME, null);
if (ds == null || ds.Tables.Count == 0)
{
wait.Close();
HelpMsgBox.ShowNotificationMessage("Tập tin excel có cấu trúc không đúng mẫu, vui lòng kiểm tra lại");
return;
}
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)//Loại bỏ dòng rỗng
{
ds.Tables[0].Rows.RemoveAt(0);
}
conn.Close();
DataTable dt = ds.Tables[0];
dt.Columns.Add(CHUONG_TRINH.CT_ID, typeof(Int64));
dt.Columns.Add("CODE_CT_OLD");
DataRow r = null;
string sql = "select ct.*,ct.code_ct code_ct_old from "+ CHUONG_TRINH.TABLE_NAME + " ct where ct." + CHUONG_TRINH.MA_CT + "='{0}'";
DataSet dsSource = HelpDB.getDBService().LoadDataSet(string.Format(sql, "-1"),CHUONG_TRINH.TABLE_NAME);
DataTable dtSource = dsSource.Tables[0];
DataSet dsTemp = null;
string error = "";
for (int i = 0; i < dt.Rows.Count; i++)
{
r = dt.Rows[i];
dsTemp = HelpDB.getDBService().LoadDataSet(string.Format(sql, r[CHUONG_TRINH.MA_CT]));
if (dsTemp != null && dsTemp.Tables.Count > 0 && dsTemp.Tables[0].Rows.Count > 0)
{
dsTemp.Tables[0].Rows[0][CHUONG_TRINH.CODE_CT] = r[CHUONG_TRINH.CODE_CT];
dtSource.ImportRow(dsTemp.Tables[0].Rows[0]);
}
else
{
error += "";
}
}
gridControlImport.DataSource = dtSource;
;
}
catch (Exception ex)
{
if (wait != null) wait.Close();
HelpMsgBox.ShowNotificationMessage("Tập tin excel có cấu trúc không đúng mẫu, vui lòng kiểm tra lại.");
PLException.AddException(ex);
return;
}
finally
{
if (wait != null) wait.Close();
}
}
private void CreateExcel()
{
FWDBService db = HelpDB.getDBService();
DbCommand cmd = db.GetStoredProcCommand("SP_LPS_KE_TOAN");
db.AddInParameter(cmd, "@I_TU_NGAY", DbType.Date, startDate);
db.AddInParameter(cmd, "@I_DEN_NGAY", DbType.Date, endDate);
db.AddInParameter(cmd, "@I_KENH_PHAT", DbType.Int64, KenhPhat._getSelectedID());
DataSet ds = db.LoadDataSet(cmd);
if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
{
HelpMsgBox.ShowNotificationMessage("Không có chương trình phát sóng trênh kênh " + KenhPhat.Text + " trong khoảng thời gian này!");
return;
}
string filenamepath = BangGiaKeToan.Text;
DataTable dtGia = null;
if (filenamepath != "")
{
string tempFilePath = AppUtil.GetTempFile(Path.GetExtension(filenamepath));
File.Copy(filenamepath, tempFilePath, true);
FileInfo inf = new FileInfo(tempFilePath);
if (inf.IsReadOnly)
{
inf.IsReadOnly = false;
}
XlsFileConnection conn = new XlsFileConnection(tempFilePath);
if (ValidateConnection(conn, filenamepath, null) == false)
{
return;
}
DataSet dsBG = new DataSet();
dsBG = conn.LoadDataSet(AppConst.BANG_GIA_KE_TOAN_NAME, null);
if (dsBG == null || dsBG.Tables.Count == 0)
{
HelpMsgBox.ShowNotificationMessage("Tập tin excel có cấu trúc không đúng mẫu, vui lòng kiểm tra lại");
return;
}
if (dsBG.Tables.Count > 0 && dsBG.Tables[0].Rows.Count > 0)//Loại bỏ dòng rỗng
{
dsBG.Tables[0].Rows.RemoveAt(0);
}
conn.Close();
System.Windows.Forms.Application.DoEvents();
dtGia = dsBG.Tables[0];
}
DataTable dt = ds.Tables[0];
DataRow[] rows = null;
if (dtGia != null)
{
decimal giatien = -1;
foreach (DataRow r in dt.Rows)
{
if (r["CODE_CT"].ToString().Length > 0)
{
giatien = -1;
if (r["RUN"] is DBNull)
{
rows = dtGia.Select("CODE_CT='" + r["CODE_CT"] + "'");
}
else
{
rows = dtGia.Select("CODE_CT='" + r["CODE_CT"] + "' and RUN='" + r["RUN"] + "'");
}
if (rows.Length > 0&&rows[0]["GIA_TIEN"].ToString() != "")
{
giatien = HelpNumber.ParseDecimal(rows[0]["GIA_TIEN"]);
}
if (giatien > -1)
{
r["GIA_TIEN"] = giatien.ToString();
}
}
}
}
FWDBService dbc = HelpDB.getDBService();
DbCommand cmdc = dbc.GetStoredProcCommand("SP_LPS_KE_TOAN_COLS");
DataSet dsCols = dbc.LoadDataSet(cmdc);
DataTable dtCols = dsCols.Tables[0];
dtCols.Columns.Add("GIA_TIEN");
DataRow rCols = dtCols.Rows[0];
rCols["GIA_TIEN"] = "Giá tiền";
DataRow rAlign = dtCols.Rows[1];
rAlign["GIA_TIEN"] = "C";
Excel.ApplicationClass excelApp = new Excel.ApplicationClass();
Excel.Workbook excelWorkbook = excelApp.Workbooks.Add(Type.Missing);
Excel.Worksheet excelSheet = null;
List<Excel.Worksheet> lSheet = new List<Excel.Worksheet>();
int startR = 1;
int startC = 1;
int curR = 0;
int curC = 0;
Excel.Range startCell = null;
Excel.Range endCell = null;
Excel.Range range = null;
DataTable dtdays = dt.DefaultView.ToTable(true, "NGAY_PHAT");
DateTime date = new DateTime();
//DataRow[] rows = null;
Color bdc = Color.FromArgb(178, 178, 178);
int i = 1;
string align = "";
foreach (DataRow rd in dtdays.Rows)
{
date = (DateTime)rd["NGAY_PHAT"];
excelSheet = (Excel.Worksheet)excelWorkbook.Sheets.Add(
excelWorkbook.Sheets.get_Item(1),
Type.Missing, 1, Excel.XlSheetType.xlWorksheet);
excelSheet.Name = AppCtrl.GetThuTrongTuanTitle(date.DayOfWeek) + ", " + date.ToString("dd-MM");
try
{
excelSheet.Cells.Font.Name = "Times New Roman";
}
catch
{
}
curR = startR;
curC = startC;
startCell = (Excel.Range)excelSheet.Cells[curR, curC];
endCell = (Excel.Range)excelSheet.Cells[curR, curC + dtCols.Columns.Count - 2];
range = excelSheet.get_Range(startCell, endCell);
range.Merge(Type.Missing);
range.set_Value(Type.Missing, "CHƯƠNG TRÌNH PHÁT SÓNG TRÊN KÊNH " + KenhPhat.Text.ToUpper() + " (KÊNH GIẢI TRÍ TỔNG HỢP)");
range.Font.Bold = true;
startCell = (Excel.Range)excelSheet.Cells[++curR, curC];
endCell = (Excel.Range)excelSheet.Cells[curR, curC + dtCols.Columns.Count - 2];
range = excelSheet.get_Range(startCell, endCell);
range.Merge(Type.Missing);
range.set_Value(Type.Missing, AppCtrl.GetThuTrongTuanTitle(date.DayOfWeek) + ": " + date.ToString(FrameworkParams.option.dateFormat));
range.Font.Bold = true;
startCell = (Excel.Range)excelSheet.Cells[++curR, curC];
endCell = (Excel.Range)excelSheet.Cells[curR, curC + dtCols.Columns.Count - 2];
range = excelSheet.get_Range(startCell, endCell);
range.Merge(Type.Missing);
range.set_Value(Type.Missing, "Trực biên tập:");
range.Font.Bold = true;
curR++; ;
foreach (DataColumn c in dtCols.Columns)
{
if (c.ColumnName == "NGAY_PHAT") continue;
range = ((Excel.Range)excelSheet.Cells[curR, curC]);
range.set_Value(Type.Missing, rCols[c.ColumnName]);
range.Font.Bold = true;
range.HorizontalAlignment = hacenter;
BorderCell(range);
curC++;
}
rows = dt.Select(string.Format("NGAY_PHAT='{0}'", rd["NGAY_PHAT"]));
curR++;
decimal tongtien = 0;
decimal tien = 0;
foreach (DataRow r in rows)
{
curC = startC;
foreach (DataColumn c in dtCols.Columns)
{
if (c.ColumnName == "NGAY_PHAT") continue;
range = ((Excel.Range)excelSheet.Cells[curR, curC]);
range.set_Value(Type.Missing, r[c.ColumnName]);
align = rAlign[c.ColumnName].ToString().Trim();
if (align == "" || align == "L")
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
else if (align == "C")
range.HorizontalAlignment = hacenter;
else if (align == "R")
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
curC++;
}
curR++;
tien = HelpNumber.ParseDecimal(r["GIA_TIEN"]);
if (tien > 0)
tongtien += tien;
}
int col = dtCols.Columns.Count;
range = ((Excel.Range)excelSheet.Cells[curR, startC + col-2]);
range.set_Value(Type.Missing,tongtien);
range.Font.Bold = true;
startCell = ((Excel.Range)excelSheet.Cells[curR, startC]);
endCell = ((Excel.Range)excelSheet.Cells[curR, startC + col - 3]);
range = excelSheet.get_Range(startCell, endCell);
range.Merge(Type.Missing);
range.set_Value(Type.Missing, "Tổng tiền");
range.Font.Bold = true;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
startCell = (Excel.Range)excelSheet.Cells[startR + 3, startC];
endCell = (Excel.Range)excelSheet.Cells[curR, startC + dtCols.Columns.Count - 2];
range = excelSheet.get_Range(startCell, endCell);
range.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
//range.Font.Size = 6;
range.Columns.AutoFit();
lSheet.Add(excelSheet);
}
excelSheet.Application.ActiveWindow.DisplayGridlines = false;
string name = AppUtil.GetTempFile();
string file = name + ".htm";
// TemplFiles.Add(file);
crFile = name + extension;
// TemplFiles.Add(crFile);
excelWorkbook.SaveAs(crFile, xlFormat, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive,
true, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
excelWorkbook.SaveAs(file, Excel.XlFileFormat.xlHtml, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive,
false, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
excelWorkbook.Close(true, Type.Missing, Type.Missing);
excelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
excelSheet = null;
excelWorkbook = null;
excelApp = null;
GC.Collect();
GC.WaitForPendingFinalizers();
excelWrapper1.OpenFile(file);
}
public DataSet GetImportDataSet()
{
WaitDialogForm wait = null;
try
{
OpenFileDialog oP = new OpenFileDialog();
oP.InitialDirectory = "My Documents://";
oP.Filter = HelpExcel.FILTER_FILE_OPEN;
oP.Title = HelpApplication.getTitleForm("Import dữ liệu từ tập tin excel");
if (oP.ShowDialog() == DialogResult.Cancel) return null;
System.Windows.Forms.Application.DoEvents();
string filenamepath = oP.FileName;
wait = new WaitDialogForm("Đang xử lý..", "Import dữ liệu từ excel!", new Size(250, 50));
string tempFilePath = AppUtil.GetTempFile(Path.GetExtension(filenamepath));
File.Copy(filenamepath, tempFilePath, true);
FileInfo inf = new FileInfo(tempFilePath);
if (inf.IsReadOnly)
{
inf.IsReadOnly = false;
}
wait.SetCaption("Đang kết nối tập tin excel...");
XlsFileConnection conn = new XlsFileConnection(tempFilePath);
if (ValidateConnection(conn, filenamepath, wait) == false)
{
if (wait != null) wait.Close();
return null;
}
DataSet ds = new DataSet();
ds = conn.LoadDataSet(BANG_GIA_BUDGET_CT.TABLE_NAME, null);
if (ds == null)
{
wait.Close();
HelpMsgBox.ShowNotificationMessage("Tập tin excel có cấu trúc không đúng mẫu, vui lòng kiểm tra lại");
return null;
}
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)//Loại bỏ dòng rỗng
{
ds.Tables[0].Rows.RemoveAt(0);
}
conn.Close();
System.Windows.Forms.Application.DoEvents();
return ds;
}
catch (Exception ex)
{
if (wait != null) wait.Close();
HelpMsgBox.ShowNotificationMessage("Tập tin excel có cấu trúc không đúng mẫu, vui lòng kiểm tra lại.");
PLException.AddException(ex);
return null;
}
finally
{
if (wait != null) wait.Close();
}
}
