/// <summary>
/// Delete Bảng
/// </summary>
public bool Delete(Entities.SoDuCongNo pxh)
{
try
{
bool kt = false;
pt = new Constants.CongNo();
Sql = new Constants.Sql();
string sql = Sql.DeleteSoDuCongNo;
Connection conn = new Connection();
SqlConnection cn = conn.openConnection();
SqlCommand cmd = new SqlCommand(sql, cn);
cmd.Parameters.Add(pt.MaSoDuCongNo, SqlDbType.VarChar, 20).Value = pxh.MaSoDuCongNo;
int i = cmd.ExecuteNonQuery();
if (i == 1)
{
kt = true;
}
else
{
kt = false;
}
cmd.Connection.Dispose();
cn.Close();
conn.closeConnection();
cn = null;
conn = null;
return(kt);
}
catch
{
return(false);
}
}
/// <summary>
/// Select Bảng
/// </summary>
/// <returns></returns>
public Entities.SoDuCongNo[] Select()
{
try
{
Sql = new Constants.Sql();
pt = new Constants.CongNo();
string sql = Sql.SelectSoDuCongNo;
Connection conn = new Connection();
SqlConnection cn = conn.openConnection();
SqlCommand cmd = new SqlCommand(sql, cn);
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
//Insert Category into ArrayList
ArrayList arr = new ArrayList();
while (dr.Read())
{
Entities.SoDuCongNo phieuthu = new Entities.SoDuCongNo();
phieuthu.SoDuCongNoID = Convert.ToInt32(dr[pt.SoDuCongNoID].ToString());
phieuthu.MaSoDuCongNo = dr[pt.MaSoDuCongNo].ToString();
phieuthu.MaDoiTuong = dr[pt.MaDoiTuong].ToString();
phieuthu.TenDoiTuong = dr[pt.TenDoiTuong].ToString();
phieuthu.DiaChi = dr[pt.DiaChi].ToString();
phieuthu.SoDuDauKy = dr[pt.SoDuDauKy].ToString();
phieuthu.NgayKetChuyen = DateTime.Parse(dr[pt.NgayKetChuyen].ToString());
phieuthu.SoDuCuoiKy = dr[pt.SoDuCuoiKy].ToString();
phieuthu.LoaiDoiTuong = Boolean.Parse(dr[pt.LoaiDoiTuong].ToString());
phieuthu.TrangThai = Boolean.Parse(dr[pt.TrangThai].ToString());
arr.Add(phieuthu);
}
int n = arr.Count;
if (n == 0)
{
return(null);
}
Entities.SoDuCongNo[] arrC = new Entities.SoDuCongNo[n];
for (int i = 0; i < n; i++)
{
arrC[i] = (Entities.SoDuCongNo)arr[i];
}
//Giai phong bo nho
return(arrC);
}
catch
{
return(null);
}
}
/// <summary>
/// Insert Update Bảng
/// </summary>
public bool Insert(Entities.SoDuCongNo pxh)
{
try
{
bool kt = false;
pt = new Constants.CongNo();
Sql = new Constants.Sql();
string sql = Sql.InsertSoDuCongNo;
Connection conn = new Connection();
SqlConnection cn = conn.openConnection();
SqlCommand cmd = new SqlCommand(sql, cn);
cmd.Parameters.Add(pt.MaSoDuCongNo, SqlDbType.VarChar, 20).Value = pxh.MaSoDuCongNo;
cmd.Parameters.Add(pt.MaDoiTuong, SqlDbType.VarChar, 20).Value = pxh.MaDoiTuong;
cmd.Parameters.Add(pt.TenDoiTuong, SqlDbType.NVarChar, 200).Value = pxh.TenDoiTuong;
cmd.Parameters.Add(pt.DiaChi, SqlDbType.NVarChar, 200).Value = pxh.DiaChi;
cmd.Parameters.Add(pt.SoDuDauKy, SqlDbType.Float).Value = pxh.SoDuDauKy;
cmd.Parameters.Add(pt.NgayKetChuyen, SqlDbType.DateTime).Value = pxh.NgayKetChuyen;
cmd.Parameters.Add(pt.SoDuCuoiKy, SqlDbType.Float).Value = pxh.SoDuCuoiKy;
cmd.Parameters.Add(pt.LoaiDoiTuong, SqlDbType.Bit).Value = pxh.LoaiDoiTuong;
cmd.Parameters.Add(pt.TrangThai, SqlDbType.Bit).Value = pxh.TrangThai;
int i = cmd.ExecuteNonQuery();
if (i == 1)
{
kt = true;
}
else
{
kt = false;
}
cmd.Connection.Dispose();
cn.Close();
conn.closeConnection();
cn = null;
conn = null;
return(kt);
}
catch
{
return(false);
}
}
public bool Update(Entities.SoDuCongNo[] sdcn)
{
int soluong = 0;
Entities.SoDuCongNo[] hienthi = Select();
// nếu obj null
int year1 = 0;
int month1 = 0;
if (hienthi == null)
{
hienthi = new SoDuCongNo[0];
}
else
{
bool k1 = false;
Entities.SoDuCongNo[] sdc = new SoDuCongNo[hienthi.Length];
// kiểm tra số dư hiện tại
for (int i = 0; i < sdc.Length; i++)
{
k1 = false;
int nam1 = sdcn[0].NgayKetChuyen.Year;
int thang1 = sdcn[0].NgayKetChuyen.Month;
if (nam1 == hienthi[i].NgayKetChuyen.Year && thang1 == hienthi[i].NgayKetChuyen.Month && sdcn[0].LoaiDoiTuong == hienthi[i].LoaiDoiTuong)
{
k1 = true;
break;
}
}
if (k1 == true)
{
for (int i = 0; i < sdc.Length; i++)
{
if (sdcn[0].NgayKetChuyen.Year == hienthi[i].NgayKetChuyen.Year && sdcn[0].NgayKetChuyen.Month == hienthi[i].NgayKetChuyen.Month && sdcn[0].LoaiDoiTuong == hienthi[i].LoaiDoiTuong)
{
year1 = sdcn[0].NgayKetChuyen.Year;
month1 = sdcn[0].NgayKetChuyen.Month;
sdc[soluong] = hienthi[i];
soluong++;
}
}
hienthi = new SoDuCongNo[soluong];
for (int i = 0; i < soluong; i++)
{
hienthi[i] = sdc[i];
}
}
else
{
for (int i = 0; i < sdc.Length; i++)
{
if (sdcn[0].NgayKetChuyen.Month != 1)
{
if (sdcn[0].NgayKetChuyen.Year == hienthi[i].NgayKetChuyen.Year && (sdcn[0].NgayKetChuyen.Month - 1) == hienthi[i].NgayKetChuyen.Month && sdcn[0].LoaiDoiTuong == hienthi[i].LoaiDoiTuong)
{
Convert.ToDateTime((hienthi[0].NgayKetChuyen.Month - 1).ToString() + "/01/" + (hienthi[0].NgayKetChuyen.Year).ToString());
year1 = hienthi[0].NgayKetChuyen.Year;
month1 = hienthi[0].NgayKetChuyen.Month-1;
sdc[soluong] = hienthi[i];
soluong++;
}
}
else
{
if ((sdcn[0].NgayKetChuyen.Year - 1) == hienthi[i].NgayKetChuyen.Year && 12 == hienthi[i].NgayKetChuyen.Month && sdcn[0].LoaiDoiTuong == hienthi[i].LoaiDoiTuong)
{
year1 = hienthi[0].NgayKetChuyen.Year;
month1 = 12;
hienthi[i].NgayKetChuyen = Convert.ToDateTime(12.ToString() + "/01/" + year1.ToString());
sdc[soluong] = hienthi[i];
soluong++;
}
}
}
hienthi = new SoDuCongNo[soluong];
for (int i = 0; i < soluong; i++)
{
hienthi[i] = sdc[i];
}
}
}
Entities.SoDuCongNo[] mangluu;
int sotang = 0;
// đếm số bản ghi tồn tại rồi
for (int i = 0; i < hienthi.Length; i++)
{
if (hienthi[i].LoaiDoiTuong == sdcn[0].LoaiDoiTuong)
{
if (sdcn[0].MaDoiTuong != null)
sotang++;
}
}
if (hienthi.Length == 0)
mangluu = new SoDuCongNo[sotang];
else
mangluu = new SoDuCongNo[hienthi.Length];
sotang = 0;
bool kt1 = true;
// gán giá trị cho obj mangluu khởi tạo
if (hienthi.Length == 0)
{
for (int i = 0; i < hienthi.Length; i++)
{
if (hienthi[i].LoaiDoiTuong == sdcn[0].LoaiDoiTuong)
{
mangluu[sotang] = hienthi[i];
sotang++;
}
}
}
else
{
for (int i = 0; i < hienthi.Length; i++)
{
mangluu[i] = hienthi[i];
}
}
bool kt = false;
for (int i = 0; i < sdcn.Length; i++)
{
kt = false;
for (int j = 0; j < mangluu.Length; j++)
{
if (sdcn[i].MaDoiTuong == mangluu[j].MaDoiTuong && sdcn[0].LoaiDoiTuong == mangluu[j].LoaiDoiTuong)
{
kt = true;
break;
}
}
string maSoDuCongNo = "";
Entities.LayID b = new LayID("", "SoDuCongNo");
b = (Entities.LayID)new BizLogic.Lay_ID().Select(b);
if (b == null)
{
maSoDuCongNo = "SDCN_0001";
}
else
{
maSoDuCongNo = new Common.Utilities().ProcessID(b.ID);
}
if (kt == true)
{
sdcn[i].MaSoDuCongNo = hienthi[i].MaSoDuCongNo;
Update(sdcn[i]);
int nam = sdcn[i].NgayKetChuyen.Year;
int thang = sdcn[i].NgayKetChuyen.Month;
int namhientai=0;
int thanghientai=0;
if (hienthi.Length == 0)
{
namhientai = DateTime.Now.Year;
thanghientai = DateTime.Now.Month;
}
else
{
namhientai = hienthi[0].NgayKetChuyen.Year;
thanghientai = hienthi[0].NgayKetChuyen.Month;
}
Entities.SoDuCongNo a;
if (nam == namhientai && thang == thanghientai)
{
if (thang == 12)
a = new SoDuCongNo(maSoDuCongNo, sdcn[i].MaDoiTuong, sdcn[i].TenDoiTuong, sdcn[i].DiaChi, sdcn[i].SoDuCuoiKy, Convert.ToDateTime("01/01/" + (namhientai + 1).ToString()), "0", sdcn[i].LoaiDoiTuong, false);
else
a = new SoDuCongNo(maSoDuCongNo, sdcn[i].MaDoiTuong, sdcn[i].TenDoiTuong, sdcn[i].DiaChi, sdcn[i].SoDuCuoiKy, Convert.ToDateTime((thang + 1).ToString() + "/01/" + namhientai.ToString()), "0", sdcn[i].LoaiDoiTuong, false);
}
else
if (thanghientai == 12)
a = new SoDuCongNo(maSoDuCongNo, sdcn[i].MaDoiTuong, sdcn[i].TenDoiTuong, sdcn[i].DiaChi, sdcn[i].SoDuCuoiKy, Convert.ToDateTime(thang.ToString() + "/01/" + (namhientai + 1).ToString()), "0", sdcn[i].LoaiDoiTuong, false);
else
a = new SoDuCongNo(maSoDuCongNo, sdcn[i].MaDoiTuong, sdcn[i].TenDoiTuong, sdcn[i].DiaChi, sdcn[i].SoDuCuoiKy, DateTime.Now, "0", sdcn[i].LoaiDoiTuong, false);
Insert(a);
}
else
{
int nam = sdcn[i].NgayKetChuyen.Year;
int thang = sdcn[i].NgayKetChuyen.Month;
int namhientai = DateTime.Now.Year;
int thanghientai = DateTime.Now.Month;
Entities.SoDuCongNo a;
if (sdcn[0].MaDoiTuong != null)
{
//a = new SoDuCongNo(maSoDuCongNo, sdcn[i].MaDoiTuong, sdcn[i].TenDoiTuong, sdcn[i].DiaChi, "0", Convert.ToDateTime(thang + "/01/" + nam), sdcn[i].SoDuCuoiKy, sdcn[i].LoaiDoiTuong, true);
a = new SoDuCongNo(maSoDuCongNo, sdcn[i].MaDoiTuong, sdcn[i].TenDoiTuong, sdcn[i].DiaChi, "0", new DateTime(nam, thang, 1), sdcn[i].SoDuCuoiKy, sdcn[i].LoaiDoiTuong, true);
Insert(a);
//if (nam == namhientai && thang == thanghientai)
//{
// if (thang == 12)
// a = new SoDuCongNo(new Common.Utilities().ProcessID(maSoDuCongNo), sdcn[i].MaDoiTuong, sdcn[i].TenDoiTuong, sdcn[i].DiaChi, sdcn[i].SoDuCuoiKy, Convert.ToDateTime("01/01/" + (namhientai + 1).ToString()), "0", sdcn[i].LoaiDoiTuong, false);
// else
// a = new SoDuCongNo(new Common.Utilities().ProcessID(maSoDuCongNo), sdcn[i].MaDoiTuong, sdcn[i].TenDoiTuong, sdcn[i].DiaChi, sdcn[i].SoDuCuoiKy, Convert.ToDateTime((thang + 1).ToString() + "/01/" + namhientai.ToString()), "0", sdcn[i].LoaiDoiTuong, false);
//}
//else
// if (thang == 12)
// a = new SoDuCongNo(new Common.Utilities().ProcessID(maSoDuCongNo), sdcn[i].MaDoiTuong, sdcn[i].TenDoiTuong, sdcn[i].DiaChi, sdcn[i].SoDuCuoiKy, Convert.ToDateTime(thang.ToString() + "/01/" + (namhientai).ToString()), "0", sdcn[i].LoaiDoiTuong, false);
// else
// a = new SoDuCongNo(new Common.Utilities().ProcessID(maSoDuCongNo), sdcn[i].MaDoiTuong, sdcn[i].TenDoiTuong, sdcn[i].DiaChi, sdcn[i].SoDuCuoiKy, DateTime.Now, "0", sdcn[i].LoaiDoiTuong, false);
a = new SoDuCongNo(new Common.Utilities().ProcessID(maSoDuCongNo), sdcn[i].MaDoiTuong, sdcn[i].TenDoiTuong, sdcn[i].DiaChi, sdcn[i].SoDuCuoiKy, new DateTime(nam, thang, 1).AddMonths(1), "0", sdcn[i].LoaiDoiTuong, false);
Insert(a);
}
else
{//Có rồi thì update ngày kết chuyển = DateTime.Now
a = new SoDuCongNo(hienthi[i].MaSoDuCongNo, hienthi[i].MaDoiTuong, hienthi[i].TenDoiTuong, hienthi[i].DiaChi, "0", DateTime.Now, hienthi[i].SoDuDauKy, hienthi[i].LoaiDoiTuong, true);
Update(a); //gán số dư cuối kỳ, trạng thái = 1 where mã số dư công nợ, loại đối tượng
//if (nam == namhientai && thang == thanghientai)
//{
// if (thang == 12)
// a = new SoDuCongNo(new Common.Utilities().ProcessID(maSoDuCongNo), hienthi[i].MaDoiTuong, hienthi[i].TenDoiTuong, hienthi[i].DiaChi, hienthi[i].SoDuDauKy, Convert.ToDateTime("01/01/" + (namhientai + 1).ToString()), "0", hienthi[i].LoaiDoiTuong, false);
// else
// a = new SoDuCongNo(new Common.Utilities().ProcessID(maSoDuCongNo), hienthi[i].MaDoiTuong, hienthi[i].TenDoiTuong, hienthi[i].DiaChi, hienthi[i].SoDuDauKy, Convert.ToDateTime((thang + 1).ToString() + "/01/" + namhientai.ToString()), "0", hienthi[i].LoaiDoiTuong, false);
//}
//else
// if (thang == 12)
// a = new SoDuCongNo(new Common.Utilities().ProcessID(maSoDuCongNo), hienthi[i].MaDoiTuong, hienthi[i].TenDoiTuong, hienthi[i].DiaChi, hienthi[i].SoDuDauKy, Convert.ToDateTime(thang.ToString() + "/01/" + (namhientai).ToString()), "0", hienthi[i].LoaiDoiTuong, false);
// else
// a = new SoDuCongNo(new Common.Utilities().ProcessID(maSoDuCongNo), hienthi[i].MaDoiTuong, hienthi[i].TenDoiTuong, hienthi[i].DiaChi, hienthi[i].SoDuDauKy, DateTime.Now, "0", hienthi[i].LoaiDoiTuong, false);
a = new SoDuCongNo(new Common.Utilities().ProcessID(maSoDuCongNo), hienthi[i].MaDoiTuong, hienthi[i].TenDoiTuong, hienthi[i].DiaChi, hienthi[i].SoDuDauKy, new DateTime(nam, thang, 1).AddMonths(1), "0", hienthi[i].LoaiDoiTuong, false);
Insert(a);
}
}
}
return true;
}
/// <summary>
/// Select Bảng
/// </summary>
/// <returns></returns>
public Entities.SoDuCongNo[] Select()
{
try
{
Sql = new Constants.Sql();
pt = new Constants.CongNo();
string sql = Sql.SelectSoDuCongNo;
Connection conn = new Connection();
SqlConnection cn = conn.openConnection();
SqlCommand cmd = new SqlCommand(sql, cn);
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
//Insert Category into ArrayList
ArrayList arr = new ArrayList();
while (dr.Read())
{
Entities.SoDuCongNo phieuthu = new Entities.SoDuCongNo();
phieuthu.SoDuCongNoID = Convert.ToInt32(dr[pt.SoDuCongNoID].ToString());
phieuthu.MaSoDuCongNo = dr[pt.MaSoDuCongNo].ToString();
phieuthu.MaDoiTuong = dr[pt.MaDoiTuong].ToString();
phieuthu.TenDoiTuong = dr[pt.TenDoiTuong].ToString();
phieuthu.DiaChi = dr[pt.DiaChi].ToString();
phieuthu.SoDuDauKy = dr[pt.SoDuDauKy].ToString();
phieuthu.NgayKetChuyen = DateTime.Parse(dr[pt.NgayKetChuyen].ToString());
phieuthu.SoDuCuoiKy = dr[pt.SoDuCuoiKy].ToString();
phieuthu.LoaiDoiTuong = Boolean.Parse(dr[pt.LoaiDoiTuong].ToString());
phieuthu.TrangThai = Boolean.Parse(dr[pt.TrangThai].ToString());
arr.Add(phieuthu);
}
int n = arr.Count;
if (n == 0) return null;
Entities.SoDuCongNo[] arrC = new Entities.SoDuCongNo[n];
for (int i = 0; i < n; i++)
{
arrC[i] = (Entities.SoDuCongNo)arr[i];
}
//Giai phong bo nho
return arrC;
}
catch
{
return null;
}
}
