// load a book
private void _btnLoad_Click(object sender, System.EventArgs e)
{
// choose file
OpenFileDialog dlg = new OpenFileDialog();
dlg.DefaultExt = "xls";
dlg.FileName = "*.xls";
if (dlg.ShowDialog() != DialogResult.OK)
{
return;
}
// clear everything
_book.Clear();
_tab.TabPages.Clear();
// load book
_book.Load(dlg.FileName);
// create one grid per sheet and add them to listbox
foreach (XLSheet sheet in _book.Sheets)
{
// create a new grid for this sheet
C1FlexGrid flex = new C1FlexGrid();
flex.BorderStyle = _flex.BorderStyle;
flex.AllowMerging = _flex.AllowMerging;
flex.Dock = _flex.Dock;
// load sheet into new grid
LoadSheet(flex, sheet, true);
// add new grid to the list
TabPage pg = new TabPage();
pg.Text = sheet.Name;
flex.Name = sheet.Name;
pg.Controls.Add(flex);
_tab.TabPages.Add(pg);
}
// select current sheet
_tab.SelectedIndex = _book.Sheets.SelectedIndex;
}
private void _btCreate_Click(object sender, RoutedEventArgs e)
{
// create new workbook
if (_book == null)
{
_book = new C1XLBook();
}
// clear the book
_book.Clear();
_book.Sheets.Clear();
// load from resources
Assembly a = Assembly.GetExecutingAssembly();
foreach (string res in a.GetManifestResourceNames())
{
if (!res.ToLower().EndsWith(".xlsx"))
{
continue;
}
using (var stream = a.GetManifestResourceStream(res))
{
// load Excel file
var book = new C1XLBook();
book.Load(stream, FileFormat.OpenXml);
// clone and rename first sheet (sheet names must be unique)
var ss = res.Split('.');
Debug.Assert(ss.Length >= 3);
XLSheet clone = book.Sheets[0].Clone();
clone.Name = ss[ss.Length - 2];
// add cloned sheet to main book
_book.Sheets.Add(clone);
}
}
// allow save the file
_lblStatus.Text = "You can save workbook";
_btnSave.IsEnabled = true;
}
private void _btCreate_Click(object sender, RoutedEventArgs e)
{
// create new workbook
if (_book == null)
{
_book = new C1XLBook();
}
// clear the book
_book.Clear();
// add some styles
XLStyle s1 = new XLStyle(_book);
XLStyle s2 = new XLStyle(_book);
XLStyle s3 = new XLStyle(_book);
s1.Format = "#,##0.00000";
s2.Format = "#,##0.00000";
s2.Font = new XLFont("Courier New", 14);
s3.Format = "dd-MMM-yy";
// populate sheet with some random values
XLSheet sheet = _book.Sheets[0];
Random r = new Random();
for (int i = 0; i < 100; i++)
{
sheet[i, 0].Value = r.NextDouble() * 100000;
sheet[i, 0].Style = (i % 13 == 0) ? s2 : s1;
}
for (int i = 0; i < 100; i++)
{
sheet[i, 1].Value = new DateTime(2005, r.Next(1, 12), r.Next(1, 28));
sheet[i, 1].Style = s3;
}
// automatic sizing
AutoSizeColumns(sheet);
// allow save the file
_lblStatus.Text = "You can save workbook";
_btnSave.IsEnabled = true;
}
private string CreateExcelFile()
{
//clear Excel book, remove the single blank sheet
_c1xl.Clear();
_c1xl.Sheets.Clear();
_c1xl.DefaultFont = new Font("Tahoma", 8);
//create Excel styles
_styTitle = new XLStyle(_c1xl);
_styHeader = new XLStyle(_c1xl);
_styMoney = new XLStyle(_c1xl);
_styOrder = new XLStyle(_c1xl);
//set up styles
_styTitle.Font = new Font(_c1xl.DefaultFont.Name, 15, FontStyle.Bold);
_styTitle.ForeColor = Color.Blue;
_styHeader.Font = new Font(_c1xl.DefaultFont, FontStyle.Bold);
_styHeader.ForeColor = Color.White;
_styHeader.BackColor = Color.DarkGray;
_styMoney.Format = XLStyle.FormatDotNetToXL("c");
_styOrder.Font = _styHeader.Font;
_styOrder.ForeColor = Color.Red;
//create report with one sheet per category
DataTable dt = GetCategories();
foreach (DataRow dr in dt.Rows)
{
CreateSheet(dr);
}
//save xls file
string uid = System.Guid.NewGuid().ToString();
string filename = Server.MapPath("~") + "\\Temp\\testexcel" + uid + ".xls";
_c1xl.Save(filename);
return(filename);
}
private void _btCreate_Click(object sender, RoutedEventArgs e)
{
// create new workbook
if (_book == null)
{
_book = new C1XLBook();
}
// clear the book
_book.Clear();
// first sheet
XLSheet sheet = _book.Sheets[0];
// column width in twips
sheet.Columns[0].Width = 2000;
sheet.Columns[1].Width = 2200;
// string formulas
string s = "String:";
sheet[0, 0].Value = s;
sheet[1, 0].Value = s;
sheet[2, 0].Value = s;
sheet[0, 1].Value = "apples";
sheet[1, 1].Value = "and";
sheet[2, 1].Value = "oranges";
s = "String formula:";
sheet[4, 0].Value = s;
sheet[5, 0].Value = s;
sheet[4, 1].Value = "apples and oranges";
sheet[5, 1].Value = "apples an";
sheet[4, 1].Formula = "CONCATENATE(B1,\" \",B2, \" \",B3)";
sheet[5, 1].Formula = "LEFT(B5,9)";
// simple formulas
sheet[7, 0].Value = "Formula: 5!";
sheet[7, 1].Value = 120;
sheet[7, 1].Formula = "1*2*3*4*5";
sheet[8, 0].Value = "Formula: 12/0";
sheet[8, 1].Value = 0;
sheet[8, 1].Formula = "12/0";
sheet[9, 0].Value = "Formula: 1 = 1";
sheet[9, 1].Value = true;
sheet[9, 1].Formula = "1=1";
sheet[10, 0].Value = "Formula: 1 = 2";
sheet[10, 1].Value = false;
sheet[10, 1].Formula = "1 = 2";
// now function
sheet[12, 0].Value = "Formula: Now()";
sheet[12, 1].Value = DateTime.Now;
sheet[12, 1].Formula = "Now()";
XLStyle style = new XLStyle(_book);
DateTimeFormatInfo dtfi = CultureInfo.CurrentCulture.DateTimeFormat;
style.Format = XLStyle.FormatDotNetToXL(dtfi.ShortDatePattern + " " + dtfi.ShortTimePattern);
sheet[12, 1].Style = style;
// allow save the file
_lblStatus.Text = "You can save workbook";
_btnSave.IsEnabled = true;
}
public void DoExport()
{
int rBillNo = 0;
int cBillNo = 1;
int rBillDate = 0;
int cBillDate = 10;
int rBillMonth = 2;
int cBillMonth = 0;
int rContact = 5;
int cContact = 3;
int rCustomer = 5;
int cCustomer = 7;
int rContract = 7;
int cContract = 1;
int rRate = 11;
int cRate = 9;
int rRateDate = 11;
int cRateDate = 12;
int rRent = 15;
int rManager = 23;
int rParking = 31;
int rExtra = 39;
int rElec = 47;
int rWater = 55;
int rService = 63;
int rPaid = 70;
int rDept = 77;
int rOffice = 88;
int cOffice = 3;
int rPhone = 89;
int cPhone = 3;
int rBank = 88;
int cBank = 7;
int rAccountName = 89;
int cAccountName = 7;
int rAccount = 90;
int cAccount = 7;
int rSum = 81;
int cSum = 12;
int rSumVND = 80;
int cSumVND = 12;
int rSumRead = 82;
int cSumRead = 13;
////
using (SqlDatabase db = new SqlDatabase())
{
DataSet ds = new DataSet();
DataSet dsCus = new DataSet();
string sql = string.Empty;
string Bank = "";
string Account = "";
string AccountName = "";
string Office = "";
string OfficeAddress = "";
string OfficePhone = "";
ds = new DataSet();
sql = " SELECT Bank,Account,AccountName,Office,OfficeAddress,OfficePhone";
sql += " FROM Mst_Building";
sql += " WHERE BuildingId = '" + sBuildingId + "' ";
using (SqlCommand cm = db.CreateCommand(sql))
{
SqlDataAdapter da = new SqlDataAdapter(cm);
da.Fill(ds);
if (ds != null)
{
DataTable dt = ds.Tables[0];
foreach (DataRow rowType in dt.Rows)
{
Bank = rowType["Bank"].ToString();
Account = rowType["Account"].ToString();
AccountName = rowType["AccountName"].ToString();
Office = rowType["Office"].ToString();
OfficeAddress = rowType["OfficeAddress"].ToString();
OfficePhone = rowType["OfficePhone"].ToString();
}
}
}
//Danh sách Bill
sql = " Select BillDate,UsdExchangeDate,UsdExchange,BillNo,B.Name,B.ContactName,A.CustomerId, A.YearMonths, A.Id ";
sql += " From PaymentBillInfo A, Customer B";
sql += " Where A.BuildingId = B.BuildingId and A.CustomerId = B.CustomerId and B.DelFlag = 0 and A.BuildingId = '" + sBuildingId + "' and YearMonth = '" + sYearMonth + "'";
string BillDate = "";
string UsdExchangeDate = "";
string UsdExchange = "";
string BillNo = "";
string Name = "";
string ContactName = "";
string CustomerId = "";
string lsYearmonth = "";
string id = "";
string maxYearMonth = "";
using (SqlCommand cmCus = db.CreateCommand(sql))
{
SqlDataAdapter daCus = new SqlDataAdapter(cmCus);
daCus.Fill(dsCus);
if (dsCus != null)
{
string strDT = DateTime.Now.ToString("yyyyMMddHHmmss");
DataTable dtCus = dsCus.Tables[0];
foreach (DataRow rowCus in dtCus.Rows)
{
BillDate = rowCus[0].ToString();
UsdExchangeDate = rowCus[1].ToString();
UsdExchange = rowCus[2].ToString();
BillNo = rowCus[3].ToString();
Name = rowCus[4].ToString();
ContactName = rowCus[5].ToString();
CustomerId = rowCus[6].ToString();
lsYearmonth = rowCus[7].ToString();
id = rowCus[8].ToString();
string[] strTmpYearMonth = lsYearmonth.Split(',');
for (int l = 0; l < strTmpYearMonth.Length; l++)
{
string tmp = strTmpYearMonth[l];
if (maxYearMonth == "")
maxYearMonth = tmp;
if (maxYearMonth.CompareTo(tmp) < 0)
maxYearMonth = tmp;
}
C1XLBook xlbBook = new C1XLBook();
//ShowData(drpYear.SelectedValue + drpMonth.SelectedValue);
XLStyle xlstStyle = new XLStyle(xlbBook);
xlstStyle.AlignHorz = XLAlignHorzEnum.Center;
xlstStyle.AlignVert = XLAlignVertEnum.Center;
xlstStyle.WordWrap = true;
xlstStyle.Font = new Font("", 8, FontStyle.Regular);
xlstStyle.SetBorderColor(Color.Black);
xlstStyle.BorderBottom = XLLineStyleEnum.Thin;
xlstStyle.BorderTop = XLLineStyleEnum.Thin;
xlstStyle.BorderLeft = XLLineStyleEnum.Thin;
xlstStyle.BorderRight = XLLineStyleEnum.Thin;
xlstStyle.Format = "#,##0.00_);(#,##0.00)";
XLStyle xlstStyleH = new XLStyle(xlbBook);
xlstStyleH.AlignHorz = XLAlignHorzEnum.Center;
xlstStyleH.AlignVert = XLAlignVertEnum.Center;
xlstStyleH.Font = new Font("", 8, FontStyle.Bold);
xlstStyleH.SetBorderColor(Color.Black);
xlstStyleH.BorderBottom = XLLineStyleEnum.Thin;
xlstStyleH.BorderTop = XLLineStyleEnum.Thin;
xlstStyleH.BorderLeft = XLLineStyleEnum.Thin;
xlstStyleH.BorderRight = XLLineStyleEnum.Thin;
xlstStyleH.WordWrap = true;
XLStyle xlstStyleSum = new XLStyle(xlbBook);
xlstStyleSum.AlignHorz = XLAlignHorzEnum.Right;
xlstStyleSum.AlignVert = XLAlignVertEnum.Center;
xlstStyleSum.Font = new Font("", 8, FontStyle.Bold);
xlstStyleSum.SetBorderColor(Color.Black);
xlstStyleSum.BorderBottom = XLLineStyleEnum.Thin;
xlstStyleSum.BorderTop = XLLineStyleEnum.Thin;
xlstStyleSum.BorderLeft = XLLineStyleEnum.Thin;
xlstStyleSum.BorderRight = XLLineStyleEnum.Thin;
xlstStyleSum.WordWrap = true;
string fileName = sFilePath + @"\Template\BillTongQuat.xlsx";
if (!Directory.Exists(sFilePath + @"\Building\" + sBuildingId + @"\Bill"))
{
Directory.CreateDirectory(sFilePath + @"\Report\Building\" + sBuildingId + @"\Bill");
}
string strFilePath = sFilePath + @"\Building\" + sBuildingId + @"\Bill\Bill" + "_" + CustomerId + "_" + BillNo + id + "_" + strDT + ".xlsx";
string strFilePathExport = sFilePath.Replace(@"\", "/") + @"/Building/" + sBuildingId + @"/Bill/Bill" + "_" + CustomerId + "_" + BillNo + id + "_" + strDT + ".xlsx";
string fileNameDes = strFilePath;
//string fileNameDes = HttpContext.Current.Server.MapPath(@"~\Report\Building\" + sBuildingId + @"\TongHopDienTich" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");
File.Copy(fileName, fileNameDes);
xlbBook.Load(fileNameDes);
XLSheet xlsSheet = xlbBook.Sheets["TongHop"];
XLSheet xlsSheetEn = xlbBook.Sheets["TongHop_En"];
//Thông tin về Ngân hàng của Tòa Nhà
xlsSheet[rOffice, cOffice].Value = xlsSheet[rOffice, cOffice].Value.ToString().Replace("{%VAN_PHONG%}", Office);
xlsSheet[rPhone, cPhone].Value = xlsSheet[rPhone, cPhone].Value.ToString().Replace("{%DIEN_THOAI%}", OfficePhone);
xlsSheet[rBank, cBank].Value = xlsSheet[rBank, cBank].Value.ToString().Replace("{%NGAN_HANG%}", Bank);
xlsSheet[rAccountName, cAccountName].Value = xlsSheet[rAccountName, cAccountName].Value.ToString().Replace("{%TEN_TAI_KHOAN%}", AccountName);
xlsSheet[rAccount, cAccount].Value = xlsSheet[rAccount, cAccount].Value.ToString().Replace("{%SO_TAI_KHOAN%}", Account);
//Thông tin về Ngân hàng của Tòa Nhà
//Customer
xlsSheet[rCustomer, cCustomer].Value = xlsSheet[rCustomer, cCustomer].Value.ToString().Replace("{%TEN_CONG_TY%}", Name);
//Contact
xlsSheet[rContact, cContact].Value = xlsSheet[rContact, cContact].Value.ToString().Replace("{%NGUOI_DAI_DIEN%}", ContactName);
//Bill No
xlsSheet[rBillNo, cBillNo].Value = xlsSheet[rBillNo, cBillNo].Value.ToString().Replace("{%BILL_NO%}", BillNo);
///////////////////////////////////////////
//Thông tin về Ngân hàng của Tòa Nhà
xlsSheetEn[rOffice, cOffice].Value = xlsSheetEn[rOffice, cOffice].Value.ToString().Replace("{%VAN_PHONG%}", Office);
xlsSheetEn[rPhone, cPhone].Value = xlsSheetEn[rPhone, cPhone].Value.ToString().Replace("{%DIEN_THOAI%}", OfficePhone);
xlsSheetEn[rBank, cBank].Value = xlsSheetEn[rBank, cBank].Value.ToString().Replace("{%NGAN_HANG%}", Bank);
xlsSheetEn[rAccountName, cAccountName].Value = xlsSheetEn[rAccountName, cAccountName].Value.ToString().Replace("{%TEN_TAI_KHOAN%}", AccountName);
xlsSheetEn[rAccount, cAccount].Value = xlsSheetEn[rAccount, cAccount].Value.ToString().Replace("{%SO_TAI_KHOAN%}", Account);
//Thông tin về Ngân hàng của Tòa Nhà
//Customer
xlsSheetEn[rCustomer, cCustomer].Value = xlsSheetEn[rCustomer, cCustomer].Value.ToString().Replace("{%TEN_CONG_TY%}", Name);
//Contact
xlsSheetEn[rContact, cContact].Value = xlsSheetEn[rContact, cContact].Value.ToString().Replace("{%NGUOI_DAI_DIEN%}", ContactName);
//Bill No
xlsSheetEn[rBillNo, cBillNo].Value = xlsSheetEn[rBillNo, cBillNo].Value.ToString().Replace("{%BILL_NO%}", BillNo);
///////////////////////////////////////////
//Ngay Thang Nam
DateTime dtime = DateTime.Today;
string strTmp = xlsSheet[rBillDate, cBillDate].Value.ToString().Replace("{%NGAY%}", dtime.ToString("dd"));
strTmp = strTmp.Replace("{%THANG%}", dtime.ToString("MM"));
xlsSheet[rBillDate, cBillDate].Value = strTmp.Replace("{%NAM%}", dtime.ToString("yyyy"));
strTmp = xlsSheetEn[rBillDate, cBillDate].Value.ToString().Replace("{%NGAY%}", dtime.ToString("dd"));
strTmp = strTmp.Replace("{%THANG%}", dtime.ToString("MM"));
xlsSheetEn[rBillDate, cBillDate].Value = strTmp.Replace("{%NAM%}", dtime.ToString("yyyy"));
//Nam
xlsSheet[rBillMonth, cBillMonth].Value = xlsSheet[rBillMonth, cBillMonth].Value.ToString().Replace("{%NAM_THANG%}", sMonth + "/" + sYear);
xlsSheetEn[rBillMonth, cBillMonth].Value = xlsSheetEn[rBillMonth, cBillMonth].Value.ToString().Replace("{%NAM_THANG%}", sMonth + "/" + sYear);
//Thông tin Tỉ giá
xlsSheet[rRate, cRate].Value = xlsSheet[rRate, cRate].Value.ToString().Replace("{%TI_GIA%}", UsdExchange);
xlsSheet[rRateDate, cRateDate].Value = xlsSheet[rRateDate, cRateDate].Value.ToString().Replace("{%NGAY_AP_DUNG%}", UsdExchangeDate);
//Thông tin Tỉ giá
xlsSheetEn[rRate, cRate].Value = xlsSheetEn[rRate, cRate].Value.ToString().Replace("{%TI_GIA%}", UsdExchange);
xlsSheetEn[rRateDate, cRateDate].Value = xlsSheetEn[rRateDate, cRateDate].Value.ToString().Replace("{%NGAY_AP_DUNG%}", UsdExchangeDate);
Hashtable contractIdLst = new Hashtable();
string contract = "";
////
//Thue phong
ds = new DataSet();
sql = " Select A.*, B.ContractDate";
sql += " FROM PaymentRoom A, RentContract B";
sql += " WHERE A.ContractId = B.ContractId and A.BuildingId = B.BuildingId and A.BuildingId = '" + sBuildingId + "' and A.CustomerId = '" + CustomerId + "' and A.YearMonth in (" + lsYearmonth + ")";
int sumRow = 0;
int j = 0;
decimal[] LastSumPriceVND = new decimal[7] { 0, 0, 0, 0, 0, 0, 0 };
decimal[] LastSumPriceUSD = new decimal[7] { 0, 0, 0, 0, 0, 0, 0 };
decimal PaidPriceVND = 0;
decimal PaidPriceUSD = 0;
int line = 0;
using (SqlCommand cm = db.CreateCommand(sql))
{
SqlDataAdapter da = new SqlDataAdapter(cm);
da.Fill(ds);
line = rRent - 3 + j;
XLCellRange mCell = new XLCellRange(line, line + 2, 1, 3);
xlsSheet.MergedCells.Add(mCell);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line, 4, 5);
xlsSheet.MergedCells.Add(mCell);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 1, 4, 5);
xlsSheet.MergedCells.Add(mCell);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 2, line + 2, 4, 5);
xlsSheet.MergedCells.Add(mCell);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line, 6, 7);
xlsSheet.MergedCells.Add(mCell);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 1, 6, 7);
xlsSheet.MergedCells.Add(mCell);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line, 8, 9);
xlsSheet.MergedCells.Add(mCell);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 1, 8, 9);
xlsSheet.MergedCells.Add(mCell);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line, 10, 11);
xlsSheet.MergedCells.Add(mCell);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 1, 10, 11);
xlsSheet.MergedCells.Add(mCell);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line, 12, 13);
xlsSheet.MergedCells.Add(mCell);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 1, 12, 13);
xlsSheet.MergedCells.Add(mCell);
xlsSheetEn.MergedCells.Add(mCell);
if (ds != null)
{
int count = 0;
DataTable dt = ds.Tables[0];
int k = 0;
foreach (DataRow rowType in dt.Rows)
{
decimal tmp01 = Convert.ToDecimal(rowType["LastRentSumUSD"]);
decimal tmp02 = Convert.ToDecimal(rowType["LastRentSumVND"]);
string ContractId = Func.ParseString(rowType["ContractId"]);
string ContractNo = Func.ParseString(rowType["ContractNo"]);
string YearMonth = Func.ParseString(rowType["YearMonth"]);
string Area = Func.ParseString(rowType["Area"]);
string Regional = Func.ParseString(rowType["Regional"]);
string Floor = Func.ParseString(rowType["Floor"]);
string BeginContract = Func.ParseString(rowType["ContractDate"]);
if (!contractIdLst.ContainsKey(ContractId + "(" + Func.FormatDMY(BeginContract) + ")"))
{
contractIdLst.Add(ContractId + "(" + Func.FormatDMY(BeginContract) + ")", ContractNo + "(" + Func.FormatDMY(BeginContract) + ")");
contract += ";" + ContractNo + "(" + Func.FormatDMY(BeginContract) + ")";
}
if (tmp01 > 0 || tmp02 > 0)
{
if (count >= 1)
{
xlsSheet.Rows.Insert(rRent + 1 + j);
xlsSheetEn.Rows.Insert(rRent + 1 + j);
j++;
}
count++;
int tmp = rRent + j;
xlsSheet[tmp, 1].Value = Name;
xlsSheet[tmp, 4].Value = rowType["Area"];
xlsSheet[tmp, 6].Value = rowType["MonthRentPriceUSD"];
xlsSheet[tmp, 7].Value = rowType["MonthRentPriceVND"];
xlsSheet[tmp, 8].Value = rowType["MonthRentSumUSD"];
xlsSheet[tmp, 9].Value = rowType["MonthRentSumVND"];
xlsSheet[tmp, 10].Value = rowType["VatRentPriceUSD"];
xlsSheet[tmp, 11].Value = rowType["VatRentPriceVND"];
xlsSheet[tmp, 12].Value = rowType["LastRentSumUSD"];
xlsSheet[tmp, 13].Value = rowType["LastRentSumVND"];
XLCellRange mrCell = new XLCellRange(tmp, tmp, 1, 3);
xlsSheet.MergedCells.Add(mrCell);
mrCell = new XLCellRange(tmp, tmp, 4, 5);
xlsSheet.MergedCells.Add(mrCell);
////EN
xlsSheetEn[tmp, 1].Value = Name;
xlsSheetEn[tmp, 4].Value = rowType["Area"];
xlsSheetEn[tmp, 6].Value = rowType["MonthRentPriceUSD"];
xlsSheetEn[tmp, 7].Value = rowType["MonthRentPriceVND"];
xlsSheetEn[tmp, 8].Value = rowType["MonthRentSumUSD"];
xlsSheetEn[tmp, 9].Value = rowType["MonthRentSumVND"];
xlsSheetEn[tmp, 10].Value = rowType["VatRentPriceUSD"];
xlsSheetEn[tmp, 11].Value = rowType["VatRentPriceVND"];
xlsSheetEn[tmp, 12].Value = rowType["LastRentSumUSD"];
xlsSheetEn[tmp, 13].Value = rowType["LastRentSumVND"];
mrCell = new XLCellRange(tmp, tmp, 1, 3);
xlsSheetEn.MergedCells.Add(mrCell);
mrCell = new XLCellRange(tmp, tmp, 4, 5);
xlsSheetEn.MergedCells.Add(mrCell);
////EN
LastSumPriceVND[0] += Convert.ToDecimal(rowType["LastRentSumVND"]);
LastSumPriceUSD[0] += Convert.ToDecimal(rowType["LastRentSumUSD"]);
}
else
{
k++;
}
}
mCell = new XLCellRange(rRent + 1 + j, rRent + 1 + j, 1, 11);
xlsSheet.MergedCells.Add(mCell);
xlsSheetEn.MergedCells.Add(mCell);
xlsSheet[rRent + 1 + j, 12].Value = LastSumPriceUSD[0];
xlsSheet[rRent + 1 + j, 13].Value = LastSumPriceVND[0];
xlsSheetEn[rRent + 1 + j, 12].Value = LastSumPriceUSD[0];
xlsSheetEn[rRent + 1 + j, 13].Value = LastSumPriceVND[0];
for (int row = rRent + sumRow; row <= rRent + dt.Rows.Count - k; row++)
{
for (int col = 1; col <= 13; col++)
{
xlsSheet[row, col].Style = xlstStyle;
xlsSheetEn[row, col].Style = xlstStyle;
}
}
sumRow += dt.Rows.Count - 1 - k;
////////////////////////
for (int col = 1; col <= 13; col++)
{
xlsSheet[rRent + 1 + j, col].Style = xlstStyleSum;
xlsSheetEn[rRent + 1 + j, col].Style = xlstStyleSum;
}
line = rManager - 3 + j;
mCell = new XLCellRange(line, line + 2, 1, 3);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line, 4, 5);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 1, 4, 5);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 2, line + 2, 4, 5);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line, 6, 7);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 1, 6, 7);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line, 8, 9);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 1, 8, 9);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line, 10, 11);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 1, 10, 11);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line, 12, 13);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 1, 12, 13);
xlsSheet.MergedCells.Add(mCell);
////En
mCell = new XLCellRange(line, line + 2, 1, 3);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line, 4, 5);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 1, 4, 5);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 2, line + 2, 4, 5);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line, 6, 7);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 1, 6, 7);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line, 8, 9);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 1, 8, 9);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line, 10, 11);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 1, 10, 11);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line, 12, 13);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 1, 12, 13);
xlsSheetEn.MergedCells.Add(mCell);
////En
k = 0;
count = 0;
foreach (DataRow row in dt.Rows)
{
decimal tmp01 = Convert.ToDecimal(row["LastManagerSumUSD"]);
decimal tmp02 = Convert.ToDecimal(row["LastManagerSumVND"]);
if (tmp01 > 0 || tmp02 > 0)
{
if (count >= 1)
{
xlsSheet.Rows.Insert(rManager + 1 + j);
xlsSheetEn.Rows.Insert(rManager + 1 + j);
j++;
}
count++;
int tmp = rManager + j;
string YearMonth = Func.ParseString(row["YearMonth"]);
string Area = Func.ParseString(row["Area"]);
xlsSheet[tmp, 1].Value = Name;
xlsSheet[tmp, 4].Value = row["Area"];
xlsSheet[tmp, 6].Value = row["MonthManagerPriceUSD"];
xlsSheet[tmp, 7].Value = row["MonthManagerPriceVND"];
xlsSheet[tmp, 8].Value = row["MonthManagerSumUSD"];
xlsSheet[tmp, 9].Value = row["MonthManagerSumVND"];
xlsSheet[tmp, 10].Value = row["VatManagerPriceUSD"];
xlsSheet[tmp, 11].Value = row["VatManagerPriceVND"];
xlsSheet[tmp, 12].Value = row["LastManagerSumUSD"];
xlsSheet[tmp, 13].Value = row["LastManagerSumVND"];
XLCellRange mrCell = new XLCellRange(tmp, tmp, 1, 3);
xlsSheet.MergedCells.Add(mrCell);
mrCell = new XLCellRange(tmp, tmp, 4, 5);
xlsSheet.MergedCells.Add(mrCell);
////En
xlsSheetEn[tmp, 1].Value = Name;
xlsSheetEn[tmp, 4].Value = row["Area"];
xlsSheetEn[tmp, 6].Value = row["MonthManagerPriceUSD"];
xlsSheetEn[tmp, 7].Value = row["MonthManagerPriceVND"];
xlsSheetEn[tmp, 8].Value = row["MonthManagerSumUSD"];
xlsSheetEn[tmp, 9].Value = row["MonthManagerSumVND"];
xlsSheetEn[tmp, 10].Value = row["VatManagerPriceUSD"];
xlsSheetEn[tmp, 11].Value = row["VatManagerPriceVND"];
xlsSheetEn[tmp, 12].Value = row["LastManagerSumUSD"];
xlsSheetEn[tmp, 13].Value = row["LastManagerSumVND"];
mrCell = new XLCellRange(tmp, tmp, 1, 3);
xlsSheetEn.MergedCells.Add(mrCell);
mrCell = new XLCellRange(tmp, tmp, 4, 5);
xlsSheetEn.MergedCells.Add(mrCell);
////En
LastSumPriceVND[1] += Convert.ToDecimal(row["LastManagerSumVND"]);
LastSumPriceUSD[1] += Convert.ToDecimal(row["LastManagerSumUSD"]);
}
else { k++; }
}
mCell = new XLCellRange(rManager + 1 + j, rManager + 1 + j, 1, 11);
xlsSheet.MergedCells.Add(mCell);
xlsSheetEn.MergedCells.Add(mCell);
xlsSheet[rManager + 1 + j, 12].Value = LastSumPriceUSD[1];
xlsSheet[rManager + 1 + j, 13].Value = LastSumPriceVND[1];
xlsSheetEn[rManager + 1 + j, 12].Value = LastSumPriceUSD[1];
xlsSheetEn[rManager + 1 + j, 13].Value = LastSumPriceVND[1];
for (int row = rManager + sumRow; row <= rManager + sumRow + dt.Rows.Count - k; row++)
{
for (int col = 1; col <= 13; col++)
{
xlsSheet[row, col].Style = xlstStyle;
xlsSheetEn[row, col].Style = xlstStyle;
}
}
for (int col = 1; col <= 13; col++)
{
xlsSheet[rManager + 1 + j, col].Style = xlstStyleSum;
xlsSheetEn[rManager + 1 + j, col].Style = xlstStyleSum;
}
sumRow += dt.Rows.Count - 1 - k;
}
}
ds = new DataSet();
//Xuất ra toàn bộ nội dung theo Trang
sql = " SELECT COUNT(*) AS Num, YearMonth, TariffsParkingName, PriceVND, PriceUSD, SUM(VatVND) AS VatVND,SUM(VatUSD) AS VatUSD, SUM(SumVND) AS SumVND, SUM(SumUSD) AS SumUSD, SUM(LastPriceVND) AS LastPriceVND";
sql += " , SUM(LastPriceUSD) AS LastPriceUSD";
sql += " FROM dbo.PaymentParking";
sql += " WHERE BuildingId = '" + sBuildingId + "' and CustomerId = '" + CustomerId + "' and YearMonth in (" + lsYearmonth + ")";
sql += " GROUP BY YearMonth, TariffsParkingName, PriceVND, PriceUSD, Vat, daysParking";
sql += " HAVING (SUM(LastPriceVND) >0 or SUM(LastPriceUSD) >0)";
using (SqlCommand cm = db.CreateCommand(sql))
{
SqlDataAdapter da = new SqlDataAdapter(cm);
da.Fill(ds);
line = rParking - 3 + j;
XLCellRange mCell = new XLCellRange(line, line + 2, 1, 3);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line, 4, 5);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 1, 4, 5);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 2, line + 2, 4, 5);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line, 6, 7);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 1, 6, 7);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line, 8, 9);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 1, 8, 9);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line, 10, 11);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 1, 10, 11);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line, 12, 13);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 1, 12, 13);
xlsSheet.MergedCells.Add(mCell);
////En
mCell = new XLCellRange(line, line + 2, 1, 3);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line, 4, 5);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 1, 4, 5);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 2, line + 2, 4, 5);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line, 6, 7);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 1, 6, 7);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line, 8, 9);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 1, 8, 9);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line, 10, 11);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 1, 10, 11);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line, 12, 13);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 1, 12, 13);
xlsSheetEn.MergedCells.Add(mCell);
////En
if (ds != null)
{
int count = 0;
DataTable dt = ds.Tables[0];
foreach (DataRow row in dt.Rows)
{
if (count >= 1)
{
xlsSheet.Rows.Insert(rParking + 1 + j);
xlsSheetEn.Rows.Insert(rParking + 1 + j);
j++;
}
count++;
int tmp = rParking + j;
string Num = Func.ParseString(row["Num"]);
string TariffsParkingName = Func.ParseString(row["TariffsParkingName"]);
xlsSheet[tmp, 1].Value = TariffsParkingName;
xlsSheet[tmp, 4].Value = Num;
xlsSheet[tmp, 6].Value = row["PriceUSD"];
xlsSheet[tmp, 7].Value = row["PriceVND"];
xlsSheet[tmp, 8].Value = row["SumUSD"];
xlsSheet[tmp, 9].Value = row["SumVND"];
xlsSheet[tmp, 10].Value = row["VatUSD"];
xlsSheet[tmp, 11].Value = row["VatVND"];
xlsSheet[tmp, 12].Value = row["LastPriceUSD"];
xlsSheet[tmp, 13].Value = row["LastPriceVND"];
XLCellRange mrCell = new XLCellRange(tmp, tmp, 1, 3);
xlsSheet.MergedCells.Add(mrCell);
mrCell = new XLCellRange(tmp, tmp, 4, 5);
xlsSheet.MergedCells.Add(mrCell);
/////En
xlsSheetEn[tmp, 1].Value = TariffsParkingName;
xlsSheetEn[tmp, 4].Value = Num;
xlsSheetEn[tmp, 6].Value = row["PriceUSD"];
xlsSheetEn[tmp, 7].Value = row["PriceVND"];
xlsSheetEn[tmp, 8].Value = row["SumUSD"];
xlsSheetEn[tmp, 9].Value = row["SumVND"];
xlsSheetEn[tmp, 10].Value = row["VatUSD"];
xlsSheetEn[tmp, 11].Value = row["VatVND"];
xlsSheetEn[tmp, 12].Value = row["LastPriceUSD"];
xlsSheetEn[tmp, 13].Value = row["LastPriceVND"];
mrCell = new XLCellRange(tmp, tmp, 1, 3);
xlsSheetEn.MergedCells.Add(mrCell);
mrCell = new XLCellRange(tmp, tmp, 4, 5);
xlsSheetEn.MergedCells.Add(mrCell);
/////En
LastSumPriceVND[2] += Convert.ToDecimal(row["LastPriceVND"]);
LastSumPriceUSD[2] += Convert.ToDecimal(row["LastPriceUSD"]);
}
xlsSheet[rParking + 1 + j, 12].Value = LastSumPriceUSD[2];
xlsSheet[rParking + 1 + j, 13].Value = LastSumPriceVND[2];
mCell = new XLCellRange(rParking + 1 + j, rParking + 1 + j, 1, 11);
xlsSheet.MergedCells.Add(mCell);
/////En
xlsSheetEn[rParking + 1 + j, 12].Value = LastSumPriceUSD[2];
xlsSheetEn[rParking + 1 + j, 13].Value = LastSumPriceVND[2];
mCell = new XLCellRange(rParking + 1 + j, rParking + 1 + j, 1, 11);
xlsSheetEn.MergedCells.Add(mCell);
/////En
for (int row = rParking + sumRow; row <= rParking + sumRow + dt.Rows.Count; row++)
{
for (int col = 1; col <= 13; col++)
{
xlsSheet[row, col].Style = xlstStyle;
xlsSheetEn[row, col].Style = xlstStyle;
}
}
for (int col = 1; col <= 13; col++)
{
xlsSheet[rParking + 1 + j, col].Style = xlstStyleSum;
xlsSheetEn[rParking + 1 + j, col].Style = xlstStyleSum;
}
sumRow += dt.Rows.Count - 1;
}
}
ds = new DataSet();
sql = "SELECT id";
sql += " ,YearMonth,BuildingId,CustomerId,RoomId,ExtraHour,VAT,OtherFee01,OtherFee02";
sql += " ,PriceUSD,PriceVND,VatUSD,VatVND,SumUSD,SumVND,IsNull(LastPriceUSD,0) LastPriceUSD ,IsNull(LastPriceVND,0) LastPriceVND ";
sql += " ,RentArea,dbo.fnDateTime(FromWD) BeginDate,dbo.fnDateTime(EndWD) EndDate,ExtratimeType";
sql += " FROM PaymentExtraTimeMonth";
sql += " WHERE BuildingId = '" + sBuildingId + "' and CustomerId = '" + CustomerId + "' and YearMonth in (" + lsYearmonth + ")";
using (SqlCommand cm = db.CreateCommand(sql))
{
SqlDataAdapter da = new SqlDataAdapter(cm);
da.Fill(ds);
line = rExtra - 3 + j;
//Phi dien
XLCellRange mCell = new XLCellRange(line, line + 2, 1, 3);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line + 2, 4, 4);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line, 6, 7);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 1, 6, 7);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line, 8, 9);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 1, 8, 9);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line, 10, 11);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 1, 10, 11);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line, 12, 13);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 1, 12, 13);
xlsSheet.MergedCells.Add(mCell);
/////En
mCell = new XLCellRange(line, line + 2, 1, 3);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line + 2, 4, 4);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line, 6, 7);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 1, 6, 7);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line, 8, 9);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 1, 8, 9);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line, 10, 11);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 1, 10, 11);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line, 12, 13);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 1, 12, 13);
xlsSheetEn.MergedCells.Add(mCell);
/////En
if (ds != null)
{
int count = 0;
DataTable dt = ds.Tables[0];
foreach (DataRow row in dt.Rows)
{
if (count >= 1)
{
xlsSheet.Rows.Insert(rExtra + 1 + j);
xlsSheetEn.Rows.Insert(rExtra + 1 + j);
j++;
}
count++;
int tmp = rExtra + j;
string ExtraHour = Func.ParseString(row["ExtraHour"]);
string BeginDate = Func.ParseString(row["BeginDate"]);
string EndDate = Func.ParseString(row["EndDate"]);
string ExtratimeType = Func.ParseString(row["ExtratimeType"]);
xlsSheet[tmp, 1].Value = BeginDate + "~" + EndDate;
xlsSheet[tmp, 5].Value = ExtraHour;
xlsSheet[tmp, 4].Value = "Diện tích";
if ("0".Equals(ExtratimeType))
{
xlsSheet[tmp, 4].Value = "m2*h";
}
xlsSheet[tmp, 6].Value = row["PriceUSD"];
xlsSheet[tmp, 7].Value = row["PriceVND"];
xlsSheet[tmp, 8].Value = row["SumUSD"];
xlsSheet[tmp, 9].Value = row["SumVND"];
xlsSheet[tmp, 10].Value = row["VatUSD"];
xlsSheet[tmp, 11].Value = row["VatVND"];
xlsSheet[tmp, 12].Value = row["LastPriceUSD"];
xlsSheet[tmp, 13].Value = row["LastPriceVND"];
LastSumPriceVND[3] += Convert.ToDecimal(row["LastPriceVND"]);
LastSumPriceUSD[3] += Convert.ToDecimal(row["LastPriceUSD"]);
XLCellRange mrCell = new XLCellRange(tmp, tmp, 1, 3);
xlsSheet.MergedCells.Add(mrCell);
//////En
xlsSheetEn[tmp, 1].Value = BeginDate + "~" + EndDate;
xlsSheetEn[tmp, 5].Value = ExtraHour;
xlsSheetEn[tmp, 4].Value = "Di?n tích";
if ("0".Equals(ExtratimeType))
{
xlsSheetEn[tmp, 4].Value = "m2*h";
}
xlsSheetEn[tmp, 6].Value = row["PriceUSD"];
xlsSheetEn[tmp, 7].Value = row["PriceVND"];
xlsSheetEn[tmp, 8].Value = row["SumUSD"];
xlsSheetEn[tmp, 9].Value = row["SumVND"];
xlsSheetEn[tmp, 10].Value = row["VatUSD"];
xlsSheetEn[tmp, 11].Value = row["VatVND"];
xlsSheetEn[tmp, 12].Value = row["LastPriceUSD"];
xlsSheetEn[tmp, 13].Value = row["LastPriceVND"];
//LastSumPriceVND[3] += Convert.ToDecimal(row["LastPriceVND"]);
//LastSumPriceUSD[3] += Convert.ToDecimal(row["LastPriceUSD"]);
mrCell = new XLCellRange(tmp, tmp, 1, 3);
xlsSheetEn.MergedCells.Add(mrCell);
//////En
for (int col = 1; col <= 13; col++)
{
xlsSheet[tmp, col].Style = xlstStyle;
xlsSheetEn[tmp, col].Style = xlstStyle;
}
}
mCell = new XLCellRange(rExtra + 1 + j, rExtra + 1 + j, 1, 11);
xlsSheet.MergedCells.Add(mCell);
xlsSheetEn.MergedCells.Add(mCell);
xlsSheet[rExtra + 1 + j, 12].Value = LastSumPriceUSD[3];
xlsSheet[rExtra + 1 + j, 13].Value = LastSumPriceVND[3];
xlsSheetEn[rExtra + 1 + j, 12].Value = LastSumPriceUSD[3];
xlsSheetEn[rExtra + 1 + j, 13].Value = LastSumPriceVND[3];
for (int row = rExtra + sumRow; row <= rExtra + sumRow + dt.Rows.Count; row++)
{
for (int col = 1; col <= 13; col++)
{
xlsSheet[row, col].Style = xlstStyle;
xlsSheetEn[row, col].Style = xlstStyle;
}
}
for (int col = 1; col <= 13; col++)
{
xlsSheet[rExtra + 1 + j, col].Style = xlstStyleSum;
xlsSheetEn[rExtra + 1 + j, col].Style = xlstStyleSum;
}
sumRow += dt.Rows.Count - 1;
}
}
ds = new DataSet();
//Dien
//Xuất ra toàn bộ nội dung theo Trang
sql = " SELECT dbo.fnDateTime(A.DateFrom) DateFrom, dbo.fnDateTime(A.DateTo) DateTo, A.Vat, B.id, B.UsedElecWaterId, B.FromIndex, B.ToIndex, B.OtherFee01, B.OtherFee02, B.Mount, B.PriceVND, B.PriceUSD, B.SumVND, B.SumUSD, ";
sql += " B.VatVND, B.VatUSD ,IsNull(B.LastPriceUSD,0) LastPriceUSD ,IsNull(B.LastPriceVND,0) LastPriceVND , B.Name, B.WaterPricePercent,B.ElecPricePercent ";
sql += " FROM PaymentElecWater AS A INNER JOIN ";
sql += " PaymentElecWaterDetail AS B ON A.UsedElecWaterId = B.UsedElecWaterId";
sql += " WHERE A.BuildingId = '" + sBuildingId + "' and A.CustomerId = '" + CustomerId + "' and TarrifsOfWaterId = 0 and A.YearMonth in (" + lsYearmonth + ")";
sql += " Order by A.DateFrom, B.FromIndex";
using (SqlCommand cm = db.CreateCommand(sql))
{
SqlDataAdapter da = new SqlDataAdapter(cm);
da.Fill(ds);
line = rElec - 3 + j;
//Phi dien
XLCellRange mCell = new XLCellRange(line, line + 2, 1, 1);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line + 2, 2, 2);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 2, 3, 3);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 2, 4, 4);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 2, 5, 5);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 2, 7, 7);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 2, 8, 8);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 2, 9, 9);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 2, 10, 10);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 2, 11, 11);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line, 12, 13);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 2, 12, 13);
xlsSheet.MergedCells.Add(mCell);
/////En
mCell = new XLCellRange(line, line + 2, 1, 1);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line + 2, 2, 2);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 2, 3, 3);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 2, 4, 4);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 2, 5, 5);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 2, 7, 7);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 2, 8, 8);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 2, 9, 9);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 2, 10, 10);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 2, 11, 11);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line, 12, 13);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 2, 12, 13);
xlsSheetEn.MergedCells.Add(mCell);
/////En
for (int col = 1; col < 13; col++)
{
xlsSheet[line, col].Style = xlstStyleH;
xlsSheet[line + 1, col].Style = xlstStyleH;
xlsSheet[line + 2, col].Style = xlstStyleH;
xlsSheetEn[line, col].Style = xlstStyleH;
xlsSheetEn[line + 1, col].Style = xlstStyleH;
xlsSheetEn[line + 2, col].Style = xlstStyleH;
}
if (ds != null)
{
int count = 0;
DataTable dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
if (count >= 1)
{
xlsSheet.Rows.Insert(rElec + 1 + j);
xlsSheetEn.Rows.Insert(rElec + 1 + j);
j++;
}
count++;
int tmp = rElec + j;
string DateFrom = Func.ParseString(row["DateFrom"]);
string DateTo = Func.ParseString(row["DateTo"]);
string FromIndex = Func.ParseString(row["FromIndex"]);
string ToIndex = Func.ParseString(row["ToIndex"]);
string OtherFee01 = Func.ParseString(row["OtherFee01"]);
string OtherFee02 = Func.ParseString(row["OtherFee02"]);
string Mount = Func.ParseString(row["Mount"]);
string ElecPricePercent = Func.ParseString(row["ElecPricePercent"]);
xlsSheet[tmp, 1].Value = DateFrom;
xlsSheet[tmp, 2].Value = DateTo;
xlsSheet[tmp, 3].Value = FromIndex;
xlsSheet[tmp, 4].Value = ToIndex;
xlsSheet[tmp, 5].Value = OtherFee01;
xlsSheet[tmp, 6].Value = Mount;
xlsSheet[tmp, 7].Value = row["PriceVND"];
xlsSheet[tmp, 8].Value = row["VatVND"];
xlsSheet[tmp, 9].Value = row["SumVND"];
xlsSheet[tmp, 10].Value = row["OtherFee02"];
xlsSheet[tmp, 11].Value = row["ElecPricePercent"];
xlsSheet[tmp, 12].Value = row["LastPriceVND"];
mCell = new XLCellRange(tmp, tmp, 12, 13);
xlsSheet.MergedCells.Add(mCell);
/////En
xlsSheetEn[tmp, 1].Value = DateFrom;
xlsSheetEn[tmp, 2].Value = DateTo;
xlsSheetEn[tmp, 3].Value = FromIndex;
xlsSheetEn[tmp, 4].Value = ToIndex;
xlsSheetEn[tmp, 5].Value = OtherFee01;
xlsSheetEn[tmp, 6].Value = Mount;
xlsSheetEn[tmp, 7].Value = row["PriceVND"];
xlsSheetEn[tmp, 8].Value = row["VatVND"];
xlsSheetEn[tmp, 9].Value = row["SumVND"];
xlsSheetEn[tmp, 10].Value = row["OtherFee02"];
xlsSheetEn[tmp, 11].Value = row["ElecPricePercent"];
xlsSheetEn[tmp, 12].Value = row["LastPriceVND"];
mCell = new XLCellRange(tmp, tmp, 12, 13);
xlsSheetEn.MergedCells.Add(mCell);
/////En
for (int col = 1; col <= 12; col++)
{
xlsSheet[tmp, col].Style = xlstStyle;
xlsSheetEn[tmp, col].Style = xlstStyle;
}
LastSumPriceVND[4] += Convert.ToDecimal(row["LastPriceVND"]);
LastSumPriceUSD[4] += Convert.ToDecimal(row["LastPriceUSD"]);
}
xlsSheet[rElec + 1 + j, 12].Value = LastSumPriceVND[4];
mCell = new XLCellRange(rElec + 1 + j, rElec + 1 + j, 1, 11);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(rElec + 1 + j, rElec + 1 + j, 12, 13);
xlsSheet.MergedCells.Add(mCell);
xlsSheetEn[rElec + 1 + j, 12].Value = LastSumPriceVND[4];
mCell = new XLCellRange(rElec + 1 + j, rElec + 1 + j, 1, 11);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(rElec + 1 + j, rElec + 1 + j, 12, 13);
xlsSheetEn.MergedCells.Add(mCell);
for (int col = 1; col <= 13; col++)
{
xlsSheet[rElec + 1 + j, col].Style = xlstStyleSum;
xlsSheetEn[rElec + 1 + j, col].Style = xlstStyleSum;
}
sumRow += dt.Rows.Count - 1;
}
}
}
ds = new DataSet();
//Nuoc
//Xuất ra toàn bộ nội dung theo Trang
sql = " SELECT dbo.fnDateTime(A.DateFrom) DateFrom, dbo.fnDateTime(A.DateTo) DateTo, A.Vat, B.id, B.UsedElecWaterId, B.FromIndex, B.ToIndex, B.OtherFee01, B.OtherFee02, B.Mount, B.PriceVND, B.PriceUSD, B.SumVND, B.SumUSD, ";
sql += " B.VatVND, B.VatUSD,IsNull(B.LastPriceUSD,0) LastPriceUSD,IsNull(B.LastPriceVND,0) LastPriceVND, B.Name, B.WaterPricePercent,B.ElecPricePercent ";
sql += " FROM PaymentElecWater AS A INNER JOIN ";
sql += " PaymentElecWaterDetail AS B ON A.UsedElecWaterId = B.UsedElecWaterId";
sql += " WHERE A.BuildingId = '" + sBuildingId + "' and A.CustomerId = '" + CustomerId + "' and TarrifsOfElecId = 0 and A.YearMonth in (" + lsYearmonth + ")";
sql += " Order by A.DateFrom, B.FromIndex";
using (SqlCommand cm = db.CreateCommand(sql))
{
SqlDataAdapter da = new SqlDataAdapter(cm);
da.Fill(ds);
line = rWater - 3 + j;
//Phi dien
XLCellRange mCell = new XLCellRange(line, line + 2, 1, 1);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line + 2, 2, 2);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 2, 3, 3);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 2, 4, 4);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 2, 6, 6);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 2, 7, 7);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 2, 8, 8);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 2, 9, 9);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 2, 10, 10);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 2, 11, 11);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line, 12, 13);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 2, 12, 13);
xlsSheet.MergedCells.Add(mCell);
/////En
mCell = new XLCellRange(line, line + 2, 1, 1);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line + 2, 2, 2);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 2, 3, 3);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 2, 4, 4);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 2, 6, 6);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 2, 7, 7);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 2, 8, 8);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 2, 9, 9);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 2, 10, 10);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 2, 11, 11);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line, 12, 13);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 2, 12, 13);
xlsSheetEn.MergedCells.Add(mCell);
/////En
for (int col = 1; col < 13; col++)
{
xlsSheet[line, col].Style = xlstStyleH;
xlsSheet[line + 1, col].Style = xlstStyleH;
xlsSheet[line + 2, col].Style = xlstStyleH;
xlsSheetEn[line, col].Style = xlstStyleH;
xlsSheetEn[line + 1, col].Style = xlstStyleH;
xlsSheetEn[line + 2, col].Style = xlstStyleH;
}
if (ds != null)
{
int count = 0;
DataTable dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
if (count >= 1)
{
xlsSheet.Rows.Insert(rWater + 1 + j);
xlsSheetEn.Rows.Insert(rWater + 1 + j);
j++;
}
count++;
int tmp = rWater + j;
string DateFrom = Func.ParseString(row["DateFrom"]);
string DateTo = Func.ParseString(row["DateTo"]);
string FromIndex = Func.ParseString(row["FromIndex"]);
string ToIndex = Func.ParseString(row["ToIndex"]);
string OtherFee01 = Func.ParseString(row["OtherFee01"]);
string OtherFee02 = Func.ParseString(row["OtherFee02"]);
string Mount = Func.ParseString(row["Mount"]);
xlsSheet[tmp, 1].Value = DateFrom;
xlsSheet[tmp, 2].Value = DateTo;
xlsSheet[tmp, 3].Value = FromIndex;
xlsSheet[tmp, 4].Value = ToIndex;
xlsSheet[tmp, 5].Value = Mount;
xlsSheet[tmp, 6].Value = row["PriceVND"];
xlsSheet[tmp, 7].Value = row["OtherFee01"];
xlsSheet[tmp, 8].Value = row["VatVND"];
xlsSheet[tmp, 9].Value = row["SumVND"];
xlsSheet[tmp, 10].Value = row["OtherFee02"];
xlsSheet[tmp, 11].Value = row["WaterPricePercent"];
xlsSheet[tmp, 12].Value = row["LastPriceVND"];
/////En
xlsSheetEn[tmp, 1].Value = DateFrom;
xlsSheetEn[tmp, 2].Value = DateTo;
xlsSheetEn[tmp, 3].Value = FromIndex;
xlsSheetEn[tmp, 4].Value = ToIndex;
xlsSheetEn[tmp, 5].Value = Mount;
xlsSheetEn[tmp, 6].Value = row["PriceVND"];
xlsSheetEn[tmp, 7].Value = row["OtherFee01"];
xlsSheetEn[tmp, 8].Value = row["VatVND"];
xlsSheetEn[tmp, 9].Value = row["SumVND"];
xlsSheetEn[tmp, 10].Value = row["OtherFee02"];
xlsSheetEn[tmp, 11].Value = row["WaterPricePercent"];
xlsSheetEn[tmp, 12].Value = row["LastPriceVND"];
/////En
for (int col = 1; col <= 12; col++)
{
xlsSheet[tmp, col].Style = xlstStyle;
xlsSheetEn[tmp, col].Style = xlstStyle;
}
LastSumPriceVND[5] += Convert.ToDecimal(row["LastPriceVND"]);
LastSumPriceUSD[5] += Convert.ToDecimal(row["LastPriceUSD"]);
mCell = new XLCellRange(tmp, tmp, 12, 13);
xlsSheet.MergedCells.Add(mCell);
xlsSheetEn.MergedCells.Add(mCell);
}
xlsSheet[rWater + 1 + j, 12].Value = LastSumPriceVND[5];
xlsSheetEn[rWater + 1 + j, 12].Value = LastSumPriceVND[5];
mCell = new XLCellRange(rWater + 1 + j, rWater + 1 + j, 1, 11);
xlsSheet.MergedCells.Add(mCell);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(rWater + 1 + j, rWater + 1 + j, 12, 13);
xlsSheet.MergedCells.Add(mCell);
xlsSheetEn.MergedCells.Add(mCell);
for (int col = 1; col <= 13; col++)
{
xlsSheet[rWater + 1 + j, col].Style = xlstStyleSum;
xlsSheetEn[rWater + 1 + j, col].Style = xlstStyleSum;
}
sumRow += dt.Rows.Count - 1;
}
}
}
//Service
ds = new DataSet();
sql = string.Empty;
sql = " SELECT Service,dbo.fnDateTime(ServiceDateFrom) ServiceDateFrom,dbo.fnDateTime(ServiceDateTo) ServiceDateTo,PriceVND,PriceUSD,VatUSD,VatVND,Mount,Unit,SumVND,SumUSD,isnull(LastPriceVND,0) LastPriceVND,isnull(LastPriceUSD,0) LastPriceUSD";
sql += " FROM PaymentService";
sql += " WHERE BuildingId = '" + sBuildingId + "' and CustomerId = '" + CustomerId + "' and YearMonth in (" + lsYearmonth + ")";
sql += " Order By ServiceDate ";
using (SqlCommand cm = db.CreateCommand(sql))
{
SqlDataAdapter da = new SqlDataAdapter(cm);
da.Fill(ds);
line = rService - 3 + j;
//Phi khác
XLCellRange mCell = new XLCellRange(line, line + 2, 1, 1);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line + 2, 2, 2);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line + 2, 3, 3);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line + 2, 4, 4);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 2, 5, 5);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line, 6, 7);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 1, 6, 7);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line, 8, 9);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 1, 8, 9);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line, 10, 11);
xlsSheet.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 1, 10, 11);
xlsSheet.MergedCells.Add(mCell);
/////En
mCell = new XLCellRange(line, line + 2, 1, 1);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line + 2, 2, 2);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line + 2, 3, 3);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line + 2, 4, 4);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 2, 5, 5);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line, 6, 7);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 1, 6, 7);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line, 8, 9);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 1, 8, 9);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line, line, 10, 11);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 1, 10, 11);
xlsSheetEn.MergedCells.Add(mCell);
/////En
for (int col = 1; col < 13; col++)
{
xlsSheet[line, col].Style = xlstStyleH;
xlsSheet[line + 1, col].Style = xlstStyleH;
xlsSheet[line + 2, col].Style = xlstStyleH;
xlsSheetEn[line, col].Style = xlstStyleH;
xlsSheetEn[line + 1, col].Style = xlstStyleH;
xlsSheetEn[line + 2, col].Style = xlstStyleH;
}
mCell = new XLCellRange(line, line, 12, 13);
xlsSheet.MergedCells.Add(mCell);
xlsSheetEn.MergedCells.Add(mCell);
mCell = new XLCellRange(line + 1, line + 1, 12, 13);
xlsSheet.MergedCells.Add(mCell);
xlsSheetEn.MergedCells.Add(mCell);
if (ds != null)
{
int count = 0;
DataTable dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
if (count >= 1)
{
xlsSheet.Rows.Insert(rService + 1 + j);
xlsSheetEn.Rows.Insert(rService + 1 + j);
j++;
}
count++;
int tmp = rService + j;
string Service = Func.ParseString(row["Service"]);
string ServiceDateFrom = Func.ParseString(row["ServiceDateFrom"]);
string ServiceDateTo = Func.ParseString(row["ServiceDateTo"]);
string Mount = Func.ParseString(row["Mount"]);
xlsSheet[tmp, 1].Value = Service;
xlsSheet[tmp, 2].Value = Func.ParseString(row["Unit"]);
xlsSheet[tmp, 3].Value = ServiceDateFrom;
xlsSheet[tmp, 4].Value = ServiceDateTo;
xlsSheet[tmp, 5].Value = Mount;
xlsSheet[tmp, 6].Value = row["PriceUSD"];
xlsSheet[tmp, 7].Value = row["PriceVND"];
xlsSheet[tmp, 8].Value = row["SumUSD"];
xlsSheet[tmp, 9].Value = row["SumVND"];
xlsSheet[tmp, 10].Value = row["VatUSD"];
xlsSheet[tmp, 11].Value = row["VatVND"];
xlsSheet[tmp, 12].Value = row["LastPriceUSD"];
xlsSheet[tmp, 13].Value = row["LastPriceVND"];
/////En
xlsSheetEn[tmp, 1].Value = Service;
xlsSheetEn[tmp, 2].Value = Func.ParseString(row["Unit"]);
xlsSheetEn[tmp, 3].Value = ServiceDateFrom;
xlsSheetEn[tmp, 4].Value = ServiceDateTo;
xlsSheetEn[tmp, 5].Value = Mount;
xlsSheetEn[tmp, 6].Value = row["PriceUSD"];
xlsSheetEn[tmp, 7].Value = row["PriceVND"];
xlsSheetEn[tmp, 8].Value = row["SumUSD"];
xlsSheetEn[tmp, 9].Value = row["SumVND"];
xlsSheetEn[tmp, 10].Value = row["VatUSD"];
xlsSheetEn[tmp, 11].Value = row["VatVND"];
xlsSheetEn[tmp, 12].Value = row["LastPriceUSD"];
xlsSheetEn[tmp, 13].Value = row["LastPriceVND"];
/////En
for (int col = 1; col <= 13; col++)
{
xlsSheet[tmp, col].Style = xlstStyle;
xlsSheetEn[tmp, col].Style = xlstStyle;
}
LastSumPriceVND[6] += Convert.ToDecimal(row["LastPriceVND"]);
LastSumPriceUSD[6] += Convert.ToDecimal(row["LastPriceUSD"]);
}
xlsSheet[rService + 1 + j, 12].Value = LastSumPriceUSD[6];
xlsSheet[rService + 1 + j, 13].Value = LastSumPriceVND[6];
xlsSheetEn[rService + 1 + j, 12].Value = LastSumPriceUSD[6];
xlsSheetEn[rService + 1 + j, 13].Value = LastSumPriceVND[6];
mCell = new XLCellRange(rService + 1 + j, rService + 1 + j, 1, 11);
xlsSheet.MergedCells.Add(mCell);
xlsSheetEn.MergedCells.Add(mCell);
for (int col = 1; col <= 13; col++)
{
xlsSheet[rService + 1 + j, col].Style = xlstStyleSum;
xlsSheetEn[rService + 1 + j, col].Style = xlstStyleSum;
}
sumRow += dt.Rows.Count - 1;
}
}
}
//Paid
sql = "Select *";
sql += " From PaymentBillDetail";
sql += " Where BuildingId = '" + sBuildingId + "' and CustomerId = '" + CustomerId + "' and YearMonth in (" + lsYearmonth + ") and YearMonth < " + maxYearMonth + "";
string strYearMonth = "";
int lineTmp = rPaid - 2 + j;
//Paid
XLCellRange mCellTmp = new XLCellRange(lineTmp, lineTmp + 1, 1, 1);
xlsSheet.MergedCells.Add(mCellTmp);
mCellTmp = new XLCellRange(lineTmp, lineTmp, 2, 3);
xlsSheet.MergedCells.Add(mCellTmp);
mCellTmp = new XLCellRange(lineTmp, lineTmp, 4, 5);
xlsSheet.MergedCells.Add(mCellTmp);
mCellTmp = new XLCellRange(lineTmp, lineTmp, 6, 7);
xlsSheet.MergedCells.Add(mCellTmp);
mCellTmp = new XLCellRange(lineTmp, lineTmp, 8, 9);
xlsSheet.MergedCells.Add(mCellTmp);
mCellTmp = new XLCellRange(lineTmp, lineTmp, 12, 13);
xlsSheet.MergedCells.Add(mCellTmp);
/////En
mCellTmp = new XLCellRange(lineTmp, lineTmp + 1, 1, 1);
xlsSheetEn.MergedCells.Add(mCellTmp);
mCellTmp = new XLCellRange(lineTmp, lineTmp, 2, 3);
xlsSheetEn.MergedCells.Add(mCellTmp);
mCellTmp = new XLCellRange(lineTmp, lineTmp, 4, 5);
xlsSheetEn.MergedCells.Add(mCellTmp);
mCellTmp = new XLCellRange(lineTmp, lineTmp, 6, 7);
xlsSheetEn.MergedCells.Add(mCellTmp);
mCellTmp = new XLCellRange(lineTmp, lineTmp, 8, 9);
xlsSheetEn.MergedCells.Add(mCellTmp);
mCellTmp = new XLCellRange(lineTmp, lineTmp, 12, 13);
xlsSheetEn.MergedCells.Add(mCellTmp);
/////En
Hashtable rowNo = new Hashtable();
decimal[] PaidSumVND = new decimal[7] { 0, 0, 0, 0, 0, 0, 0 };
decimal[] PaidSumUSD = new decimal[7] { 0, 0, 0, 0, 0, 0, 0 };
DataTable dtPaid = DbHelper.GetDataTable(sql);
for (int i = 0; i < dtPaid.Rows.Count; i++)
{
string PaymentType = Func.ParseString(dtPaid.Rows[i]["PaymentType"]);
string MoneyUSD = Func.ParseString(dtPaid.Rows[i]["MoneyUSD"]);
string MoneyVND = Func.ParseString(dtPaid.Rows[i]["MoneyVND"]);
string PaidUSD = Func.ParseString(dtPaid.Rows[i]["PaidUSD"]);
string PaidVND = Func.ParseString(dtPaid.Rows[i]["PaidVND"]);
string ExchangeType = Func.ParseString(dtPaid.Rows[i]["ExchangeType"]);
string YearMonth = Func.ParseString(dtPaid.Rows[i]["YearMonth"]);
if (!rowNo.Contains(YearMonth))
{
if (rowNo.Count != 0)
{
xlsSheet.Rows.Insert(rPaid + j + 1);
xlsSheetEn.Rows.Insert(rPaid + j + 1);
j++;
}
rowNo.Add(YearMonth, j);
}
int m = Func.ParseInt(rowNo[YearMonth]);
strYearMonth = YearMonth;
decimal tmpUSD = Convert.ToDecimal(MoneyUSD) - Convert.ToDecimal(PaidUSD);
decimal tmpVND = Convert.ToDecimal(MoneyVND) - Convert.ToDecimal(PaidVND);
PaidPriceUSD += Convert.ToDecimal(dtPaid.Rows[i]["PaidUSD"]);
PaidPriceVND += Convert.ToDecimal(dtPaid.Rows[i]["PaidVND"]);
xlsSheet[rPaid + m, 1].Value = YearMonth.Substring(4, 2) + "/" + YearMonth.Substring(0, 4);
xlsSheetEn[rPaid + m, 1].Value = YearMonth.Substring(4, 2) + "/" + YearMonth.Substring(0, 4);
switch (PaymentType)
{
case "1":
//Rent
xlsSheet[rPaid + m, 2].Value = dtPaid.Rows[i]["PaidUSD"];
xlsSheet[rPaid + m, 3].Value = dtPaid.Rows[i]["PaidVND"];
xlsSheetEn[rPaid + m, 2].Value = dtPaid.Rows[i]["PaidUSD"];
xlsSheetEn[rPaid + m, 3].Value = dtPaid.Rows[i]["PaidVND"];
PaidSumUSD[0] += Convert.ToDecimal(dtPaid.Rows[i]["PaidUSD"]);
PaidSumVND[0] += Convert.ToDecimal(dtPaid.Rows[i]["PaidVND"]);
break;
case "2":
//Manager
xlsSheet[rPaid + m, 4].Value = dtPaid.Rows[i]["PaidUSD"];
xlsSheet[rPaid + m, 5].Value = dtPaid.Rows[i]["PaidVND"];
xlsSheetEn[rPaid + m, 4].Value = dtPaid.Rows[i]["PaidUSD"];
xlsSheetEn[rPaid + m, 5].Value = dtPaid.Rows[i]["PaidVND"];
PaidSumUSD[1] += Convert.ToDecimal(dtPaid.Rows[i]["PaidUSD"]);
PaidSumVND[1] += Convert.ToDecimal(dtPaid.Rows[i]["PaidVND"]);
break;
case "3":
//Parking
xlsSheet[rPaid + m, 6].Value = dtPaid.Rows[i]["PaidUSD"];
xlsSheet[rPaid + m, 7].Value = dtPaid.Rows[i]["PaidVND"];
xlsSheetEn[rPaid + m, 6].Value = dtPaid.Rows[i]["PaidUSD"];
xlsSheetEn[rPaid + m, 7].Value = dtPaid.Rows[i]["PaidVND"];
PaidSumUSD[2] += Convert.ToDecimal(dtPaid.Rows[i]["PaidUSD"]);
PaidSumVND[2] += Convert.ToDecimal(dtPaid.Rows[i]["PaidVND"]);
break;
case "4":
//Extra
xlsSheet[rPaid + m, 8].Value = dtPaid.Rows[i]["PaidUSD"];
xlsSheet[rPaid + m, 9].Value = dtPaid.Rows[i]["PaidVND"];
xlsSheetEn[rPaid + m, 8].Value = dtPaid.Rows[i]["PaidUSD"];
xlsSheetEn[rPaid + m, 9].Value = dtPaid.Rows[i]["PaidVND"];
PaidSumUSD[3] += Convert.ToDecimal(dtPaid.Rows[i]["PaidUSD"]);
PaidSumVND[3] += Convert.ToDecimal(dtPaid.Rows[i]["PaidVND"]);
break;
case "5":
xlsSheet[rPaid + m, 10].Value = dtPaid.Rows[i]["PaidVND"];
xlsSheetEn[rPaid + m, 10].Value = dtPaid.Rows[i]["PaidVND"];
PaidSumUSD[4] += Convert.ToDecimal(dtPaid.Rows[i]["PaidUSD"]);
PaidSumVND[4] += Convert.ToDecimal(dtPaid.Rows[i]["PaidVND"]);
break;
case "6":
xlsSheet[rPaid + m, 11].Value = dtPaid.Rows[i]["PaidVND"];
xlsSheetEn[rPaid + m, 11].Value = dtPaid.Rows[i]["PaidVND"];
PaidSumUSD[5] += Convert.ToDecimal(dtPaid.Rows[i]["PaidUSD"]);
PaidSumVND[5] += Convert.ToDecimal(dtPaid.Rows[i]["PaidVND"]);
break;
case "7":
xlsSheet[rPaid + m, 12].Value = dtPaid.Rows[i]["PaidUSD"];
xlsSheet[rPaid + m, 13].Value = dtPaid.Rows[i]["PaidVND"];
xlsSheetEn[rPaid + m, 12].Value = dtPaid.Rows[i]["PaidUSD"];
xlsSheetEn[rPaid + m, 13].Value = dtPaid.Rows[i]["PaidVND"];
break;
default:
break;
}
for (int row = rPaid + m; row <= rPaid + 1 + j; row++)
{
for (int col = 1; col <= 13; col++)
{
xlsSheet[row, col].Style = xlstStyle;
}
}
}
lineTmp = rPaid - 2 + j;
xlsSheet[lineTmp + 3, 2].Value = PaidSumUSD[0];
xlsSheet[lineTmp + 3, 3].Value = PaidSumVND[0];
xlsSheet[lineTmp + 3, 4].Value = PaidSumUSD[1];
xlsSheet[lineTmp + 3, 5].Value = PaidSumVND[1];
xlsSheet[lineTmp + 3, 6].Value = PaidSumUSD[2];
xlsSheet[lineTmp + 3, 7].Value = PaidSumVND[2];
xlsSheet[lineTmp + 3, 8].Value = PaidSumUSD[3];
xlsSheet[lineTmp + 3, 9].Value = PaidSumVND[3];
xlsSheet[lineTmp + 3, 10].Value = PaidSumVND[4];
xlsSheet[lineTmp + 3, 11].Value = PaidSumVND[5];
xlsSheet[lineTmp + 3, 12].Value = PaidSumUSD[6];
xlsSheet[lineTmp + 3, 13].Value = PaidSumVND[6];
/////En
xlsSheetEn[lineTmp + 3, 2].Value = PaidSumUSD[0];
xlsSheetEn[lineTmp + 3, 3].Value = PaidSumVND[0];
xlsSheetEn[lineTmp + 3, 4].Value = PaidSumUSD[1];
xlsSheetEn[lineTmp + 3, 5].Value = PaidSumVND[1];
xlsSheetEn[lineTmp + 3, 6].Value = PaidSumUSD[2];
xlsSheetEn[lineTmp + 3, 7].Value = PaidSumVND[2];
xlsSheetEn[lineTmp + 3, 8].Value = PaidSumUSD[3];
xlsSheetEn[lineTmp + 3, 9].Value = PaidSumVND[3];
xlsSheetEn[lineTmp + 3, 10].Value = PaidSumVND[4];
xlsSheetEn[lineTmp + 3, 11].Value = PaidSumVND[5];
xlsSheetEn[lineTmp + 3, 12].Value = PaidSumUSD[6];
xlsSheetEn[lineTmp + 3, 13].Value = PaidSumVND[6];
/////En
for (int col = 1; col <= 13; col++)
{
xlsSheet[lineTmp + 3, col].Style = xlstStyleSum;
xlsSheetEn[lineTmp + 3, col].Style = xlstStyleSum;
}
///////////////DEPT
sql = " Select *";
sql += " From v_DeptBill";
sql += " Where BuildingId = '" + sBuildingId + "' and CustomerId = '" + CustomerId + "' and YearMonth not in (" + lsYearmonth + ") and YearMonth < " + maxYearMonth + "";
sql += " And (DeptUsd <> 0 or DeptVnd <> 0)";
strYearMonth = "";
lineTmp = rDept - 2 + j;
//Paid
mCellTmp = new XLCellRange(lineTmp, lineTmp + 1, 1, 1);
xlsSheet.MergedCells.Add(mCellTmp);
mCellTmp = new XLCellRange(lineTmp, lineTmp, 2, 3);
xlsSheet.MergedCells.Add(mCellTmp);
mCellTmp = new XLCellRange(lineTmp, lineTmp, 4, 5);
xlsSheet.MergedCells.Add(mCellTmp);
mCellTmp = new XLCellRange(lineTmp, lineTmp, 6, 7);
xlsSheet.MergedCells.Add(mCellTmp);
mCellTmp = new XLCellRange(lineTmp, lineTmp, 8, 9);
xlsSheet.MergedCells.Add(mCellTmp);
mCellTmp = new XLCellRange(lineTmp, lineTmp, 12, 13);
xlsSheet.MergedCells.Add(mCellTmp);
//////En
mCellTmp = new XLCellRange(lineTmp, lineTmp + 1, 1, 1);
xlsSheetEn.MergedCells.Add(mCellTmp);
mCellTmp = new XLCellRange(lineTmp, lineTmp, 2, 3);
xlsSheetEn.MergedCells.Add(mCellTmp);
mCellTmp = new XLCellRange(lineTmp, lineTmp, 4, 5);
xlsSheetEn.MergedCells.Add(mCellTmp);
mCellTmp = new XLCellRange(lineTmp, lineTmp, 6, 7);
xlsSheetEn.MergedCells.Add(mCellTmp);
mCellTmp = new XLCellRange(lineTmp, lineTmp, 8, 9);
xlsSheetEn.MergedCells.Add(mCellTmp);
mCellTmp = new XLCellRange(lineTmp, lineTmp, 12, 13);
xlsSheetEn.MergedCells.Add(mCellTmp);
//////En
rowNo = new Hashtable();
decimal DeptPriceVND = 0;
decimal DeptPriceUSD = 0;
decimal[] DeptSumVND = new decimal[7] { 0, 0, 0, 0, 0, 0, 0 };
decimal[] DeptSumUSD = new decimal[7] { 0, 0, 0, 0, 0, 0, 0 };
DataTable dtDept = DbHelper.GetDataTable(sql);
for (int i = 0; i < dtDept.Rows.Count; i++)
{
string PaymentType = Func.ParseString(dtDept.Rows[i]["PaymentType"]);
string DeptUSD = Func.ParseString(dtDept.Rows[i]["DeptUSD"]);
string DeptVND = Func.ParseString(dtDept.Rows[i]["DeptVND"]);
string YearMonth = Func.ParseString(dtDept.Rows[i]["YearMonth"]);
if (!rowNo.Contains(YearMonth))
{
if (rowNo.Count != 0)
{
xlsSheet.Rows.Insert(rDept + j + 1);
xlsSheetEn.Rows.Insert(rDept + j + 1);
j++;
}
rowNo.Add(YearMonth, j);
}
int m = Func.ParseInt(rowNo[YearMonth]);
strYearMonth = YearMonth;
DeptPriceUSD += Convert.ToDecimal(dtDept.Rows[i]["DeptUSD"]);
DeptPriceVND += Convert.ToDecimal(dtDept.Rows[i]["DeptVND"]);
xlsSheet[rDept + m, 1].Value = YearMonth.Substring(4, 2) + "/" + YearMonth.Substring(0, 4);
xlsSheetEn[rDept + m, 1].Value = YearMonth.Substring(4, 2) + "/" + YearMonth.Substring(0, 4);
switch (PaymentType)
{
case "1":
//Rent
xlsSheet[rDept + m, 2].Value = dtDept.Rows[i]["DeptUSD"];
xlsSheet[rDept + m, 3].Value = dtDept.Rows[i]["DeptVND"];
xlsSheetEn[rDept + m, 2].Value = dtDept.Rows[i]["DeptUSD"];
xlsSheetEn[rDept + m, 3].Value = dtDept.Rows[i]["DeptVND"];
DeptSumUSD[0] += Convert.ToDecimal(dtDept.Rows[i]["DeptUSD"]);
DeptSumVND[0] += Convert.ToDecimal(dtDept.Rows[i]["DeptVND"]);
break;
case "2":
//Manager
xlsSheet[rDept + m, 4].Value = dtDept.Rows[i]["DeptUSD"];
xlsSheet[rDept + m, 5].Value = dtDept.Rows[i]["DeptVND"];
xlsSheetEn[rDept + m, 4].Value = dtDept.Rows[i]["DeptUSD"];
xlsSheetEn[rDept + m, 5].Value = dtDept.Rows[i]["DeptVND"];
DeptSumUSD[1] += Convert.ToDecimal(dtDept.Rows[i]["DeptUSD"]);
DeptSumVND[1] += Convert.ToDecimal(dtDept.Rows[i]["DeptVND"]);
break;
case "3":
//Parking
xlsSheet[rDept + m, 6].Value = dtDept.Rows[i]["DeptUSD"];
xlsSheet[rDept + m, 7].Value = dtDept.Rows[i]["DeptVND"];
xlsSheetEn[rDept + m, 6].Value = dtDept.Rows[i]["DeptUSD"];
xlsSheetEn[rDept + m, 7].Value = dtDept.Rows[i]["DeptVND"];
DeptSumUSD[2] += Convert.ToDecimal(dtDept.Rows[i]["DeptUSD"]);
DeptSumVND[2] += Convert.ToDecimal(dtDept.Rows[i]["DeptVND"]);
break;
case "4":
//Extra
xlsSheet[rDept + m, 8].Value = dtDept.Rows[i]["DeptUSD"];
xlsSheet[rDept + m, 9].Value = dtDept.Rows[i]["DeptVND"];
xlsSheetEn[rDept + m, 8].Value = dtDept.Rows[i]["DeptUSD"];
xlsSheetEn[rDept + m, 9].Value = dtDept.Rows[i]["DeptVND"];
DeptSumUSD[3] += Convert.ToDecimal(dtDept.Rows[i]["DeptUSD"]);
DeptSumVND[3] += Convert.ToDecimal(dtDept.Rows[i]["DeptVND"]);
break;
case "5":
xlsSheet[rDept + m, 10].Value = dtDept.Rows[i]["DeptVND"];
xlsSheetEn[rDept + m, 10].Value = dtDept.Rows[i]["DeptVND"];
DeptSumUSD[4] += Convert.ToDecimal(dtDept.Rows[i]["DeptUSD"]);
DeptSumVND[4] += Convert.ToDecimal(dtDept.Rows[i]["DeptVND"]);
break;
case "6":
xlsSheet[rDept + m, 11].Value = dtDept.Rows[i]["DeptVND"];
xlsSheetEn[rDept + m, 11].Value = dtDept.Rows[i]["DeptVND"];
DeptSumUSD[5] += Convert.ToDecimal(dtDept.Rows[i]["DeptUSD"]);
DeptSumVND[5] += Convert.ToDecimal(dtDept.Rows[i]["DeptVND"]);
break;
case "7":
xlsSheet[rDept + m, 12].Value = dtDept.Rows[i]["DeptUSD"];
xlsSheet[rDept + m, 13].Value = dtDept.Rows[i]["DeptVND"];
xlsSheetEn[rDept + m, 12].Value = dtDept.Rows[i]["DeptUSD"];
xlsSheetEn[rDept + m, 13].Value = dtDept.Rows[i]["DeptVND"];
DeptSumUSD[6] += Convert.ToDecimal(dtDept.Rows[i]["DeptUSD"]);
DeptSumVND[6] += Convert.ToDecimal(dtDept.Rows[i]["DeptVND"]);
break;
default:
break;
}
for (int row = rDept + m; row <= rDept + 1 + j; row++)
{
for (int col = 1; col <= 13; col++)
{
xlsSheet[row, col].Style = xlstStyle;
xlsSheetEn[row, col].Style = xlstStyle;
}
}
}
lineTmp = rDept - 2 + j;
xlsSheet[lineTmp + 3, 2].Value = DeptSumUSD[0];
xlsSheet[lineTmp + 3, 3].Value = DeptSumVND[0];
xlsSheet[lineTmp + 3, 4].Value = DeptSumUSD[1];
xlsSheet[lineTmp + 3, 5].Value = DeptSumVND[1];
xlsSheet[lineTmp + 3, 6].Value = DeptSumUSD[2];
xlsSheet[lineTmp + 3, 7].Value = DeptSumVND[2];
xlsSheet[lineTmp + 3, 8].Value = DeptSumUSD[3];
xlsSheet[lineTmp + 3, 9].Value = DeptSumVND[3];
xlsSheet[lineTmp + 3, 10].Value = DeptSumVND[4];
xlsSheet[lineTmp + 3, 11].Value = DeptSumVND[5];
xlsSheet[lineTmp + 3, 12].Value = DeptSumUSD[6];
xlsSheet[lineTmp + 3, 13].Value = DeptSumVND[6];
//////En
xlsSheetEn[lineTmp + 3, 2].Value = DeptSumUSD[0];
xlsSheetEn[lineTmp + 3, 3].Value = DeptSumVND[0];
xlsSheetEn[lineTmp + 3, 4].Value = DeptSumUSD[1];
xlsSheetEn[lineTmp + 3, 5].Value = DeptSumVND[1];
xlsSheetEn[lineTmp + 3, 6].Value = DeptSumUSD[2];
xlsSheetEn[lineTmp + 3, 7].Value = DeptSumVND[2];
xlsSheetEn[lineTmp + 3, 8].Value = DeptSumUSD[3];
xlsSheetEn[lineTmp + 3, 9].Value = DeptSumVND[3];
xlsSheetEn[lineTmp + 3, 10].Value = DeptSumVND[4];
xlsSheetEn[lineTmp + 3, 11].Value = DeptSumVND[5];
xlsSheetEn[lineTmp + 3, 12].Value = DeptSumUSD[6];
xlsSheetEn[lineTmp + 3, 13].Value = DeptSumVND[6];
//////En
for (int col = 1; col <= 13; col++)
{
xlsSheet[lineTmp + 3, col].Style = xlstStyleSum;
xlsSheetEn[lineTmp + 3, col].Style = xlstStyleSum;
}
xlsSheet[lineTmp + 3, 1].Style = xlstStyleSum;
xlsSheetEn[lineTmp + 3, 1].Style = xlstStyleSum;
decimal AllSumVND = 0;
decimal AllSumUSD = 0;
for (int i = 0; i < 7; i++)
{
AllSumVND += LastSumPriceVND[i];
AllSumUSD += LastSumPriceUSD[i];
}
AllSumVND -= PaidPriceVND;
AllSumUSD -= PaidPriceUSD;
AllSumVND += DeptPriceVND;
AllSumUSD += DeptPriceUSD;
xlsSheet[rSumVND + j, cSumVND].Value = Func.FormatNumber_New(AllSumUSD);
xlsSheet[rSumVND + j, cSumVND].Value += "(USD)";
xlsSheet[rSumVND + j, cSumVND + 1].Value = Func.FormatNumber_New(AllSumVND);
xlsSheet[rSumVND + j, cSumVND + 1].Value += "(VND)";
xlsSheetEn[rSumVND + j, cSumVND].Value = Func.FormatNumber_New(AllSumUSD);
xlsSheetEn[rSumVND + j, cSumVND].Value += "(USD)";
xlsSheetEn[rSumVND + j, cSumVND + 1].Value = Func.FormatNumber_New(AllSumVND);
xlsSheetEn[rSumVND + j, cSumVND + 1].Value += "(VND)";
AllSumVND += Convert.ToDecimal(AllSumUSD * Convert.ToDecimal(UsdExchange));
string strMoney = Func.docso(Convert.ToInt32(AllSumVND));
string strMoneyEn = Func.DocSo_En(Convert.ToInt32(AllSumVND));
xlsSheet[rContract, cContract].Value = xlsSheet[rContract, cContract].Value.ToString().Replace("{%HOP_DONG%}", String.IsNullOrEmpty(contract) ? "" : contract.Substring(1));
xlsSheet[rSum + j, cSum].Value = Convert.ToInt32(AllSumVND);
mCellTmp = new XLCellRange(rSum + j, rSum + j, cSum, cSum + 1);
xlsSheet.MergedCells.Add(mCellTmp);
xlsSheet[rSum + j, cSum].Style = xlstStyleSum;
xlsSheet[rSum + j, cSum + 1].Style = xlstStyleSum;
xlsSheet[rSumRead + j, cSumRead].Value = xlsSheet[rSumRead + j, cSumRead].Value.ToString().Replace("{%TONG_CHU%}", strMoney.ToUpper());
xlsSheetEn[rContract, cContract].Value = xlsSheetEn[rContract, cContract].Value.ToString().Replace("{%HOP_DONG%}", String.IsNullOrEmpty(contract) ? "" : contract.Substring(1));
xlsSheetEn[rSum + j, cSum].Value = Convert.ToInt32(AllSumVND);
mCellTmp = new XLCellRange(rSum + j, rSum + j, cSum, cSum + 1);
xlsSheetEn.MergedCells.Add(mCellTmp);
xlsSheetEn[rSum + j, cSum].Style = xlstStyleSum;
xlsSheetEn[rSum + j, cSum + 1].Style = xlstStyleSum;
xlsSheetEn[rSumRead + j, cSumRead].Value = xlsSheetEn[rSumRead + j, cSumRead].Value.ToString().Replace("{%TONG_CHU%}", strMoneyEn.ToUpper());
xlbBook.Save(fileNameDes);
xlbBook.Clear();
//ScriptManager.RegisterStartupScript(Page, this.GetType(), "", "PopUp('" + strFilePathExport + "'," + PopupWidth + "," + PopupHeight + ",'EditReport', true);", true);
}
}
}
}
}
