public override void TinhGiatri() { string sql; //Lấy Tổng giá trị cần phân bổ object[] os = DbData.GetValueByStore("sopsKetChuyen", new string[] { "@tk", "@ngayct", "@ngayct1", "@dk", "@psno" }, new object[] { tk, _Tungay, _Denngay, null, null }, new SqlDbType[] { SqlDbType.NVarChar, SqlDbType.DateTime, SqlDbType.DateTime, SqlDbType.NVarChar, SqlDbType.Float }, new ParameterDirection[] { ParameterDirection.Input, ParameterDirection.Input, ParameterDirection.Input, ParameterDirection.Input, ParameterDirection.Output }); if (os == null || os.Length == 0) { return; } Tongtien = Double.Parse(os[0].ToString()); //Lấy hệ số phân bổ sql = "select " + dryt["TruongSP"].ToString() + " as Masp," + dryt["Heso"].ToString() + " as Heso from " + dryt["BangDM"].ToString(); //sql += " where ngayct between cast('" + _Tungay.ToShortDateString() + "'as datetime) and cast('" + _Denngay.ToShortDateString() + "'as datetime) "; sql += " where " + dryt["TruongSP"].ToString() + " in (select mavt from dmvt where nhomgt ='" + this.Manhom + "') and Thang = " + _Tungay.Month.ToString() + " and Nam = " + _Tungay.Year.ToString(); DataTable tbHeso = DbData.GetDataTable(sql); tbHeso.PrimaryKey = new DataColumn[] { tbHeso.Columns["MaSP"] }; //Lấy tổng hệ số phân bổ double TongHeso = 0; DataColumn col = new DataColumn("Heso", typeof(double)); col.DefaultValue = 0; dtkq.Columns.Add(col); foreach (DataRow dr in dtkq.Rows) { string Masp = dr["MaSP"].ToString(); DataRow drHeso; if (!tbHeso.Rows.Contains(Masp)) { continue; } drHeso = tbHeso.Rows.Find(Masp); //double heso=double.Parse(dr["soluong"].ToString()) * double.Parse(drHeso["Heso"].ToString()); double heso = double.Parse(drHeso["Heso"].ToString()); dr["Heso"] = heso; TongHeso += heso; } //Phân bổ if (TongHeso == 0) { return; } foreach (DataRow dr in dtkq.Rows) { dr[Name] = double.Parse(dr["Heso"].ToString()) * Tongtien / TongHeso; } dtkq.Columns.Remove("Heso"); dtkq.Columns.Remove("Soluong"); }
private void FormReport() { string query = _drTable["Query"].ToString().ToUpper(); query = trimSpace(query); query = query.Replace("+ @@PS", "+ '" + _psString.Replace("'", "''") + "' "); query = query.Replace("+@@PS", "+ '" + _psString.Replace("'", "''") + "' "); query = query.Replace("'@@PS'", " '" + _psString.Replace("'", "''") + "' "); query = query.Replace("@@PS", _psString); query = UpdateSpecialCondition(query); query = query.Replace("@@SYSREPORTID", _drTable["sysReportID"].ToString()); if (_dtData == null) { _dtData = DbData.GetDataTable(query); } if (_dtData == null) { return; } int n = _dtData.Columns.Count; for (int i = 0; i < _dtData.Rows.Count; i++) { if (_dtData.Rows[i]["CachTinh"].ToString() != string.Empty && _dtData.Rows[i]["CachTinh"].ToString() != "0") { FColumn x = new FColumn(i, _dtData.Rows[i]["CachTinh"].ToString(), false); listFColumn.Add(x); } else { continue; } } for (int i = _dtData.Columns.Count - 1; i >= 0; i--) { if (_dtData.Columns[i].DataType == typeof(System.Decimal)) { foreach (FColumn fc in listFColumn) { if (fc.IsEvaluated == false) { _dtData.Rows[fc.Rowstt][i] = Evaluated(fc, i, _dtData); } } } else { break; } } }
private void DefaultReport() { string query = _drTable["Query"].ToString().ToUpper(); query = trimSpace(query); query = query.Replace("+ @@PS", "+ '" + _psString.Replace("'", "''") + "' "); query = query.Replace("+@@PS", "+ '" + _psString.Replace("'", "''") + "' "); query = query.Replace("'@@PS'", " '" + _psString.Replace("'", "''") + "' "); query = query.Replace("@@PS", _psString); query = UpdateSpecialCondition(query); _dtData = DbData.GetDataTable(query); }
private void DynamicReport() { _dtData = null; string colField = _drTable["ColField"].ToString(); if (colField == string.Empty) { return; } string tableName = _dbStruct.GetValue("select TableName from sysTable where sysTableID = " + _drTable["mtTableID"].ToString()).ToString(); string s = "select " + colField + " as colField from " + tableName + " where " + _psString + " group by " + colField; DataTable dtColStruct = DbData.GetDataTable(s); if (dtColStruct == null) { return; } DataTable dtTmp = null; string query = _drTable["Query"].ToString().ToUpper(); query = UpdateSpecialCondition(query); query = query.Replace("@@SYSREPORTID", _drTable["sysReportID"].ToString()); foreach (DataRow dr in dtColStruct.Rows) { if (dr[0] == DBNull.Value) { continue; } string colName = dr[0].ToString(); string psTmp = _psString + " and " + colField + " = '" + colName + "'"; string queryNew = query.Replace("@@PS", psTmp); colName = "T" + colName; dtTmp = DbData.GetDataTable(queryNew); if (dtTmp == null) { break; } if (dtTmp.Rows.Count == 0) { continue; } if (_dtData == null) { _dtData = dtTmp.Clone(); _dtData.PrimaryKey = new DataColumn[] { _dtData.Columns[0] }; _dtData.Columns.RemoveAt(_dtData.Columns.Count - 1); _dtData.Columns.Add("TongCong", typeof(System.Decimal)); } _dtData.Columns.Add(colName, typeof(System.Decimal)); MergeData(dtTmp); } if (_dtData == null) { return; } foreach (DataRow dr in _dtData.Rows) { decimal t = 0; for (int i = dtTmp.Columns.Count; i <= _dtData.Columns.Count - 1; i++) { if (dr[i].ToString() != string.Empty) { t += Decimal.Parse(dr[i].ToString()); } } dr["TongCong"] = t; } // _dtData.DefaultView.Sort = _dtData.Columns[0].ColumnName; }
private void CreateTreeData() { string sql = _drTable["TreeData"].ToString(); if (sql == string.Empty) { return; } DataTable dtTreeData = DbData.GetDataTable(sql); if (dtTreeData == null || dtTreeData.Rows.Count == 0) { return; } dtTreeData.PrimaryKey = new DataColumn[] { dtTreeData.Columns[0] }; _dtData.Columns.Add("TTSX", typeof(string)); _dtData.PrimaryKey = new DataColumn[] { _dtData.Columns[1] }; _dtData.DefaultView.Sort = _dtData.Columns[1].ColumnName; for (int i = 0; i < _dtData.Rows.Count; i++) { //if( _dtData.DefaultView[i][0].ToString()=="") // _dtData.Rows[i]["TTSX"] = _dtData.DefaultView[i][1]; if (_dtData.Rows[i][0].ToString() == "") { _dtData.Rows[i]["TTSX"] = _dtData.Rows[i][1]; } } List <string> LstLastId = new List <string>(); for (int i = 0; i < _dtData.Rows.Count; i++) { if (_dtData.Rows[i]["TTSX"].ToString() != "") { continue; } if (_dtData.Rows[i].RowState == DataRowState.Added) { _dtData.Rows[i].AcceptChanges(); } string meId = _dtData.Rows[i][0].ToString(); if (_dtData.Rows.Contains(meId)) { continue; } DataRow drDataTemp = _dtData.NewRow(); DataRow drTreeData = dtTreeData.Rows.Find(meId); if (drTreeData == null) { continue; } drDataTemp[1] = meId; drDataTemp[0] = drTreeData[1].ToString(); for (int j = 2; j < drDataTemp.Table.Columns.Count; j++) { DataColumn colChar = drDataTemp.Table.Columns[j]; if (colChar.DataType == typeof(string) && drTreeData.Table.Columns.Contains(colChar.ColumnName)) { drDataTemp[colChar] = drTreeData[colChar.ColumnName].ToString(); } } // _dtData.Rows.Add(drDataTemp); if (drDataTemp[0].ToString() == "") { LstLastId.Add(meId); } } foreach (string LastId in LstLastId) { DataRow drT = _dtData.Rows.Find(LastId); drT["TTSX"] = drT[1].ToString(); ExecuteLastId(drT[1].ToString(), 0); } _dtData.DefaultView.Sort = "TTSX"; }
private void DynamicReport() { _dtData = null; string colField = _drTable["ColField"].ToString(); if (colField == string.Empty) { return; } string tableName = _dbStruct.GetValue("select TableName from sysTable where sysTableID = " + _drTable["mtTableID"].ToString()).ToString(); string s = "select " + colField + " as colField from " + tableName + " where " + _psString + " group by " + colField; DataTable dtColStruct = DbData.GetDataTable(s); if (dtColStruct == null) { return; } DataTable dtTmp = null; string query = _drTable["Query"].ToString().ToUpper(); query = UpdateSpecialCondition(query); query = query.Replace("@@SYSREPORTID", _drTable["sysReportID"].ToString()); List <string> lstColName = new List <string>(); foreach (DataRow dr in dtColStruct.Rows) { if (dr[0] == DBNull.Value) { continue; } string fieldName = dr[0].ToString(); string psTmp = _psString + " and " + colField + " = '" + fieldName + "'"; string queryNew = query.Replace("@@PS", psTmp); dtTmp = DbData.GetDataTable(queryNew); if (dtTmp == null) { break; } if (dtTmp.Rows.Count == 0) { continue; } if (_dtData == null) { _dtData = dtTmp.Clone(); _dtData.PrimaryKey = new DataColumn[] { _dtData.Columns[0] }; //Add mấy column Tổng cộng foreach (DataColumn Col in dtTmp.Columns) { string ColName = Col.ColumnName; if (dtTmp.Columns[ColName].DataType == typeof(decimal)) { lstColName.Add(ColName); _dtData.Columns.Remove(ColName); _dtData.Columns.Add("Tong_" + ColName, typeof(System.Decimal)); } } } MergeData(dtTmp, fieldName); } if (_dtData == null) { return; } //Tính tổng foreach (DataRow dr in _dtData.Rows) { foreach (string ColName in lstColName) { decimal t = 0; foreach (DataColumn Col in _dtData.Columns) { if (Col.ColumnName.Contains(ColName + "_")) { if (dr[Col] != DBNull.Value) { t += (decimal)dr[Col]; } } } dr["Tong_" + ColName] = t; } } // _dtData.DefaultView.Sort = _dtData.Columns[0].ColumnName; }
public override void TinhGiatri() { if (YTPt.Count == 0) { return; } //dtkq.PrimaryKey = new DataColumn[] { dtkq.Columns["MaSP"] }; object[] os = DbData.GetValueByStore("sopsKetChuyen", new string[] { "@tk", "@ngayct", "@ngayct1", "@dk", "@psno" }, new object[] { tk, _Tungay, _Denngay, null, null }, new SqlDbType[] { SqlDbType.NVarChar, SqlDbType.DateTime, SqlDbType.DateTime, SqlDbType.NVarChar, SqlDbType.Float }, new ParameterDirection[] { ParameterDirection.Input, ParameterDirection.Input, ParameterDirection.Input, ParameterDirection.Input, ParameterDirection.Output }); if (os == null || os.Length == 0) { return; } Tongtien = Double.Parse(os[0].ToString()); double TongHeso = 0; string BangDM = ""; string TruongSP; string TruongHSDC = ""; DataTable TbHSDC = null; string sql = ""; DataColumn col = new DataColumn("HESO"); col.DataType = typeof(double); col.DefaultValue = 0.0; col.Caption = "HESO"; this.dtkq.Columns.Add(col); DataColumn col1 = new DataColumn("HSDC"); col1.DataType = typeof(double); col1.DefaultValue = 0.0; col1.Caption = "HSDC"; this.dtkq.Columns.Add(col1); if (dryt["BangDM"] != null && dryt["BangDM"].ToString().Trim() != "") { BangDM = dryt["BangDM"].ToString(); TruongSP = dryt["TruongSP"].ToString(); TruongHSDC = dryt["Heso"].ToString(); sql = "select * from " + BangDM + " where ngayct between '" + _Tungay.ToShortDateString() + "' and '" + _Denngay.ToShortDateString() + "'"; TbHSDC = DbData.GetDataTable(sql); if (TbHSDC == null) { return; } TbHSDC.PrimaryKey = new DataColumn[] { TbHSDC.Columns[TruongSP] }; foreach (DataRow dr in dtkq.Rows) { Hashtable h = new Hashtable(); foreach (Ytgt YT1 in YTPt) { DataRow tmp = YT1.Kq.Rows.Find(dr["masp"].ToString()); if (tmp != null) { h.Add(YT1.Name.ToUpper(), double.Parse(tmp[YT1.Name].ToString())); } else { h.Add(YT1.Name.ToUpper(), 0); } } dr["HESO"] = bt.Evaluate(h); DataRow drtmp; drtmp = TbHSDC.Rows.Find(dr[TruongSP].ToString()); if (drtmp != null) { dr["HSDC"] = double.Parse(drtmp[TruongHSDC].ToString()); } else { dr["HSDC"] = 0; } TongHeso += double.Parse(dr["HESO"].ToString()) * double.Parse(dr["HSDC"].ToString()); } } else { foreach (DataRow dr in dtkq.Rows) { Hashtable h = new Hashtable(); foreach (Ytgt YT1 in YTPt) { DataRow tmp = YT1.Kq.Rows.Find(dr["masp"].ToString()); if (tmp != null) { h.Add(YT1.Name.ToUpper(), double.Parse(tmp[YT1.Name].ToString())); } else { h.Add(YT1.Name.ToUpper(), 0); } } dr["HESO"] = bt.Evaluate(h); dr["HSDC"] = 1; TongHeso += double.Parse(dr["HESO"].ToString()); } } foreach (DataRow dr in dtkq.Rows) { double heso = double.Parse(dr["HESO"].ToString()); double HSDC = double.Parse(dr["HSDC"].ToString()); dr[Name] = Tongtien * heso * HSDC / (TongHeso); } dtkq.Columns.Remove("Soluong"); this.dtkq.Columns.Remove("HESO"); this.dtkq.Columns.Remove("HSDC"); }