public void Apgia()
{
string blvtid;
string sql = "select systableid from systable where TableName='BLVT' and syspackageid=" + Config.GetValue("sysPackageID").ToString();
blvtid = _dbStruct.GetValue(sql).ToString();
sql = "select sysfieldid from sysfield where systableid=" + blvtid + " and FieldName='Dongia'";
string dongiaID = _dbStruct.GetValue(sql).ToString();
sql = "select sysfieldid from sysfield where systableid=" + blvtid + " and FieldName='Soluong_x'";
string Soluong_xID = _dbStruct.GetValue(sql).ToString();
sql = "select sysfieldid from sysfield where systableid=" + blvtid + " and FieldName='Soluong'";
string Soluong_nID = _dbStruct.GetValue(sql).ToString();
sql = "select b.mttableid,b.dttableid,a.* from sysdataconfigdt a,sysdataconfig b ";
sql += "where b.blconfigid=a.blconfigid ";
sql += "and systableid=" + blvtid + " AND blfieldid=" + dongiaID;
sql += " AND (a.blconfigid in(select blconfigid from sysdataconfigdt where blfieldid=" + Soluong_xID + "))";
DataTable dsMtdt = _dbStruct.GetDataTable(sql);
string mt;
string dt;
string GiaField = dongiaID; //
string tygiaField = _dbStruct.GetValue("select sysfieldid from sysfield where sysTableid=" + blvtid + " and FieldName='TyGia'").ToString(); //trong blvt là 1795 - rồi tìm trong bảng nguồn tương ứng
string GiaNTField = _dbStruct.GetValue("select sysfieldid from sysfield where sysTableid=" + blvtid + " and FieldName='DonGiaNT'").ToString(); //trong blvt là 1792 -
string mavtField = _dbStruct.GetValue("select sysfieldid from sysfield where sysTableid=" + blvtid + " and FieldName='MaVT'").ToString(); //trong blvt là 1796 - rồi tìm trong bảng nguồn tương ứng
string makhoField = _dbStruct.GetValue("select sysfieldid from sysfield where sysTableid=" + blvtid + " and FieldName='MaKho'").ToString(); //trong blvt là 1797 - rồi tìm trong bảng nguồn tương ứng
string ngayctField = _dbStruct.GetValue("select sysfieldid from sysfield where sysTableid=" + blvtid + " and FieldName='NgayCt'").ToString(); //trong blvt là 1782 - rồi tìm trong bảng nguồn tương ứng
string mtiddtField = _dbStruct.GetValue("select sysfieldid from sysfield where sysTableid=" + blvtid + " and FieldName='MTIDDT'").ToString(); //trong blvt là 1782 - rồi tìm trong bảng nguồn tương ứng
UpdateFormula formula;
PostBl post;
try
{
foreach (DataRow dr in dsMtdt.Rows)//duyệt qua từ loại chứng từ
{
string MaCT = _dbStruct.GetValue("select MaCT from systable where systableid=" + dr["dttableid"].ToString()).ToString();
mt = _dbStruct.GetValue("select tableName from systable where systableid=" + dr["mttableid"].ToString()).ToString();
dt = _dbStruct.GetValue("select tableName from systable where systableid=" + dr["dttableid"].ToString()).ToString();
string GiaFieldName;
string tygiaFieldName = "";
string GiaNTFieldName;
string mavtFieldName;
string ngayctFieldName;
if (!(dr["dtfieldid"] is DBNull) || !(dr["formula"] is DBNull))
{//Lấy thông tin các trường cần dùng
string MTIDDT = _dbStruct.GetValue(" select dtfieldid from sysdataconfigdt where blfieldid=" + mtiddtField + " and blconfigid=" + dr["blconfigid"].ToString()).ToString();
MTIDDT = _dbStruct.GetValue(" select fieldName from sysfield where sysfieldid=" + MTIDDT).ToString();
//GiaFieldName = _dbStruct.GetValue(" select dtfieldid from sysdataconfigdt where blfieldid=" + GiaField + " and blconfigid=" + dr["blconfigid"].ToString()).ToString();
//GiaFieldName = _dbStruct.GetValue(" select fieldName from sysfield where sysfieldid=" + GiaFieldName).ToString();
GiaFieldName = "DGQuyDoi";
mavtFieldName = _dbStruct.GetValue(" select dtfieldid from sysdataconfigdt where blfieldid=" + mavtField + " and blconfigid=" + dr["blconfigid"].ToString()).ToString();
mavtFieldName = _dbStruct.GetValue(" select fieldName from sysfield where sysfieldid=" + mavtFieldName).ToString();
ngayctFieldName = _dbStruct.GetValue(" select mtfieldid from sysdataconfigdt where blfieldid=" + ngayctField + " and blconfigid=" + dr["blconfigid"].ToString()).ToString();
ngayctFieldName = _dbStruct.GetValue(" select fieldName from sysfield where sysfieldid=" + ngayctFieldName).ToString();
//GiaNTFieldName = _dbStruct.GetValue(" select dtfieldid from sysdataconfigdt where blfieldid=" + GiaNTField + " and blconfigid=" + dr["blconfigid"].ToString()).ToString();
//GiaNTFieldName = _dbStruct.GetValue(" select fieldName from sysfield where sysfieldid=" + GiaNTFieldName).ToString();
//tygiaFieldName = _dbStruct.GetValue(" select mtfieldid from sysdataconfigdt where blfieldid=" + tygiaField + " and blconfigid=" + dr["blconfigid"].ToString()).ToString();
//if (tygiaFieldName != "")
//{
// tygiaFieldName = _dbStruct.GetValue(" select fieldName from sysfield where sysfieldid=" + tygiaFieldName).ToString();
//}
foreach (DataRow rGia in _dtVatTu.Select("MaCT='" + MaCT + "'"))//duyệt qua bảng giá tương ứng với bảng dữ liệu
{
updateMTDT(mt, dt, GiaFieldName, rGia, MTIDDT);
}
formula = new UpdateFormula(_dbData, dr["mttableid"].ToString(), dr["dttableid"].ToString(), GiaFieldName, TuNgay, DenNgay, ngayctFieldName, _condition.Replace("MaVT in", mavtFieldName + " in"));
formula.Update();
string timeCon = mt + "." + ngayctFieldName + " between cast('" + TuNgay.ToString() + "' as datetime) and cast('" + DenNgay.ToString() + "' as datetime)";
post = new PostBl(_dbData, dr["mttableid"].ToString(), timeCon, _condition.Replace("MaVT in", mavtFieldName + " in"));
post.Post();
}
}
}
catch (SqlException ex)
{
XtraMessageBox.Show(ex.Message);
_dbData.RollbackMultiTrans();
}
}
public void Apgia(DataTable dtXuat)
{
string blvtid;
string sql = "select systableid from systable where TableName='BLVT' and syspackageid=" + Config.GetValue("sysPackageID").ToString();
blvtid = _dbStruct.GetValue(sql).ToString();
sql = "select sysfieldid from sysfield where systableid=" + blvtid + " and FieldName='Dongia'";
string dongiaID = _dbStruct.GetValue(sql).ToString();
sql = "select d.MaCT,b.mttableid,b.dttableid,a.* from sysdataconfigdt a,sysdataconfig b, systable c, systable d ";
sql += "where b.mttableid = c.systableid and b.blconfigid=a.blconfigid and c.tablename in ('MT24','MT32','MT33','MT42','MT43','MT44','MT45') ";
sql += "and b.dttableid = d.systableid ";
sql += "and b.systableid=" + blvtid + " AND blfieldid=" + dongiaID;
DataTable dsMtdt = _dbStruct.GetDataTable(sql);
string mt;
string dt;
string tygiaField = _dbStruct.GetValue("select sysfieldid from sysfield where sysTableid=" + blvtid + " and FieldName='TyGia'").ToString(); //trong blvt là 1795 - rồi tìm trong bảng nguồn tương ứng
string mavtField = _dbStruct.GetValue("select sysfieldid from sysfield where sysTableid=" + blvtid + " and FieldName='MaVT'").ToString(); //trong blvt là 1796 - rồi tìm trong bảng nguồn tương ứng
string makhoField = _dbStruct.GetValue("select sysfieldid from sysfield where sysTableid=" + blvtid + " and FieldName='MaKho'").ToString(); //trong blvt là 1797 - rồi tìm trong bảng nguồn tương ứng
string ngayctField = _dbStruct.GetValue("select sysfieldid from sysfield where sysTableid=" + blvtid + " and FieldName='NgayCt'").ToString(); //trong blvt là 1782 - rồi tìm trong bảng nguồn tương ứng
string mtiddtField = _dbStruct.GetValue("select sysfieldid from sysfield where sysTableid=" + blvtid + " and FieldName='MTIDDT'").ToString(); //trong blvt là 1782 - rồi tìm trong bảng nguồn tương ứng
UpdateFormula formula;
PostBl post;
try
{
foreach (DataRow dr in dsMtdt.Rows)
{
string MaCT = dr["MaCT"].ToString();
mt = _dbStruct.GetValue("select tableName from systable where systableid=" + dr["mttableid"].ToString()).ToString();
dt = _dbStruct.GetValue("select tableName from systable where systableid=" + dr["dttableid"].ToString()).ToString();
string GiaFieldName;
string mavtFieldName;
string ngayctFieldName;
if (!(dr["dtfieldid"] is DBNull) || !(dr["formula"] is DBNull))
{//Lấy thông tin các trường cần dùng
string MTIDDT = _dbStruct.GetValue(" select dtfieldid from sysdataconfigdt where blfieldid=" + mtiddtField + " and blconfigid=" + dr["blconfigid"].ToString()).ToString();
MTIDDT = _dbStruct.GetValue(" select fieldName from sysfield where sysfieldid=" + MTIDDT).ToString();
GiaFieldName = "DGQuyDoi";
mavtFieldName = _dbStruct.GetValue(" select dtfieldid from sysdataconfigdt where blfieldid=" + mavtField + " and blconfigid=" + dr["blconfigid"].ToString()).ToString();
mavtFieldName = _dbStruct.GetValue(" select fieldName from sysfield where sysfieldid=" + mavtFieldName).ToString();
ngayctFieldName = _dbStruct.GetValue(" select mtfieldid from sysdataconfigdt where blfieldid=" + ngayctField + " and blconfigid=" + dr["blconfigid"].ToString()).ToString();
ngayctFieldName = _dbStruct.GetValue(" select fieldName from sysfield where sysfieldid=" + ngayctFieldName).ToString();
DataRow[] drs = dtXuat.Select("MaCT='" + MaCT + "'");
if (drs.Length == 0)
{
continue;
}
foreach (DataRow drXuat in drs)
{
updateMTDT(mt, dt, GiaFieldName, drXuat, MTIDDT);
}
formula = new UpdateFormula(_dbData, dr["mttableid"].ToString(), dr["dttableid"].ToString(), GiaFieldName, TuNgay, DenNgay, ngayctFieldName, _condition.Replace("MaVT in", mavtFieldName + " in"));
formula.Update();
string timeCon = mt + "." + ngayctFieldName + " between cast('" + TuNgay.ToString() + "' as datetime) and cast('" + DenNgay.ToString() + "' as datetime)";
post = new PostBl(_dbData, dr["mttableid"].ToString(), timeCon, _condition.Replace("MaVT in", mavtFieldName + " in"));
post.Post();
}
}
}
catch (SqlException ex)
{
XtraMessageBox.Show(ex.Message);
_dbData.RollbackMultiTrans();
}
}
public void ApGia()
{
string blvtid;
string sql = "select systableid from systable where TableName='BLVT' and syspackageid=" + Config.GetValue("sysPackageID").ToString();
blvtid = _dbStruct.GetValue(sql).ToString();
sql = "select sysfieldid from sysfield where systableid=" + blvtid + " and FieldName='Dongia'";
string dongiaID = _dbStruct.GetValue(sql).ToString();
//sql = "select sysfieldid from sysfield where systableid=" + blvtid + " and FieldName='Soluong_x'";
//string Soluong_xID=_dbStruct.GetValue(sql).ToString();
//sql = "select sysfieldid from sysfield where systableid=" + blvtid + " and FieldName='Soluong'";
//string Soluong_nID = _dbStruct.GetValue(sql).ToString();
sql = "select b.mttableid,b.dttableid,a.*,c.Pk from sysdataconfigdt a,sysdataconfig b, systable c ";
sql += "where b.mttableid = c.systableid and b.blconfigid=a.blconfigid and c.tablename in ('MT24','MT32','MT33','MT42','MT43','MT44','MT45','MTGiaCong')";
//sql +="where b.blconfigid=a.blconfigid ";
sql += "and b.systableid=" + blvtid + " AND blfieldid=" + dongiaID;
//sql += " AND (a.blconfigid in(select blconfigid from sysdataconfigdt where blfieldid=" + Soluong_xID + "))";
DataTable dsMtdt = _dbStruct.GetDataTable(sql);
string mt;
string dt;
//string GiaField = dongiaID;//
string tygiaField = _dbStruct.GetValue("select sysfieldid from sysfield where sysTableid=" + blvtid + " and FieldName='TyGia'").ToString(); //trong blvt là 1795 - rồi tìm trong bảng nguồn tương ứng
//string GiaNTField = _dbStruct.GetValue("select sysfieldid from sysfield where sysTableid=" + blvtid + " and FieldName='DonGiaNT'").ToString();//trong blvt là 1792 -
string mavtField = _dbStruct.GetValue("select sysfieldid from sysfield where sysTableid=" + blvtid + " and FieldName='MaVT'").ToString(); //trong blvt là 1796 - rồi tìm trong bảng nguồn tương ứng
string makhoField = _dbStruct.GetValue("select sysfieldid from sysfield where sysTableid=" + blvtid + " and FieldName='MaKho'").ToString(); //trong blvt là 1797 - rồi tìm trong bảng nguồn tương ứng
string ngayctField = _dbStruct.GetValue("select sysfieldid from sysfield where sysTableid=" + blvtid + " and FieldName='NgayCt'").ToString(); //trong blvt là 1782 - rồi tìm trong bảng nguồn tương ứng
UpdateFormula formula;
PostBl post;
try
{
foreach (DataRow dr in dsMtdt.Rows)//duyệt qua từng loại chứng từ
{
mt = _dbStruct.GetValue("select tableName from systable where systableid=" + dr["mttableid"].ToString()).ToString();
dt = _dbStruct.GetValue("select tableName from systable where systableid=" + dr["dttableid"].ToString()).ToString();
string GiaFieldName;
string tygiaFieldName = "";
string GiaNTFieldName;
string mavtFieldName;
string makhoFieldName;
string ngayctFieldName;
if (!(dr["dtfieldid"] is DBNull) || !(dr["formula"] is DBNull))
{//Lấy thông tin các trường cần dùng
//GiaFieldName = _dbStruct.GetValue(" select dtfieldid from sysdataconfigdt where blfieldid=" + GiaField + " and blconfigid=" + dr["blconfigid"].ToString()).ToString();
//GiaFieldName = _dbStruct.GetValue(" select fieldName from sysfield where sysfieldid=" + GiaFieldName).ToString();
GiaFieldName = "DGQuyDoi";
mavtFieldName = _dbStruct.GetValue(" select dtfieldid from sysdataconfigdt where blfieldid=" + mavtField + " and blconfigid=" + dr["blconfigid"].ToString()).ToString();
mavtFieldName = _dbStruct.GetValue(" select fieldName from sysfield where sysfieldid=" + mavtFieldName).ToString();
//xét trường hợp mã kho nằm trong dt hay mt
bool khoinMt = false;
makhoFieldName = _dbStruct.GetValue(" select dtfieldid from sysdataconfigdt where blfieldid=" + makhoField + " and blconfigid=" + dr["blconfigid"].ToString()).ToString();
if (makhoFieldName == null || makhoFieldName == "")
{
makhoFieldName = _dbStruct.GetValue(" select mtfieldid from sysdataconfigdt where blfieldid=" + makhoField + " and blconfigid=" + dr["blconfigid"].ToString()).ToString();
if (makhoFieldName != "")
{
khoinMt = true;
}
}
makhoFieldName = makhoFieldName == "" ? "MaKho" : _dbStruct.GetValue(" select fieldName from sysfield where sysfieldid=" + makhoFieldName).ToString();
ngayctFieldName = _dbStruct.GetValue(" select mtfieldid from sysdataconfigdt where blfieldid=" + ngayctField + " and blconfigid=" + dr["blconfigid"].ToString()).ToString();
ngayctFieldName = _dbStruct.GetValue(" select fieldName from sysfield where sysfieldid=" + ngayctFieldName).ToString();
//GiaNTFieldName = _dbStruct.GetValue(" select dtfieldid from sysdataconfigdt where blfieldid=" + GiaNTField + " and blconfigid=" + dr["blconfigid"].ToString()).ToString();
//GiaNTFieldName = _dbStruct.GetValue(" select fieldName from sysfield where sysfieldid=" + GiaNTFieldName).ToString();
//tygiaFieldName = _dbStruct.GetValue(" select mtfieldid from sysdataconfigdt where blfieldid=" + tygiaField + " and blconfigid=" + dr["blconfigid"].ToString()).ToString();
//if (tygiaFieldName != "")
//{
// tygiaFieldName = _dbStruct.GetValue(" select fieldName from sysfield where sysfieldid=" + tygiaFieldName).ToString();
//}
if (mt == "MT44" && makhoFieldName.ToUpper() == "MAKHON")
{
continue;
}
updateMTDT(mt, dt, GiaFieldName, mavtFieldName, makhoFieldName, ngayctFieldName, khoinMt, dr["Pk"].ToString());
formula = new UpdateFormula(_dbData, dr["mttableid"].ToString(), dr["dttableid"].ToString(), GiaFieldName, TuNgay, DenNgay, ngayctFieldName, _condition.Replace("MaVT in", mavtFieldName + " in"), dr["Pk"].ToString());
formula.Update();
string timeCon = mt + "." + ngayctFieldName + " between cast('" + TuNgay.ToString() + "' as datetime) and cast('" + DenNgay.ToString() + "' as datetime)";
post = new PostBl(_dbData, dr["mttableid"].ToString(), timeCon, _condition.Replace("MaVT in", mavtFieldName + " in"), dr["Pk"].ToString());
post.Post();
}
}
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
_dbData.RollbackMultiTrans();
}
}