private bool Logon(string ServerName, string DatabaseName, string UserName, string Password)
{
try
{
string connectionString = "Data Source=" + ServerName + ";Initial Catalog=" + DatabaseName + ";Persist Security Info=True;User ID=" + UserName + ";Password="******"";
var con = new SqlConnection(connectionString);
con.Open();
//Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
//config.ConnectionStrings.ConnectionStrings["ketoansoft.app.Properties.Settings.dbConnectionString"].ConnectionString = connectionString;
//config.Save(ConfigurationSaveMode.Modified, true);
//ConfigurationManager.RefreshSection("connectionStrings");
ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings["ketoansoft.app.Properties.Settings.dbConnectionString"];
SqlConnectionStringBuilder builder;
dbVstoreAppDataContext db;
if (null != settings)
{
string connection = settings.ConnectionString;
builder = new SqlConnectionStringBuilder(connection);
// passwordTextBox being the control where joe the user actually enters his credentials
builder.DataSource = ServerName;
builder.InitialCatalog = DatabaseName;
builder.UserID = UserName;
builder.Password = Password;
db = new dbVstoreAppDataContext(builder.ConnectionString);
Const.builder = builder;
Const.ConnectionString = connectionString;
return true;
}
return false;
}
catch { return false; }
}
private void btnIn_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[] { new DataColumn("DTPN"), new DataColumn("TK"), new DataColumn("LOAI_CT"), new DataColumn("SO_CT"), new DataColumn("NGAY_CT"),
new DataColumn("SO_HOADON"), new DataColumn("NGAY_HOADON"),new DataColumn("TEN_KH"),new DataColumn("NGUOI_GD"), new DataColumn("MA_CTRINH"), new DataColumn("MUA"),
new DataColumn("MADM"), new DataColumn("DIENGIAI"), new DataColumn("DONVI"), new DataColumn("SOLUONG", System.Type.GetType("System.Double")), new DataColumn("DAI"),
new DataColumn("RONG"), new DataColumn("SOM2"), new DataColumn("DONGIA", System.Type.GetType("System.Double")), new DataColumn("TKDU"), new DataColumn("NO_VND",System.Type.GetType("System.Double")),
new DataColumn("NO_CUOIKY_VND", System.Type.GetType("System.Double")), new DataColumn("CO_CUOIKY_VND", System.Type.GetType("System.Double")),
new DataColumn("MAHH"), new DataColumn("TENHH"),
new DataColumn("CO_VND", System.Type.GetType("System.Double")), new DataColumn("NO_USD", System.Type.GetType("System.Double")), new DataColumn("CO_USD", System.Type.GetType("System.Double")) });
_db = new dbVstoreAppDataContext(Const.builder.ConnectionString);
var list = this._db.KT_CTuGocs.Where(u => u.NGAY_CTU.Value != null && u.NGAY_CTU.Value.Date >= dtpTuNgay.Value.Date && u.NGAY_CTU.Value.Date <= dtpDenngay.Value.Date
&& (u.TK_NO == cboTaikhoan.Text || cboTaikhoan.Text == "") && (u.MA_DTPN_NO == cboMaDT.Text || cboMaDT.Text == ""));
foreach (var item in list)
{
DataRow dr = dt.NewRow();
dr["DTPN"] = item.MA_DTPN_NO;
dr["MA_CTRINH"] = item.MA_CTRINH;
dr["TK"] = item.TK_NO;
dr["LOAI_CT"] = item.MA_CTU;
dr["SO_CT"] = item.SO_CTU;
dr["NGAY_CT"] = item.NGAY_CTU;
dr["SO_HOADON"] = item.HD_SO;
dr["NGAY_HOADON"] = item.HD_NGAY;
dr["TEN_KH"] = item.TEN_KH;
dr["NGUOI_GD"] = item.TEN_KH_GD;
dr["MADM"] = item.MA_DM_XUAT;
dr["MAHH"] = "";
dr["TENHH"] = "";
dr["DIENGIAI"] = item.DIEN_GIAI;
dr["DONVI"] = item.DON_VI1;
dr["SOLUONG"] = Utils.CDblDef(item.SO_LUONG, 0);
dr["DAI"] = "";
dr["RONG"] = "";
dr["SOM2"] = "";
dr["DONGIA"] = Utils.CDblDef(item.DON_GIA_VND, 0);
dr["TKDU"] = item.TK_CO;
dr["MUA"] = Utils.CDblDef(150000, 0);
dr["NO_CUOIKY_VND"] = Utils.CDblDef(150000, 0);
dr["CO_CUOIKY_VND"] = Utils.CDblDef(150000, 0);
dr["NO_VND"] = Utils.CDblDef(150000, 0);
dr["CO_VND"] = Utils.CDblDef(150000, 0);
dr["NO_USD"] = Utils.CDblDef(150000, 0);
dr["CO_USD"] = Utils.CDblDef(150000, 0);
dt.Rows.Add(dr);
}
ExcelUtlity Utlity = new ExcelUtlity();
if (rdMauchuan.Checked)
Utlity.WriteDataTableToExcel_SOCTCN19_01_V_Mauchuan(dt, cboTaikhoan.Text, cboMaDT.Text, dtpTuNgay.Value, dtpDenngay.Value, dtNgayin.Value);
else if (rdChitiettheosohoadon.Checked)
Utlity.WriteDataTableToExcel_SOCTCN26_Chitiettheosohoadon(dt, cboTaikhoan.Text, cboMaDT.Text, dtpTuNgay.Value, dtpDenngay.Value, dtNgayin.Value);
else if (rdChitiettheosohoadonhanghoa.Checked)
Utlity.WriteDataTableToExcel_SOCTCN27_Chitiettheosohoadonhanghoa(dt, cboTaikhoan.Text, cboMaDT.Text, dtpTuNgay.Value, dtpDenngay.Value, dtNgayin.Value);
else if (rdChitiettheohanghoa.Checked)
Utlity.WriteDataTableToExcel_SOCTCN19_04_Chitiettheohanghoa(dt, cboTaikhoan.Text, cboMaDT.Text, dtpTuNgay.Value, dtpDenngay.Value, dtNgayin.Value);
else if (rdChitiettheosohoadon2.Checked)
Utlity.WriteDataTableToExcel_SOCTCN26_Chitiettheosohoadon(dt, cboTaikhoan.Text, cboMaDT.Text, dtpTuNgay.Value, dtpDenngay.Value, dtNgayin.Value);
else
Utlity.WriteDataTableToExcel_SOCTCN19_01_V_Mauchuan(dt, cboTaikhoan.Text, cboMaDT.Text, dtpTuNgay.Value, dtpDenngay.Value, dtNgayin.Value);
}
private void btnIn_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[] { new DataColumn("DTPN"), new DataColumn("TK"), new DataColumn("LOAI_CT"), new DataColumn("SO_CT"), new DataColumn("NGAY_CT"),
new DataColumn("SO_HOADON"), new DataColumn("NGAY_HOADON"),new DataColumn("TEN_KH"),new DataColumn("NGUOI_GD"), new DataColumn("MA_CTRINH"),
new DataColumn("MADM"), new DataColumn("DIENGIAI"), new DataColumn("DONVI"), new DataColumn("SOLUONG", System.Type.GetType("System.Double")), new DataColumn("DAI"), new DataColumn("RONG"),
new DataColumn("SOM2"), new DataColumn("DONGIA", System.Type.GetType("System.Double")), new DataColumn("TKDU")
, new DataColumn("NO_VND",System.Type.GetType("System.Double")), new DataColumn("CO_VND", System.Type.GetType("System.Double")), new DataColumn("NO_USD", System.Type.GetType("System.Double")), new DataColumn("CO_USD", System.Type.GetType("System.Double")) });
_db = new dbVstoreAppDataContext(Const.builder.ConnectionString);
var list = this._db.KT_CTuGocs.Where(u => u.NGAY_CTU.Value != null && u.NGAY_CTU.Value.Date >= dtpTuNgay.Value.Date && u.NGAY_CTU.Value.Date <= dtpDenngay.Value.Date
&& (u.TK_NO == cboTaikhoan.Text || cboTaikhoan.Text == "") && (u.MA_DTPN_NO == cboMaDT.Text || cboMaDT.Text == ""));
foreach (var item in list)
{
DataRow dr = dt.NewRow();
dr["DTPN"] = item.MA_DTPN_NO;
dr["MA_CTRINH"] = item.MA_CTRINH;
dr["TK"] = item.TK_NO;
dr["LOAI_CT"] = item.MA_CTU;
dr["SO_CT"] = item.SO_CTU;
dr["NGAY_CT"] = item.NGAY_CTU;
dr["SO_HOADON"] = item.HD_SO;
dr["NGAY_HOADON"] = item.HD_NGAY;
dr["TEN_KH"] = item.TEN_KH;
dr["NGUOI_GD"] = item.TEN_KH_GD;
dr["MADM"] = item.MA_DM_XUAT;
dr["DIENGIAI"] = item.DIEN_GIAI;
dr["DONVI"] = item.DON_VI1;
dr["SOLUONG"] = Utils.CDblDef(item.SO_LUONG, 0);
dr["DAI"] = "";
dr["RONG"] = "";
dr["SOM2"] = "";
dr["DONGIA"] = Utils.CDblDef(item.DON_GIA_VND, 0);
dr["TKDU"] = item.TK_CO == cboTaikhoan.Text ? item.TK_NO : item.TK_CO;
dr["NO_VND"] = item.TK_NO == cboTaikhoan.Text ? Utils.CDblDef(item.THANH_TIEN_VND, 0) : 0;
dr["CO_VND"] = item.TK_CO == cboTaikhoan.Text ? Utils.CDblDef(item.THANH_TIEN_VND, 0) : 0;
dr["NO_USD"] = Utils.CDblDef(50000, 0);
dr["CO_USD"] = Utils.CDblDef(50000, 0);
dt.Rows.Add(dr);
}
ExcelUtlity Utlity = new ExcelUtlity();
if(rdMauchuan.Checked)
Utlity.WriteDataTableToExcel_SOCTCN11_Mauchuan(dt, cboTaikhoan.Text, cboMaDT.Text, dtpTuNgay.Value, dtpDenngay.Value, dtNgayin.Value);
else if(rdInchitietquycach.Checked)
Utlity.WriteDataTableToExcel_SOCTCN30_InChiTietQuyCach(dt, cboTaikhoan.Text, cboMaDT.Text, dtpTuNgay.Value, dtpDenngay.Value, dtNgayin.Value);
else if (rdViewtatcaDT.Checked)
Utlity.WriteDataTableToExcel_SOCTCN11_03_V_ViewtatcaDT(dt, list, dtpTuNgay.Value, dtpDenngay.Value, dtNgayin.Value);
else if (rdSubtotaltheochungtu.Checked)
Utlity.WriteDataTableToExcel_SOCTCN11_04_V_Subtotaltheochungtu(dt, list, cboTaikhoan.Text, cboMaDT.Text, dtpTuNgay.Value, dtpDenngay.Value, dtNgayin.Value);
else if (rdSubtotaltheothang.Checked)
Utlity.WriteDataTableToExcel_SOCTCN11_05_V_Subtotaltheothang(dt, cboTaikhoan.Text, cboMaDT.Text, dtpTuNgay.Value, dtpDenngay.Value, dtNgayin.Value);
else if (rdSubtotaltheocongtrinh.Checked)
Utlity.WriteDataTableToExcel_SOCTCN11_06_V_Subtotaltheocongtrinh(dt, cboTaikhoan.Text, cboMaDT.Text, dtpTuNgay.Value, dtpDenngay.Value, dtNgayin.Value);
else
Utlity.WriteDataTableToExcel_SOCTCN11_Mauchuan(dt, cboTaikhoan.Text, cboMaDT.Text, dtpTuNgay.Value, dtpDenngay.Value, dtNgayin.Value);
}
private void btnIn_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[] { new DataColumn("DTPN"), new DataColumn("TK"), new DataColumn("MA_HD"), new DataColumn("SO_CT"), new DataColumn("NGAY_CT"), new DataColumn("HD_SR"),
new DataColumn("SO_HOADON"), new DataColumn("NGAY_HOADON"),new DataColumn("TEN_KH"),new DataColumn("TEN_NV_BAN"), new DataColumn("MA_CTRINH"), new DataColumn("MASO_THUE"),
new DataColumn("MAT_HANG"), new DataColumn("TS_GTGT"), new DataColumn("DIA_CHI"), new DataColumn("SOLUONG", System.Type.GetType("System.Double")), new DataColumn("DAI"), new DataColumn("RONG"),
new DataColumn("GHI_CHU"), new DataColumn("DONGIA", System.Type.GetType("System.Double")), new DataColumn("THANH_TIEN_VND")
, new DataColumn("TIEN_THUE_VND",System.Type.GetType("System.Double")), new DataColumn("CO_VND", System.Type.GetType("System.Double")), new DataColumn("NO_USD", System.Type.GetType("System.Double")), new DataColumn("CO_USD", System.Type.GetType("System.Double")) });
_db = new dbVstoreAppDataContext(Const.builder.ConnectionString);
var list = this._db.KT_CTuGocs.Where(u => u.NGAY_CTU.Value != null && u.NGAY_CTU.Value.Date >= dtpTuNgay.Value.Date && u.NGAY_CTU.Value.Date <= dtpDenngay.Value.Date
&& (u.TK_NO == cboTaikhoan.Text || cboTaikhoan.Text == "")
&& (u.LOAI_THUE.Substring(0, 1) == "V" || u.LOAI_THUE == "NK")
&& ((rdMau3.Checked)?u.TS_GTGT == "0": true)
&& ((rdMau4.Checked) ? u.TS_GTGT == "5" : true)
&& ((rdMau5.Checked) ? u.TS_GTGT == "10" : true));
foreach (var item in list)
{
DataRow dr = dt.NewRow();
dr["DTPN"] = item.MA_DTPN_NO;
dr["MA_CTRINH"] = item.MA_CTRINH;
dr["TK"] = item.TK_NO;
dr["MA_HD"] = item.MA_HD;
dr["SO_CT"] = item.SO_CTU;
dr["NGAY_CT"] = item.NGAY_CTU;
dr["HD_SR"] = item.HD_SR;
dr["SO_HOADON"] = item.HD_SO;
dr["NGAY_HOADON"] = item.HD_NGAY;
dr["TEN_KH"] = item.TEN_KH;
dr["TEN_NV_BAN"] = item.TEN_NV_BAN;
dr["MASO_THUE"] = item.MASO_THUE;
dr["MAT_HANG"] = item.MA_DM_XUAT;
dr["TS_GTGT"] = item.TS_GTGT;
dr["DIA_CHI"] = item.DIA_CHI;
dr["SOLUONG"] = Utils.CDblDef(item.SO_LUONG, 0);
dr["DONGIA"] = Utils.CDblDef(item.DON_GIA_VND, 0);
dr["THANH_TIEN_VND"] = item.THANH_TIEN_VND;
dr["TIEN_THUE_VND"] = item.TIEN_THUE_VND;
dr["GHI_CHU"] = item.GHI_CHU;
dt.Rows.Add(dr);
}
ExcelUtlity Utlity = new ExcelUtlity();
if (rdMau1.Checked)
Utlity.WriteDataTableToExcel_THUEDV_Mau1(dt, dtpTuNgay.Value, dtpDenngay.Value, dtNgayin.Value);
if (rdMau2.Checked)
Utlity.WriteDataTableToExcel_THUEDV_Mau2(dt, dtpTuNgay.Value, dtpDenngay.Value, dtNgayin.Value);
if (rdMau3.Checked)
Utlity.WriteDataTableToExcel_THUEDV_Mau1(dt, dtpTuNgay.Value, dtpDenngay.Value, dtNgayin.Value);
if (rdMau4.Checked)
Utlity.WriteDataTableToExcel_THUEDV_Mau1(dt, dtpTuNgay.Value, dtpDenngay.Value, dtNgayin.Value);
if (rdMau5.Checked)
Utlity.WriteDataTableToExcel_THUEDV_Mau1(dt, dtpTuNgay.Value, dtpDenngay.Value, dtNgayin.Value);
else
Utlity.WriteDataTableToExcel_THUEDV_Mau1(dt, dtpTuNgay.Value, dtpDenngay.Value, dtNgayin.Value);
}
private void btnIn_Click(object sender, EventArgs e)
{
string tencongty = "CÔNG TY ABCDab";
string diachi = "M17 LÊ HOÀNG PHÁI,GÒ VẤP,TP.HCM";
string masothue = "Mã số thuế : 0300688235";
string tungaydenngay = "Từ ngày 01/01/08 đến ngày 31/01/08";
string ngayin = "Ngày 01 tháng 01 năm 2015";
string taikhoan = "Tài khoản : 1112 - - Ngoại tệ";
string dtpn = "Mã ĐTPN : - ";
_db = new dbVstoreAppDataContext(Const.builder.ConnectionString);
var list = this._db.KT_CTuGocs.Where(u => u.NGAY_CTU.Value != null && u.NGAY_CTU.Value.Date >= dtpTuNgay.Value.Date && u.NGAY_CTU.Value.Date <= dtpDenngay.Value.Date
&& (u.TK_NO == cboTaikhoan.Text || cboTaikhoan.Text == "") && (u.MA_DTPN_NO == cboMaDT.Text || cboMaDT.Text == ""));
ExcelUtlity Utlity = new ExcelUtlity();
if (rdMau1.Checked)
{
SOCTCN14_Mau1 report = new SOCTCN14_Mau1();
report.lbTencongty.Text = tencongty;
report.lbDiachi.Text = diachi;
report.lbMasothue.Text = masothue;
report.lbTungaydenngay.Text = tungaydenngay;
report.lbNgayin.Text = ngayin;
report.lbTaikhoan.Text = taikhoan;
report.lbDtpn.Text = dtpn;
report.DataSource = list;
report.xrLoaiCT.DataBindings.Add("Text", list, "MA_CTU");
report.xrSoCT.DataBindings.Add("Text", list, "SO_CTU");
report.xrNgayCT.DataBindings.Add("Text", list, "NGAY_CTU", "{0:dd/MM/yyyy}");
report.xrSoHD.DataBindings.Add("Text", list, "HD_SO");
report.xrNgayHD.DataBindings.Add("Text", list, "HD_NGAY", "{0:dd/MM/yyyy}");
report.xrDiengiai.DataBindings.Add("Text", list, "DIEN_GIAI");
report.xrTenhanghoa.DataBindings.Add("Text", list, "TEN_HH_IN");
report.xrTKDU.DataBindings.Add("Text", list, "TK_CO");
//report.xrPhatsinhtrongkyNo.DataBindings.Add("Text", list, "HD_NGAY", "{0:###,###}");
//report.xrPhatsinhtrongkyCo.DataBindings.Add("Text", list, "HD_NGAY", "{0:###,###}");
//report.xrSoducuoikyNo.DataBindings.Add("Text", list, "HD_NGAY", "{0:###,###}");
//report.xrSoducuoikyCo.DataBindings.Add("Text", list, "HD_NGAY", "{0:###,###}");
report.ShowPreview();
}
else if (rdMau2.Checked)
{
SOCTCN14_Mau2 report = new SOCTCN14_Mau2();
report.lbTencongty.Text = tencongty;
report.lbDiachi.Text = diachi;
report.lbMasothue.Text = masothue;
report.lbTungaydenngay.Text = tungaydenngay;
report.lbNgayin.Text = ngayin;
report.lbTaikhoan.Text = taikhoan;
report.lbDtpn.Text = dtpn;
report.DataSource = list;
report.xrNgay.DataBindings.Add("Text", list, "NGAY_CTU");
report.xrTensanpham.DataBindings.Add("Text", list, "TEN_THANH_PHAM");
//report.xrNgayCT.DataBindings.Add("Text", list, "NGAY_CTU", "{0:dd/MM/yyyy}");
//report.xrSoHD.DataBindings.Add("Text", list, "HD_SO");
//report.xrNgayHD.DataBindings.Add("Text", list, "HD_NGAY", "{0:dd/MM/yyyy}");
//report.xrDiengiai.DataBindings.Add("Text", list, "DIEN_GIAI");
//report.xrTenhanghoa.DataBindings.Add("Text", list, "TEN_HH_IN");
//report.xrTKDU.DataBindings.Add("Text", list, "TK_CO");
//report.xrPhatsinhtrongkyNo.DataBindings.Add("Text", list, "HD_NGAY", "{0:###,###}");
//report.xrPhatsinhtrongkyCo.DataBindings.Add("Text", list, "HD_NGAY", "{0:###,###}");
//report.xrSoducuoikyNo.DataBindings.Add("Text", list, "HD_NGAY", "{0:###,###}");
//report.xrSoducuoikyCo.DataBindings.Add("Text", list, "HD_NGAY", "{0:###,###}");
report.ShowPreview();
}
}