private void btnOK_Click(object sender, EventArgs e)
{
if (lstDaiLy.CheckedItems.Count == 0)
{
XuLyGiaoDien.Alert("Vui lòng nhập đầy đủ thông tin", Form_Alert.enmType.Info);
}
else
{
if (!XuLyGiaoDien.wait.IsSplashFormVisible)
{
XuLyGiaoDien.wait.ShowWaitForm();
}
//simpleButton1.Text = "Cần chạy lại: " + daiLyD.DemNgayPhiSai().ToString();
D_GIAODICH gdb = new D_GIAODICH();
string daily = string.Format("{0}", lstDaiLy.CheckedItems[0]);
int n = lstDaiLy.CheckedItems.Count;
for (int i = 1; i < n; i++)
{
daily += string.Format(",{0}", lstDaiLy.CheckedItems[i]);
}
giaoDichOBindingSource.DataSource = gdb.LayDanhSachCN((DateTime)dtpTuNgay.EditValue, (DateTime)dtpDenNgay.EditValue, daily);
if (giaoDichOBindingSource.DataSource != null)
{
btnGuiMail.Enabled = true;
btnExcel.Enabled = true;
}
if (XuLyGiaoDien.wait.IsSplashFormVisible)
{
XuLyGiaoDien.wait.CloseWaitForm();
}
}
}
private void groupControl3_CustomButtonClick(object sender, DevExpress.XtraBars.Docking2010.BaseButtonEventArgs e)
{
D_GIAODICH giaoDichD = new D_GIAODICH();
List <Dictionary <string, object> > lstDicS = new List <Dictionary <string, object> >();
List <string> lstTb = new List <string>();
List <string> lstCTV = new List <string>();
List <string> lstThem = new List <string>();
lstThem.Add("S");
lstThem.Add("S");
lstThem.Add("T");
lstTb.Add("GIAODICH");
lstTb.Add("GIAODICH");
lstTb.Add("LS_GIAODICH");
if (!XuLyGiaoDien.wait.IsSplashFormVisible)
{
XuLyGiaoDien.wait.ShowWaitForm();
}
List <O_GIAODICH> sss = lstGDR4.Where(w => w.GhiChu.Contains("Khác kì") || w.GhiChu.Contains("Khác ngày")).ToList();
for (int i = 0; i < sss.Count(); i += 2)
{
XuLyGiaoDien.wait.SetWaitFormDescription(string.Format("Đang chỉnh ngày GD {0}/{1}", i, sss.Count()));
Thread.Sleep(10);
O_GIAODICH gd = sss[i];
O_GIAODICH gd2 = sss[i + 1];
Dictionary <string, object> dicS = new Dictionary <string, object>();
Dictionary <string, object> dicS3 = new Dictionary <string, object>();
dicS3.Add("NgayGD", gd2.NgayGD);
lstCTV.Add(string.Format("WHERE ID = {0}", gd2.ID));
lstDicS.Add(dicS3);
dicS.Add("NgayGD", gd.NgayGD);
lstCTV.Add(string.Format("WHERE ID = {0}", gd.ID));
lstDicS.Add(dicS);
Dictionary <string, object> dic = new Dictionary <string, object>();
dic.Add("FormName", "Tự động");
dic.Add("MaCho", gd.SoVeVN);
dic.Add("NoiDung", "[Vé hoàn]: Chỉnh ngày bằng so sánh VN");
dic.Add("NVGiaoDich", DuLieuTaoSan.NV.ID);
dic.Add("LoaiKhachHang", 0);
dic.Add("Ma", 0);
lstCTV.Add(string.Empty);
lstDicS.Add(dic);
if (lstDicS.Count == 3)
{
giaoDichD.CapNhat_ThemNhieu(lstDicS, lstTb, lstCTV, lstThem);
lstDicS.Clear();
lstCTV.Clear();
}
}
XuLyGiaoDien.ExportExcel(gridControl4, gridView8, "ExHoan-" + DateTime.Now.ToString("dd-MM-yyy"));
if (XuLyGiaoDien.wait.IsSplashFormVisible)
{
XuLyGiaoDien.wait.CloseWaitForm();
}
}
public void NapDatCho()
{
if (!XuLyGiaoDien.wait.IsSplashFormVisible)
{
XuLyGiaoDien.wait.ShowWaitForm();
}
string query = "LoaiGiaoDich in (0,1,5,6,7,10,11,12)";
if (query.Length > 0)
{
if (chk2.Checked)
{
if (bdtpTu.EditValue != null && bdtpDen.EditValue != null)
{
query = string.Format("({2}) AND (convert(date, NgayGD) BETWEEN '{0}' AND '{1}')", ((DateTime)bdtpTu.EditValue).ToString("yyyyMMdd"), ((DateTime)bdtpDen.EditValue).ToString("yyyyMMdd"), query);
lstGDDC = new D_GIAODICH().DuLieu(query, DuLieuTaoSan.Q.VeAdmin);
}
}
else if (chk1.Checked)
{
query = string.Format("({0}) {1}", query, DuLieuTaoSan.ThoiGianRutGon("NgayGD")[idThoiGian]);
lstGDDC = new D_GIAODICH().DuLieu(query, DuLieuTaoSan.Q.VeAdmin);
}
giaoDichOBindingSource.DataSource = lstGDDC;
}
if (XuLyGiaoDien.wait.IsSplashFormVisible)
{
XuLyGiaoDien.wait.CloseWaitForm();
}
}
private void btnExcel_Click(object sender, EventArgs e)
{
XtraFolderBrowserDialog fbd = new XtraFolderBrowserDialog();
if (fbd.ShowDialog() == DialogResult.OK)
{
D_GIAODICH gdb = new D_GIAODICH();
bool isok = false;
int n = lstDaiLy.CheckedItems.Count;
for (int i = 0; i < n; i++)
{
O_DAILY dl = lstDaiLy.GetItem(lstDaiLy.CheckedIndices[0]) as O_DAILY;
string daily = string.Format("{0}", dl.ID);
List <O_GIAODICH> lstCongNo = gdb.LayDanhSachCN((DateTime)dtpTuNgay.EditValue, (DateTime)dtpDenNgay.EditValue, daily, true);
if (lstCongNo.Count > 1)
{
gridCTCongNo.Refresh();
isok = true;
CTGiaoDichDindingSource.DataSource = lstCongNo;
// Xuất excel
string strFile = fbd.SelectedPath + @"\" + dl.Ten + " - " + ((DateTime)dtpTuNgay.EditValue).ToString("dd_MM_yyyy") + " - " + ((DateTime)dtpDenNgay.EditValue).ToString("dd_MM_yyyy") + ".xlsx";
XlsxExportOptionsEx opt = new XlsxExportOptionsEx();
opt.CustomizeCell += op_CustomizeCell;
opt.SheetName = "Bản Công Nợ";
gridCTCongNo.ForceInitialize();
grvCTCongNo.Columns["LuyKe"].Width = 90;
grvCTCongNo.Columns["GiaThu"].Width = 90;
grvCTCongNo.Columns["GiaHeThong"].Width = 90;
grvCTCongNo.Columns["TaiKhoanCo"].Width = 90;
grvCTCongNo.Columns["TenKhach"].Width = 400;
grvCTCongNo.OptionsPrint.AutoWidth = false;
grvCTCongNo.OptionsView.ColumnAutoWidth = false;
gridCTCongNo.ExportToXlsx(strFile, opt);
int index = lstDaiLy.FindItem(0, true, delegate(ListBoxFindItemArgs ei) { ei.IsFound = object.Equals(dl.ID, ei.ItemValue); });
lstDaiLy.SetItemChecked(index, false);
}
}
if (isok)
{
XuLyGiaoDien.Alert("Xuất file thành công!", Form_Alert.enmType.Success);
}
else
{
XuLyGiaoDien.Alert("Không có thông tin", Form_Alert.enmType.Info);
}
}
}
private void repositoryItemButtonEdit1_Click(object sender, EventArgs e)
{
O_BAOCAOCTNH c = (gridView2.GetRow(gridView2.GetSelectedRows()[0]) as O_BAOCAOCTNH);
switch (c.LoaiGiaoDich)
{
case 1:
case 2:
List <O_KHACHSAN> ks = new D_KHACHSAN().DuLieu_ID(c.IDGDLienKet);
new frmKhachSanThem(ks).ShowDialog(this);
break;
case 3:
case 0:
List <O_GIAODICH> gd = new D_GIAODICH().DuLieu_ID(c.IDGDLienKet);
new frmVeThem(gd).ShowDialog(this);
break;
}
}
private void btnCH_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
List <O_GIAODICH> l1 = new D_GIAODICH().GDRutGon(_NCCO.ID, ((DateTime)eDate1.EditValue), ((DateTime)eDate2.EditValue), false) as List <O_GIAODICH>;
List <O_GIAODICH> l2 = new D_GIAODICH().GDRutGon(_NCCO.ID, ((DateTime)eDate1.EditValue), ((DateTime)eDate2.EditValue), true) as List <O_GIAODICH>;
if (!chkMC.Checked)
{
for (int i = 0; i < l1.Count; i++)
{
l1[i].MaCho = l1[i].SoVeVN;
}
for (int i = 0; i < l2.Count; i++)
{
l2[i].MaCho = l2[i].SoVeVN;
}
}
GCVTCT.DataSource = l1;
GVVTCT.BestFitColumns();
GCVHCT.DataSource = l2;
GVVHCT.BestFitColumns();
}
public void DuLieu()
{
if (!XuLyGiaoDien.wait.IsSplashFormVisible)
{
XuLyGiaoDien.wait.ShowWaitForm();
}
string _Query = string.Empty;
if (chk1.Checked)
{
_Query = DuLieuTaoSan.ThoiGianRutGon("NgayGD")[_IDThoiGian];
}
else if (chk2.Checked)
{
if (bdtpTu.EditValue != null && bdtpDen.EditValue != null)
{
_Query = string.Format("AND (convert(date, NgayGD) BETWEEN '{0}' AND '{1}')", ((DateTime)bdtpTu.EditValue).ToString("yyyyMMdd"), ((DateTime)bdtpDen.EditValue).ToString("yyyyMMdd"));
}
}
else if (chk3.Checked && _SV_MC.Length > 0)
{
_Query = string.Format("AND REPLACE(COALESCE(SoVeVN,''),' ','') in ('{0}')", String.Join("' ,'", _SV_MC));
}
else if (chk4.Checked && _SV_MC.Length > 0)
{
_Query = string.Format("AND REPLACE(COALESCE(MaCho,''),' ','') in ('{0}')", String.Join("' ,'", _SV_MC));
}
if (_Query != string.Empty)
{
_ListGiaoDichO = new D_GIAODICH().DuLieu(string.Format("TheoDoi = 1 AND ({0}{1})", _Query.Substring(3), chkAll.Checked ? " or TinhCongNo = 0" : string.Empty), DuLieuTaoSan.Q.VeAdmin);
giaoDichOBindingSource.DataSource = _ListGiaoDichO;
}
if (XuLyGiaoDien.wait.IsSplashFormVisible)
{
XuLyGiaoDien.wait.CloseWaitForm();
}
}
private void simpleButton1_Click(object sender, EventArgs e)
{
D_GIAODICH nhb = new D_GIAODICH();
switch (iLoaiGiaoDich.EditValue.ToString())
{
case "1":
case "0":
if (!DuLieuTaoSan.Q.KhacThemSua)
{
return;
}
break;
default:
if (!DuLieuTaoSan.Q.Lv2KhacAdmin)
{
return;
}
break;
}
List <KiemTra> kiemTras = new List <KiemTra>()
{
new KiemTra()
{
_Control = iIDKhachHang, _Tu = 2, _Den = 50
},
new KiemTra()
{
_Control = iLoaiKhachHang, _Tu = 2, _Den = 50
},
new KiemTra()
{
_Control = iLoaiGiaoDich, _Tu = 2, _Den = 50
},
new KiemTra()
{
_Control = iTenKhach, _Tu = 2, _Den = 50
},
new KiemTra()
{
_Control = iMaCho, _Tu = 2, _Den = 50, _ChoQuaThang = !(new List <int>()
{
1, 0
}).Contains((int)iLoaiGiaoDich.EditValue)
}
};
XuLyGiaoDien.KiemTra(kiemTras, dxValidationProvider1);
if (!dxValidationProvider1.Validate())
{
XuLyGiaoDien.Alert("Thông tin không hợp lệ", Form_Alert.enmType.Warning);
return;
}
#region Lưu
Dictionary <string, object> dic = new Dictionary <string, object>();
dic = XuLyDuLieu.FormToDictionary(this, dic);
dic.Add("NgayCuonChieu", "getdate()");
dic.Add("NVGiaoDich", DuLieuTaoSan.NV.ID);
dic.Add("CoDinh", 1);
if ((new List <int>()
{
7, 11, 12
}).Contains((int)iLoaiGiaoDich.EditValue))
{
dic.Remove("GiaThu");
dic.Add("GiaHeThong", 0);
dic.Add("GiaThu", 0);
dic.Add("GiaHoan", iGiaThu.Value);
}
else
{
dic.Add("GiaHeThong", iGiaThu.Value);
}
if (XuLyGiaoDien.ThongBao(Text, Gd.ID != 0 ? (nhb.CapNhat(dic, Gd.ID) > 0) : (nhb.ThemMoi(dic) > 0)))
{
(Owner.ActiveMdiChild as frmTongHop).NapDatCho();
GhiChuCmt(Gd.ID);
Close();
}
#endregion
}
void DieuChinh(string azs = "GiaNet")
{
if (bdtpTu.EditValue == null || bdtpDen.EditValue == null)
{
return;
}
DataTable dt = new DataTable();
for (int i = 0; i < GVTK.Bands.Count; i++)
{
if (!GVTK.Bands[i].Name.Equals("Gb1"))
{
GVTK.Bands.Remove(GVTK.Bands[i]); i--;
}
}
for (int i = 0; i < GVTK.Columns.Count; i++)
{
if (!"colDaiLy, colHang, colSale".Contains(GVTK.Columns[i].Name))
{
GVTK.GroupSummary.Remove(GVTK.GroupSummary[i - 3]);
GVTK.Columns.Remove(GVTK.Columns[i]);
}
}
DateTime startDate = ((DateTime)bdtpTu.EditValue).Date;
DateTime endDate = ((DateTime)bdtpDen.EditValue).Date;
var dates = Enumerable.Range(0, (int)(endDate - startDate).TotalDays + 1)
.Select(x => startDate.AddDays(x))
.ToList();
List <DateTime> Ngay = dates.ToList();
List <string> Nam;
List <string> Quy;
List <string> Thang;
List <string> Tuan;
List <string> ListTenCot = new List <string>();
string TenCot = string.Empty;
CultureInfo cul = CultureInfo.GetCultureInfo("vi-VN");
Nam = Ngay.Select(w => w.Year.ToString()).Distinct().AsEnumerable().Reverse().ToList();
Quy = Ngay.Select(w => (1 + (w.Month - 1) / 3) + "/" + w.Year).Distinct().AsEnumerable().Reverse().ToList();
Thang = Ngay.Select(w => w.Month + "/" + (1 + (w.Month - 1) / 3) + "/" + w.Year).Distinct().AsEnumerable().Reverse().ToList();
Tuan = Ngay.Select(w => cul.Calendar.GetWeekOfYear(w, CalendarWeekRule.FirstDay, DayOfWeek.Monday) + "/" + w.Year).Distinct().AsEnumerable().Reverse().ToList();
string TenCotTao = string.Empty;
string TenCotTao2 = string.Empty;
switch (_IDThoiGian)
{
case 0:
TenCotTao = "datepart(YEAR, NgayGD)";
TenCotTao2 = "datepart(YEAR, NgayGD)";
GridBand gridBand = new GridBand()
{
Caption = "Năm", Name = "Gb2", VisibleIndex = 1, Width = 225
};
GVTK.Bands.Add(gridBand);
foreach (string a in Nam)
{
TenCot = a;
ListTenCot.Add(TenCot);
dt.Columns.Add(TenCot, typeof(long));
BandedGridColumn bandedGridColumn = new BandedGridColumn()
{
Caption = $"-{a}-", FieldName = TenCot, Name = $"col{a}", Visible = true, Width = 50
};
bandedGridColumn.DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
bandedGridColumn.OptionsColumn.AllowMerge = DevExpress.Utils.DefaultBoolean.False;
bandedGridColumn.DisplayFormat.FormatString = "{0:#,##0;(#,##0)}";
bandedGridColumn.Summary.AddRange(new GridSummaryItem[] { new GridColumnSummaryItem(DevExpress.Data.SummaryItemType.Sum, TenCot, "{0:#,##0;(#,##0)}") });
gridBand.Columns.Add(bandedGridColumn);
}
break;
case 1:
foreach (string a in Nam)
{
TenCotTao = "cast(datepart(QUARTER, NgayGD) as varchar)+cast(datepart(YEAR, NgayGD) as varchar)";
TenCotTao2 = "datepart(YEAR, NgayGD), datepart(QUARTER, NgayGD)";
GridBand gridBand1 = new GridBand()
{
Caption = $"-{a}-", Name = $"Gb{a}", VisibleIndex = 1, Width = 225
};
GVTK.Bands.Add(gridBand1);
List <string> _Quy = Quy.Where(w => w.Split('/')[1].Contains(a)).ToList();
foreach (string b in _Quy)
{
TenCot = b.Replace("/", string.Empty);
ListTenCot.Add(TenCot);
dt.Columns.Add(TenCot, typeof(long));
BandedGridColumn bandedGridColumn = new BandedGridColumn()
{
Caption = $"Quý {b.Split('/')[0]}", FieldName = TenCot, Name = $"col{b.Replace("/", string.Empty)}", Visible = true, Width = 50
};
bandedGridColumn.DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
bandedGridColumn.OptionsColumn.AllowMerge = DevExpress.Utils.DefaultBoolean.False;
bandedGridColumn.DisplayFormat.FormatString = "{0:#,##0;(#,##0)}";
bandedGridColumn.Summary.AddRange(new GridSummaryItem[] { new GridColumnSummaryItem(DevExpress.Data.SummaryItemType.Sum, TenCot, "{0:#,##0;(#,##0)}") });
gridBand1.Columns.Add(bandedGridColumn);
}
}
break;
case 2:
foreach (string a in Nam)
{
TenCotTao = "cast(datepart(MONTH, NgayGD) as varchar)+cast(datepart(YEAR, NgayGD) as varchar)";
TenCotTao2 = "datepart(YEAR, NgayGD), datepart(MONTH, NgayGD)";
GridBand gridBand1 = new GridBand()
{
Caption = $"-{a}-", Name = $"Gb{a}", VisibleIndex = 1, Width = 225
};
GVTK.Bands.Add(gridBand1);
List <string> _Quy = Quy.Where(w => w.Contains(a)).ToList();
foreach (string b in _Quy)
{
GridBand gridBand2 = new GridBand()
{
Caption = $"Quý {b.Split('/')[0]}", Name = $"Gb{a}", VisibleIndex = 1, Width = 225
};
gridBand1.Children.Add(gridBand2);
List <string> _Thang = Thang.Where(w => w.Split('/')[2].Equals(a) && w.EndsWith(b)).ToList();
foreach (string c in _Thang)
{
TenCot = c.Replace($"/{b}", string.Empty) + a;
ListTenCot.Add(TenCot);
dt.Columns.Add(TenCot, typeof(long));
BandedGridColumn bandedGridColumn = new BandedGridColumn()
{
Caption = $"Tháng {c.Split('/')[0]}", FieldName = TenCot, Name = $"col{c.Replace("/", string.Empty)}", Visible = true, Width = 50
};
bandedGridColumn.DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
bandedGridColumn.OptionsColumn.AllowMerge = DevExpress.Utils.DefaultBoolean.False;
bandedGridColumn.DisplayFormat.FormatString = "{0:#,##0;(#,##0)}";
bandedGridColumn.Summary.AddRange(new GridSummaryItem[] { new GridColumnSummaryItem(DevExpress.Data.SummaryItemType.Sum, TenCot, "{0:#,##0;(#,##0)}") });
gridBand2.Columns.Add(bandedGridColumn);
GVTK.GroupSummary.Add(new GridGroupSummaryItem(DevExpress.Data.SummaryItemType.Sum, bandedGridColumn.FieldName, bandedGridColumn, "{0:#,##0;(#,##0)}"));
}
}
}
break;
case 3:
foreach (string a in Nam)
{
TenCotTao = "cast(datepart(WEEK, NgayGD) as varchar)+cast(datepart(YEAR, NgayGD) as varchar)";
TenCotTao2 = "datepart(YEAR, NgayGD), datepart(WEEK, NgayGD)";
GridBand gridBand1 = new GridBand()
{
Caption = $"-{a}-", Name = $"Gb{a}", VisibleIndex = 1, Width = 225
};
GVTK.Bands.Add(gridBand1);
List <string> _Tuan = Tuan.Where(w => w.Split('/')[1].Contains(a)).ToList();
foreach (string b in _Tuan)
{
TenCot = b.Replace("/", string.Empty);
ListTenCot.Add(TenCot);
dt.Columns.Add(TenCot, typeof(long));
BandedGridColumn bandedGridColumn = new BandedGridColumn()
{
Caption = $"Tuần {b.Split('/')[0]}", FieldName = TenCot, Name = $"col{b.Replace("/", string.Empty)}", Visible = true, Width = 50
};
bandedGridColumn.DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
bandedGridColumn.OptionsColumn.AllowMerge = DevExpress.Utils.DefaultBoolean.False;
bandedGridColumn.DisplayFormat.FormatString = "{0:#,##0;(#,##0)}";
bandedGridColumn.Summary.AddRange(new GridSummaryItem[] { new GridColumnSummaryItem(DevExpress.Data.SummaryItemType.Sum, TenCot, "{0:#,##0;(#,##0)}") });
gridBand1.Columns.Add(bandedGridColumn);
}
}
break;
}
string az = "";
if (barCheckItem2.Checked)
{
colHang.Visible = false;
az = string.Format(@"
SET DATEFIRST 1
select * from (
select IDKhachHang,dl.NVGiaoDich,{0} 'CotMoc',SUM({5}) Gia from GIAODICH
left join (select ID MaIDDL,NVGiaoDich from DAILY where LoaiKhachHang = 1) dl on IDKhachHang = MaIDDL where MaIDDL is not null and LoaiGiaoDich in ({6}) and CONVERT(date,NgayGD) between '{2}' and '{3}' group by {1} ,IDKhachHang ,dl.NVGiaoDich
) Y pivot (max(Gia) for CotMoc in ([{4}])) as pv
order by IDKhachHang", TenCotTao, TenCotTao2, startDate.ToString("yyyyMMdd"), endDate.ToString("yyyyMMdd"), String.Join("],[", ListTenCot.ToArray()), azs, azs == "GiaNet" ? "4" : "9");
}
else
{
colHang.Visible = true;
az = string.Format(@"
SET DATEFIRST 1
select * from (
select IDKhachHang,dl.NVGiaoDich,NhaCungCap,{0} 'CotMoc',SUM({5}) Gia from GIAODICH
left join (select ID MaIDDL,NVGiaoDich from DAILY where LoaiKhachHang = 1) dl on IDKhachHang = MaIDDL where MaIDDL is not null and LoaiGiaoDich in ({6}) and CONVERT(date,NgayGD) between '{2}' and '{3}' and coalesce(NhaCungCap,0)>0 group by {1} ,IDKhachHang,NhaCungCap ,dl.NVGiaoDich
) Y pivot (max(Gia) for CotMoc in ([{4}])) as pv
order by IDKhachHang,NhaCungCap", TenCotTao, TenCotTao2, startDate.ToString("yyyyMMdd"), endDate.ToString("yyyyMMdd"), String.Join("],[", ListTenCot.ToArray()), azs, azs == "GiaNet" ? "4" : "9");
}
dt = new D_GIAODICH().LayDataTable(az);
GCTK.DataSource = dt;
GVTK.BestFitColumns();
}
private void btnGuiMail_Click(object sender, EventArgs e)
{
if (XtraMessageBox.Show("Bạn muốn gửi mail ?", "Câu hỏi", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
D_GIAODICH gdb = new D_GIAODICH();
List <string> vs = new List <string>();
if ((txtFileDinhKem.EditValue ?? string.Empty) != string.Empty)
{
vs = txtFileDinhKem.EditValue.ToString().Split(',').ToList();
}
int n = lstDaiLy.CheckedItems.Count;
if (n == 0)
{
XuLyGiaoDien.Alert("Chưa chọn đại lý cần gửi", Form_Alert.enmType.Info);
}
else
{
try
{
O_CAUHINHSMTP cauHinhSMTPO = cauHinhSMTPD.DuLieu();
O_MAUEMAIL ma = new D_MAUEMAIL().DuLieu()[0];
SmtpClient client = new SmtpClient();
client.Port = cauHinhSMTPO.Port;
client.Host = cauHinhSMTPO.Host;
client.EnableSsl = cauHinhSMTPO.SSL;
client.Timeout = 10000;
client.DeliveryMethod = SmtpDeliveryMethod.Network;
client.UseDefaultCredentials = false;
client.Credentials = new System.Net.NetworkCredential(cauHinhSMTPO.Email, cauHinhSMTPO.Password);
grvCTCongNo.Columns["Hang"].Width = 50;
grvCTCongNo.Columns["SoVeVN"].Width = 90;
grvCTCongNo.Columns["LuyKe"].Width = 90;
grvCTCongNo.Columns["GiaThu"].Width = 90;
grvCTCongNo.Columns["GiaHeThong"].Width = 90;
grvCTCongNo.Columns["TaiKhoanCo"].Width = 90;
grvCTCongNo.Columns["TenKhach"].Width = 250;
grvCTCongNo.OptionsPrint.AutoWidth = false;
grvCTCongNo.OptionsView.ColumnAutoWidth = false;
XlsxExportOptionsEx opt = new XlsxExportOptionsEx();
opt.CustomizeCell += op_CustomizeCell;
opt.SheetName = "Bản Công Nợ";
bool sendOK = false;
if (!XuLyGiaoDien.wait.IsSplashFormVisible)
{
XuLyGiaoDien.wait.ShowWaitForm();
}
for (int i = 0; i < n; i++)
{
O_DAILY dl = lstDaiLy.GetItem(lstDaiLy.CheckedIndices[0]) as O_DAILY;
if (!dl.GuiMail)
{
int index1 = lstDaiLy.FindItem(0, true, delegate(ListBoxFindItemArgs ei) { ei.IsFound = object.Equals(dl.ID, ei.ItemValue); });
lstDaiLy.SetItemChecked(index1, false);
continue;
}
lstInt.Add(dl.ID);
string daily = string.Format("{0}", dl.ID);
List <O_GIAODICH> lstCongNo = gdb.LayDanhSachCN((DateTime)dtpTuNgay.EditValue, (DateTime)dtpDenNgay.EditValue, daily, true);
txtMauEmail.HtmlText = ma.NoiDung.Replace("{0}", dl.MaDL).Replace("{1}", XuLyDuLieu.NotVietKey(dl.Ten));
if ((dl.EmailKeToan ?? string.Empty) == string.Empty)
{
goto RE1;
}
string[] EmailKeToanString = Regex.Replace(dl.EmailKeToan, @"\t|\n|\r", "|").Replace("||", "|").Split('|');
for (int ii = 0; ii < EmailKeToanString.Count(); ii++)
{
if (EmailKeToanString[ii].Length > 5)
{
MailMessage mm = new MailMessage();
mm.From = new MailAddress("*****@*****.**", "Thành Hoàng");
mm.BodyEncoding = UTF8Encoding.UTF8;
mm.DeliveryNotificationOptions = DeliveryNotificationOptions.OnFailure;
mm.IsBodyHtml = true;
RichEditMailMessageExporter exporter = new RichEditMailMessageExporter(txtMauEmail, mm);
exporter.Export();
mm.To.Add(new MailAddress(EmailKeToanString[ii]));
//mm.To.Add(new MailAddress("*****@*****.**"));
if (lstCongNo.Count > 1)
{
XuLyGiaoDien.wait.SetWaitFormDescription("Gửi cho: " + dl.Ten + " (" + (i + 1) + "/" + n + ").");
CTGiaoDichDindingSource.DataSource = lstCongNo;
mm.Subject = "Công Nợ - " + dl.Ten + " - Từ ngày " + ((DateTime)dtpTuNgay.EditValue).ToString("dd_MM_yyyy") + " - đến ngày " + ((DateTime)dtpDenNgay.EditValue).ToString("dd_MM_yyyy");
#region Xuất excel
gridCTCongNo.ForceInitialize();
string strFile = @"C:\CongNo\" + dl.Ten + " - " + ((DateTime)dtpTuNgay.EditValue).ToString("dd_MM_yyyy") + " - " + ((DateTime)dtpDenNgay.EditValue).ToString("dd_MM_yyyy") + ".xlsx";
Directory.CreateDirectory(@"C:\CongNo");
gridCTCongNo.ExportToXlsx(strFile, opt);
#endregion
mm.Attachments.Add(new Attachment(strFile));
foreach (string g in vs)
{
if (g.Count() > 0)
{
mm.Attachments.Add(new Attachment(g));
}
}
client.Send(mm);
sendOK = true;
mm.Attachments.Dispose();
if (File.Exists(strFile))
{
File.Delete(strFile);
}
}
}
}
RE1 :
int index = lstDaiLy.FindItem(0, true, delegate(ListBoxFindItemArgs ei) { ei.IsFound = object.Equals(dl.ID, ei.ItemValue); });
lstDaiLy.SetItemChecked(index, false);
}
if (sendOK)
{
XuLyGiaoDien.Alert("Gửi mail thành công", Form_Alert.enmType.Success);
}
else
{
XuLyGiaoDien.Alert("Gửi mail không thành công", Form_Alert.enmType.Warning);
}
}
catch (Exception ex) { XtraMessageBox.Show(ex.Message, "Thông báo"); }
gdb.ChayCapNhatLuyKe(lstInt);
if (XuLyGiaoDien.wait.IsSplashFormVisible)
{
XuLyGiaoDien.wait.CloseWaitForm();
}
}
}
}
private void btnDel_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
if (XtraMessageBox.Show("Bạn có chắc xóa", "Thông báo", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) == DialogResult.Cancel)
{
return;
}
cTNgan = (GVCTNH.GetRow(GVCTNH.GetSelectedRows()[0]) as O_CTNGANHANG);
D_CTNGANHANG nh = new D_CTNGANHANG();
List <object> lstCtv = new List <object>();
if (cTNgan.LoaiKhachHang == 7)
{
List <O_CTNGANHANG> cTNganHangOs = new D_CTNGANHANG().Dem($"WHERE MaLienKet = '{cTNgan.MaLienKet}'");
if (cTNganHangOs.Count == 2)
{
lstCtv.Add(cTNganHangOs[0].ID);
lstCtv.Add(cTNganHangOs[1].ID);
}
else
{
XuLyGiaoDien.Alert("Sai định dạng", Form_Alert.enmType.Info);
return;
}
}
else
{
lstCtv.Add(cTNgan.ID);
new D_BAOCAOCTNH().Xoa(cTNgan.ID, "WHERE IDCTNganHang = {0}");
}
if (XuLyGiaoDien.ThongBao(Text, nh.XoaNhieu1Ban(lstCtv, "CTNGANHANG") > 0, true))
{
if (cTNgan.LoaiKhachHang == 1 || cTNgan.LoaiKhachHang == 2)
{
new D_DAILY().ChayLaiPhi(cTNgan.NgayHT);
}
else if (cTNgan.LoaiKhachHang == 4 || cTNgan.LoaiKhachHang == 30)
{
List <O_GIAODICH> _GiaoDich = new D_GIAODICH().DuLieuNganHang(cTNgan.IDGiaoDich);
List <Dictionary <string, object> > lstdic2 = new List <Dictionary <string, object> >();
List <string> CTV = new List <string>();
Dictionary <string, object> dic2 = new Dictionary <string, object>();
for (int i = 0; i < _GiaoDich.Count; i++)
{
O_GIAODICH gd = _GiaoDich[i];
dic2 = new Dictionary <string, object>();
CTV.Add(string.Format("WHERE ID = {0}", gd.ID));
dic2.Add("Khoa", false);
lstdic2.Add(dic2);
}
new D_GIAODICH().SuaNhieu1Ban(lstdic2, CTV);
}
nhD.ChayLaiSD();
string NoiDung = string.Format("Xóa GD Ngân Hàng : {0} VNĐ [{1}]", cTNgan.SoTien.ToString("#,###"), cTNgan.GhiChu);
Dictionary <string, object> dic = new Dictionary <string, object>();
dic.Add("FormName", Text);
dic.Add("MaCho", cTNgan.MaLienKet);
dic.Add("NoiDung", NoiDung);
dic.Add("NVGiaoDich", DuLieuTaoSan.NV.ID);
dic.Add("LoaiKhachHang", cTNgan.LoaiKhachHang);
dic.Add("Ma", cTNgan.MaDL);
if (NoiDung.Length > 10)
{
new D_LS_GIAODICH().ThemMoi(dic);
}
TaiLaiDuLieu();
}
}
List <ClsHanhKhach> LayHanhKhach2()
{
D_GIAODICH gdb = new D_GIAODICH();
List <O_GIAODICH> lstGD = data1;
List <ClsHanhKhach> lstHK = new List <ClsHanhKhach>();
// Danh sách hành khách
List <string> hks = new List <string>();
// Lấy tên duy nhất
for (int i = 0; i < lstGD.Count; i++)
{
if (!hks.Contains(lstGD[i].TenKhach))
{
ClsHanhKhach HKC = new ClsHanhKhach();
hks.Add(lstGD[i].TenKhach);
HKC.Khach = lstGD[i].TenKhach;
HKC.so += i;
if (lstGD[i].BiDanh == null)
{
HKC.Loaive = "Người lớn";
}
else
{
switch (lstGD[i].BiDanh.ToLower())
{
case "mstr":
case "miss":
HKC.Loaive = "Trẻ em";
break;
case "em bé":
HKC.Loaive = "Em bé";
break;
default:
HKC.Loaive = "Người lớn";
break;
}
}
if (lstGD[i].HanhLyDi == null)
{
HKC.HanhLy = "0 KG";
}
else if (lstGD[i].HanhLyDi == string.Empty)
{
HKC.HanhLy = "0 KG";
}
else
{
HKC.HanhLy = (lstGD[i].HanhLyDi).Replace("KG", string.Empty) + " KG";
}
if (data.SoLuongVe == 2)
{
if (lstGD[i].HanhLyVe == null)
{
HKC.HanhLy += " - 0 KG";
}
else if (lstGD[i].HanhLyVe == string.Empty)
{
HKC.HanhLy += " - 0 KG";
}
else
{
HKC.HanhLy += " - " + (lstGD[i].HanhLyVe).Replace("KG", string.Empty) + " KG";
}
}
lstHK.Add(HKC);
}
}
return(lstHK);
}
void XuLyVeHoanKt()
{
D_GIAODICH giaoDichD = new D_GIAODICH();
List <O_GIAODICH> lstGiaoDich = giaoDichD.DuLieu($"TinhCongNo = 0 and LoaiGiaoDich = 9 and LoaiKhachHang <> 3", false);
String range = $"Kế toán CN!A2:I";
var request = SService.Spreadsheets.Values.Get(SpreadsheetId, range);
ValueRange response = request.Execute();
IList <IList <Object> > values = response.Values;
List <APISheet> lstAPI = new List <APISheet>();
if (values != null)
{
foreach (var row in values)
{
if (row.Count < 1)//có ít nhất 2 cột có dữ liệu
{
continue;
}
if (row[0].ToString() == string.Empty)//không mã chỗ số vé
{
continue;
}
List <O_GIAODICH> GiaoDinhTamLuu = lstGiaoDich.Where(w => (w.SoVeVN ?? string.Empty).Replace(" ", string.Empty).Equals(row[0].ToString().Replace(" ", string.Empty))).ToList();
if (GiaoDinhTamLuu.Count == 1)// Mã chỗ có tồn tại theo điều kiện
{
O_GIAODICH ac = new O_GIAODICH();
ac.LoaiKhachHang = 0;
ac.GhiChu = row.Count > 7 ? row[7].ToString() : string.Empty;
ac.GiaHeThong = row.Count > 3 ? XuLyDuLieu.ConvertStringToLong(row[3].ToString() == string.Empty ? "0" : row[3].ToString()) : -1;
ac.GiaHoan = row.Count > 4 ? XuLyDuLieu.ConvertStringToLong(row[4].ToString() == string.Empty ? "0" : row[4].ToString()) : -1;
ac.GiaNet = row.Count > 5 ? XuLyDuLieu.ConvertStringToLong(row[5].ToString() == string.Empty ? "0" : row[5].ToString()) : -1;
ac.HangHoan = row.Count > 6 ? XuLyDuLieu.ConvertStringToLong(row[6].ToString() == string.Empty ? "0" : row[6].ToString()) : -1;
if (row.Count > 1)
{
if (row[1].ToString() != string.Empty)// tìm DL theo sheet
{
if (lstDaiLy.Where(w => w.Ten.TrimEnd().ToLower().Normalize(NormalizationForm.FormKD).Equals(row[1].ToString().TrimEnd().ToLower().Normalize(NormalizationForm.FormKD))).Count() > 0)
{
ac.IDKhachHang = lstDaiLy.Where(w => w.Ten.TrimEnd().ToLower().Normalize(NormalizationForm.FormKD).Equals(row[1].ToString().TrimEnd().ToLower().Normalize(NormalizationForm.FormKD))).First().ID;
ac.LoaiKhachHang = 1;
}
if (ac.LoaiKhachHang == 0)
{
lstAPI.Add(aPI("Không tìm thấy khách", row[0].ToString(), row[1].ToString(), row[2].ToString(), row[5].ToString(), row[6].ToString(), ac.GiaHeThong, ac.GiaHoan));
continue;
}
}
else if (row.Count > 3)
{
if (row[2].ToString() != string.Empty)// tìm CTV theo sheet
{
if (lstCTV.Where(w => w.Ten.TrimEnd().ToLower().Normalize(NormalizationForm.FormKD).Equals(row[2].ToString().TrimEnd().ToLower().Normalize(NormalizationForm.FormKD))).Count() > 0)
{
ac.IDKhachHang = lstCTV.Where(w => w.Ten.TrimEnd().ToLower().Normalize(NormalizationForm.FormKD).Equals(row[2].ToString().TrimEnd().ToLower().Normalize(NormalizationForm.FormKD))).First().ID;
ac.LoaiKhachHang = 2;
}
if (ac.LoaiKhachHang == 0)
{
lstAPI.Add(aPI("Không tìm thấy khách", row[0].ToString(), row[1].ToString(), row[2].ToString(), row[5].ToString(), row[6].ToString(), ac.GiaHeThong, ac.GiaHoan));
continue;
}
}
}
}
string GhiChuTam = string.Format("GExcel Hoàn : Nhận hoàn [{0}] - ", row[0].ToString());
if (ac.LoaiKhachHang > 0 && ac.IDKhachHang != GiaoDinhTamLuu[0].IDKhachHang)
{
GhiChuTam += string.Format("[{0} => {1}] ", lstALL.Where(w => w.ID.Equals(GiaoDinhTamLuu[0].IDKhachHang)).Count() > 0 ? lstALL.Where(w => w.ID.Equals(GiaoDinhTamLuu[0].IDKhachHang)).ToList()[0].Ten : string.Empty, lstALL.Where(w => w.ID.Equals(ac.IDKhachHang)).ToList()[0].Ten);
}
if (ac.GiaHeThong > 10 && ac.GiaHeThong != GiaoDinhTamLuu[0].GiaHeThong)
{
GhiChuTam += string.Format("[Phí hoàn {0} => {1}] ", GiaoDinhTamLuu[0].GiaHeThong.ToString("#,##0"), ac.GiaHeThong.ToString("#,##0"));
}
if (ac.GiaHoan > 10 && ac.GiaHoan != GiaoDinhTamLuu[0].GiaHoan)
{
GhiChuTam += string.Format("[Giá hoàn {0} => {1}] ", GiaoDinhTamLuu[0].GiaHoan.ToString("#,##0"), ac.GiaHoan.ToString("#,##0"));
}
if (ac.GiaNet > 10 && ac.GiaNet != GiaoDinhTamLuu[0].GiaNet)
{
GhiChuTam += string.Format("[Phí hãng {0} => {1}] ", GiaoDinhTamLuu[0].GiaNet.ToString("#,##0"), ac.GiaNet.ToString("#,##0"));
}
if (ac.HangHoan > 10 && ac.HangHoan != GiaoDinhTamLuu[0].HangHoan)
{
GhiChuTam += string.Format("[Hãng hoàn {0} => {1}] ", GiaoDinhTamLuu[0].HangHoan.ToString("#,##0"), ac.HangHoan.ToString("#,##0"));
}
GhiChuTam += "[";
for (int u = 0; u < GiaoDinhTamLuu.Count; u++)
{
GiaoDinhTamLuu[u].TinhCongNo = true;
if (ac.LoaiKhachHang > 0)
{
GiaoDinhTamLuu[u].LoaiKhachHang = ac.LoaiKhachHang;
GiaoDinhTamLuu[u].IDKhachHang = ac.IDKhachHang;
}
if (ac.GiaHeThong > 10)
{
GiaoDinhTamLuu[u].GiaHeThong = ac.GiaHeThong;
}
if (ac.GiaHoan > 10)
{
GiaoDinhTamLuu[u].GiaHoan = ac.GiaHoan;
}
if (ac.GiaNet > 10)
{
GiaoDinhTamLuu[u].GiaNet = ac.GiaNet;
}
if (ac.HangHoan > 100)
{
GiaoDinhTamLuu[u].HangHoan = ac.HangHoan;
}
GhiChuTam += GiaoDinhTamLuu[u].ID;
GhiChuTam += (u != GiaoDinhTamLuu.Count - 1) ? ", " : "]";
}
if (giaoDichD.ThucThiSua(GiaoDinhTamLuu) > 0)
{
Dictionary <string, object> dic1 = new Dictionary <string, object>();
dic1.Add("FormName", "Hệ thống");
dic1.Add("MaCho", row[0].ToString());
dic1.Add("NoiDung", GhiChuTam);
dic1.Add("LoaiKhachHang", 0);
dic1.Add("Ma", 0);
new D_LS_GIAODICH().ThemMoi(dic1);
}
}
else if (GiaoDinhTamLuu.Count == 0)
{
lstAPI.Add(aPI("Không tìm thấy hoàn", row[0].ToString(), row.Count > 1 ? row[1].ToString() : string.Empty, row.Count > 2 ? row[2].ToString() : string.Empty, row.Count > 7 ? row[7].ToString() : string.Empty, row.Count > 7 ? row[7].ToString() : string.Empty, row.Count > 3 ? long.Parse(row[3].ToString() == string.Empty ? "0" : row[3].ToString()) : 0, row.Count > 4 ? long.Parse(row[4].ToString() == string.Empty ? "0" : row[4].ToString()) : 0, row.Count > 5 ? long.Parse(row[5].ToString() == string.Empty ? "0" : row[5].ToString()) : 0, row.Count > 6 ? long.Parse(row[6].ToString() == string.Empty ? "0" : row[6].ToString()) : 0));
}
else
{
lstAPI.Add(aPI("Trên 2 giao dịch", row[0].ToString(), row.Count > 1 ? row[1].ToString() : string.Empty, row.Count > 2 ? row[2].ToString() : string.Empty, row.Count > 7 ? row[7].ToString() : string.Empty, row.Count > 7 ? row[7].ToString() : string.Empty, row.Count > 3 ? long.Parse(row[3].ToString() == string.Empty ? "0" : row[3].ToString()) : 0, row.Count > 4 ? long.Parse(row[4].ToString() == string.Empty ? "0" : row[4].ToString()) : 0, row.Count > 5 ? long.Parse(row[5].ToString() == string.Empty ? "0" : row[5].ToString()) : 0, row.Count > 6 ? long.Parse(row[6].ToString() == string.Empty ? "0" : row[6].ToString()) : 0));
}
}
#region Xóa
var requestbody = new ClearValuesRequest();
var request1 = SService.Spreadsheets.Values.Clear(requestbody, SpreadsheetId, range);
var Drequest1 = request1.Execute();
#endregion
#region Thêm lại các dòng lỗi
if (lstAPI.Count > 0)
{
var valueRange = new ValueRange();
List <IList <object> > lstList = new List <IList <object> >();
foreach (APISheet aPI in lstAPI)
{
var objectList = new List <object>()
{
aPI.MaCho, aPI.DaiLy, aPI.CTV, aPI.PhiHoanDaiLy, aPI.GiaHoanDaiLy, aPI.PhiHoan, aPI.GiaHoan, aPI.GhiChu, aPI.GhiChuHeThong
};
lstList.Add(objectList);
}
valueRange.Values = lstList;
var appendRequest = SService.Spreadsheets.Values.Append(valueRange, SpreadsheetId, range);
appendRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
var appendResponse = appendRequest.Execute();
lstAPI.Clear();
}
#endregion
}
}
void XuLyVeHoanBk()
{
D_GIAODICH giaoDichD = new D_GIAODICH();
for (int i = 0; i < NhanVienName.Count; i++)
{
String range = $"{NhanVienName[i]}!F3:L";
var request = SService.Spreadsheets.Values.Get(SpreadsheetId, range);
ValueRange response = request.Execute();
IList <IList <Object> > values = response.Values;
List <APISheet> lstAPI = new List <APISheet>();
if (values != null)
{
List <string> str = new List <string>();
foreach (var row in values)
{
if (row.Count < 1)//có ít nhất 2 cột có dữ liệu
{
continue;
}
if (row[0].ToString() == string.Empty)//không mã chỗ số vé
{
continue;
}
str.Add(row[0].ToString());
}
if (str.Count < 0)
{
return;
}
List <O_GIAODICH> lstGiaoDich = giaoDichD.DuLieu($"LoaiGiaoDich = 9 and LoaiKhachHang <> 3 and SoVeVN in ('{String.Join("' ,'", str.ToArray())}')", false);
//List<GiaoDichO> lstGiaoDich = giaoDichD.DuLieu($"TinhCongNo = 0 and LoaiGiaoDich = 9 and LoaiKhachHang <> 3 and NVGiaoDich in (0,{NhanVienID[i]})", false);
//List<GiaoDichO> DaNhan2 = giaoDichD.DuLieu($"TinhCongNo = 1 and LoaiGiaoDich = 9 and LoaiKhachHang <> 3 and NVGiaoDich in (0,{NhanVienID[i]})", false);
foreach (var row in values)
{
if (row.Count < 1)//có ít nhất 2 cột có dữ liệu
{
continue;
}
if (row[0].ToString() == string.Empty)//không mã chỗ số vé
{
continue;
}
List <O_GIAODICH> GiaoDinhTamLuu = lstGiaoDich.Where(w => w.SoVeVN.Replace(" ", "").Equals(row[0].ToString().Replace(" ", "")) && !w.TinhCongNo).ToList();
List <O_GIAODICH> DaNhan = lstGiaoDich.Where(w => w.SoVeVN.Replace(" ", "").Equals(row[0].ToString().Replace(" ", "")) && w.TinhCongNo).ToList();
if (GiaoDinhTamLuu.Count == 1)// Mã chỗ có tồn tại theo điều kiện
{
O_GIAODICH ac = new O_GIAODICH();
ac.NVGiaoDich = NhanVienID[i];
ac.LoaiKhachHang = 0;
ac.GhiChu = row.Count > 5 ? row[5].ToString() : string.Empty;
ac.GiaHeThong = row.Count > 3 ? long.Parse(row[3].ToString() == string.Empty ? "0" : row[3].ToString()) : -1;
ac.GiaHoan = row.Count > 4 ? long.Parse(row[4].ToString() == string.Empty ? "0" : row[4].ToString()) : -1;
if (GiaoDinhTamLuu[0].NVGiaoDich != NhanVienID[i] && GiaoDinhTamLuu[0].NVGiaoDich != 0)
{
lstAPI.Add(aPI($"Vé của {lstNV.Where(w => w.ID.Equals(GiaoDinhTamLuu[0].NVGiaoDich)).ToList()[0].TenDangNhapCty}", row[0].ToString(), row.Count > 1 ? row[1].ToString() : string.Empty, row.Count > 2 ? row[2].ToString() : string.Empty, row.Count > 5 ? row[5].ToString() : string.Empty, row.Count > 6 ? row[6].ToString() : string.Empty, row.Count > 3 ? long.Parse(row[3].ToString() == string.Empty ? "0" : row[3].ToString()) : 0, row.Count > 4 ? long.Parse(row[4].ToString() == string.Empty ? "0" : row[4].ToString()) : 0));
continue;
}// Vé đã có người nhận
if (row[1].ToString() != string.Empty)// tìm DL theo sheet
{
if (lstDaiLy.Where(w => w.Ten.TrimEnd().ToLower().Normalize(NormalizationForm.FormKD).Equals(row[1].ToString().TrimEnd().ToLower().Normalize(NormalizationForm.FormKD))).Count() > 0)
{
ac.IDKhachHang = lstDaiLy.Where(w => w.Ten.TrimEnd().ToLower().Normalize(NormalizationForm.FormKD).Equals(row[1].ToString().TrimEnd().ToLower().Normalize(NormalizationForm.FormKD))).First().ID;
ac.LoaiKhachHang = 1;
}
if (ac.LoaiKhachHang == 0)
{
lstAPI.Add(aPI("Không tìm thấy khách", row[0].ToString(), row[1].ToString(), row[2].ToString(), row[5].ToString(), row[6].ToString(), ac.GiaHeThong, ac.GiaHoan));
continue;
}
}
else if (row.Count > 3)
{
if (row[2].ToString() != string.Empty)// tìm CTV theo sheet
{
if (lstCTV.Where(w => w.Ten.TrimEnd().ToLower().Normalize(NormalizationForm.FormKD).Equals(row[2].ToString().TrimEnd().ToLower().Normalize(NormalizationForm.FormKD))).Count() > 0)
{
ac.IDKhachHang = lstCTV.Where(w => w.Ten.TrimEnd().ToLower().Normalize(NormalizationForm.FormKD).Equals(row[2].ToString().TrimEnd().ToLower().Normalize(NormalizationForm.FormKD))).First().ID;
ac.LoaiKhachHang = 2;
}
if (ac.LoaiKhachHang == 0)
{
lstAPI.Add(aPI("Không tìm thấy khách", row[0].ToString(), row[1].ToString(), row[2].ToString(), row[5].ToString(), row[6].ToString(), ac.GiaHeThong, ac.GiaHoan));
continue;
}
}
}
string GhiChuTam = string.Format("GExcel Hoàn : [{1}] {0} - ", row[0].ToString(), NhanVienName[i]);
if (ac.LoaiKhachHang > 0 && ac.IDKhachHang != GiaoDinhTamLuu[0].IDKhachHang)
{
GhiChuTam += string.Format("[{0} => {1}] ", lstALL.Where(w => w.ID.Equals(GiaoDinhTamLuu[0].IDKhachHang)).Count() > 0 ? lstALL.Where(w => w.ID.Equals(GiaoDinhTamLuu[0].IDKhachHang)).ToList()[0].Ten : string.Empty, lstALL.Where(w => w.ID.Equals(ac.IDKhachHang)).ToList()[0].Ten);
}
if (ac.GiaHeThong != -1 && ac.GiaHeThong != GiaoDinhTamLuu[0].GiaHeThong)
{
GhiChuTam += string.Format("[Phí hoàn {0} => {1}] ", GiaoDinhTamLuu[0].GiaHeThong.ToString("#,##0"), ac.GiaHeThong.ToString("#,##0"));
}
if (ac.GiaHoan != -1 && ac.GiaHoan != GiaoDinhTamLuu[0].GiaHoan)
{
GhiChuTam += string.Format("[Giá hoàn {0} => {1}] ", GiaoDinhTamLuu[0].GiaHoan.ToString("#,##0"), ac.GiaHoan.ToString("#,##0"));
}
GhiChuTam += "[";
if (GhiChuTam == string.Format("GExcel Hoàn : [{1}] {0} - [", row[0].ToString(), NhanVienName[i]) && ac.NVGiaoDich == GiaoDinhTamLuu[0].NVGiaoDich)
{
continue;
}
for (int u = 0; u < GiaoDinhTamLuu.Count; u++)
{
GiaoDinhTamLuu[u].NVGiaoDich = ac.NVGiaoDich;
GiaoDinhTamLuu[u].GhiChu = ac.GhiChu;
if (ac.LoaiKhachHang > 0)
{
GiaoDinhTamLuu[u].LoaiKhachHang = ac.LoaiKhachHang;
GiaoDinhTamLuu[u].IDKhachHang = ac.IDKhachHang;
}
if (ac.GiaHeThong > 10000)
{
GiaoDinhTamLuu[u].GiaHeThong = ac.GiaHeThong;
}
if (ac.GiaHoan > 10000)
{
GiaoDinhTamLuu[u].GiaHoan = ac.GiaHoan;
}
GhiChuTam += GiaoDinhTamLuu[u].ID;
GhiChuTam += (u != GiaoDinhTamLuu.Count - 1) ? ", " : "]";
}
if (giaoDichD.ThucThiSua(GiaoDinhTamLuu) > 0)
{
Dictionary <string, object> dic1 = new Dictionary <string, object>();
dic1.Add("FormName", "Hệ thống");
dic1.Add("MaCho", row[0].ToString());
dic1.Add("NoiDung", GhiChuTam);
dic1.Add("LoaiKhachHang", 0);
dic1.Add("Ma", 0);
new D_LS_GIAODICH().ThemMoi(dic1);
}
}
else if (GiaoDinhTamLuu.Count == 0)
{
if (DaNhan.Count > 0)
{
if (DaNhan[0].NVGiaoDich != NhanVienID[i] && DaNhan[0].NVGiaoDich != 0)
{
lstAPI.Add(aPI($"Vé đã nhận cho {lstNV.Where(w => w.ID.Equals(DaNhan[0].NVGiaoDich)).ToList()[0].TenDangNhapCty}", row[0].ToString(), row.Count > 1 ? row[1].ToString() : string.Empty, row.Count > 2 ? row[2].ToString() : string.Empty, row.Count > 5 ? row[5].ToString() : string.Empty, row.Count > 6 ? row[6].ToString() : string.Empty, row.Count > 3 ? long.Parse(row[3].ToString() == string.Empty ? "0" : row[3].ToString()) : 0, row.Count > 4 ? long.Parse(row[4].ToString() == string.Empty ? "0" : row[4].ToString()) : 0));
}
}
else
{
lstAPI.Add(aPI("Không tìm thấy hoàn", row[0].ToString(), row.Count > 1 ? row[1].ToString() : string.Empty, row.Count > 2 ? row[2].ToString() : string.Empty, row.Count > 5 ? row[5].ToString() : string.Empty, row.Count > 6 ? row[6].ToString() : string.Empty, row.Count > 3 ? long.Parse(row[3].ToString() == string.Empty ? "0" : row[3].ToString()) : 0, row.Count > 4 ? long.Parse(row[4].ToString() == string.Empty ? "0" : row[4].ToString()) : 0));
}
}
else
{
lstAPI.Add(aPI("Trên 2 giao dịch", row[0].ToString(), row.Count > 1 ? row[1].ToString() : string.Empty, row.Count > 2 ? row[2].ToString() : string.Empty, row.Count > 5 ? row[5].ToString() : string.Empty, row.Count > 6 ? row[6].ToString() : string.Empty, row.Count > 3 ? long.Parse(row[3].ToString() == string.Empty ? "0" : row[3].ToString()) : 0, row.Count > 4 ? long.Parse(row[4].ToString() == string.Empty ? "0" : row[4].ToString()) : 0));
}
}
#region Xóa
var requestbody = new ClearValuesRequest();
var request1 = SService.Spreadsheets.Values.Clear(requestbody, SpreadsheetId, range);
var Drequest1 = request1.Execute();
#endregion
#region Thêm lại các dòng lỗi
if (lstAPI.Count > 0)
{
var valueRange = new ValueRange();
List <IList <object> > lstList = new List <IList <object> >();
foreach (APISheet aPI in lstAPI)
{
var objectList = new List <object>()
{
aPI.MaCho, aPI.DaiLy, aPI.CTV, aPI.PhiHoanDaiLy, aPI.GiaHoanDaiLy, aPI.GhiChu, aPI.GhiChuHeThong
};
lstList.Add(objectList);
}
valueRange.Values = lstList;
var appendRequest = SService.Spreadsheets.Values.Append(valueRange, SpreadsheetId, range);
appendRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
var appendResponse = appendRequest.Execute();
lstAPI.Clear();
}
#endregion
}
}
}
void XulyVeThuong()
{
D_GIAODICH giaoDichD = new D_GIAODICH();
for (int i = 0; i < NhanVienName.Count; i++)
{
List <O_GIAODICH> lstGiaoDich = giaoDichD.DuLieu($"TinhCongNo = 1 and LoaiGiaoDich in (4,13,14) and LoaiKhachHang <> 3 and convert(date,NgayGD) > convert(date,getdate()-10) and IDKhachHang <> 87451 and NVGiaoDich in (0,{NhanVienID[i]})", false);
String range = $"{NhanVienName[i]}!A3:E";
var request = SService.Spreadsheets.Values.Get(SpreadsheetId, range);
ValueRange response = request.Execute();
IList <IList <Object> > values = response.Values;
List <APISheet> lstAPI = new List <APISheet>();
if (values != null)
{
#region Cập nhật khách
foreach (var row in values)
{
#region Điều kiện
if (row.Count < 2)//có ít nhất 2 cột có dữ liệu
{
continue;
}
else if (row.Count > 1)
{
if (row[0].ToString() == string.Empty)//không mã chỗ số vé
{
continue;
}
if (row.Count == 5)
{
if (row[4].ToString() != string.Empty)//Xóa Giao dịch quá hạn
{
if (DateTime.ParseExact(row[4].ToString().Split(':')[0].Replace("-", "/"), "dd/MM/yyyy", null).Subtract(DateTime.Now.AddDays(-7)).Days < 0)
{
continue;
}
}
}
if (row.Count == 2)
{
if (row[1].ToString() == string.Empty)// Đại lý và CTV trống
{
lstAPI.Add(aPI("Thiếu thông tin khách", row[0].ToString(), row[1].ToString(), string.Empty, string.Empty, string.Empty));
continue;
}
}
else
{
if (row[1].ToString() == string.Empty && row[2].ToString() == string.Empty)// Đại lý và CTV trống
{
lstAPI.Add(aPI("Thiếu thông tin khách", row[0].ToString(), row[1].ToString(), row[2].ToString(), (row.Count == 4) ? row[3].ToString() : string.Empty, string.Empty));
continue;
}
}
}
#endregion
List <O_GIAODICH> GiaoDinhTamLuu = lstGiaoDich.Where(w => w.MaCho.Replace(" ", string.Empty).Equals(row[0].ToString().ToUpper().Replace(" ", string.Empty)) || (w.SoVeVN ?? string.Empty).Replace(" ", string.Empty).Equals(row[0].ToString().Replace(" ", string.Empty))).ToList();
if (GiaoDinhTamLuu.Count > 0)// Mã chỗ có tồn tại theo điều kiện
{
O_GIAODICH ac = new O_GIAODICH();
ac.NVGiaoDich = NhanVienID[i];
ac.GhiChu = row.Count > 3 ? row[3].ToString() : string.Empty;
if (row[1].ToString() != string.Empty)// tìm DL theo sheet
{
if (lstDaiLy.Where(w => w.Ten.TrimEnd().ToLower().Normalize(NormalizationForm.FormKD).Equals(row[1].ToString().TrimEnd().ToLower().Normalize(NormalizationForm.FormKD))).Count() > 0)
{
ac.IDKhachHang = lstDaiLy.Where(w => w.Ten.TrimEnd().ToLower().Normalize(NormalizationForm.FormKD).Equals(row[1].ToString().TrimEnd().ToLower().Normalize(NormalizationForm.FormKD))).First().ID;
ac.LoaiKhachHang = 1;
}
}
else// tìm CTV theo sheet
{
if (lstCTV.Where(w => w.Ten.TrimEnd().ToLower().Normalize(NormalizationForm.FormKD).Equals(row[2].ToString().TrimEnd().ToLower().Normalize(NormalizationForm.FormKD))).Count() > 0)
{
ac.IDKhachHang = lstCTV.Where(w => w.Ten.TrimEnd().ToLower().Normalize(NormalizationForm.FormKD).Equals(row[2].ToString().TrimEnd().ToLower().Normalize(NormalizationForm.FormKD))).First().ID;
ac.LoaiKhachHang = 2;
}
}
if (ac.NVGiaoDich == GiaoDinhTamLuu[0].NVGiaoDich && ac.IDKhachHang == GiaoDinhTamLuu[0].IDKhachHang)
{
continue;
}
if (ac.LoaiKhachHang > 0 && ac.IDKhachHang > 0)//Thay đổi dữ liệu trên hệ thống
{
string GhiChuTam = string.Format("GExcel : [{1}] {0} - ", row[0].ToString(), NhanVienName[i]);
if (ac.IDKhachHang != GiaoDinhTamLuu[0].IDKhachHang)
{
GhiChuTam += string.Format("[{0} => {1}] ", lstALL.Where(w => w.ID.Equals(GiaoDinhTamLuu[0].IDKhachHang)).Count() > 0 ? lstALL.Where(w => w.ID.Equals(GiaoDinhTamLuu[0].IDKhachHang)).ToList()[0].Ten : string.Empty, lstALL.Where(w => w.ID.Equals(ac.IDKhachHang)).ToList()[0].Ten);
}
GhiChuTam += "[";
if (GhiChuTam == string.Format("GExcel : [{1}] {0} - [", row[0].ToString(), NhanVienName[i]) && ac.NVGiaoDich == GiaoDinhTamLuu[0].NVGiaoDich)
{
continue;
}
for (int u = 0; u < GiaoDinhTamLuu.Count; u++)
{
GiaoDinhTamLuu[u].GhiChu = ac.GhiChu;
GiaoDinhTamLuu[u].NVGiaoDich = ac.NVGiaoDich;
GiaoDinhTamLuu[u].LoaiKhachHang = ac.LoaiKhachHang;
GiaoDinhTamLuu[u].IDKhachHang = ac.IDKhachHang;
GhiChuTam += GiaoDinhTamLuu[u].ID;
GhiChuTam += (u != GiaoDinhTamLuu.Count - 1) ? ", " : "]";
}
if (giaoDichD.ThucThiSua(GiaoDinhTamLuu) > 0)
{
Dictionary <string, object> dic1 = new Dictionary <string, object>();
dic1.Add("FormName", "Hệ thống");
dic1.Add("MaCho", row[0].ToString());
dic1.Add("NoiDung", GhiChuTam);
dic1.Add("LoaiKhachHang", 0);
dic1.Add("Ma", 0);
new D_LS_GIAODICH().ThemMoi(dic1);
}
}
else
{
lstAPI.Add(aPI("Không tìm thấy khách", row[0].ToString(), row[1].ToString(), row.Count > 2 ? row[2].ToString() : string.Empty, row.Count > 3 ? row[3].ToString() : string.Empty, row.Count > 4 ? row[4].ToString() : string.Empty));
}
}
else
{
lstAPI.Add(aPI("Không tìm thấy vé", row[0].ToString(), row[1].ToString(), row.Count > 2 ? row[2].ToString() : string.Empty, row.Count > 3 ? row[3].ToString() : string.Empty, row.Count > 4 ? row[4].ToString() : string.Empty));
}
}
#endregion
#region Xóa
var requestbody = new ClearValuesRequest();
var request1 = SService.Spreadsheets.Values.Clear(requestbody, SpreadsheetId, range);
var Drequest1 = request1.Execute();
#endregion
#region Thêm lại các dòng lỗi
if (lstAPI.Count > 0)
{
var valueRange = new ValueRange();
List <IList <object> > lstList = new List <IList <object> >();
foreach (APISheet aPI in lstAPI)
{
var objectList = new List <object>()
{
aPI.MaCho, aPI.DaiLy, aPI.CTV, aPI.GhiChu, aPI.GhiChuHeThong
};
lstList.Add(objectList);
}
valueRange.Values = lstList;
var appendRequest = SService.Spreadsheets.Values.Append(valueRange, SpreadsheetId, range);
appendRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
var appendResponse = appendRequest.Execute();
lstAPI.Clear();
}
#endregion
}
}
}
private void btnTHu_Click(object sender, EventArgs e)
{
if (XtraMessageBox.Show("Bạn muốn thêm phí ?", "Câu hỏi", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
int n = lstDaiLyz.CheckedItems.Count;
if (n == 0)
{
XuLyGiaoDien.Alert("Chưa chọn đại lý tìm", Form_Alert.enmType.Info);
}
else
{
if (!XuLyGiaoDien.wait.IsSplashFormVisible)
{
XuLyGiaoDien.wait.ShowWaitForm();
}
List <long> a = new List <long>();
string daily = string.Format("{0}", lstDaiLyz.CheckedItems[0]);
a.Add(long.Parse(lstDaiLyz.CheckedItems[0].ToString()));
for (int i = 1; i < n; i++)
{
a.Add(long.Parse(lstDaiLyz.CheckedItems[i].ToString()));
daily += string.Format(",{0}", lstDaiLyz.CheckedItems[i]);
}
lst = new D_HOADON().DuLieu(string.Format("CONVERT(date, NgayThucHien) BETWEEN '{0}' AND '{1}' AND MaHD <> '0' AND ((GiaYeuCau - GiaHeThong) * PhanTram / 100) > 0 AND IDKhachHang in ({2}) ORDER BY IDKhachHang,MaHD,MaCho,GiaHeThong Desc", bdtpTu.DateTime.ToString("yyyyMMdd"), bdtpDen.DateTime.ToString("yyyyMMdd"), daily));
O_GIAODICH gdo;
O_KHOANGAY kn = new D_KHOANGAY().KiemTraNgayKhoa(bdtpTu.DateTime);
if (kn.KhoaAdmin)
{
return;
}
D_GIAODICH giaoDichD = new D_GIAODICH();
foreach (int b in a)
{
O_DAILY dl = lstDaiLy.Where(w => w.ID.Equals(b)).ToList()[0];
XuLyGiaoDien.wait.SetWaitFormDescription("Thu phí: " + dl.Ten);
gdo = new O_GIAODICH();
gdo.LoaiKhachHang = dl.LoaiKhachHang;
gdo.IDKhachHang = dl.ID;
gdo.MaCho = "HD";
gdo.NVGiaoDich = DuLieuTaoSan.NV.ID;
gdo.GiaThu = gdo.GiaHeThong = long.Parse(lst.Where(w => w.IDKhachHang.Equals(b)).Sum(w => w.CL2).ToString());
gdo.TenKhach = "Phí hóa đơn tháng " + bdtpTu.DateTime.Month;
gdo.CoDinh = true;
gdo.HTTT = 1;
gdo.LoaiGiaoDich = 5;
if (gdo.GiaHeThong > 0)
{
giaoDichD.ThemMoi(XuLyDuLieu.ConvertClassToDic(gdo));
}
}
if (XuLyGiaoDien.wait.IsSplashFormVisible)
{
XuLyGiaoDien.wait.CloseWaitForm();
}
XuLyGiaoDien.Alert("Thêm giao dịch thành công!", Form_Alert.enmType.Success);
}
}
}
