public static void GetChildren(String table_name, String parent_key, String primary_key, String value, ref List <String> result)
{
String sql = "SELECT " + primary_key + " FROM " + table_name + " WHERE " + parent_key + " = @value";
var dt = DataSupport.RunDataSet(sql, "value", value).Tables[0];
foreach (DataRow row in dt.Rows)
{
String child_value = row[primary_key].ToString();
result.Add(child_value);
GetChildren(table_name, parent_key, primary_key, child_value, ref result);
}
}
public bool update()
{
try
{
Dictionary <String, Object> Params = new Dictionary <String, Object>();
Params.Add("_nudindex", _nudindex);
Params.Add("customer_name", cboCustomer.Text);
Params.Add("totalamount", totalinvoicev);
Params.Add("terms", cboTerms.Text);
Params.Add("clientname", cboclientname.SelectedValue);
Params.Add("deldate", dtdelivered.Value.ToShortDateString());
Params.Add("dateprepared", dtprepared.Value.ToShortDateString());
Params.Add("datestamp", DateTime.Now.Date);
Params.Add("delman", txtdelman.Text);
List <String> primary = new List <String>();
primary.Add("_nudindex");
string sql = DataSupport.GetUpdate("global_NUD", Params, primary);
foreach (DataGridViewRow row in nudgrid.Rows)
{
if (row.IsNewRow)
{
continue;
}
Dictionary <String, Object> Param1 = new Dictionary <String, Object>();
Param1.Add("_index", row.Cells["index"].Value.ToString().Trim());
Param1.Add("invoice_no", row.Cells["invoice_no"].Value.ToString().Trim());
Param1.Add("invoice_date", row.Cells["invoice_date"].Value.ToString().Trim());
Param1.Add("amount", row.Cells["nudvalue"].Value.ToString().Trim());
Param1.Add("reason", row.Cells["reason"].Value.ToString().Trim());
Param1.Add("delstat", row.Cells["delstat"].Value.ToString().Trim());
Param1.Add("datestamp", DateTime.Now.Date);
Param1.Add("paidby", row.Cells["account"].Value.ToString().Trim());
Param1.Add("sdrrno", row.Cells["sdrrno"].Value.ToString().Trim());
primary = new List <String>();
primary.Add("_index");
sql += DataSupport.GetUpdate("global_NUDDetails", Param1, primary);
}
Utils.ExecuteNonQuery(sql, null);
DevComponents.DotNetBar.MessageBoxEx.Show("Update Complete");
return(true);
}
catch (Exception ex)
{
DevComponents.DotNetBar.MessageBoxEx.Show(ex.Message + " \n Please Report this to the programmer!");
return(false);
}
}
public static string GetDataString(String sql, params Object[] parameters)
{
DataTable dt = DataSupport.RunDataSet(sql).Tables[0];
if (dt.Rows.Count == 0)
{
return("");
}
else
{
return(dt.Rows[0][0].ToString());
}
}
public static int GetDataInt(String sql, params Object[] parameters)
{
DataTable dt = DataSupport.RunDataSet(sql).Tables[0];
if (dt.Rows.Count == 0)
{
return(0);
}
else
{
return(Convert.ToInt32(dt.Rows[0][0]));
}
}
public static decimal GetDataDecimal(String sql, params Object[] parameters)
{
DataTable dt = DataSupport.RunDataSet(sql).Tables[0];
if (dt.Rows.Count == 0)
{
return(Convert.ToDecimal("0.00"));
}
else
{
return(Convert.ToDecimal(dt.Rows[0][0]));
}
}
private void InitializeGrid()
{
String ConnectionString;
lblSDRRNo.Text = _clientname[1];
lblpath.Text = "";
invoicedata.Columns.Add(new DataColumn("gridcolno", typeof(Boolean)));
invoicedata.Columns.Add(new DataColumn("gridcolinvno", typeof(string)));
invoicedata.Columns.Add(new DataColumn("gridcolamt", typeof(string)));
invoicedata.Columns.Add(new DataColumn("Invoice_Date", typeof(string)));
invoicedata.Columns.Add(new DataColumn("gridcolcust", typeof(string)));
invoicedata.Columns.Add(new DataColumn("gridcolzone", typeof(string)));
invoicedata.Columns.Add(new DataColumn("Term", typeof(string)));
invoicedata.Columns.Add(new DataColumn("index", typeof(string)));
if (Utils.ClientConnection.ContainsKey(_clientname[0]))
{
if (Utils.ClientConnection[_clientname[0]]["DBTYPE"] == "DOS")
{
try
{
ConnectionString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source = {0};Extended Properties =dBase IV;", Utils.ClientConnection[_clientname[0]]["Directory"]);
using (OleDbConnection dBaseConnection = new OleDbConnection(ConnectionString))
{
dBaseConnection.Open();
System.Data.OleDb.OleDbCommand dBaseCommand;
dBaseCommand = new System.Data.OleDb.OleDbCommand("SELECT TERM, ZONEFLAG, SINUMBER, SIDATE, GROSSAMT, CUSTNAME ORDER BY sidate DESC", dBaseConnection);
System.Data.OleDb.OleDbDataReader dBaseDataReader;
dBaseDataReader = dBaseCommand.ExecuteReader();
while (dBaseDataReader.Read())
{
DataRow tblRow = invoicedata.NewRow();
tblRow["gridcolno"] = false;
tblRow["term"] = dBaseDataReader["term"];
tblRow["gridcolzone"] = dBaseDataReader["zoneflag"];
tblRow["gridcolinvno"] = dBaseDataReader["sinumber"];
tblRow["invoice_date"] = dBaseDataReader["sidate"];
tblRow["gridcolamt"] = dBaseDataReader["grossamt"];
tblRow["gridcolcust"] = dBaseDataReader["custname"];
tblRow["gridcolindex"] = 0;
invoicedata.Rows.Add(tblRow);
}
}
lblpath.Text = Utils.ClientConnection[_clientname[0]]["Directory"];
}
catch (OleDbException ex)
{
DevComponents.DotNetBar.MessageBoxEx.Show(String.Format("{0}:{1}", ex.ErrorCode, ex.Message));
}
}
else if (Utils.ClientConnection[_clientname[0]]["DBTYPE"] == "SQL")
{
DataSupport.setDBConnection(Utils.ClientConnection[_clientname[0]]["Directory"]);
invoicedata = Utils.GetDataTable("SELECT 'false'[gridcolno],R.terms[term],R.zone[gridcolzone],docno[gridcolinvno],R.docdate[invoice_date],grossamt[gridcolamt],c.name[gridcolcust],0[gridcolindex] FROM REPORT_register R join base_customer C ON C._custindex = R.sourcecode WHERE R.DocType = 2 order by convert(datetime, R.docdate, 101) DESC", null);
}
else if (Utils.ClientConnection[_clientname[0]]["DBTYPE"] == "SDF")
{
}
if (_invoice.Count >= 1)
{
foreach (KeyValuePair <String, List <String> > item in _invoice)
{
foreach (DataRow invoice in invoicedata.Select(String.Format("gridcolinvno={0}", item.Value[0])))
{
invoice["gridcolno"] = true;
invoice["gridcolindex"] = item.Value[item.Value.Count - 3];
string s = item.Value[item.Value.Count - 3];
}
}
}
gridinvoice.PrimaryGrid.DataSource = invoicedata;
DataSupport.setDBConnection("Integrated Security=SSPI;Initial Catalog=sdrr;Data Source=.\\SQLEXPRESS; User Id = vgl_data; Password = tqbfjotld;");
}
}
private Dictionary <String, DataTable> getPrintableData(Dictionary <String, Object> param)
{
Dictionary <String, DataTable> result = new Dictionary <String, DataTable>();
System.Data.DataTable tbl = new System.Data.DataTable();
tbl.Columns.Add(new DataColumn("TripNo", typeof(string)));
tbl.Columns.Add(new DataColumn("PlateNo", typeof(string)));
tbl.Columns.Add(new DataColumn("TruckType", typeof(string)));
tbl.Columns.Add(new DataColumn("Client", typeof(string)));
tbl.Columns.Add(new DataColumn("SDRRNo", typeof(string)));
tbl.Columns.Add(new DataColumn("Customer", typeof(string)));
tbl.Columns.Add(new DataColumn("Zone", typeof(string)));
tbl.Columns.Add(new DataColumn("isCovered", typeof(string)));
tbl.Columns.Add(new DataColumn("InvoiceNo", typeof(string)));
tbl.Columns.Add(new DataColumn("Amount", typeof(Decimal)));
tbl.Columns.Add(new DataColumn("DateRec", typeof(string)));
tbl.Columns.Add(new DataColumn("Status", typeof(string)));
tbl.Columns.Add(new DataColumn("Remarks", typeof(string)));
tbl.Columns.Add(new DataColumn("invoicedate", typeof(string)));
Dictionary <String, DataRow> sdrrmast = new Dictionary <String, DataRow>();
sdrrmast = Utils.BuildIndex_SQL("SELECT * FROM GLOBAL_SDRRMaster", "sdrrno");
Dictionary <String, DataRow> updatedsdrr = new Dictionary <String, DataRow>();
List <String> col = new List <string>();
col.Add("sdrrno");
col.Add("invoice_no");
updatedsdrr = Utils.BuildIndex_SQL("SELECT * FROM GLOBAL_ProductTrans", col, " ");
DataSupport.setDBConnection("Integrated Security=SSPI;Initial Catalog=dispatch;Data Source=.\\SQLEXPRESS; User Id = vgl_data; Password = tqbfjotld;");
Dictionary <String, DataRow> sdrrtrip = new Dictionary <String, DataRow>();
sdrrtrip = Utils.BuildIndex_SQL("select SD.tripindex,SD.SDRRNo, TT.VehlIndex FROM GLOBAL_SDRRTrip sd LEFT JOIN GLOBAL_tRIPtICKET tt ON tt.tripno = sd.tripindex AND TT.yearcycle = sd.yearcycle", "SDRRno");
Dictionary <String, DataRow> vehltrip = new Dictionary <String, DataRow>();
vehltrip = Utils.BuildIndex_SQL("select * FROM BASE_Vehicle", "_VehlIndex");
DataSupport.setDBConnection("Integrated Security=SSPI;Initial Catalog=sdrr;Data Source=.\\SQLEXPRESS; User Id = vgl_data; Password = tqbfjotld;");
DataTable zone_sbs = new DataTable();
zone_sbs.Columns.Add(new DataColumn("zone1", typeof(Decimal)));
zone_sbs.Columns.Add(new DataColumn("zone2", typeof(Decimal)));
zone_sbs.Columns.Add(new DataColumn("zone3", typeof(Decimal)));
zone_sbs.Columns.Add(new DataColumn("zone4", typeof(Decimal)));
zone_sbs.Columns.Add(new DataColumn("zone5", typeof(Decimal)));
zone_sbs.Columns.Add(new DataColumn("zone6", typeof(Decimal)));
zone_sbs.Columns.Add(new DataColumn("zone7", typeof(Decimal)));
zone_sbs.Columns.Add(new DataColumn("zone8", typeof(Decimal)));
zone_sbs.Columns.Add(new DataColumn("totalamt", typeof(Decimal)));
DataRow tblRow;
String SQLCommand = "";
if (param["client"].ToString() == "ALL CLIENTS")
{
if (param["delstat"].ToString() == "DELIVERED")
{
HeaderName = "SUMMARY OF BILLING SUPPORT DELIVERED";
SQLCommand = "SELECT DISTINCT US.*, SM.Client FROM GLOBAL_ProductTrans US JOIN GLOBAL_SDRRMaster SM ON SM.SDRRNO = US.SDRRNO WHERE US.[delstat] = '" + param["delstat"].ToString() + "' AND MONTH(US.[datedel]) = " + param["monthcycle"].ToString() + " AND Year(US.[datedel]) = " + param["yearcycle"].ToString() + " AND invoice_date IS NOT NULL ORDER BY [zone],[datedel],US.[invoice_no] ASC,US.[delstat] DESC";
}
else if (param["delstat"].ToString() == "ALL NON-DELIVERED")
{
HeaderName = "SUMMARY OF BILLING SUPPORT " + param["delstat"].ToString();
SQLCommand = "SELECT DISTINCT US.*, SM.Client FROM GLOBAL_ProductTrans US JOIN GLOBAL_SDRRMaster SM ON SM.SDRRNO = US.SDRRNO WHERE US.[delstat] != 'DELIVERED' AND US.[delstat] != '' AND MONTH(SM.[date]) = " + param["monthcycle"].ToString() + " AND Year(SM.[date]) = " + param["yearcycle"].ToString() + " AND invoice_date IS NOT NULL ORDER BY [zone],US.[datedel],US.[invoice_no] ASC, US.[delstat] DESC";
}
else if (param["delstat"].ToString() == "ALL STATUS")
{
HeaderName = "SUMMARY OF BILLING SUPPORT " + param["delstat"].ToString();
SQLCommand = "SELECT DISTINCT US.*, SM.Client FROM GLOBAL_ProductTrans US JOIN GLOBAL_SDRRMaster SM ON SM.SDRRNO = US.SDRRNO WHERE US.[delstat] != 'DELIVERED' AND MONTH(SM.[date]) = " + param["monthcycle"].ToString() + " AND Year(SM.[date]) = " + param["yearcycle"].ToString() + " AND invoice_date IS NOT NULL ORDER BY [zone],US.[datedel],US.[invoice_no] ASC, US.[delstat] DESC";
}
else
{
HeaderName = "SUMMARY OF BILLING SUPPORT " + param["delstat"].ToString();
SQLCommand = "SELECT DISTINCT US.*, SM.Client FROM GLOBAL_ProductTrans US JOIN GLOBAL_SDRRMaster SM ON SM.SDRRNO = US.SDRRNO WHERE US.[delstat] = '" + param["delstat"].ToString() + "' AND US.[delstat] != '' AND MONTH(SM.[date]) = " + param["monthcycle"].ToString() + " AND Year(SM.[date]) = " + param["yearcycle"].ToString() + " AND invoice_date IS NOT NULL ORDER BY [zone],US.[datedel],US.[invoice_no] ASC, US.[delstat] DESC";
}
}
else
{
//if (param["delstat"].ToString() == "DELIVERED")
// SQLCommand = "SELECT DISTINCT US.*, SM.Client FROM GLOBAL_ProductTrans US JOIN GLOBAL_SDRRMaster SM ON SM.SDRRNO = US.SDRRNO WHERE US.[delstat] = '" + param["delstat"].ToString() + "' AND SM.Client = '" + param["client"].ToString() + "' AND MONTH(US.[datedel]) = " + param["monthcycle"].ToString() + " AND Year(US.[datedel]) = " + param["yearcycle"].ToString() + " ORDER BY [zone],[datedel],US.[invoice_no] ASC,US.[delstat] DESC";
//else
//{
// HeaderName = "SUMMARY OF BILLING SUPPORT OTHERS";
// SQLCommand = "SELECT DISTINCT US.*, SM.Client FROM GLOBAL_ProductTrans US JOIN GLOBAL_SDRRMaster SM ON SM.SDRRNO = US.SDRRNO WHERE US.[delstat] != 'DELIVERED' AND US.[delstat] != '' AND SM.Client = '" + param["client"].ToString() + "' AND MONTH(SM.[date]) = " + param["monthcycle"].ToString() + " AND Year(SM.[date]) = " + param["yearcycle"].ToString() + " ORDER BY [zone],US.[datedel],US.[invoice_no] ASC, US.[delstat] DESC";
//}
}
DataTable dtRep = Utils.GetDataTable(SQLCommand);
decimal billedamt = 0;
decimal[] zone = new decimal[8];
foreach (DataRow dRow in dtRep.Rows)
{
DataRow Rowmast = null;
DataRow RowUpdate = null;
DataRow RowTrip = null;
DataRow RowVehl = null;
tblRow = tbl.NewRow();
if (sdrrtrip.TryGetValue(dRow["sdrrno"].ToString().Trim(), out RowTrip))
{
tblRow["TripNo"] = RowTrip["tripindex"].ToString();
if (vehltrip.TryGetValue(RowTrip["VehlIndex"].ToString(), out RowVehl))
{
tblRow["PlateNo"] = RowVehl["TruckType"].ToString();
tblRow["TruckType"] = RowVehl["maker"].ToString();
}
}
if (sdrrmast.TryGetValue(dRow["sdrrno"].ToString().Trim(), out Rowmast))
{
tblRow["Client"] = Rowmast["Client"].ToString();
}
String cols = dRow["sdrrno"].ToString().Trim() + " " + dRow["invoice_no"].ToString().Trim();
if (updatedsdrr.TryGetValue(cols, out RowUpdate))
{
tblRow["SDRRNo"] = RowUpdate["sdrrno"].ToString();
tblRow["Customer"] = RowUpdate["Customer_name"].ToString();
tblRow["Zone"] = RowUpdate["Zone"].ToString();
tblRow["isCovered"] = RowUpdate["isCovered"].ToString();
}
if (dRow["DateDel"].ToString() != "")
{
tblRow["DateRec"] = Convert.ToDateTime(dRow["DateDel"].ToString()).ToShortDateString();
}
tblRow["InvoiceNo"] = dRow["invoice_no"].ToString();;
tblRow["Amount"] = dRow["invoice_value"].ToString();
tblRow["Status"] = dRow["delStat"].ToString();
tblRow["Remarks"] = dRow["remarks"].ToString();
if (dRow["invoice_date"].ToString() != "")
{
tblRow["invoicedate"] = Convert.ToDateTime(dRow["invoice_date"]).ToShortDateString();
}
else
{
tblRow["invoicedate"] = dRow["invoice_date"].ToString();
}
tbl.Rows.Add(tblRow);
billedamt += Convert.ToDecimal(tblRow["Amount"]);
switch (tblRow["Zone"].ToString())
{
case "1":
zone[0] += Convert.ToDecimal(tblRow["Amount"]);
break;
case "2":
zone[1] += Convert.ToDecimal(tblRow["Amount"]);
break;
case "3":
zone[2] += Convert.ToDecimal(tblRow["Amount"]);
break;
case "4":
zone[3] += Convert.ToDecimal(tblRow["Amount"]);
break;
case "5":
zone[4] += Convert.ToDecimal(tblRow["Amount"]);
break;
case "6":
zone[5] += Convert.ToDecimal(tblRow["Amount"]);
break;
case "7":
zone[6] += Convert.ToDecimal(tblRow["Amount"]);
break;
case "8":
zone[7] += Convert.ToDecimal(tblRow["Amount"]);
break;
}
}
//if (_reportname == "GENBILLREPORT")
//{
sbs_total = new Dictionary <String, String>();
sbs_total.Add("totalinvoice", dtRep.Rows.Count.ToString());
sbs_total.Add("billedamt", billedamt.ToString());
tblRow = zone_sbs.NewRow();
tblRow["zone1"] = zone[0];
tblRow["zone2"] = zone[1];
tblRow["zone3"] = zone[2];
tblRow["zone4"] = zone[3];
tblRow["zone5"] = zone[4];
tblRow["zone6"] = zone[5];
tblRow["zone7"] = zone[6];
tblRow["zone8"] = zone[7];
tblRow["totalamt"] = zone[0] + zone[1] + zone[2] + zone[3] + zone[4] + zone[5] + zone[6] + zone[7];
zone_sbs.Rows.Add(tblRow);
result.Add("zone", zone_sbs);
sbs_total.Add("batchcode", "");
//}
result.Add("main", tbl);
return(result);
}
public static int ExecuteNonQuery(String sql, Dictionary <String, Object> parameters)
{
return(DataSupport.RunNonQuery(sql, parameters));
}
public static int ExecuteStoredProcedureNonQuery(String ProcedureName, Dictionary <String, Object> parameters)
{
return(DataSupport.ExecuteStoredProcedureNonQuery(ProcedureName, parameters));
}
public static DataTable ExecuteStoredProcedure(String ProcedureName, Dictionary <String, Object> parameters)
{
return(DataSupport.ExecuteStoredProcedure(ProcedureName, parameters));
}
public static DataTable GetDataTable(String sql, params Object[] parameters)
{
return(DataSupport.RunDataSet(sql).Tables[0]);
}
public static System.Data.DataSet GetDatasetFast(String sql, params Object[] parameters)
{
return(DataSupport.RunDataSetFast(sql, Utils.ToDict(parameters)));
}
public static System.Data.DataSet GetDataset(String sql)
{
return(DataSupport.RunDataSet(sql));
}
