public DataTable DanhSachBuuGui()
{
//Lay tat ca so lieu cua mot buu cuc
//db.ChuoiKetNoi = ChuoiKetNoi;
//db.TaoKetNoi();
db.TaoKetNoi(FileConfigBCCP);
DataSet ds;
ds = db.ChayThuTuc("select DT.ItemCode SoHieu, isnull(A.SendingContent,'') NoiDungBuuGui, DT.ServiceCode MaDichVu, S.ServiceName as TenDichVu, A.ItemTypeCode as LoaiBuuGui,A.AcceptancePOSCode as MaBCChapNhan,SendingTime as NgayGioNhan,DT.ToPOSCode as BuuCucNhanCT, DT.MailTripNumber SoChuyen, DT.PostBagIndex SoTui,DT.FromPOSCode MaDuongThu, " +
"A.Weight TrongLuong, isnull(A.WeightConvert,0) TrongLuongQuiDoi,A.ProvinceCode+'0000' MaTinhPhat,A.CountryCode MaNuocPhat,A.CustomerCode MaKhachHang, A.SenderFullname TenKhachHang, " +
"A.SenderAddress DiaChiKhachHang,A.ReceiverFullname TenNguoiNhan, A.ReceiverAddress DiaChiNguoiNhan, A.MainFreight CuocChinh, A.FuelSurchargeFreight PhuPhiXD, " +
"A.FarRegionFreight PhuPhiVX, (isnull(A.SubFreight,0)-isnull(B.Freight,0)) as CuocCongThem, '' DichVuCongThem, 0 as TongCuoc,A.VATFreight as VAT,A.TotalFreightVAT as ThanhTien, C.Value SoTienCOD, B.Freight CuocCOD, B.FreightVAT CuocCODVAT, A.IsDebt as No, convert(bit,0,101) as GuiLo,cast('' as nvarchar(30)) as SoLo " +
"from Dispatch DT inner join Item A on DT.ItemCode=A.ItemCode " +
"left join (select * from ValueAddedServiceItem where ValueAddedServiceCode='COD')B " +
"on A.ItemCode=B.ItemCode " +
"left join (select * from ItemVASPropertyValue where PropertyCode='Amount' and ValueAddedServiceCode='COD') C " +
"on A.ItemCode=C.ItemCode " +
"left join Service S on A.ServiceCode=S.ServiceCode " +
"where A.BatchCode is null and DT.Year='" + Ngay.ToString("yyyyMMdd") + "' and DT.FromPOSCode='" + SoHieuBuuCuc + "' " +
" and A.SendingTime between '" + Ngay.AddDays(-1).ToString("MM/dd/yyyy") + " 14:00:00' and '" + Ngay.ToString("MM/dd/yyyy") + " 23:59:00'" +
" order by ToPOSCode,DT.MailTripNumber,DT.PostBagIndex,DT.IndexNumber");
DataTable dt = ds.Tables[0];
ds = new DataSet();
ds = db.ChayThuTuc("select A.ItemCode SoHieu, isnull(A.SendingContent,'') NoiDungBuuGui, DT.ServiceCode MaDichVu, S.ServiceName as TenDichVu, A.ItemTypeCode as LoaiBuuGui,A.AcceptancePOSCode as MaBCChapNhan,A.SendingTime as NgayGioNhan,DT.ToPOSCode as BuuCucNhanCT, DT.MailTripNumber SoChuyen, 0 SoTui,DT.FromPOSCode MaDuongThu, " +
"(A.Weight) TrongLuong, (isnull(A.WeightConvert,0)) TrongLuongQuiDoi,A.ProvinceCode+'0000' MaTinhPhat,A.CountryCode MaNuocPhat,A.CustomerCode MaKhachHang, A.SenderFullname TenKhachHang, " +
"A.SenderAddress DiaChiKhachHang,A.ReceiverFullname TenNguoiNhan, A.ReceiverAddress DiaChiNguoiNhan, A.MainFreight CuocChinh, A.FuelSurchargeFreight PhuPhiXD, " +
"A.FarRegionFreight PhuPhiVX, (isnull(A.SubFreight,0)-isnull(B.Freight,0)) as CuocCongThem, CAST(''as nvarchar(5)) DichVuCongThem, (isnull(A.TotalFreight,0)-isnull(B.Freight,0)) as TongCuoc,A.VATFreight as VAT,A.TotalFreightVAT as ThanhTien, C.Value SoTienCOD, B.Freight CuocCOD, B.FreightVAT CuocCODVAT, A.IsDebt as No, convert(bit,1,101) as GuiLo,A.BatchCode as SoLo " +
"from Dispatch DT inner join Item A on DT.ItemCode=A.ItemCode " +
"left join (select * from ValueAddedServiceItem where ValueAddedServiceCode='COD')B " +
"on A.ItemCode=B.ItemCode " +
"left join (select * from ItemVASPropertyValue where PropertyCode='Amount' and ValueAddedServiceCode='COD') C " +
"on A.ItemCode=C.ItemCode " +
"left join Service S on A.ServiceCode=S.ServiceCode " +
"where A.BatchCode is not null and DT.Year='" + Ngay.ToString("yyyyMMdd") + "' and DT.FromPOSCode='" + SoHieuBuuCuc + "' " +
" and A.SendingTime between '" + Ngay.AddDays(-1).ToString("MM/dd/yyyy") + " 14:00:00' and '" + Ngay.ToString("MM/dd/yyyy") + " 23:59:00'" +
//" group by A.BatchCode,isnull(A.SendingContent,''), DT.ServiceCode, DT.MailTripNumber,DT.FromPOSCode,A.ProvinceCode+'0000',A.CountryCode,A.CustomerCode, A.SenderFullname, B.FreightVAT, " +
//"A.SenderAddress,A.ReceiverFullname, A.ReceiverAddress, A.MainFreight, A.FuelSurchargeFreight,A.ItemTypeCode,A.AcceptancePOSCode,A.SendingTime,DT.ToPOSCode, " +
//"A.FarRegionFreight, (isnull(A.SubFreight,0)-isnull(B.Freight,0)), 0,A.VATFreight,A.TotalFreightVAT, C.Value, B.Freight, A.IsDebt " +
" order by DT.MailTripNumber");
DataTable dt1;
dt1 = db.ChayThuTuc("select DT.ServiceCode, A.BatchCode, " +
"(A.MainFreight)/count(*) CuocChinh, (A.FuelSurchargeFreight)/count(*) PhuPhiXD, " +
"(A.FarRegionFreight)/count(*) PhuPhiVX, (isnull(A.SubFreight,0))/count(*) as CuocCongThem, CAST(COUNT(*)as nvarchar(5)) DichVuCongThem, (A.VATFreight)/count(*) as VAT,(A.TotalFreightVAT)/count(*) as ThanhTien " +
"from Dispatch DT inner join Item A on DT.ItemCode=A.ItemCode " +
"left join Service S on A.ServiceCode=S.ServiceCode " +
"where A.BatchCode is not null and DT.Year='" + Ngay.ToString("yyyyMMdd") + "' and DT.FromPOSCode='" + SoHieuBuuCuc + "' " +
" and A.SendingTime between '" + Ngay.AddDays(-1).ToString("MM/dd/yyyy") + " 14:00:00' and '" + Ngay.ToString("MM/dd/yyyy") + " 23:59:00'" +
" group by A.BatchCode,DT.ServiceCode,A.MainFreight,A.FuelSurchargeFreight,A.FarRegionFreight,A.SubFreight,A.TotalFreightVAT,A.VATFreight " +
"order by A.BatchCode").Tables[0];
DataRow drDL, drCL;
string _MaDV = "", _MaLo = "";
if (dt1.Rows.Count > 0)
{
drCL = dt1.Rows[0];
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
drDL = ds.Tables[0].Rows[i];
if (_MaLo != drDL["SoLo"].ToString() || _MaDV != drDL["MaDichVu"].ToString())
{
_MaLo = drDL["SoLo"].ToString();
_MaDV = drDL["MaDichVu"].ToString();
drCL = TimCuocLo(_MaLo, _MaDV, dt1);
}
drDL["CuocChinh"] = drCL["CuocChinh"];
drDL["PhuPhiXD"] = drCL["PhuPhiXD"];
drDL["PhuPhiVX"] = drCL["PhuPhiVX"];
drDL["CuocCongThem"] = drCL["CuocCongThem"];
drDL["DichVuCongThem"] = drCL["DichVuCongThem"];
drDL["VAT"] = drCL["VAT"];
drDL["ThanhTien"] = drCL["ThanhTien"];
dt.ImportRow(drDL);
}
}
ChuoiKetNoiChay = db.ChuoiKetNoi;
return(dt);
}
