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 frmInVe(List <O_GIAODICH> dr)
{
InitializeComponent();
data1 = dr;
data = data1[0];
Load += new EventHandler(frmInVe_Load);
panelControl1.Visible = false;
}
private void btnThem_ItemClick(object sender, ItemClickEventArgs e)
{
List <O_GIAODICH> VTCT = new List <O_GIAODICH>();
List <O_GIAODICH> VTNCC = new List <O_GIAODICH>();
List <O_GIAODICH> VHCT = new List <O_GIAODICH>();
List <O_GIAODICH> VHNCC = new List <O_GIAODICH>();
for (int i1 = 0; i1 < GVVTNCC.DataRowCount; i1++)
{
O_GIAODICH gd = GVVTNCC.GetRow(i1) as O_GIAODICH;
gd.GhiChu = "Công ty thiếu vé";
VTNCC.Add(gd);
}// vé NCC
for (int i1 = 0; i1 < GVVHNCC.DataRowCount; i1++)
{
O_GIAODICH gd = GVVHNCC.GetRow(i1) as O_GIAODICH;
gd.GhiChu = "Công ty thiếu hoàn";
VHNCC.Add(gd);
}
for (int i1 = 0; i1 < GVVTCT.DataRowCount; i1++)
{
O_GIAODICH gd = GVVTCT.GetRow(i1) as O_GIAODICH;
if (VTNCC.Where(w => w.GiaNet.Equals(gd.GiaNet) &&
w.MaCho.Replace(" ", string.Empty).Equals(gd.MaCho.Replace(" ", string.Empty))).Count() == 0)
{
gd.GhiChu = "Công ty dư vé";
VTCT.Add(gd);// tìm không thấy là vé dư
}
else
{
VTNCC.Remove(VTNCC.Where(w => w.GiaNet.Equals(gd.GiaNet) &&// tìm thấy xóa đi để tránh bị trùng
w.MaCho.Replace(" ", string.Empty).Equals(gd.MaCho.Replace(" ", string.Empty))).First());
}
}
VTCT.AddRange(VTNCC); //còn lại là code thiếu
for (int i1 = 0; i1 < GVVHCT.DataRowCount; i1++)
{
O_GIAODICH gd = GVVHCT.GetRow(i1) as O_GIAODICH;
if (VHNCC.Where(w => w.HangHoan.Equals(gd.HangHoan) &&
w.MaCho.Replace(" ", string.Empty).Equals(gd.MaCho.Replace(" ", string.Empty))).Count() == 0)
{
gd.GhiChu = "Công ty dư hoàn";
VHCT.Add(gd);// tìm không thấy là vé dư
}
else
{
VHNCC.Remove(VHNCC.Where(w => w.HangHoan.Equals(gd.HangHoan) &&// tìm thấy xóa đi để tránh bị trùng
w.MaCho.Replace(" ", string.Empty).Equals(gd.MaCho.Replace(" ", string.Empty))).First());
}
}
VHCT.AddRange(VHNCC); //còn lại là code thiếu
GCKQT.DataSource = VTCT;
GCKQH.DataSource = VHCT;
}
private void grvDatCho_DoubleClick(object sender, EventArgs e)
{
O_GIAODICH giaoDichO = (GVDC.GetRow(GVDC.GetSelectedRows()[0]) as O_GIAODICH);
if (giaoDichO.LoaiGiaoDich != 10)
{
new frmTongHopThem(giaoDichO).ShowDialog(ParentForm);
}
}
void GetDataInExcel(bool isCT)
{
XtraOpenFileDialog ofd = new XtraOpenFileDialog();
ofd.Title = "Mở File";
ofd.Filter = "Excel File (*.xlsx, *.xls) | *.xlsx; *.xls";
ofd.DefaultExt = ".xlsx";
if (ofd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
string ChuoiKetNoi = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ofd.FileName + "; Extended Properties='Excel 12.0 Xml;HDR=YES';";
using (OleDbConnection conn = new OleDbConnection(ChuoiKetNoi))
{
conn.Open();
DataTable dbSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string CauTruyVan = "SELECT * FROM [" + dbSchema.Rows[0].Field <string>("TABLE_NAME").Replace("'", string.Empty) + ']';
OleDbDataAdapter da = new OleDbDataAdapter(CauTruyVan, conn);
DataTable dt = new DataTable();
int Dong = 0;
string Loi = string.Empty;
da.Fill(dt);
List <O_GIAODICH> lst = new List <O_GIAODICH>();
foreach (DataRow row in dt.Rows)
{
Dong++;
if (row["MaCho"].ToString().Length > 0 && (row["TienVe"].ToString().Length + row["TienHoan"].ToString().Length) > 0 && row["TenKhach"].ToString().Length > 0)
{
O_GIAODICH CVJ = new O_GIAODICH();
CVJ.TenKhach = row["TenKhach"].ToString();
CVJ.MaCho = row["MaCho"].ToString().Replace(" ", string.Empty);
if (long.Parse(row["TienVe"].ToString().Split('.')[0].Replace(",", string.Empty).Replace("-", string.Empty)) > 0)
{
CVJ.GiaNet = long.Parse(row["TienVe"].ToString().Split('.')[0].Replace(",", string.Empty).Replace("-", string.Empty));
}
else
{
CVJ.HangHoan = long.Parse(row["TienHoan"].ToString().Split('.')[0].Replace(",", string.Empty).Replace("-", string.Empty));
}
lst.Add(CVJ);
}
else
{
Loi += Dong.ToString() + ',';
}
}
if (isCT)
{
GCVTCT.DataSource = lst.Where(w => w.GiaNet > 0);
GCVHCT.DataSource = lst.Where(w => w.HangHoan > 0);
}
else
{
GCVTNCC.DataSource = lst.Where(w => w.GiaNet > 0);
GCVHNCC.DataSource = lst.Where(w => w.HangHoan > 0);
}
}
}
}
public frmVeThem(List <O_GIAODICH> lst)
{
InitializeComponent();
GCGD.EmbeddedNavigator.Buttons.Append.Visible = false;
GCGD.EmbeddedNavigator.Buttons.Remove.Visible = false;
_GiaoDichO = lst[0];
DSGiaoDich.DataSource = lst;
Text += " sửa";
}
public frmVeThem(O_GIAODICH lst)
{
InitializeComponent();
GCGD.EmbeddedNavigator.Buttons.Append.Visible = false;
GCGD.EmbeddedNavigator.Buttons.Remove.Visible = false;
_GiaoDichO = lst;
DSGiaoDich.DataSource = lst;
Text += " xem";
btnLuu.Visible = false;
}
private void GVGD_RowCellStyle(object sender, DevExpress.XtraGrid.Views.Grid.RowCellStyleEventArgs e)
{
GridView View = sender as GridView;
if (e.RowHandle >= 0)
{
O_GIAODICH dl = View.GetRow(e.RowHandle) as O_GIAODICH;
switch (e.Column.FieldName)
{
case "IDKhachHang":
e.Appearance.BackColor = Color.Transparent;
break;
case "LoaiKhachHang":
if (dl.LoaiKhachHang == 0)
{
e.Appearance.BackColor = Color.ForestGreen;
}
break;
case "HTTT":
if (dl.HTTT == 7)
{
e.Appearance.BackColor = Color.Yellow;
}
break;
case "NgayGD":
List <O_KHOANGAY> khoaNgayO = _ListKhoaNgayO.Where(w => w.TuNgay.ToString("ddMMyy").Equals(dl.NgayGD.ToString("ddMMyy"))).ToList();
if (khoaNgayO.Count > 0)
{
if (khoaNgayO[0].HoatDong)
{
e.Appearance.BackColor = Color.Gold;
}
}
break;
case "ChieuDi":
if (dl.TuyenBayDi == 0)
{
e.Appearance.BackColor = Color.Gold;
}
break;
case "ChieuVe":
if (dl.TuyenBayVe == 0 && dl.SoLuongVe == 2)
{
e.Appearance.BackColor = Color.Gold;
}
break;
}
}
}
private void btnIn_ItemClick(object sender, ItemClickEventArgs e)
{
try
{
_GiaoDichO = GVGD.GetRow(GVGD.GetSelectedRows()[0]) as O_GIAODICH;
List <O_GIAODICH> lst = _ListGiaoDichO.Where(w => w.MaCho.Equals(_GiaoDichO.MaCho) && w.LoaiGiaoDich.Equals(_GiaoDichO.LoaiGiaoDich) && w.IDKhachHang.Equals(_GiaoDichO.IDKhachHang) && w.NgayGD.ToString("ddMMyyy").Equals(_GiaoDichO.NgayGD.ToString("ddMMyyy")) && w.NhaCungCap.Equals(_GiaoDichO.NhaCungCap) && w.TuyenBayDi.Equals(_GiaoDichO.TuyenBayDi)).ToList();
frmInVe frm = new frmInVe(lst);
frm.ShowDialog();
}
catch { }
}
List <O_GIAODICH> ChuyenDoi(List <O_GIAODICH> lst)
{
List <O_GIAODICH> giaoDichOs = new List <O_GIAODICH>();
foreach (O_GIAODICH giao in lst)
{
O_GIAODICH giaoDich = new O_GIAODICH(giao);
giaoDichOs.Add(giaoDich);
}
return(giaoDichOs.OrderBy(w => w.SoVeVN).ToList());
}
private void grvCTCongNo_RowCellStyle(object sender, RowCellStyleEventArgs e)
{
GridView View = sender as GridView;
if (e.RowHandle >= 0)
{
O_GIAODICH dl = View.GetRow(e.RowHandle) as O_GIAODICH;
if (dl.TenKhach.Equals("TỔNG CỘNG:"))
{
e.Appearance.FontStyleDelta = FontStyle.Bold;
}
}
}
void ChinhSua()
{
if (GVGD.GetSelectedRows().Count() < 1)
{
return;
}
_GiaoDichO = GVGD.GetRow(GVGD.GetSelectedRows()[0]) as O_GIAODICH;
if (_GiaoDichO == null)
{
return;
}
_index = GVGD.GetFocusedDataSourceRowIndex();
List <O_GIAODICH> lst = _ListGiaoDichO.Where(w => w.MaCho.Equals(_GiaoDichO.MaCho) && w.LoaiGiaoDich.Equals(_GiaoDichO.LoaiGiaoDich) && w.IDKhachHang.Equals(_GiaoDichO.IDKhachHang) && w.NgayGD.ToString("ddMMyyy").Equals(_GiaoDichO.NgayGD.ToString("ddMMyyy")) && w.NhaCungCap.Equals(_GiaoDichO.NhaCungCap) && w.TuyenBayDi.Equals(_GiaoDichO.TuyenBayDi)).ToList();
if (lst.Count > 1)
{
DialogResult dc = XtraMessageBox.Show("Bạn muốn sửa tất cả giao dịch cùng Mã Chỗ ?", "Câu hỏi", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
switch (dc)
{
case DialogResult.No:
lst = _ListGiaoDichO.Where(w => w.ID.Equals(_GiaoDichO.ID)).ToList();
break;
case DialogResult.Cancel:
return;
}
}
if (new List <int>()
{
4, 13, 14
}.Contains(_GiaoDichO.LoaiGiaoDich))
{
frmVeThem frm = new frmVeThem(lst);
frm.ShowDialog(ParentForm);
}
else
{
frmVeHoanThem frm2 = new frmVeHoanThem(lst);
frm2.ShowDialog(ParentForm);
}
}
List <O_GIAODICH> _ListGiaoDichO = new List <O_GIAODICH>(); //Dữ liệu chính dùng để lọc dữ liệu cần xử lí
#endregion
#region Giao diện
private void gridView1_RowCellStyle(object sender, RowCellStyleEventArgs e)
{
GridView View = sender as GridView;
if (e.RowHandle >= 0)
{
O_GIAODICH dl = View.GetRow(e.RowHandle) as O_GIAODICH;
if (e.Column.FieldName == "TinhCongNo")
{
if (dl.TinhCongNo)
{
e.Appearance.BackColor = Color.Green;
}
else
{
e.Appearance.BackColor = Color.Crimson;
}
}
}
}
private void gridView1_DoubleClick(object sender, EventArgs e)
{
_GiaoDichO = GVH.GetRow(GVH.GetSelectedRows()[0]) as O_GIAODICH;
List <O_GIAODICH> lst = _ListGiaoDichO.Where(w => w.MaCho.Equals(_GiaoDichO.MaCho) && w.IDKhachHang.Equals(_GiaoDichO.IDKhachHang) && w.TinhCongNo.Equals(_GiaoDichO.TinhCongNo) && w.NgayGD.Date.Equals(_GiaoDichO.NgayGD.Date)).ToList();
if (lst.Count > 1)
{
DialogResult dc = XtraMessageBox.Show("Bạn muốn sửa tất cả giao dịch cùng Mã Chỗ ?", "Câu hỏi", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
switch (dc)
{
case DialogResult.No:
lst = _ListGiaoDichO.Where(w => w.ID.Equals(_GiaoDichO.ID)).ToList();
break;
case DialogResult.Cancel:
return;
}
}
new frmVeHoanThem(lst).ShowDialog(this);
}
private void btnXDC_ItemClick(object sender, ItemClickEventArgs e)
{
O_GIAODICH GD = (GVDC.GetRow(GVDC.GetSelectedRows()[0]) as O_GIAODICH);
switch (GD.LoaiGiaoDich)
{
case 1:
case 0:
if (!DuLieuTaoSan.Q.KhacXoa)
{
return;
}
break;
default:
if (!DuLieuTaoSan.Q.KhacAdminXoa)
{
return;
}
break;
}
if (XuLyGiaoDien.ThongBao("giao dịch", new D_GIAODICH().Xoa(GD.ID) > 0, true))
{
string NoiDung = string.Format("Xóa GD ", GD.GhiChu);
Dictionary <string, object> dic = new Dictionary <string, object>();
dic.Add("FormName", Text);
dic.Add("MaCho", GD.MaCho);
dic.Add("NoiDung", NoiDung);
dic.Add("NVGiaoDich", DuLieuTaoSan.NV.ID);
dic.Add("LoaiKhachHang", GD.LoaiKhachHang);
dic.Add("Ma", GD.IDKhachHang);
new D_LS_GIAODICH().ThemMoi(dic);
NapDatCho();
}
else
{
XuLyGiaoDien.Alert("Xóa không thành công", Form_Alert.enmType.Error);
}
}
void Xuli(List <O_GIAODICH> lstgd)
{
foreach (O_GIAODICH gd in lstgd)
{
if (_ListGiaoDichO.Where(w => (w.SoVeVN ?? string.Empty).Equals(gd.SoVeVN) && w.GiaHoan.Equals(gd.GiaThu)).Count() > 0)
{
continue;
}
O_GIAODICH g1 = new O_GIAODICH(gd);
g1.GiaHoan = g1.GiaThu;
g1.GiaNet = g1.GiaHeThong = g1.GiaThu = 0;
g1.LoiNhuan = g1.GiaHeThong + g1.HangHoan - g1.GiaNet + g1.GiaHoan;
g1.TenKhach += "/Hoàn vé";
g1.LoaiGiaoDich = 9;
g1.NVHoTro = g1.NVHoTro;
_ListGiaoDichO.Add(g1);
}
if (lstgd.Count > 0)
{
DSGiaoDich.DataSource = null;
DSGiaoDich.DataSource = _ListGiaoDichO;
lstgd[0].NVHoTro = _ListGiaoDichO[0].NVGiaoDich;
lstgd[0].NVGiaoDich = DuLieuTaoSan.NV.ID;
lstgd[0].NgayGD = DateTime.Now;
XuLyDuLieu.ConvertClassToTable(this, lstgd[0]);
iTinhCongNo.Checked = false;
btnLuu.Visible = true;
}
if (Owner.ActiveMdiChild is frmVe)
{
iTinhCongNo.Visible = true;
iTinhCongNo.Checked = true;
}
}
public frmVeHoanThem(List <O_GIAODICH> lst)
{
InitializeComponent();
_GiaoDichO = lst[0];
DSGiaoDich.DataSource = lst;
Text += " sửa";
if (lst[0].Hang == "VN")
{
List <O_GIAODICH> Sos = _GiaoDichD.VeThuongVN(lst);
foreach (O_GIAODICH a in lst)
{
if (Sos.Where(w => (w.SoVeVN ?? string.Empty).Equals(a.SoVeVN)).Count() > 0)
{
O_GIAODICH b = Sos.Where(w => (w.SoVeVN ?? string.Empty).Equals(a.SoVeVN)).ToList()[0];
if (b.GiaThu != a.GiaHoan)
{
memoEdit1.Text += string.Format("Số vé {0} thay đổi giá {1} sang {2}\r\n", a.SoVeVN, b.GiaThu.ToString("#,##0"), a.GiaHoan.ToString("#,##0"));
}
}
}
}
}
private void btnLuu_Click(object sender, EventArgs e)
{
int seledtedCell = GVDSC.GetSelectedRows().Count();
List <O_BAOCAOCTNH> lst = new List <O_BAOCAOCTNH>();
if (seledtedCell > 0)
{
for (int i = 0; i < seledtedCell; i++)
{
O_GIAODICH gd = GVDSC.GetRow(GVDSC.GetSelectedRows()[i]) as O_GIAODICH;
O_BAOCAOCTNH bc = new O_BAOCAOCTNH();
bc.LoaiGiaoDich = 0;
bc.IDGDLienKet = gd.ID;
bc.IDCTNganHang = ID;
bc.SoTien = _nCC ? gd.HangHoan : gd.GiaHoan;
bc.Thu = _nCC;
bc.Code = gd.MaCho;
lst.Add(bc);
}
(Owner as frmNHCTThem).DSSOVE(lst);
Close();
}
}
private void grvGiaoDich_RowCellStyle(object sender, RowCellStyleEventArgs e)
{
try
{
GridView View = sender as GridView;
if (e.RowHandle >= 0)
{
O_GIAODICH dl = View.GetRow(e.RowHandle) as O_GIAODICH;
if (e.Column.FieldName == "Khoa")
{
if (dl.Khoa)
{
e.Appearance.BackColor = Color.Green;
}
else
{
e.Appearance.BackColor = Color.Crimson;
}
}
}
}
catch { }
}
private void grvCongNo_RowCellStyle(object sender, RowCellStyleEventArgs e)
{
GridView View = sender as GridView;
if (e.RowHandle >= 0)
{
O_GIAODICH dl = View.GetRow(e.RowHandle) as O_GIAODICH;
if (e.Column.FieldName == "LoaiGiaoDich")
{
switch (dl.LoaiGiaoDich)
{
case 0:
case 1:
e.Appearance.BackColor = Color.FromArgb(237, 105, 28);
e.Appearance.BackColor2 = Color.SeaShell;
break;
case 2:
e.Appearance.BackColor = Color.Lime;
e.Appearance.BackColor2 = Color.SeaShell;
break;
case 5:
e.Appearance.BackColor = Color.Lime;
e.Appearance.BackColor = Color.FromArgb(66, 221, 245);
break;
case 6:
case 7:
e.Appearance.BackColor = Color.FromArgb(250, 52, 131);
e.Appearance.BackColor2 = Color.SeaShell;
break;
case 8:
case 9:
e.Appearance.BackColor = Color.FromArgb(232, 72, 72);
e.Appearance.BackColor2 = Color.SeaShell;
break;
case 10:
e.Appearance.BackColor = Color.Gold;
e.Appearance.BackColor2 = Color.SeaShell;
break;
}
}
else if (dl.CapNhatLuyKe)
{
if (e.Column.FieldName == "TaiKhoanCo" && dl.TaiKhoanCo > 0)
{
e.Appearance.BackColor = Color.Gold;
e.Appearance.BackColor2 = Color.SeaShell;
}
else if (e.Column.FieldName == "GiaThu" && dl.GiaThu > 0)
{
e.Appearance.BackColor = Color.Gold;
e.Appearance.BackColor2 = Color.SeaShell;
}
else if (e.Column.FieldName == "GiaHoan" && dl.GiaHoan > 0)
{
e.Appearance.BackColor = Color.Gold;
e.Appearance.BackColor2 = Color.SeaShell;
}
}
}
}
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
}
}
}
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 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
}
}
private void wVJ_DocumentCompleted(object sender, WebBrowserDocumentCompletedEventArgs e)
{
if (wVJ.ReadyState == WebBrowserReadyState.Complete && !wVJ.IsBusy)
{
HtmlElement head = wVJ.Document.GetElementsByTagName("head")[0];
HtmlElement scriptEl = wVJ.Document.CreateElement("script");
IHTMLScriptElement element = (IHTMLScriptElement)scriptEl.DomElement;
if (wVJ.Url.AbsolutePath.Contains("/CompanyPayRpt.aspx"))//lấy bản báo cáo ()
{
int isM = 13 - (DateTime.Now.Year - ((DateTime)eDate1.EditValue).Year) * 12 - (DateTime.Now.Month - ((DateTime)eDate1.EditValue).Month);
int isM2 = 13 - (DateTime.Now.Year - ((DateTime)eDate2.EditValue).Year) * 12 - (DateTime.Now.Month - ((DateTime)eDate2.EditValue).Month);
string Datestr = string.Format(@"document.getElementById('dlstFromDate_Day').options.item({0}).selected = true;
document.getElementById('dlstToDate_Day').options.item({1}).selected = true;
document.getElementById('dlstFromDate_Month').options.item({2}).selected = true;
document.getElementById('dlstToDate_Month').options.item({3}).selected = true;", ((DateTime)eDate2.EditValue).Day, ((DateTime)eDate1.EditValue).Day, isM2, isM);
int a = wVJ.Document.GetElementById("lstCompany").GetElementsByTagName("option").Count - 1;
switch (stepGetData)
{
case 0:
element.text = @"function doPost() { " + Datestr + " document.forms['CompanyPayments'].button.value='GetPaymnt';SubmitForm(); }";
head.AppendChild(scriptEl);
wVJ.Document.InvokeScript("doPost");
break;
default:
HtmlElement table = wVJ.Document.GetElementById("PayDetsGrd");
if (table != null)
{
HtmlElementCollection eles = wVJ.Document.GetElementsByTagName("tr");
foreach (HtmlElement ele in eles)
{
switch (ele.GetAttribute("className"))
{
case "hdr_midTable":
if (DataVJ.Columns.Count < 1)
{
HtmlElementCollection ths = ele.GetElementsByTagName("TH");
if (ths.Count == 12)
{
for (int u = 0; u < 12; u++)
{
DataVJ.Columns.Add(ths[u].InnerText, ths[u].InnerText.StartsWith("Số tiền") ? typeof(double) : typeof(string));
}
}
}
break;
case "GridPayDetsEven":
case "GridPayDetsOdd":
HtmlElementCollection tds = ele.GetElementsByTagName("TD");
O_GIAODICH gd = new O_GIAODICH();
gd.NgayGD = DateTime.ParseExact(tds[3].InnerText.Substring(0, tds[3].InnerText.IndexOf(' ')), "dd/MM/yyyy", null);
gd.MaCho = tds[1].InnerText;
gd.TenKhach = tds[2].InnerText.Replace(",", string.Empty);
if (tds[11].InnerText.Contains("("))
{
gd.HangHoan = long.Parse(new String(tds[9].InnerText.Where(Char.IsDigit).ToArray())) / 100;
}
else
{
gd.GiaNet = long.Parse(new String(tds[9].InnerText.Where(Char.IsDigit).ToArray())) / 100;
}
if (gd.NgayGD.Date > ((DateTime)eDate1.EditValue).AddDays(-1).Date&& gd.NgayGD.Date < ((DateTime)eDate2.EditValue).AddDays(1).Date)
{
gdL.Add(gd);
}
List <string> lststr1 = new List <string>();
for (int u = 0; u < tds.Count; u++)
{
if (tds[u].InnerText != null)
{
if (tds[u].InnerText.Contains("."))
{
lststr1.Add(tds[u].InnerText.Split('.')[0].Replace(",", string.Empty).Replace(")", string.Empty).Replace("(", "-"));
}
else
{
lststr1.Add(tds[u].InnerText);
}
}
else
{
lststr1.Add(tds[u].InnerText);
}
}
if (lststr1.Count == DataVJ.Columns.Count)
{
DataVJ.Rows.Add(lststr1.ToArray());
}
break;
}
}
}
if (stepGetData != a) //chưa lấy hết sub nên chạy tiếp
{
element.text = @"function doPost() { document.getElementById('lstCompany').options.item(" + (stepGetData + 1) + ").selected = true; document.forms['CompanyPayments'].button.value='GetPaymnt';SubmitForm();}";
head.AppendChild(scriptEl);
wVJ.Document.InvokeScript("doPost");
}
else //xử lí dữ liệu sau khi đến sub cuối
{
GCVTNCC.DataSource = gdL.Where(w => w.GiaNet > 0);
GCVHNCC.DataSource = gdL.Where(w => w.HangHoan > 0);
if (XuLyGiaoDien.wait.IsSplashFormVisible)
{
XuLyGiaoDien.wait.CloseWaitForm();
}
XtraMessageBox.Show("Lấy dữ liệu thành công", "Thông báo");
wVJ.Visible = false;
}
break;
}
stepGetData++;
}
else if (wVJ.Url.AbsolutePath.Contains("/AgentOptions.aspx"))//Đăng nhập thành công
{
wVJ.Navigate("https://agents.vietjetair.com/CompanyPayRpt.aspx?lang=vi&st=sl&sesid=");
}
else if (wVJ.Url.AbsolutePath.Contains("/sitelogin.aspx"))//Bước đăng nhập
{
head = wVJ.Document.GetElementById("wrapper");
if (head != null)
{
if (head.InnerText.ToLower().Contains("mật khẩu chưa đúng") || head.InnerText.ToLower().Contains("wrong password input"))
{
Close();
}
}
try
{
wVJ.Document.GetElementById("txtAgentID").SetAttribute("value", _NCCO.TaiKhoan);
wVJ.Document.GetElementById("txtAgentPswd").SetAttribute("value", _NCCO.MatKhau);
wVJ.Document.GetElementById("SiteLogin").InvokeMember("submit");
}
catch { }
}
else if (wVJ.Url.AbsolutePath.Contains("/Home"))
{
element.text = "function doPost() { location.href = 'https://agents.vietjetair.com/sitelogin.aspx?lang=vi'; }";
head.AppendChild(scriptEl);
wVJ.Document.InvokeScript("doPost");
}
}
}
private void wQH_DocumentCompleted(object sender, WebBrowserDocumentCompletedEventArgs e)
{
if (wQH.ReadyState == WebBrowserReadyState.Complete && !wQH.IsBusy)
{
Thread.Sleep(2000);
HtmlElement head1 = wQH.Document.GetElementsByTagName("head")[0];
HtmlElement scriptEl = wQH.Document.CreateElement("script");
IHTMLScriptElement element = (IHTMLScriptElement)scriptEl.DomElement;
if (wQH.Url.AbsolutePath.Contains("/login")) // Trang login
{
wQH.Document.GetElementById("login-agency-code").SetAttribute("value", _NCCO.MaHang);
wQH.Document.GetElementById("login-agency-id").SetAttribute("value", _NCCO.TaiKhoan);
wQH.Document.GetElementById("login-password").SetAttribute("value", _NCCO.MatKhau);
element.text = @"function doPost() {submitLoginForm('en_US');}";
head1.AppendChild(scriptEl);
wQH.Document.InvokeScript("doPost");
}
else if (wQH.Url.AbsolutePath.Contains("/agent"))
{
switch (step)
{
case 0:
element.text = @"function doPost() {createInvoice(event);}";
head1.AppendChild(scriptEl);
wQH.Document.InvokeScript("doPost");
break;
case 1:
wQH.Document.GetElementById("ir-start-date").SetAttribute("value", ((DateTime)eDate1.EditValue).ToString("dd/MM/yyyy"));
wQH.Document.GetElementById("ir-end-date").SetAttribute("value", ((DateTime)eDate2.EditValue).ToString("dd/MM/yyyy"));
element.text = @"function doPost() {doInvoice('/reservation','vi',true);}";
head1.AppendChild(scriptEl);
wQH.Document.InvokeScript("doPost");
break;
case 2:
if (((DateTime)eDate1.EditValue).Subtract(DateTime.Now).Days == 0)
{
element.text = @"function doPost() {viewdetails('0', 'VND', 'AG', '" + _NCCO.MaHang + "');}";
head1.AppendChild(scriptEl);
wQH.Document.InvokeScript("doPost");
step++;
}
else
{
wQH.Document.GetElementById("invoicedetails").InvokeMember("click");
}
break;
case 4:
HtmlElementCollection eleth = wQH.Document.GetElementsByTagName("th");
if (DataVJ.Columns.Count != 25)
{
for (int u = 0; u < eleth.Count; u++)
{
DataVJ.Columns.Add(eleth[u].InnerText, (new int[] { 9, 10, 11, 13, 14, 15 }.Contains(u)) ? typeof(double) : typeof(string));
}
}
HtmlElementCollection ele = wQH.Document.GetElementsByTagName("tr");
List <O_GIAODICH> lst1 = new List <O_GIAODICH>();
foreach (HtmlElement eles in ele)
{
HtmlElementCollection elez = eles.GetElementsByTagName("td");
if (elez.Count == 0)
{
continue;
}
List <string> lststr1 = new List <string>();
for (int u = 0; u < elez.Count; u++)
{
if (elez[u].InnerText != null)
{
if (XuLyDuLieu.IsNumeric(elez[u].InnerText.Replace(",", string.Empty)))
{
lststr1.Add(elez[u].InnerText.Replace(",", string.Empty));
}
else
{
lststr1.Add(elez[u].InnerText);
}
}
else
{
if (new int[] { 9, 10, 11, 13, 14, 15 }.Contains(u))
{
lststr1.Add("0");
}
else
{
lststr1.Add(elez[u].InnerText);
}
}
}
if (lststr1.Count == DataVJ.Columns.Count)
{
DataVJ.Rows.Add(lststr1.ToArray());
}
O_GIAODICH gd = new O_GIAODICH();
string[] ten = elez[7].InnerText.Split('/');
gd.TenKhach = ten[1] + " " + ten[0];
gd.MaCho = elez[4].InnerText;
if (long.Parse(elez[15].InnerText.Replace(",", string.Empty)) < 0)
{
gd.HangHoan = XuLyDuLieu.ConvertStringToLong(elez[15].InnerText);
}
else
{
gd.GiaNet = XuLyDuLieu.ConvertStringToLong(elez[15].InnerText);
}
lst1.Add(gd);
}
GCVTNCC.DataSource = lst1.Where(w => w.GiaNet > 0);
GCVHNCC.DataSource = lst1.Where(w => w.HangHoan > 0);
if (XuLyGiaoDien.wait.IsSplashFormVisible)
{
XuLyGiaoDien.wait.CloseWaitForm();
}
XtraMessageBox.Show("Lấy dữ liệu thành công", "Thông báo");
wQH.Visible = false;
//element.text = @"function doPost() {invoiceDetailExport('xls','/reservation','vi');}";
//head1.AppendChild(scriptEl);
//wQH.Document.InvokeScript("doPost");
break;
}
step++;
}
}
}
private void btnXoa_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
_GiaoDichO = GVGD.GetRow(GVGD.GetSelectedRows()[0]) as O_GIAODICH;
O_KHOANGAY kn = new D_KHOANGAY().KiemTraNgayKhoa(_GiaoDichO.NgayGD);
if (_GiaoDichO.TinhCongNo)
{
if (!DuLieuTaoSan.Q.VeAdmin)
{
if ((kn.HoatDong) && !(kn.Code ?? string.Empty).Contains(_GiaoDichO.MaCho.Replace(" ", string.Empty)))
{
XuLyGiaoDien.Alert("Ngày đã bị khóa", Form_Alert.enmType.Warning);
return;
}
}
}
if (DateTime.Now.Date.Subtract(_GiaoDichO.NgayGD.Date).Days > 30)
{
XuLyGiaoDien.Alert("Ngày đã bị khóa", Form_Alert.enmType.Warning);
return;
}
List <object> lstCtv = new List <object>();
List <O_GIAODICH> lst = _ListGiaoDichO.Where(w => (w.MaCho ?? "").Equals(_GiaoDichO.MaCho ?? "") && w.LoaiGiaoDich.Equals(_GiaoDichO.LoaiGiaoDich) && w.IDKhachHang.Equals(_GiaoDichO.IDKhachHang) && w.NgayGD.ToString("ddMMyyy").Equals(_GiaoDichO.NgayGD.ToString("ddMMyyy")) && w.NhaCungCap.Equals(_GiaoDichO.NhaCungCap) && w.TuyenBayDi.Equals(_GiaoDichO.TuyenBayDi)).ToList();
bool ThanhCong = false;
if (lst.Count == 1)
{
DialogResult dc = XtraMessageBox.Show("Bạn muốn xóa giao dịch ?", "Câu hỏi", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
switch (dc)
{
case DialogResult.Yes:
ThanhCong = _GiaoDichD.Xoa(_GiaoDichO.ID) > 0;
break;
default:
return;
}
}
else
{
DialogResult dc = XtraMessageBox.Show("Bạn muốn xóa tất cả giao dịch cùng Mã Chỗ ?", "Câu hỏi", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question);
switch (dc)
{
case DialogResult.Yes:
foreach (O_GIAODICH gdoz in lst)
{
lstCtv.Add(gdoz.ID);
}
ThanhCong = _GiaoDichD.XoaNhieu1Ban(lstCtv) > 0;
break;
case DialogResult.No:
ThanhCong = _GiaoDichD.Xoa(_GiaoDichO.ID) > 0;
break;
default:
return;
}
}
if (XuLyGiaoDien.ThongBao(Text, ThanhCong, true))
{
string NoiDung = string.Empty;
if (_GiaoDichO.LoaiGiaoDich == 4)
{
NoiDung = string.Format("Xóa {0} GD với tổng giá hệ thống là {1} \r\n", lst.Count(), lst.Sum(w => w.GiaHeThong).ToString("#,###"));
}
else
{
NoiDung = string.Format("Xóa {0} GD với tổng giá hoàn là {1} \r\n", lst.Count(), lst.Sum(w => w.GiaHoan).ToString("#,###"));
}
Dictionary <string, object> dic = new Dictionary <string, object>();
dic.Add("FormName", Text);
dic.Add("MaCho", _GiaoDichO.MaCho);
dic.Add("NoiDung", NoiDung);
dic.Add("NVGiaoDich", DuLieuTaoSan.NV.ID);
dic.Add("LoaiKhachHang", _GiaoDichO.LoaiKhachHang);
dic.Add("Ma", _GiaoDichO.IDKhachHang);
new D_LS_GIAODICH().ThemMoi(dic);
new D_DAILY().ChayLaiPhi(_GiaoDichO.NgayGD);
DuLieu();
}
}
private void btnDel_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
List <object> lstCtv = new List <object>();
_GiaoDichO = GVH.GetRow(GVH.GetSelectedRows()[0]) as O_GIAODICH;
List <O_GIAODICH> lst = _ListGiaoDichO.Where(w => (w.SoVeVN ?? "").Equals((_GiaoDichO.SoVeVN ?? "")) && w.IDKhachHang.Equals(_GiaoDichO.IDKhachHang)).ToList();
O_KHOANGAY kn = new D_KHOANGAY().KiemTraNgayKhoa(_GiaoDichO.NgayGD);
if (_GiaoDichO.TinhCongNo)
{
if (!DuLieuTaoSan.Q.TheoDoiHoanAdmin)
{
if ((kn.HoatDong) && !(kn.Code ?? string.Empty).Contains(_GiaoDichO.MaCho.Replace(" ", string.Empty)))
{
XuLyGiaoDien.Alert("Ngày đã bị khóa", Form_Alert.enmType.Warning);
return;
}
}
if (DateTime.Now.Date.Subtract(_GiaoDichO.NgayGD.Date).Days > 30)
{
XuLyGiaoDien.Alert("Ngày đã bị khóa", Form_Alert.enmType.Warning);
return;
}
}
if (lst.Count > 1)
{
DialogResult dc = XtraMessageBox.Show("Bạn muốn xóa tất cả giao dịch cùng Mã Chỗ ?", "Câu hỏi", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
switch (dc)
{
case DialogResult.No:
lst = _ListGiaoDichO.Where(w => w.ID.Equals(_GiaoDichO.ID)).ToList();
break;
case DialogResult.Cancel:
return;
}
}
foreach (O_GIAODICH gdoz in lst)
{
lstCtv.Add(gdoz.ID);
}
if (XuLyGiaoDien.ThongBao(Text, _GiaoDichD.XoaNhieu1Ban(lstCtv) > 0, true))
{
if (_GiaoDichO.TinhCongNo)
{
new D_DAILY().ChayLaiPhi(_GiaoDichO.NgayGD);
string NoiDung = string.Format("Xóa vé hoàn\r\n");
Dictionary <string, object> dic = new Dictionary <string, object>();
dic.Add("FormName", Text);
dic.Add("MaCho", _GiaoDichO.MaCho);
dic.Add("NoiDung", NoiDung);
dic.Add("NVGiaoDich", DuLieuTaoSan.NV.ID);
dic.Add("LoaiKhachHang", _GiaoDichO.LoaiKhachHang);
dic.Add("Ma", _GiaoDichO.IDKhachHang);
if (NoiDung.Length > 10)
{
new D_LS_GIAODICH().ThemMoi(dic);
}
}
DuLieu();
}
}
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();
}
}
public frmTongHopThem(O_GIAODICH gd)
{
InitializeComponent();
Gd = gd;
TT = " sửa";
}
public List <O_GIAODICH> LayDanhSachCN(DateTime tungay, DateTime denngay, string daily, bool total = false)
{
lstDaiLyID = new List <int>();
lstDaiLyLuyKe = new List <long>();
lstDaiLyLuyKeTong = new List <long>();
List <O_GIAODICH> lst = new List <O_GIAODICH>();
// Lấy số dư cuối ngày của các đại lý
string daily_id = daily;
string CauTruyVan = string.Format(@"select dl.ID, dl.Ten, COALESCE(sd.SoDuCuoi, 0) as SoDuCuoi, COALESCE(gd.TongTien, 0) as SoDuVN from DAILY dl
left join (select * from SODU_DAILY where convert(date, Ngay)='{1}') sd on sd.DaiLyID = dl.ID
left join (select SUM(COALESCE(GiaThu,0)-COALESCE(GiaHoan,0)) 'TongTien',IDKhachHang from GIAODICH where convert(date, NgayGD) between DATEADD(DAY,1,EOMONTH('{1}',-1)) and '{1}' group by IDKhachHang) gd on gd.IDKhachHang = dl.ID
where dl.ID in ({0})", daily_id.Replace("IDKhachHang", "ID"), tungay.AddDays(-1).ToString("yyyy-MM-dd"));
DataTable data = LayDataTable(CauTruyVan);
foreach (DataRow item in data.Rows)
{
int DaiLy = (int)item["ID"];
O_GIAODICH gd = new O_GIAODICH();
gd.ID = -1;
gd.NgayGD = tungay;
gd.TenKhach = "Số dư đầu kỳ";
gd.IDKhachHang = DaiLy;
gd.LoaiGiaoDich = -1;
gd.LuyKe = int.Parse(item["SoDuCuoi"].ToString());
if (int.Parse(tungay.ToString("dd")) == 1)
{
gd.LuyKeTong = 0;
}
else
{
gd.LuyKeTong = int.Parse(item["SoDuVN"].ToString());
}
lst.Add(gd);
lstDaiLyLuyKeTong.Add(gd.LuyKeTong);
lstDaiLyLuyKe.Add(gd.LuyKe);
lstDaiLyID.Add(DaiLy);
}
string a = string.Format(@" SELECT *
FROM
(SELECT [ID],[IDKhachHang],Agent, (case when LoaiGiaoDich <> 12 then COALESCE([GiaHoan],0) else 0 end) GiaHoan, [NgayGD], [TenKhach], [MaCho], [Hang], [SoVeVN], [LoaiVeDi], [LoaiVeVe], [TuyenBayDi], [TuyenBayVe],
[PhiCK], [GiaHeThong], [GiaThu], (case when LoaiGiaoDich = 12 then GiaHoan else 0 end) [TaiKhoanCo],0 [LuyKe],[LoaiGiaoDich],[GioBayDi],[GioBayVe],0 [LuyKeTong],SoLuongVe,[PhiCoDinh],CapNhatLuyKe
FROM GIAODICH GD
WHERE IDKhachHang IN ({0}) AND (convert(date, NgayGD) BETWEEN '{1}' and '{2}') and LoaiGiaoDich not in (2,3) AND TinhCongNo = 1 and LoaiGiaoDich <> 60
union all
SELECT CT.ID,MaDL,'',0,NgayHT,TEN +': '+GhiChu,'','','','','','','',0,0,0,SoTien,0,LoaiGiaoDich,'','',0,0,0,CapNhatLuyKeNH
FROM CTNGANHANG CT
left join (select ID,Ten FROM NGANHANG) NH ON CT.NganHangID = NH.ID
WHERE MaDL IN ({0}) AND LoaiGiaoDich in (2,3,46,47,32,43) AND (convert(date, NgayHT) BETWEEN '{1}' and '{2}')) GD
ORDER BY IDKhachHang, convert(date, NgayGD), (case when LoaiGiaoDich in (2, 3,46,47,32,43) then 0 else 1 end) ,ID", daily, tungay.ToString("yyyyMMdd"), denngay.ToString("yyyyMMdd"));
DataTable q = LayDataTable(a);
if (q.Rows.Count > 0)
{
lst.AddRange(LayBanCongNo <O_GIAODICH>(q, lstDaiLyID, lstDaiLyLuyKe, lstDaiLyLuyKeTong));
}
if (total)
{
foreach (DataRow item in data.Rows)
{
int DaiLy = (int)item["ID"];
O_GIAODICH gd = new O_GIAODICH();
gd.ID = -1;
gd.NgayGD = new DateTime();
gd.TenKhach = "TỔNG CỘNG:";
gd.IDKhachHang = DaiLy;
gd.LoaiGiaoDich = -1;
gd.GiaHeThong = lst.Where(t => t.IDKhachHang.Equals((int)item["ID"])).Sum(w => w.GiaHeThong);
gd.PhiCK = lst.Where(t => t.IDKhachHang.Equals((int)item["ID"])).Sum(w => w.PhiCK);
gd.PhiCoDinh = lst.Where(t => t.IDKhachHang.Equals((int)item["ID"])).Sum(w => w.PhiCoDinh);
gd.HoaHong = lst.Where(t => t.IDKhachHang.Equals((int)item["ID"])).Sum(w => w.HoaHong);
gd.GiaThu = lst.Where(t => t.IDKhachHang.Equals((int)item["ID"])).Sum(w => w.GiaThu);
gd.TaiKhoanCo = lst.Where(t => t.IDKhachHang.Equals((int)item["ID"])).Sum(w => w.TaiKhoanCo);
lst.Add(gd);
}
}
return(lst);
}