string ApplyBranchPermissions(DataTable dtObj)
{
if (!(bool)dtObj.Rows[0]["ByBranch"])
{
return("");
}
string SQL;
string TabName;
int FldIndex;
SQL = ("SELECT BranchCode FROM UserRights WHERE UserName='******' AND ObjectID=" + (_ReportID + " AND CanRun=1"))));
SqlDataAdapter daBrns = new SqlDataAdapter(SQL, DataTools.GetConnectionStr());
DataTable dtBrns = new DataTable();
daBrns.Fill(dtBrns);
string Brns = "";
foreach (DataRow dtr in dtBrns.Rows)
{
Brns = (Brns + ("'" + (dtr[0] + "',")));
}
if ((Brns != ""))
{
Brns = Brns.Substring(0, (Brns.Length - 1));
}
FldIndex = DataTools.GetStrSerial(DataTools.ReadField(dtObj.Rows[0]["CriteriaFields"]), "BranchCode");
if ((FldIndex == -1))
{
TabName = "";
}
else
{
TabName = DataTools.GetStrPart(DataTools.ReadField(dtObj.Rows[0]["CriteriaFieldsTables"]), FldIndex);
}
if ((TabName.Substring(0, 1) == "@"))
{
if ((TabName == "@"))
{
TabName = ("@" + DataTools.ReadField(dtObj.Rows[0]["Source"]));
}
SQL = ("({" + (TabName.Substring(1) + ".BranchCode} IN ["));
SQL = (SQL + (Brns.Replace("'", "\"") + "])"));
}
else
{
SQL = ("((" + (((TabName == "") ? "BranchCode" : (TabName + ".BranchCode")) + (" IN (" + (Brns + ")))"))));
}
return(SQL);
}
public bool ValidateLine(int Index, string Op, string Value, string Sort, string Link, ref string Output)
{
DateTime dtf;
string CriteriaCap = "", Criteria = "";
string Ops = DataTools.GetStrPart(DataTools.ReadField(dtObject.Rows[0]["CriteriaFieldsOperators"]), Index);
string Unqs = DataTools.GetStrPart(DataTools.ReadField(dtObject.Rows[0]["CriteriaFieldsUnique"]), Index);
string FldType = DataTools.GetStrPart(DataTools.ReadField(dtObject.Rows[0]["CriteriaFieldsTypes"]), Index);
string Fld = DataTools.GetStrPart(DataTools.ReadField(dtObject.Rows[0]["CriteriaFields"]), Index);
if ((Index == -1))
{
Output = "Please select a field.";
goto Insert_Err;
}
else if (((Ops != "") && (DataTools.GetStrSerial(Ops, Op) < 0)))
{
Output = ("Operator must be " + Ops);
goto Insert_Err;
}
else if (((Value == "") && ((Sort == " ") || (Sort == ""))))
{
Output = "Please enter a valid value for field.";
goto Insert_Err;
}
else if (((FldType.Trim() == "7") && !DateTime.TryParse(Value, out dtf)))
{
Output = "Invalid Date.";
goto Insert_Err;
}
else if ((((FldType.Trim() == "7") || (FldType.Trim() == "3")) && (Op == "Like")))
{
Output = "Like operator is used with string fields only.";
goto Insert_Err;
}
//Output = ddlFields.Items(Index).Text + " " + Op + " " + Value + " " + Link
Output = (ddlFields[Index] + (" " + (Op + (" " + (Value + (" " + Link))))));
Session["CriteriaCap"] = (Session["CriteriaCap"] + (((Session["CriteriaCap"].ToString() == "") ? "" : ";") + Output));
CriteriaCap = CriteriaCap + (((CriteriaCap == "") ? "" : ";") + Output);
Output = (Fld + ("^^^" + (Op + ("^^^" + (Value + ("^^^" + (Sort + ("^^^" + Link))))))));
Session["Criteria"] = (Session["Criteria"] + (((Session["Criteria"].ToString() == "") ? "" : ";") + Output));
Criteria = (Criteria + (((Criteria == "") ? "" : ";") + Output));
return(true);
Insert_Err:
return(false);
}
string ApplySalesmanPermissions(DataTable dtObj)
{
// Check if salesman in criteria fields
string SQL;
int FldIndex = DataTools.GetStrSerial(DataTools.ReadField(dtObj.Rows[0]["CriteriaFields"]), "Salesman");
if ((FldIndex == -1))
{
FldIndex = DataTools.GetStrSerial(DataTools.ReadField(dtObj.Rows[0]["CriteriaFieldsCaptions"]), "Salesman");
}
if ((FldIndex == -1))
{
return("");
}
DataTable dtr = DataTools.DLookUp(DataTools.GetConnectionStr(), "Persons", "PersonCode", ("PersonType=3 AND UserName='******' AND Rank=1")));
if ((dtr.Rows.Count == 0))
{
return("");
}
string Salesman = dtr.Rows[0][0].ToString();
string TabName = DataTools.GetStrPart(DataTools.ReadField(dtObj.Rows[0]["CriteriaFieldsTables"]), FldIndex);
string FldName = DataTools.GetStrPart(DataTools.ReadField(dtObj.Rows[0]["CriteriaFields"]), FldIndex);
if ((TabName.Substring(0, 1) == "@"))
{
if ((TabName == "@"))
{
TabName = ("@" + DataTools.ReadField(dtObj.Rows[0]["Source"]));
}
SQL = ("({" + (TabName.Substring(1) + ("." + (FldName + ("} = '" + (Salesman + "'"))))));
}
else
{
SQL = ("((" + (((TabName == "") ? FldName : (TabName + ("." + FldName))) + (" = ('" + (Salesman + "')))"))));
}
return(SQL);
}
private void Load_Parameter(long RepID)
{
string strTmp;
string[] arrTmp;
List <string> ddlLink = new List <string>();
List <string> lstCriteria = new List <string>();
DataSet dsDic = new DataSet();
Paramters rep_par = new Paramters();
SqlDataAdapter daObject = new SqlDataAdapter(("SELECT ObjectTitle, UseAndOnly,ByBranch,CriteriaFields,CriteriaFieldsCaptions,SortFieldsList,Criteria" + "FieldsUnique,CriteriaFieldsTypes,CriteriaFieldsOperators,CriteriaFieldsSources FROM Objects WHERE ObjectID=" + RepID), DataTools.GetConnectionStr());
Session["ReportID"] = RepID;
daObject.Fill(dtObject);
Session["dtObject"] = dtObject;
rep_par.RepTitle = dtObject.Rows[0]["ObjectTitle"].ToString();
strTmp = ((dtObject.Rows[0]["CriteriaFields"].ToString()) == null ? "" : dtObject.Rows[0]["CriteriaFields"].ToString()).Replace("\r\n", "");
arrTmp = strTmp.Split(';');
string[] arrtmp2 = (string[])arrTmp.Clone();
for (int i = 0; (i <= (arrtmp2.Length - 1)); i++)
{
if ((DataTools.GetStrPart(DataTools.ReadField(dtObject.Rows[0]["CriteriaFieldsCaptions"]), i) != ""))
{
arrtmp2[i] = DataTools.GetStrPart(DataTools.ReadField(dtObject.Rows[0]["CriteriaFieldsCaptions"]), i);
}
}
strTmp = string.Join(";", arrtmp2);
dsDic = DataTools.GetDic(strTmp);
strTmp = "";
for (int i = 0; (i <= (arrTmp.Length - 1)); i++)
{
strTmp = "";
foreach (DataRow dr in dsDic.Tables[0].Rows)
{
if ((dr[0].ToString() == arrtmp2[i]))
{
strTmp = dr["LatinCap"].ToString();
break;
}
}
ddlFields.Add(((strTmp == "") ? arrTmp[i] : strTmp));
}
ddlLink.Add("AND");
if (!(bool)dtObject.Rows[0]["UseAndOnly"])
{
ddlLink.Add("OR");
}
DataTable dtCrit = new DataTable();
Session["Criteria"] = "";
Session["CriteriaCap"] = "";
dtCrit = DataTools.DLookUp(DataTools.GetConnectionStr(), "UsersRecent", "Criteria", ("UserName='******' AND ObjectID=" + RepID))), "", "", "AccessDate DESC", 1);
if ((dtCrit.Rows.Count > 0))
{
string CritFromTbl = dtCrit.Rows[0][0].ToString();
if (CritFromTbl != null && (CritFromTbl.Trim() != ""))
{
arrTmp = CritFromTbl.ToString().Split(';');
for (int i = 0; (i <= (arrTmp.Length - 1)); i++)
{
ValidateLine(DataTools.GetStrSerial(dtObject.Rows[0]["CriteriaFields"].ToString(), DataTools.GetStrPart(arrTmp[i], 0, "^^^")), DataTools.GetStrPart(arrTmp[i], 1, "^^^"), DataTools.GetStrPart(arrTmp[i], 2, "^^^"), DataTools.GetStrPart(arrTmp[i], 3, "^^^"), DataTools.GetStrPart(arrTmp[i], 4, "^^^"), ref strTmp);
lstCriteria.Add(DataTools.GetStrPart(Session["CriteriaCap"].ToString(), i));
//Session["lstCriteria"] = (DataTools.GetStrPart(Session["Criteria"].ToString(), i));
//Session["criteria_val"] = (DataTools.GetStrPart(Session["CriteriaCap"].ToString(), i));
}
}
}
}
public void ReadCriteria(ref string Criteria, ref string CritFormula, DataTable dtObject)
{
int i, Sort;
string Fld, Op, Link, T;
string[] Crit;
int Typ, Idx;
string Value;
DateTime Vdate = new DateTime();
if ((_RepCriteria != ""))
{
Crit = Regex.Split(_RepCriteria, ";");
//Crit = _RepCriteria.Split(';');
for (i = 0; i <= (Crit.Length - 1); i++)
{
if ((Crit[i].ToString().Trim() == ""))
{
// TODO: Continue For... Warning!!! not translated
}
Fld = DataTools.GetStrPart(Crit[i].ToString(), 0, "^^^");
Op = DataTools.GetStrPart(Crit[i].ToString(), 1, "^^^");
Value = DataTools.GetStrPart(Crit[i].ToString(), 2, "^^^");
Sort = (DataTools.GetStrPart(Crit[i].ToString(), 3, "^^^") == "" ? 0: Int32.Parse(DataTools.GetStrPart(Crit[i].ToString(), 3, "^^^")));
Link = DataTools.GetStrPart(Crit[i].ToString(), 4, "^^^");
Idx = DataTools.GetStrSerial(DataTools.ReadField(dtObject.Rows[0]["CriteriaFields"]), Fld);
T = DataTools.GetStrPart(DataTools.ReadField(dtObject.Rows[0]["CriteriaFieldsTables"]), Idx);
//Val(GenericMethods.GetStrPart(GenericMethods.ReadField(dtObject.Rows(0)("CriteriaFieldsTypes")), Idx))
Typ = (DataTools.GetStrPart(DataTools.ReadField(dtObject.Rows[0]["CriteriaFieldsTypes"]), Idx) == ""?0: Int32.Parse(DataTools.GetStrPart(DataTools.ReadField(dtObject.Rows[0]["CriteriaFieldsTypes"]), Idx)));
if ((Typ == 7))
{
Value = Value.Replace("-", "/").Replace(" ", "/").Replace("\\", "/");
Vdate = new DateTime(Int32.Parse(Value.Split('/')[2]), Int32.Parse(Value.Split('/')[1]), Int32.Parse(Value.Split('/')[0]));
}
//if ((Sort != 0))//later
//{
// rpt.DataDefinition.SortFields.Item[rpt.DataDefinition.SortFields.Count].SortDirection = ((Sort == 1) ? SortDirection.AscendingOrder : SortDirection.DescendingOrder);
// if ((T.Substring(0, 1) == "@"))
// {
// rpt.DataDefinition.SortFields.Item[rpt.DataDefinition.SortFields.Count].Field = rpt.DataDefinition.FormulaFields[Fld];
// }
// else
// {
// rpt.DataDefinition.SortFields.Item[rpt.DataDefinition.SortFields.Count].Field = rpt.Database.Tables[dtObject.Rows[0]["Source"].ToString()].Fields[Fld];
// }
//}
if ((T == "@@"))
{
if ((Typ == 7))
{
rpt.DataDefinition.FormulaFields[Fld].Text = ("Date (" + (Vdate.Year + ("," + (Vdate.Month + ("," + (Vdate.Day + ")"))))));
}
else if ((Typ == 3))
{
rpt.DataDefinition.FormulaFields[Fld].Text = Value;
}
else
{
rpt.DataDefinition.FormulaFields[Fld].Text = ("\'" + (Value + "\'"));
}
}
else if ((T.Substring(0, 1) == "@"))
{
if ((T == "@"))
{
T = ("@" + dtObject.Rows[0]["Source"]);
}
T = T.Substring(1);
if ((Value == "NULL"))
{
CritFormula = (CritFormula + ("(IsNull({" + (((Fld.Substring(0, 1) == "@") ? Fld : (T + ("." + Fld))) + ("})= " + ((Op == "<>") ? "FALSE" : "TRUE")))));
}
else
{
CritFormula = (CritFormula + ("({" + (((Fld.Substring(0, 1) == "@") ? Fld : (T + ("." + Fld))) + "} ")));
if ((Typ == 7))
{
CritFormula = (CritFormula + (Op + (" Date (" + (Vdate.Year + ("," + (Vdate.Month + ("," + (Vdate.Day + ")"))))))));
}
else if ((Typ == 3))
{
CritFormula = (CritFormula + (Op + (" " + Value)));
}
else
{
CritFormula = (CritFormula + (Op + (" \'" + (Value + "\'"))));
}
}
CritFormula = (CritFormula + (") " + (Link + " ")));
}
else
{
Value = Value.Replace("*", "%");
if ((Value == "NULL"))
{
Criteria = (Criteria + (((Op == "<>") ? " NOT" : "") + ("(" + (((T == "") ? Fld : (T + ("." + Fld))) + " IS Null"))));
}
else
{
Value = (((Typ == 3) ? "" : "\'") + (((Typ == 7) ? string.Format("MM/dd/yyyy", Vdate) : Value) + ((Typ == 3) ? "" : "\'")));
Criteria = (Criteria + ("(" + (((T == "") ? "" : (T + ".")) + (Fld + (" " + (Op + (" " + Value)))))));
}
Criteria = (Criteria + (") " + (Link + " ")));
}
}
}
if ((CritFormula != ""))
{
CritFormula = ("(" + (CritFormula.Substring(0, (CritFormula.Length - 4)) + ")"));
CritFormula = CritFormula.Replace(" AND ", ") AND (");
}
if ((DataTools.ReadField(dtObject.Rows[0]["FilterOnFormula"]) != ""))
{
CritFormula = (CritFormula + (((CritFormula == "") ? "" : " AND ") + ("(" + (DataTools.ReadField(dtObject.Rows[0]["FilterOnFormula"]) + ")"))));
}
if ((Criteria != ""))
{
Criteria = ("(" + (Criteria.Substring(0, (Criteria.Length - 4)) + ")"));
Criteria = Criteria.Replace(") AND (", ")) AND ((");
}
if ((DataTools.ReadField(dtObject.Rows[0]["FilterOn"]) != ""))
{
Criteria = (Criteria + (((Criteria == "") ? "" : " AND ") + ("(" + (DataTools.ReadField(dtObject.Rows[0]["FilterOn"]) + ")"))));
}
Fld = ApplyBranchPermissions(dtObject);
if ((Fld != ""))
{
if ((Fld.IndexOf("{")) == 0)
{
CritFormula = (CritFormula + (((CritFormula == "") ? "" : " AND ") + ("(" + (Fld + ")"))));
}
else
{
Criteria = (Criteria + (((Criteria == "") ? "" : " AND ") + ("(" + (Fld + ")"))));
}
}
Fld = ApplySalesmanPermissions(dtObject);
if ((Fld != ""))
{
if ((Fld.IndexOf("{")) == 0)
{
CritFormula = (CritFormula + (((CritFormula == "") ? "" : " AND ") + ("(" + (Fld + ")"))));
}
else
{
Criteria = (Criteria + (((Criteria == "") ? "" : " AND ") + ("(" + (Fld + ")"))));
}
}
}
public ReportDocument PrepareReport()
{
int i;
DataTable dtObject = new DataTable();
string Op;
string SQL = "";
string Criteria = "";
string CritFormula = "";
string T;
//DateTime Vdate = new DateTime();
SqlDataAdapter daObject = new SqlDataAdapter(("SELECT * FROM Objects WHERE ObjectID=" + _ReportID), DataTools.GetConnectionStr());
bool SP;
daObject.Fill(dtObject);
SQL = dtObject.Rows[0]["SqlStatment"].ToString();
_RepTitle = dtObject.Rows[0]["ObjectTitle"].ToString();
string RepPath = DataTools.GetConfigSetting("RepPath");
if ((RepPath == ""))
{
RepPath = Server.MapPath("Reports/");
}
rpt.Load((RepPath + dtObject.Rows[0]["ObjectName"].ToString().Split(';')[0]));
if ((bool)dtObject.Rows[0]["ConnectToSQL"])
{
SetReportConnections(dtObject);
try
{
rpt.VerifyDatabase();
}
catch
{
}
}
ReadCriteria(ref Criteria, ref CritFormula, dtObject);
if (!(bool)dtObject.Rows[0]["ConnectToSQL"])
{
if ((Criteria != ""))
{
SQL = DataTools.AddCriteriaToSql(SQL, Criteria);
}
SqlDataAdapter daReport = new SqlDataAdapter(SQL, DataTools.GetConnectionStr());
if ((SQL.IndexOf("@Crit") + 1 == 0))
{
daReport.Fill(dsReport, dtObject.Rows[0]["Source"].ToString());
}
}
else
{
rpt.SetParameterValue(SQL, Criteria);
}
if ((CritFormula != ""))
{
if ((rpt.RecordSelectionFormula == ""))
{
rpt.RecordSelectionFormula = CritFormula;
}
else
{
rpt.RecordSelectionFormula = ("(" + (rpt.RecordSelectionFormula + (") AND (" + (CritFormula + ")"))));
}
}
SQL = DataTools.ReadField(dtObject.Rows[0]["SqlNext"]);
if ((SQL != ""))
{
string[] NArr;
string NCrit;
string TmpStr;
string Sstr;
NArr = SQL.Split(';');
for (i = 0; (i <= (NArr.Length - 1)); i++)
{
NCrit = "";
SQL = DataTools.GetStrPart(NArr[i].ToString(), 0, "^");
TmpStr = DataTools.GetStrPart(NArr[i].ToString(), 3, "^");
Sstr = DataTools.GetStrPart(NArr[i].ToString(), 10, "^");
if ((Sstr.Substring(0, 1) == "S"))
{
NCrit = Criteria;
string[] Sarr = Sstr.Split('=');
if (((Sarr).Length > 0))
{
for (int k = 1; k < Sarr.Length; k++)
{
// If InStr(1, Sarr(k), ">") Then
if ((Sarr[k].IndexOf(">", 0) + 1) != 0) //not sure
{
NCrit = NCrit.Replace(DataTools.GetStrPart(Sarr[k], 0, ">"), DataTools.GetStrPart(Sarr[k], 1, ">"));
}
else
{
NCrit = DelSQLFldCriteria(Sarr[k], NCrit);
}
}
}
}
if ((TmpStr != ""))
{
string MinMax;
string DataField = DataTools.GetStrPart(NArr[i].ToString(), 4, "^");
DataField = ((DataField == "") ? TmpStr : DataField);
Op = DataTools.GetStrPart(NArr[i].ToString(), 6, "^");
Op = ((Op == "") ? "<" : Op);
if ((DataTools.GetStrPart(NArr[i].ToString(), 5, "^") == "MAX"))
{
MinMax = GetFldCriteriaLmt(TmpStr, Criteria, true);
if ((MinMax == ""))
{
//If MinMax = "" Then MinMax = Format(DateAdd(DateInterval.Year, 100, Date.Today), "MM/dd/yyyy")
MinMax = string.Format("MM/dd/yyyy", DateTime.Today.AddYears(100)); //increase date by 100y
}
else
{
MinMax = GetFldCriteriaLmt(TmpStr, Criteria, false);
if ((MinMax == ""))
{
MinMax = string.Format("MM/dd/yyyy", DateTime.Today.AddYears(-100));
}
if ((NCrit != ""))
{
NCrit = (NCrit + " AND ");
}
NCrit = (NCrit + ("(" + (DataField + (Op + ("'" + (MinMax + "')"))))));
}
if (!(bool)dtObject.Rows[0]["ConnectToSQL"])
{
// SP = GenericMethods.GetStrPart(NArr(i), 11, "^") = "SP"
//If NCrit <> "" And Not SP Then
// SQL = GenericMethods.AddCriteriaToSql(SQL, NCrit)
//End If
SP = (DataTools.GetStrPart(NArr[i].ToString(), 11, "^") == "SP");
if (((NCrit != "") && !SP))
{
SQL = DataTools.AddCriteriaToSql(SQL, NCrit);
}
string IndexName = DataTools.GetStrPart(NArr[i].ToString(), 2, "^");
//Dim Access As Boolean = UCase(GenericMethods.GetStrPart(NArr(i), 7, "^")) = "Y"
bool Access = (DataTools.GetStrPart(NArr[i].ToString(), 7, "^")).ToUpper() == "Y";
//IIf(IndexName = "", False, True) Or UCase(GenericMethods.GetStrPart(NArr(i), 8, "^")) = "T"
bool TB = (((IndexName == "") ? false : true) | (DataTools.GetStrPart(NArr[i].ToString(), 8, "^").ToUpper()) == "T");
string AppToTb = DataTools.GetStrPart(NArr[i].ToString(), 9, "^");
T = DataTools.GetStrPart(NArr[i].ToString(), 1, "^");
if (!Access)
{
SqlConnection Cnn = new SqlConnection(DataTools.GetConnectionStr());
SqlCommand cmd = new SqlCommand(SQL, Cnn);
if (SP)
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Crit", NCrit);
}
SqlDataAdapter daN = new SqlDataAdapter(cmd);
if ((AppToTb != ""))
{
daN.Fill(dsReport.Tables[AppToTb]);
}
else
{
daN.Fill(dsReport, T);
}
}
else
{
// 'lbl()
}
}
else
{
rpt.SetParameterValue(SQL, NCrit);
}
}
// Connection Info Was Here '''''''''''''''''''''''''
// Connection Info '''''''''''''''''''''''''''''''''''''''''''''''''''''''
if (!(bool)dtObject.Rows[0]["ConnectToSQL"])
{
SetReportConnections(dtObject);
}
// ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
// '''''''''''''''''''''''''''''''''''''''''''''''''''
string objectTitle = dtObject.Rows[0]["ObjectTitle"].ToString();
rpt.DataDefinition.FormulaFields["CompanyName"].Text = "'Saudisoft Company Limited'";
if ((_RepLan == "Arabic"))
{
dtObject.Rows[0]["ObjectTitle"] = DataTools.GetDataTable(("Select ArabicCap From Dic Where FieldName='" + (objectTitle + "'")), DataTools.GetConnectionStr());
}
rpt.DataDefinition.FormulaFields["ReportTitle"].Text = ("'" + (objectTitle + "'"));
rpt.DataDefinition.FormulaFields["CompanyBranch"].Text = "'Jeddah Branch'";
rpt.DataDefinition.FormulaFields["Issuedby"].Text = ("'" + (DataTools.GetUserName() + "'"));
}
}
}
return(rpt);
}