Ejemplo n.º 1
0
        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);
            }
        }
Ejemplo n.º 2
0
        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);
            }
        }
Ejemplo n.º 3
0
        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());
            }
        }
Ejemplo n.º 4
0
        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]));
            }
        }
Ejemplo n.º 5
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]));
            }
        }
Ejemplo n.º 6
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;");
            }
        }
Ejemplo n.º 7
0
        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);
        }
Ejemplo n.º 8
0
 public static int ExecuteNonQuery(String sql, Dictionary <String, Object> parameters)
 {
     return(DataSupport.RunNonQuery(sql, parameters));
 }
Ejemplo n.º 9
0
 public static int ExecuteStoredProcedureNonQuery(String ProcedureName, Dictionary <String, Object> parameters)
 {
     return(DataSupport.ExecuteStoredProcedureNonQuery(ProcedureName, parameters));
 }
Ejemplo n.º 10
0
 public static DataTable ExecuteStoredProcedure(String ProcedureName, Dictionary <String, Object> parameters)
 {
     return(DataSupport.ExecuteStoredProcedure(ProcedureName, parameters));
 }
Ejemplo n.º 11
0
 public static DataTable GetDataTable(String sql, params Object[] parameters)
 {
     return(DataSupport.RunDataSet(sql).Tables[0]);
 }
Ejemplo n.º 12
0
 public static System.Data.DataSet GetDatasetFast(String sql, params Object[] parameters)
 {
     return(DataSupport.RunDataSetFast(sql, Utils.ToDict(parameters)));
 }
Ejemplo n.º 13
0
 public static System.Data.DataSet GetDataset(String sql)
 {
     return(DataSupport.RunDataSet(sql));
 }