Exemple #1
0
        private void writeTableContentToInsertStatement(string tableName, StreamWriter sw, Data_Access DAccess, bool isHQConnection = false, bool skipFirstField = false, bool skipAddBranchID = false, string sqlParam = "", string customTableName = "")
        {
            string sqlCommand = "";
            MySqlDataReader rdr;
            string insertStatement = "";
            string valueStatement = "";
            int rdrFieldIndex = 0;
            int startIndex = 0;
            DateTime tempDateTime;
            string dateTimeValue;
            object DBValue = null;
            int branchID = 0;
            string namaCabang = "";

            branchID = gUtil.loadbranchID(2, out namaCabang);

            if (sqlParam.Length <= 0)
                sqlCommand = "SELECT * FROM " + tableName;
            else
                sqlCommand = sqlParam;

            if (skipFirstField)
                startIndex = 1;

            sw.WriteLine("");
            using (rdr = DAccess.getData(sqlCommand, isHQConnection))
            {
                if (rdr.HasRows)
                {
                    while (rdr.Read())
                    {
                        if (customTableName.Length > 0)
                            insertStatement = "INSERT INTO " + customTableName + "(";
                        else
                            insertStatement = "INSERT INTO " + tableName + "(";

                        valueStatement = "";

                        for (rdrFieldIndex = startIndex; rdrFieldIndex < rdr.FieldCount; rdrFieldIndex++)
                        {
                            DBValue = rdr.GetValue(rdrFieldIndex);

                            if (DBValue.ToString().Length > 0)
                            {
                                insertStatement = insertStatement + rdr.GetName(rdrFieldIndex) + ", ";

                                if (rdr.GetDataTypeName(rdrFieldIndex) == "DATE")
                                {
                                    tempDateTime = rdr.GetDateTime(rdrFieldIndex);
                                    dateTimeValue = String.Format(culture, "{0:dd-MM-yyyy}", tempDateTime);

                                    valueStatement = valueStatement + "STR_TO_DATE('" + dateTimeValue + "', '%d-%m-%Y'), ";
                                }
                                else if (rdr.GetDataTypeName(rdrFieldIndex) == "DATETIME")
                                {
                                    tempDateTime = rdr.GetDateTime(rdrFieldIndex);
                                    dateTimeValue = gUtil.getCustomStringFormatDate(tempDateTime);

                                    valueStatement = valueStatement + "STR_TO_DATE('" + dateTimeValue + "', '%d-%m-%Y %H:%i'), ";
                                }
                                else
                                    valueStatement = valueStatement + "'" + Convert.ToString(rdr.GetValue(rdrFieldIndex)) + "', ";
                            }
                        }

                        if (!skipAddBranchID)
                        {
                            insertStatement = insertStatement + "BRANCH_ID) VALUES(";
                            valueStatement = valueStatement + branchID + ");";
                        }
                        else
                        {
                            insertStatement = insertStatement.Substring(0, insertStatement.Length - 2);
                            insertStatement = insertStatement + ") VALUES(";

                            valueStatement = valueStatement.Substring(0, valueStatement.Length - 2);
                            valueStatement = valueStatement + ");";
                        }

                        insertStatement = insertStatement + valueStatement;

                        sw.WriteLine(insertStatement);
                    }
                }
                rdr.Close();
            }
            sw.WriteLine("");
        }
        private bool saveToCSV(string fileName)
        {
            //string fileName = "";
            //string localDate;
            string          sqlCommand;
            string          line = "";
            MySqlDataReader rdr;
            StreamWriter    sw          = null;
            bool            firstColumn = true;
            StringBuilder   builder     = new StringBuilder();
            string          value       = "";

            if (fileName.Length <= 0)
            {
                return(false);
            }

            //localDate = String.Format(culture, "{0:ddMMyyyy}", DateTime.Now);

            //fileName = "EXPORT_" + localDate + ".csv";

            //localDate = String.Format(culture, "{0:dd-MMM-yyyy}", DateTime.Now);

            sqlCommand = "SELECT PRODUCT_ID, PRODUCT_BARCODE, PRODUCT_NAME, PRODUCT_STOCK_QTY, 0 AS PRODUCT_ACTUAL_QTY, '' AS DESCRIPTION FROM MASTER_PRODUCT WHERE PRODUCT_ACTIVE = 1 ORDER BY ID";

            using (rdr = DS.getData(sqlCommand))
            {
                if (rdr.HasRows)
                {
                    if (!File.Exists(fileName))
                    {
                        sw = File.CreateText(fileName);
                    }
                    else
                    {
                        File.Delete(fileName);
                        sw = File.CreateText(fileName);
                    }

                    //sw.WriteLine(localDate);

                    builder.Append("KODE PRODUK");
                    builder.Append(",");
                    builder.Append("BARCODE PRODUK");
                    builder.Append(",");
                    builder.Append("NAMA PRODUK");
                    builder.Append(",");
                    builder.Append("QTY PRODUK");
                    builder.Append(",");
                    builder.Append("QTY RIIL");
                    builder.Append(",");
                    builder.Append("DESCRIPTION");

                    line = builder.ToString();
                    sw.WriteLine(line);

                    while (rdr.Read())
                    {
                        builder.Clear();
                        firstColumn = true;
                        for (int index = 0; index < rdr.FieldCount; index++)
                        {
                            value = rdr.GetString(index);
                            // Add separator if this isn't the first value
                            if (!firstColumn)
                            {
                                builder.Append(',');
                            }
                            // Implement special handling for values that contain comma or quote
                            // Enclose in quotes and double up any double quotes
                            if (value.IndexOfAny(new char[] { '"', ',' }) != -1)
                            {
                                builder.AppendFormat("\"{0}\"", value.Replace("\"", "\"\""));
                            }
                            else
                            {
                                builder.Append(value);
                            }
                            //else
                            //    builder.AppendFormat("\'{0}\'", value);
                            firstColumn = false;
                        }
                        line = builder.ToString();
                        //line = rdr.GetString("PRODUCT_ID") + ";" + rdr.GetString("PRODUCT_BARCODE") + ";" + rdr.GetString("PRODUCT_NAME") + ";" + rdr.GetString("PRODUCT_STOCK_QTY") + ";0;";
                        sw.WriteLine(line);
                    }

                    sw.Close();
                }
            }

            return(true);
        }
Exemple #3
0
        private void loadProdukData()
        {
            MySqlDataReader rdr;
            DataTable       dt               = new DataTable();
            string          sqlCommand       = "";
            string          namaProductParam = "";
            string          kodeProductParam = "";
            string          showactive       = "";
            int             locationID       = 0;

            DS.mySqlConnect();

            //if (namaProdukTextBox.Text.Equals(""))
            //    return;
            namaProductParam = MySqlHelper.EscapeString(namaProdukTextBox.Text);
            //kodeProductParam = MySqlHelper.EscapeString(textBox1.Text);
            locationID = gutil.loadlocationID(2);
            if (produknonactiveoption.Checked == false)
            {
                showactive = "AND MP.PRODUCT_ACTIVE = 1 ";
            }

            if (originModuleID == globalConstants.RETUR_PENJUALAN)
            {
                if (locationID > 0)
                {
                    sqlCommand = "SELECT MP.ID, MP.PRODUCT_ID AS 'PRODUK ID', MP.PRODUCT_NAME AS 'NAMA PRODUK', PL.PRODUCT_LOCATION_QTY AS 'QTY' " +
                                 "FROM MASTER_PRODUCT MP, SALES_DETAIL SD, PRODUCT_LOCATION PL " +
                                 "WHERE SD.SALES_INVOICE = '" + returJualSearchParam + "' AND SD.PRODUCT_ID = MP.PRODUCT_ID AND MP.PRODUCT_IS_SERVICE = 0 " + showactive +
                                 "AND MP.PRODUCT_ID LIKE '%" + kodeProductParam + "%' AND MP.PRODUCT_NAME LIKE '%" + namaProductParam + "%' " +
                                 "AND PL.PRODUCT_ID = MP.PRODUCT_ID AND PL.LOCATION_ID = " + locationID +
                                 " GROUP BY MP.PRODUCT_ID";
                }
                else
                {
                    sqlCommand = "SELECT MP.ID, MP.PRODUCT_ID AS 'PRODUK ID', MP.PRODUCT_NAME AS 'NAMA PRODUK', MP.PRODUCT_STOCK_QTY AS 'QTY' " +
                                 "FROM MASTER_PRODUCT MP, SALES_DETAIL SD " +
                                 "WHERE SD.SALES_INVOICE = '" + returJualSearchParam + "' AND SD.PRODUCT_ID = MP.PRODUCT_ID AND MP.PRODUCT_IS_SERVICE = 0 " + showactive +
                                 "AND MP.PRODUCT_ID LIKE '%" + kodeProductParam + "%' AND MP.PRODUCT_NAME LIKE '%" + namaProductParam + "%'" +
                                 " GROUP BY MP.PRODUCT_ID";
                }
            }
            else if (originModuleID == globalConstants.RETUR_PENJUALAN_STOCK_ADJUSTMENT)
            {
                if (locationID > 0)
                {
                    sqlCommand = "SELECT MP.ID, MP.PRODUCT_ID AS 'PRODUK ID', MP.PRODUCT_NAME AS 'NAMA PRODUK', PL.PRODUCT_LOCATION_QTY AS 'QTY' " +
                                 "FROM MASTER_PRODUCT MP, SALES_DETAIL SD, SALES_HEADER SH, PRODUCT_LOCATION PL " +
                                 "WHERE SH.SALES_INVOICE = SD.SALES_INVOICE AND SD.PRODUCT_ID = MP.PRODUCT_ID AND SH.CUSTOMER_ID = " + Convert.ToInt32(returJualSearchParam) + " AND MP.PRODUCT_IS_SERVICE = 0 " + showactive +
                                 "AND MP.PRODUCT_ID LIKE '%" + kodeProductParam + "%' AND MP.PRODUCT_NAME LIKE '%" + namaProductParam + "%' " +
                                 "AND PL.PRODUCT_ID = MP.PRODUCT_ID AND PL.LOCATION_ID = " + locationID +
                                 " GROUP BY MP.PRODUCT_ID";
                }
                else
                {
                    sqlCommand = "SELECT MP.ID, MP.PRODUCT_ID AS 'PRODUK ID', MP.PRODUCT_NAME AS 'NAMA PRODUK', MP.PRODUCT_STOCK_QTY AS 'QTY' " +
                                 "FROM MASTER_PRODUCT MP, SALES_DETAIL SD, SALES_HEADER SH " +
                                 "WHERE SH.SALES_INVOICE = SD.SALES_INVOICE AND SD.PRODUCT_ID = MP.PRODUCT_ID AND SH.CUSTOMER_ID = " + Convert.ToInt32(returJualSearchParam) + " AND MP.PRODUCT_IS_SERVICE = 0 " + showactive +
                                 "AND MP.PRODUCT_ID LIKE '%" + kodeProductParam + "%' AND MP.PRODUCT_NAME LIKE '%" + namaProductParam + "%'" +
                                 " GROUP BY MP.PRODUCT_ID";
                }
            }
            else if (originModuleID == globalConstants.RETUR_PEMBELIAN)
            {
                if (locationID > 0)
                {
                    sqlCommand = "SELECT MP.ID, MP.PRODUCT_ID AS 'PRODUK ID', MP.PRODUCT_NAME AS 'NAMA PRODUK', PL.PRODUCT_LOCATION_QTY AS 'QTY' FROM MASTER_PRODUCT MP, PRODUCT_LOCATION PL WHERE MP.PRODUCT_IS_SERVICE = 0 " + showactive + "AND (MP.PRODUCT_STOCK_QTY - MP.PRODUCT_LIMIT_STOCK > 0) AND PL.PRODUCT_ID = MP.PRODUCT_ID AND PL.LOCATION_ID = " + locationID + " ORDER BY MP.PRODUCT_NAME ASC";
                }
                else
                {
                    sqlCommand = "SELECT MP.ID, MP.PRODUCT_ID AS 'PRODUK ID', MP.PRODUCT_NAME AS 'NAMA PRODUK', MP.PRODUCT_STOCK_QTY AS 'QTY' FROM MASTER_PRODUCT MP WHERE MP.PRODUCT_IS_SERVICE = 0 " + showactive + "AND (MP.PRODUCT_STOCK_QTY - MP.PRODUCT_LIMIT_STOCK > 0) ORDER BY MP.PRODUCT_NAME ASC";
                }
            }
            else if (originModuleID == globalConstants.PENERIMAAN_BARANG || (originModuleID == globalConstants.NEW_PURCHASE_ORDER) || (originModuleID == globalConstants.NEW_REQUEST_ORDER) || (originModuleID == globalConstants.PRE_ORDER_SALES) || (originModuleID == globalConstants.CASHIER_MODULE))
            {
                if (locationID > 0)
                {
                    sqlCommand = "SELECT MP.ID, MP.PRODUCT_ID AS 'PRODUK ID', MP.PRODUCT_NAME AS 'NAMA PRODUK', PL.PRODUCT_LOCATION_QTY AS 'QTY' FROM MASTER_PRODUCT MP, PRODUCT_LOCATION PL WHERE MP.PRODUCT_ID LIKE '%" + kodeProductParam + "%' AND MP.PRODUCT_NAME LIKE '%" + namaProductParam + "%'" + showactive + " AND PL.PRODUCT_ID = MP.PRODUCT_ID AND PL.LOCATION_ID = " + locationID;
                }
                else
                {
                    sqlCommand = "SELECT MP.ID, MP.PRODUCT_ID AS 'PRODUK ID', MP.PRODUCT_NAME AS 'NAMA PRODUK', MP.PRODUCT_STOCK_QTY AS 'QTY' FROM MASTER_PRODUCT MP WHERE MP.PRODUCT_ID LIKE '%" + kodeProductParam + "%' AND MP.PRODUCT_NAME LIKE '%" + namaProductParam + "%'" + showactive;
                }
            }
            //else if ((originModuleID == globalConstants.CASHIER_MODULE))
            //{
            //    if (locationID > 0)
            //        sqlCommand = "SELECT MP.ID, MP.PRODUCT_ID AS 'PRODUK ID', MP.PRODUCT_NAME AS 'NAMA PRODUK', PL.PRODUCT_LOCATION_QTY AS 'QTY' FROM MASTER_PRODUCT MP, PRODUCT_LOCATION PL WHERE MP.PRODUCT_STOCK_QTY > 0 AND MP.PRODUCT_ID LIKE '%" + kodeProductParam + "%' AND MP.PRODUCT_NAME LIKE '%" + namaProductParam + "%'" + showactive + " AND PL.PRODUCT_ID = MP.PRODUCT_ID AND PL.LOCATION_ID = " + locationID;
            //    else
            //        sqlCommand = "SELECT MP.ID, MP.PRODUCT_ID AS 'PRODUK ID', MP.PRODUCT_NAME AS 'NAMA PRODUK', MP.PRODUCT_STOCK_QTY AS 'QTY' FROM MASTER_PRODUCT MP WHERE MP.PRODUCT_STOCK_QTY > 0 AND MP.PRODUCT_ID LIKE '%" + kodeProductParam + "%' AND MP.PRODUCT_NAME LIKE '%" + namaProductParam + "%'" + showactive;
            //}
            else
            {
                if (locationID > 0)
                {
                    sqlCommand = "SELECT MP.ID, MP.PRODUCT_ID AS 'PRODUK ID', MP.PRODUCT_NAME AS 'NAMA PRODUK', MP.PRODUCT_DESCRIPTION AS 'DESKRIPSI PRODUK', PL.PRODUCT_LOCATION_QTY AS 'QTY' FROM MASTER_PRODUCT MP, PRODUCT_LOCATION PL WHERE MP.PRODUCT_ACTIVE = 1 AND MP.PRODUCT_IS_SERVICE = 0 AND MP.PRODUCT_NAME LIKE '%" + namaProductParam + "%' AND PL.PRODUCT_ID = MP.PRODUCT_ID AND PL.LOCATION_ID = " + locationID;
                }
                else
                {
                    sqlCommand = "SELECT ID, PRODUCT_ID AS 'PRODUK ID', PRODUCT_NAME AS 'NAMA PRODUK', PRODUCT_DESCRIPTION AS 'DESKRIPSI PRODUK' FROM MASTER_PRODUCT WHERE PRODUCT_ACTIVE = 1 AND PRODUCT_IS_SERVICE = 0 AND PRODUCT_NAME LIKE '%" + namaProductParam + "%'";
                }
            }

            if (originModuleID == globalConstants.STOK_PECAH_BARANG)
            {
                sqlCommand = sqlCommand + " AND PRODUCT_IS_SERVICE = 0";
            }

            using (rdr = DS.getData(sqlCommand))
            {
                if (rdr.HasRows)
                {
                    dt.Load(rdr);
                    dataProdukGridView.DataSource = dt;

                    dataProdukGridView.Columns["ID"].Visible = false;
                    //dataProdukGridView.Columns["PRODUK ID"].Width = 200;
                    dataProdukGridView.Columns["PRODUK ID"].Visible = false;
                    dataProdukGridView.Columns["NAMA PRODUK"].Width = 200;
                    //dataProdukGridView.Columns["DESKRIPSI PRODUK"].Width = 300;
                }
            }
        }
Exemple #4
0
        private int calculatePaperLength()
        {
            int result = 0;

            MySqlDataReader rdr;

            //event printing
            string dateFrom = String.Format(culture, "{0:yyyyMMdd}", DateTime.Now);

            Font font = new Font("Courier New", 9);
            int rowheight = (int)Math.Ceiling(font.GetHeight());
            int add_offset = rowheight;
            int Offset = 5;
            int totalLength = Offset;
            string sqlCommand = "";


            Offset = Offset + add_offset;

            Offset = Offset + add_offset;

            Offset = Offset + add_offset;

            Offset = Offset + add_offset;

            DS.mySqlConnect();

            sqlCommand = "SELECT MP.PRODUCT_NAME, IFNULL(PD.PRODUCT_SOLD_QTY, 0) AS PRODUCT_SOLD_QTY, IFNULL((PRODUCT_LAST_STOCK_QTY - PRODUCT_RIIL_QTY), 0) AS SELISIH " +
                                    "FROM MASTER_PRODUCT MP, PRODUCT_DAILY_ADJUSTMENT_HEADER PH, PRODUCT_DAILY_ADJUSTMENT_DETAIL PD " +
                                    "WHERE PD.PRODUCT_ID = MP.PRODUCT_ID AND DATE_FORMAT(PH.PRODUCT_ADJUSTMENT_DATE, '%Y%m%d')  = '" + dateFrom + "' AND PD.PRODUCT_ADJUSTMENT_ID = PH.PRODUCT_ADJUSTMENT_ID";

            using (rdr = DS.getData(sqlCommand))
            {
                if (rdr.HasRows)
                {
                    int i = 0;
                    while (rdr.Read())
                    {
                        Offset = Offset + add_offset;
                    }
                }
            }
            DS.mySqlClose();

            Offset = Offset + add_offset;
            Offset = Offset + add_offset;

            Offset = Offset + add_offset;

            Offset = Offset + add_offset;

            // =========================================================================
            Offset = Offset + add_offset;

            Offset = Offset + add_offset;
            // =========================================================================

            Offset = Offset + add_offset;

            Offset = Offset + add_offset;
            // =========================================================================
            Offset = Offset + add_offset;

            Offset = Offset + add_offset;
            // =========================================================================
            Offset = Offset + add_offset;
            Offset = Offset + add_offset;
            // =========================================================================

            Offset = Offset + add_offset;
            Offset = Offset + add_offset;
            // =========================================================================

            Offset = Offset + add_offset;

            Offset = Offset + add_offset;
            Offset = Offset + add_offset;

            Offset = Offset + add_offset;
            Offset = Offset + add_offset;
            Offset = Offset + add_offset;
            Offset = Offset + add_offset;
            Offset = Offset + add_offset;
            Offset = Offset + add_offset;

            //DS.mySqlConnect();

            //sqlCommand = "SELECT DJ.JOURNAL_DESCRIPTION, IFNULL(DJ.JOURNAL_NOMINAL, 0) AS NOMINAL " +
            //                       "FROM DAILY_JOURNAL DJ, MASTER_ACCOUNT MA " +
            //                       "WHERE DATE_FORMAT(DJ.JOURNAL_DATETIME, '%Y%m%d')  = '" + dateFrom + "' AND DJ.ACCOUNT_ID > 3 AND MA.ACCOUNT_TYPE_ID = 2 AND DJ.ACCOUNT_ID = MA.ACCOUNT_ID";

            //using (rdr = DS.getData(sqlCommand))
            //{
            //    if (rdr.HasRows)
            //    {
            //        int i = 0;
            //        while (rdr.Read())
            //        {
            //            Offset = Offset + add_offset;
            //        }
            //    }
            //}
            //DS.mySqlClose();

            Offset = Offset + add_offset;

            Offset = Offset + add_offset;
            Offset = Offset + add_offset;
            Offset = Offset + add_offset;

            result = totalLength  + Offset + add_offset + add_offset;

            return result;
        }
Exemple #5
0
        private string createUpdateQueryDataCabang(int branchID, string tableName, string PKField, List <string> fieldToBackup, string ipServerBranch = "")
        {
            Data_Access DS_BRANCH = new Data_Access();
            //bool result = false;
            string sqlCommand = "";

            MySqlDataReader rdr;
            string          fieldName;
            object          DBValue          = null;
            int             rdrFieldIndex    = 0;
            int             startIndex       = 0;
            StreamWriter    sw               = null;
            string          fileName         = "";
            string          commandStatement = "";
            string          valueStatement   = "";
            string          fieldValueForPK  = "";

            fileName = "EXPORT_" + branchID + "_DATA_" + tableName + ".sql";

            if (!File.Exists(fileName))
            {
                sw = File.CreateText(fileName);
            }
            else
            {
                File.Delete(fileName);
                sw = File.CreateText(fileName);
            }

            // CONNECT TO BRANCH
            if (DS_BRANCH.Branch_mySQLConnect(branchID, ipServerBranch))
            {
                sqlCommand = "SELECT * FROM " + tableName;

                using (rdr = DS_BRANCH.getData(sqlCommand, false, true))
                {
                    if (rdr.HasRows)
                    {
                        while (rdr.Read())
                        {
                            commandStatement = "UPDATE " + tableName + " SET ";
                            valueStatement   = "";
                            for (rdrFieldIndex = startIndex; rdrFieldIndex < rdr.FieldCount; rdrFieldIndex++)
                            {
                                DBValue = rdr.GetValue(rdrFieldIndex);

                                if (DBValue.ToString().Length > 0)
                                {
                                    fieldName = rdr.GetName(rdrFieldIndex);

                                    if (!fieldToSkip.Contains(fieldName) && fieldName != PKField)
                                    {
                                        continue;
                                    }

                                    if (fieldName != PKField)
                                    {
                                        valueStatement = valueStatement + fieldName + " = " + "'" + Convert.ToString(rdr.GetValue(rdrFieldIndex)) + "', ";
                                    }
                                    else
                                    {
                                        fieldValueForPK = Convert.ToString(rdr.GetValue(rdrFieldIndex));
                                    }
                                }
                            }

                            valueStatement   = valueStatement.Substring(0, valueStatement.Length - 2);
                            commandStatement = commandStatement + valueStatement;
                            commandStatement = commandStatement + " WHERE " + PKField + " = '" + fieldValueForPK + "';";

                            sw.WriteLine(commandStatement);
                        }
                    }
                }
                rdr.Close();

                // CLOSE BRANCH CONNECTION
                DS_BRANCH.Branch_mySqlClose();

                //result = true;
            }
            else
            {
                gUtil.saveSystemDebugLog(0, "[SYNC CABANG] FAILED TO CONNECT TO CABANG");
            }

            sw.Close();
            return(fileName);
        }
Exemple #6
0
        private void loadInvoiceData()
        {
            string                    sqlCommand;
            MySqlDataReader           rdr;
            DataTable                 dt        = new DataTable();
            DataGridViewTextBoxColumn qtyColumn = new DataGridViewTextBoxColumn();

            // LOAD DATA HEADER
            sqlCommand = "SELECT SH.SALES_INVOICE, IFNULL(MC.CUSTOMER_FULL_NAME, 'P-UMUM') AS CUSTOMER_NAME, IFNULL(SALES_ORDER_DELIVERED_ADDRESS, '') AS REMARK FROM SALES_HEADER SH LEFT OUTER JOIN MASTER_CUSTOMER MC ON (SH.CUSTOMER_ID = MC.CUSTOMER_ID) WHERE SH.SALES_INVOICE = '" + selectedSalesInvoice + "' AND SH.BRANCH_ID = " + branchID;

            using (rdr = DS.getData(sqlCommand))
            {
                if (rdr.HasRows)
                {
                    while (rdr.Read())
                    {
                        noInvoiceTextBox.Text    = rdr.GetString("SALES_INVOICE");
                        customerNameTextBox.Text = rdr.GetString("CUSTOMER_NAME");
                        instructionTextBox.Text  = rdr.GetString("REMARK");
                    }
                }
            }
            rdr.Close();

            // LOAD DATA DETAIL
            sqlCommand = "SELECT SD.ID, SD.PRODUCT_ID, SD.IS_COMPLETED, IF(SD.IS_COMPLETED = 1, 'COMPLETED', 'PENDING') AS STATUS, MP.PRODUCT_NAME AS 'NAMA PRODUK', SD.PRODUCT_QTY AS 'ORDER QTY', IFNULL(TAB1.QTY, 0) + IFNULL(TAB2.QTY, 0) AS 'DELIVERED QTY' " +
                         "FROM SALES_DETAIL SD LEFT OUTER JOIN " +
                         "(SELECT DH.SALES_INVOICE, DD.PRODUCT_ID, SUM(DD.PRODUCT_QTY) AS QTY FROM DELIVERY_ORDER_HEADER DH, DELIVERY_ORDER_DETAIL DD WHERE DD.DO_ID = DH.DO_ID AND DH.SALES_INVOICE = '" + selectedSalesInvoice + "' GROUP BY DD.PRODUCT_ID) TAB1 ON (TAB1.PRODUCT_ID = SD.PRODUCT_ID) " +
                         "LEFT OUTER JOIN (SELECT SH.BRANCH_ID, SH.REFERENCE_SO, SDF.PRODUCT_ID, SUM(SDF.PRODUCT_QTY) AS QTY FROM SALES_DETAIL_FULFILLMENT SDF, SALES_HEADER SH WHERE SDF.SALES_INVOICE = SH.SALES_INVOICE AND SH.REFERENCE_SO = '" + selectedSalesInvoice + "' GROUP BY SH.BRANCH_ID, SH.REFERENCE_SO, SDF.PRODUCT_ID) TAB2 ON (TAB2.PRODUCT_ID = SD.PRODUCT_ID) " +
                         ", MASTER_PRODUCT MP " +
                         "WHERE SD.PRODUCT_ID = MP.PRODUCT_ID AND SD.SALES_INVOICE = '" + selectedSalesInvoice + "' AND SD.BRANCH_ID = " + branchID;

            isLoading = true;
            using (rdr = DS.getData(sqlCommand))
            {
                if (rdr.HasRows)
                {
                    dt.Load(rdr);
                    detailGridView.DataSource = dt;

                    detailGridView.Columns["ID"].Visible           = false;
                    detailGridView.Columns["PRODUCT_ID"].Visible   = false;
                    detailGridView.Columns["IS_COMPLETED"].Visible = false;

                    detailGridView.Columns["STATUS"].ReadOnly = true;

                    detailGridView.Columns["NAMA PRODUK"].Width    = 200;
                    detailGridView.Columns["NAMA PRODUK"].ReadOnly = true;

                    detailGridView.Columns["ORDER QTY"].Width    = 200;
                    detailGridView.Columns["ORDER QTY"].ReadOnly = true;

                    detailGridView.Columns["DELIVERED QTY"].Width    = 200;
                    detailGridView.Columns["DELIVERED QTY"].ReadOnly = true;

                    qtyColumn.Name       = "qty";
                    qtyColumn.HeaderText = "QTY";
                    qtyColumn.Width      = 200;
                    detailGridView.Columns.Add(qtyColumn);

                    for (int i = 0; i < detailGridView.Rows.Count; i++)
                    {
                        if (detailGridView.Rows[i].Cells["IS_COMPLETED"].Value.ToString().Equals("1"))
                        {
                            detailGridView.Rows[i].Cells["QTY"].ReadOnly = true;
                        }
                        else
                        {
                            detailGridView.Rows[i].Cells["QTY"].Style.BackColor = Color.LightBlue;
                        }

                        detailGridView.Rows[i].Cells["QTY"].Value = 0;
                        //deliveryQty.Add("0");
                    }
                }
            }
            rdr.Close();

            isLoading = false;
        }
Exemple #7
0
        private void loadROdata()
        {
            MySqlDataReader rdr;
            DataTable       dt = new DataTable();
            string          sqlCommand;
            string          dateFrom;
            string          dateTo;
            string          noMutasiParam = "";

            DS.mySqlConnect();

            //sqlCommand = "SELECT ID, PM_INVOICE AS 'NO MUTASI', DATE_FORMAT(PM_DATETIME,'%d-%M-%Y') AS 'TGL MUTASI', M1.BRANCH_NAME AS 'ASAL MUTASI', M2.BRANCH_NAME AS 'TUJUAN MUTASI', PM_TOTAL AS 'TOTAL', RO_INVOICE AS 'NO PERMINTAAN' " +
            //                    "FROM PRODUCTS_MUTATION_HEADER LEFT OUTER JOIN MASTER_BRANCH M1 ON (BRANCH_ID_FROM = M1.BRANCH_ID) " +
            //                    "LEFT OUTER JOIN MASTER_BRANCH M2 ON (BRANCH_ID_TO = M2.BRANCH_ID) " +
            //                    "WHERE 1 = 1 AND PM_RECEIVED = 0";
            sqlCommand = "SELECT ID, PM_INVOICE AS 'NO MUTASI', DATE_FORMAT(PM_DATETIME,'%d-%M-%Y') AS 'TGL MUTASI', M2.BRANCH_NAME AS 'TUJUAN MUTASI', PM_TOTAL AS 'TOTAL', RO_INVOICE AS 'NO PERMINTAAN' " +
                         "FROM PRODUCTS_MUTATION_HEADER PH,  MASTER_BRANCH M2 " +       //LEFT OUTER JOIN MASTER_BRANCH M2 ON (BRANCH_ID_TO = M2.BRANCH_ID) " +
                         "WHERE 1 = 1 AND PH.BRANCH_ID_TO = M2.BRANCH_ID AND PM_RECEIVED = 0";

            if (!showAllCheckBox.Checked)
            {
                if (noMutasiTextBox.Text.Length > 0)
                {
                    noMutasiParam = MySqlHelper.EscapeString(noMutasiTextBox.Text);
                    sqlCommand    = sqlCommand + " AND PM_INVOICE LIKE '%" + noMutasiParam + "%'";
                }

                dateFrom   = String.Format(culture, "{0:yyyyMMdd}", Convert.ToDateTime(PMDtPicker_1.Value));
                dateTo     = String.Format(culture, "{0:yyyyMMdd}", Convert.ToDateTime(PMDtPicker_2.Value));
                sqlCommand = sqlCommand + " AND DATE_FORMAT(PM_DATETIME, '%Y%m%d')  >= '" + dateFrom + "' AND DATE_FORMAT(PM_DATETIME, '%Y%m%d')  <= '" + dateTo + "'";

                //if (branchFromCombo.Text.Length > 0)
                //{
                //    sqlCommand = sqlCommand + " AND BRANCH_ID_FROM = " + selectedBranchFromID;
                //}

                if (branchToCombo.Text.Length > 0)
                {
                    sqlCommand = sqlCommand + " AND BRANCH_ID_TO = " + selectedBranchToID;
                }
            }

            sqlCommand = sqlCommand + " ORDER BY PM_DATETIME ASC";

            using (rdr = DS.getData(sqlCommand))
            {
                dataRequestOrderGridView.DataSource = null;
                if (rdr.HasRows)
                {
                    dt.Load(rdr);
                    dataRequestOrderGridView.DataSource = dt;

                    dataRequestOrderGridView.Columns["ID"].Visible = false;

                    dataRequestOrderGridView.Columns["NO MUTASI"].Width  = 200;
                    dataRequestOrderGridView.Columns["TGL MUTASI"].Width = 200;
                    //dataRequestOrderGridView.Columns["ASAL MUTASI"].Width = 200;
                    dataRequestOrderGridView.Columns["TUJUAN MUTASI"].Width = 200;
                    dataRequestOrderGridView.Columns["TOTAL"].Width         = 200;
                    dataRequestOrderGridView.Columns["NO PERMINTAAN"].Width = 200;

                    dataRequestOrderGridView.Columns["TOTAL"].DefaultCellStyle.FormatProvider = culture;
                    dataRequestOrderGridView.Columns["TOTAL"].DefaultCellStyle.Format         = "C2";
                }

                rdr.Close();
            }
        }
Exemple #8
0
        private void writeTableContentToInsertStatement(string tableName, StreamWriter sw, Data_Access DAccess,
                                                        bool skipAddBranchID = false, string sqlParam = "", string customTableName = "", string fieldForPK = "", string fieldValueForPK = "", bool forcedInsert = false)
        {
            string          sqlCommand = "";
            MySqlDataReader rdr;
            string          valueStatement = "";
            int             rdrFieldIndex  = 0;
            int             startIndex     = 0;
            DateTime        tempDateTime;
            string          dateTimeValue;
            object          DBValue    = null;
            int             branchID   = 0;
            string          namaCabang = "";
            int             editFlag   = 1;

            string commandStatement = "";
            string fieldName        = "";

            branchID = gUtil.loadbranchID(2, out namaCabang);

            if (sqlParam.Length <= 0) // EMPTY SQL PARAM MEANS, READ FROM EDIT FLAG
            {
                if (forcedInsert == false)
                {
                    sqlCommand = "SELECT * FROM " + tableName + " WHERE EDITED <> 0";
                }
                else
                {
                    sqlCommand = "SELECT * FROM " + tableName + " WHERE 1 = 1";
                }

                if (fieldValueForPK.Length > 0)
                {
                    sqlCommand = sqlCommand + " AND " + fieldForPK + " = '" + fieldValueForPK + "'";
                }
            }
            else
            {
                sqlCommand = sqlParam;
            }

            sw.WriteLine("");
            using (rdr = DAccess.getData(sqlCommand))
            {
                if (rdr.HasRows)
                {
                    while (rdr.Read())
                    {
                        if (forcedInsert == false)
                        {
                            if (sqlParam.Length <= 0)
                            {
                                editFlag = rdr.GetInt32("EDITED");
                            }
                        }

                        if (editFlag == 1)
                        {
                            commandStatement = "INSERT INTO " + tableName + "(";
                        }
                        else
                        {
                            commandStatement = "UPDATE " + tableName + " SET ";
                        }

                        valueStatement = "";

                        for (rdrFieldIndex = startIndex; rdrFieldIndex < rdr.FieldCount; rdrFieldIndex++)
                        {
                            DBValue = rdr.GetValue(rdrFieldIndex);

                            if (DBValue.ToString().Length > 0)
                            {
                                fieldName = rdr.GetName(rdrFieldIndex);

                                if (editFlag == 2 && fieldName == fieldForPK)
                                {
                                    continue;
                                }

                                if (fieldToSkip.Contains(fieldName))
                                {
                                    continue;
                                }

                                if (editFlag == 1) // INSERT STATEMENT
                                {
                                    commandStatement = commandStatement + fieldName + ", ";
                                }
                                else // UPDATE STATEMENT
                                {
                                    valueStatement = valueStatement + fieldName + " = ";
                                }

                                if (rdr.GetDataTypeName(rdrFieldIndex) == "DATE")
                                {
                                    tempDateTime  = rdr.GetDateTime(rdrFieldIndex);
                                    dateTimeValue = String.Format(culture, "{0:dd-MM-yyyy}", tempDateTime);

                                    valueStatement = valueStatement + "STR_TO_DATE('" + dateTimeValue + "', '%d-%m-%Y'), ";
                                }
                                else if (rdr.GetDataTypeName(rdrFieldIndex) == "DATETIME")
                                {
                                    tempDateTime  = rdr.GetDateTime(rdrFieldIndex);
                                    dateTimeValue = gUtil.getCustomStringFormatDate(tempDateTime);

                                    valueStatement = valueStatement + "STR_TO_DATE('" + dateTimeValue + "', '%d-%m-%Y %H:%i'), ";
                                }
                                else
                                {
                                    valueStatement = valueStatement + "'" + Convert.ToString(rdr.GetValue(rdrFieldIndex)) + "', ";
                                }
                            }
                        }

                        if (!skipAddBranchID)
                        {
                            if (editFlag == 1)
                            {
                                commandStatement = commandStatement + "BRANCH_ID) VALUES(";
                                valueStatement   = valueStatement + branchID + ");";
                            }
                            else
                            {
                                valueStatement = valueStatement + "BRANCH_ID = " + branchID;
                            }
                        }
                        else
                        {
                            if (editFlag == 1)
                            {
                                commandStatement = commandStatement.Substring(0, commandStatement.Length - 2);
                                commandStatement = commandStatement + ") VALUES(";

                                valueStatement = valueStatement.Substring(0, valueStatement.Length - 2);
                                valueStatement = valueStatement + ");";
                            }
                            else
                            {
                                valueStatement = valueStatement.Substring(0, valueStatement.Length - 2);
                            }
                        }

                        commandStatement = commandStatement + valueStatement;

                        if (editFlag == 2 && fieldForPK.Length > 0 && fieldValueForPK.Length > 0)
                        {
                            commandStatement = commandStatement + " WHERE " + fieldForPK + " = '" + fieldValueForPK + "';";
                        }
                        //else
                        //    commandStatement = commandStatement + ";";

                        sw.WriteLine(commandStatement);
                    }
                }
                rdr.Close();
            }
            sw.WriteLine("");
        }
        private void exportData(string fileName, Data_Access DAccess, bool isHQConnection = false)
        {
            //string localDate = "";
            //string strCmdText = "";
            //string ipServer;
            //System.Diagnostics.Process proc = new System.Diagnostics.Process();
            MySqlDataReader rdr;
            string          sqlCommand      = "";
            string          insertStatement = "";
            StreamWriter    sw = null;

            // EXPORT MASTER PRODUCT DATA
            string strCmdText = "USE `sys_pos`; " + "\n" +
                                "DROP TABLE IF EXISTS `temp_master_product`;" + "\n" +
                                "\n" +
                                "CREATE TABLE `temp_master_product` (" + "\n" +
                                "`ID` int(10) unsigned NOT NULL AUTO_INCREMENT," + "\n" +
                                "`PRODUCT_ID` varchar(50) DEFAULT NULL," + "\n" +
                                "`PRODUCT_BARCODE` varchar(15) DEFAULT NULL," + "\n" +
                                "`PRODUCT_NAME` varchar(50) DEFAULT NULL," + "\n" +
                                "`PRODUCT_DESCRIPTION` varchar(100) DEFAULT NULL," + "\n" +
                                "`PRODUCT_BASE_PRICE` double DEFAULT NULL," + "\n" +
                                "`PRODUCT_RETAIL_PRICE` double DEFAULT NULL," + "\n" +
                                "`PRODUCT_BULK_PRICE` double DEFAULT NULL," + "\n" +
                                "`PRODUCT_WHOLESALE_PRICE` double DEFAULT NULL," + "\n" +
                                "`UNIT_ID` smallint(5) unsigned DEFAULT '0'," + "\n" +
                                "`PRODUCT_IS_SERVICE` tinyint(3) unsigned DEFAULT NULL," + "\n" +
                                "PRIMARY KEY(`ID`)," + "\n" +
                                "UNIQUE KEY `PRODUCT_ID_UNIQUE` (`PRODUCT_ID`)" + "\n" +
                                ") ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;" + "\n" +
                                "\n" +
                                "DROP TABLE IF EXISTS `temp_product_category`;" + "\n" +
                                "\n" +
                                "CREATE TABLE `temp_product_category` (" + "\n" +
                                "`PRODUCT_ID` varchar(50) NOT NULL," + "\n" +
                                "`CATEGORY_ID` tinyint(3) unsigned NOT NULL," + "\n" +
                                "PRIMARY KEY (`PRODUCT_ID`,`CATEGORY_ID`)" + "\n" +
                                ") ENGINE = InnoDB DEFAULT CHARSET = utf8;" + "\n";


            //localDate = String.Format(culture, "{0:ddMMyyyy}", DateTime.Now);
            //fileName = "SYNCINFO_PRODUCT_" + localDate + ".sql";

            sqlCommand = "SELECT PRODUCT_ID, IFNULL(PRODUCT_BARCODE, '') AS PRODUCT_BARCODE, IFNULL(PRODUCT_NAME, '') AS PRODUCT_NAME, IFNULL(PRODUCT_DESCRIPTION, '') AS PRODUCT_DESCRIPTION, PRODUCT_BASE_PRICE, PRODUCT_RETAIL_PRICE, PRODUCT_BULK_PRICE, PRODUCT_WHOLESALE_PRICE, UNIT_ID, PRODUCT_IS_SERVICE FROM MASTER_PRODUCT WHERE PRODUCT_ACTIVE = 1";
            using (rdr = DAccess.getData(sqlCommand, isHQConnection))
            {
                if (rdr.HasRows)
                {
                    if (!File.Exists(fileName))
                    {
                        sw = File.CreateText(fileName);
                    }
                    else
                    {
                        File.Delete(fileName);
                        sw = File.CreateText(fileName);
                    }

                    sw.WriteLine(strCmdText);

                    while (rdr.Read())
                    {
                        insertStatement = "INSERT INTO TEMP_MASTER_PRODUCT (PRODUCT_ID, PRODUCT_BARCODE, PRODUCT_NAME, PRODUCT_DESCRIPTION, PRODUCT_BASE_PRICE, PRODUCT_RETAIL_PRICE, PRODUCT_BULK_PRICE, PRODUCT_WHOLESALE_PRICE, UNIT_ID, PRODUCT_IS_SERVICE) VALUES (" +
                                          "'" + MySqlHelper.EscapeString(rdr.GetString("PRODUCT_ID")) + "', '" + MySqlHelper.EscapeString(rdr.GetString("PRODUCT_BARCODE")) + "', '" + MySqlHelper.EscapeString(rdr.GetString("PRODUCT_NAME")) + "', '" + MySqlHelper.EscapeString(rdr.GetString("PRODUCT_DESCRIPTION")) + "', " + rdr.GetString("PRODUCT_BASE_PRICE") + ", " + rdr.GetString("PRODUCT_RETAIL_PRICE") + ", " + rdr.GetString("PRODUCT_BULK_PRICE") + ", " + rdr.GetString("PRODUCT_WHOLESALE_PRICE") + ", " + rdr.GetString("UNIT_ID") + ", " + rdr.GetString("PRODUCT_IS_SERVICE") + ");";
                        sw.WriteLine(insertStatement);
                    }
                }
                rdr.Close();
            }
            sw.WriteLine("");

            // EXPORT MASTER KATEGORI DATA
            sw.WriteLine("");
            sw.WriteLine("DELETE FROM MASTER_CATEGORY;");
            sqlCommand = "SELECT CATEGORY_ID, CATEGORY_NAME, IFNULL(CATEGORY_DESCRIPTION, '') AS CATEGORY_DESCRIPTION FROM MASTER_CATEGORY WHERE CATEGORY_ACTIVE = 1";
            using (rdr = DAccess.getData(sqlCommand, isHQConnection))
            {
                if (rdr.HasRows)
                {
                    while (rdr.Read())
                    {
                        insertStatement = "INSERT INTO MASTER_CATEGORY (CATEGORY_ID, CATEGORY_NAME, CATEGORY_DESCRIPTION, CATEGORY_ACTIVE) VALUES (" +
                                          rdr.GetString("CATEGORY_ID") + ", '" + MySqlHelper.EscapeString(rdr.GetString("CATEGORY_NAME")) + "', '" + MySqlHelper.EscapeString(rdr.GetString("CATEGORY_DESCRIPTION")) + "', 1);";
                        sw.WriteLine(insertStatement);
                    }
                }
                rdr.Close();
            }
            sw.WriteLine("");

            // EXPORT MASTER UNIT DATA
            sw.WriteLine("");
            sw.WriteLine("DELETE FROM MASTER_UNIT;");
            sqlCommand = "SELECT UNIT_ID, UNIT_NAME, IFNULL(UNIT_DESCRIPTION, '') AS UNIT_DESCRIPTION FROM MASTER_UNIT WHERE UNIT_ACTIVE = 1";
            using (rdr = DAccess.getData(sqlCommand, isHQConnection))
            {
                if (rdr.HasRows)
                {
                    while (rdr.Read())
                    {
                        insertStatement = "INSERT INTO MASTER_UNIT (UNIT_ID, UNIT_NAME, UNIT_DESCRIPTION, UNIT_ACTIVE) VALUES (" +
                                          rdr.GetString("UNIT_ID") + ", '" + MySqlHelper.EscapeString(rdr.GetString("UNIT_NAME")) + "', '" + MySqlHelper.EscapeString(rdr.GetString("UNIT_DESCRIPTION")) + "', 1);";
                        sw.WriteLine(insertStatement);
                    }
                }
                rdr.Close();
            }
            sw.WriteLine("");

            // EXPORT MASTER UNIT KONVERSI DATA
            sw.WriteLine("");
            sw.WriteLine("DELETE FROM UNIT_CONVERT;");
            sqlCommand = "SELECT CONVERT_UNIT_ID_1, CONVERT_UNIT_ID_2, CONVERT_MULTIPLIER FROM UNIT_CONVERT";
            using (rdr = DAccess.getData(sqlCommand, isHQConnection))
            {
                if (rdr.HasRows)
                {
                    while (rdr.Read())
                    {
                        insertStatement = "INSERT INTO UNIT_CONVERT (CONVERT_UNIT_ID_1, CONVERT_UNIT_ID_2, CONVERT_MULTIPLIER) VALUES (" +
                                          rdr.GetString("CONVERT_UNIT_ID_1") + ", " + rdr.GetString("CONVERT_UNIT_ID_2") + ", " + rdr.GetString("CONVERT_MULTIPLIER") + ");";
                        sw.WriteLine(insertStatement);
                    }
                }
                rdr.Close();
            }
            sw.WriteLine("");

            // EXPORT PRODUCT CATEGORY DATA
            sw.WriteLine("");
            sqlCommand = "SELECT PRODUCT_ID, CATEGORY_ID FROM PRODUCT_CATEGORY";
            using (rdr = DAccess.getData(sqlCommand, isHQConnection))
            {
                if (rdr.HasRows)
                {
                    while (rdr.Read())
                    {
                        insertStatement = "INSERT INTO TEMP_PRODUCT_CATEGORY (PRODUCT_ID, CATEGORY_ID) VALUES (" +
                                          "'" + rdr.GetString("PRODUCT_ID") + "', " + rdr.GetString("CATEGORY_ID") + ");";
                        sw.WriteLine(insertStatement);
                    }
                }
                rdr.Close();
            }
            sw.WriteLine("");

            sw.Close();
            //ipServer = DS.getIPServer();
            ////strCmdText = "/C mysqldump -h " + ipServer + " -u SYS_POS_ADMIN -ppass123 sys_pos MASTER_PRODUCT > \"" + fileName + "\"";

            //proc.StartInfo.FileName = "CMD.exe";
            //proc.StartInfo.Arguments = "/C " + "mysqldump -h " + ipServer + " -u SYS_POS_ADMIN -ppass123 sys_pos > \"" + fileName + "\"";
            //proc.Exited += new EventHandler(ProcessExited);
            //proc.EnableRaisingEvents = true;
            //proc.Start();


            //System.Diagnostics.Process.Start("CMD.exe", strCmdText);
        }
        private bool updateLocalData()
        {
            bool   result     = false;
            string sqlCommand = "";

            string productID;
            string productBarcode;
            string productName;
            string productDescription;
            string productBasePrice;
            string productRetailPrice;
            string productBulkPrice;
            string productWholesalePrice;
            string productService;
            string productUnitID;

            string categoryID;

            MySqlException  internalEX = null;
            MySqlDataReader rdr;
            DataTable       dt  = new DataTable();
            DataTable       dt2 = new DataTable();
            DataTable       dt3 = new DataTable();

            int i = 0;

            DS.beginTransaction();

            gutil.saveSystemDebugLog(globalConstants.MENU_SINKRONISASI_INFORMASI, "UPDATE LOCAL DATA");
            sqlCommand = "SELECT PRODUCT_ID FROM MASTER_PRODUCT WHERE PRODUCT_ACTIVE = 1";
            try
            {
                DS.mySqlConnect();

                using (rdr = DS.getData(sqlCommand))
                {
                    if (rdr.HasRows)
                    {
                        dt.Load(rdr);
                        rdr.Close();

                        dataGridView1.DataSource = dt;
                        i = 0;
                        // UPDATE CURRENT DATA IN LOCAL DATABASE
                        gutil.saveSystemDebugLog(globalConstants.MENU_SINKRONISASI_INFORMASI, "UPDATE CURRENT DATA IN LOCAL DATABASE");
                        while (i < dataGridView1.Rows.Count)
                        {
                            productID = dataGridView1.Rows[i].Cells["PRODUCT_ID"].Value.ToString();

                            productBasePrice      = DS.getDataSingleValue("SELECT PRODUCT_BASE_PRICE FROM TEMP_MASTER_PRODUCT WHERE PRODUCT_ID = '" + productID + "'").ToString();
                            productRetailPrice    = DS.getDataSingleValue("SELECT PRODUCT_RETAIL_PRICE FROM TEMP_MASTER_PRODUCT WHERE PRODUCT_ID = '" + productID + "'").ToString();
                            productBulkPrice      = DS.getDataSingleValue("SELECT PRODUCT_BULK_PRICE FROM TEMP_MASTER_PRODUCT WHERE PRODUCT_ID = '" + productID + "'").ToString();
                            productWholesalePrice = DS.getDataSingleValue("SELECT PRODUCT_WHOLESALE_PRICE FROM TEMP_MASTER_PRODUCT WHERE PRODUCT_ID = '" + productID + "'").ToString();
                            productUnitID         = DS.getDataSingleValue("SELECT UNIT_ID FROM TEMP_MASTER_PRODUCT WHERE PRODUCT_ID = '" + productID + "'").ToString();

                            sqlCommand = "UPDATE MASTER_PRODUCT SET " +
                                         "PRODUCT_BASE_PRICE = " + productBasePrice + ", PRODUCT_RETAIL_PRICE = " + productRetailPrice + ", PRODUCT_BULK_PRICE = " + productBulkPrice + ", PRODUCT_WHOLESALE_PRICE = " + productWholesalePrice + ", UNIT_ID = " + productUnitID +
                                         " WHERE PRODUCT_ID = '" + productID + "'";

                            if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX))
                            {
                                throw internalEX;
                            }

                            //sqlCommand = "DELETE FROM TEMP_MASTER_PRODUCT WHERE PRODUCT_ID = '" + productID + "'";

                            //if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX))
                            //    throw internalEX;

                            i++;
                        }
                        gutil.saveSystemDebugLog(globalConstants.MENU_SINKRONISASI_INFORMASI, "FINISHED UPDATE CURRENT DATA IN LOCAL DATABASE");

                        dataGridView1.DataSource = null;
                        // INSERT NEW PRODUCT CATEGORY
                        sqlCommand = "SELECT * FROM TEMP_PRODUCT_CATEGORY WHERE CONCAT(PRODUCT_ID, '-', CATEGORY_ID) NOT IN (SELECT CONCAT(PRODUCT_ID, '-', CATEGORY_ID) FROM PRODUCT_CATEGORY)";
                        using (rdr = DS.getData(sqlCommand))
                        {
                            gutil.saveSystemDebugLog(globalConstants.MENU_SINKRONISASI_INFORMASI, "INSERT NEW PRODUCT CATEGORY [" + Convert.ToInt32(rdr.HasRows) + "]");

                            if (rdr.HasRows)
                            {
                                dt2.Load(rdr);
                                rdr.Close();

                                dataGridView1.DataSource = dt2;
                                i = 0;
                                while (i < dataGridView1.Rows.Count)
                                {
                                    productID  = dataGridView1.Rows[i].Cells["PRODUCT_ID"].Value.ToString();
                                    categoryID = dataGridView1.Rows[i].Cells["CATEGORY_ID"].Value.ToString();

                                    sqlCommand = "INSERT INTO PRODUCT_CATEGORY (PRODUCT_ID, CATEGORY_ID) VALUES (" +
                                                 "'" + productID + "', " + categoryID + ")";

                                    if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX))
                                    {
                                        throw internalEX;
                                    }

                                    i++;
                                }
                            }
                        }

                        dataGridView1.DataSource = null;
                        // INSERT NEW DATA
                        sqlCommand = "SELECT * FROM TEMP_MASTER_PRODUCT WHERE PRODUCT_ID NOT IN (SELECT PRODUCT_ID FROM MASTER_PRODUCT)";

                        using (rdr = DS.getData(sqlCommand))
                        {
                            gutil.saveSystemDebugLog(globalConstants.MENU_SINKRONISASI_INFORMASI, "INSERT NEW PRODUCT DATA [" + Convert.ToInt32(rdr.HasRows) + "]");
                            if (rdr.HasRows)
                            {
                                dt3.Load(rdr);
                                rdr.Close();

                                dataGridView1.DataSource = dt3;
                                i = 0;
                                while (i < dataGridView1.Rows.Count)
                                {
                                    productID             = MySqlHelper.EscapeString(dataGridView1.Rows[i].Cells["PRODUCT_ID"].Value.ToString());
                                    productBarcode        = MySqlHelper.EscapeString(dataGridView1.Rows[i].Cells["PRODUCT_BARCODE"].Value.ToString());
                                    productName           = MySqlHelper.EscapeString(dataGridView1.Rows[i].Cells["PRODUCT_NAME"].Value.ToString());
                                    productDescription    = MySqlHelper.EscapeString(dataGridView1.Rows[i].Cells["PRODUCT_DESCRIPTION"].Value.ToString());
                                    productBasePrice      = dataGridView1.Rows[i].Cells["PRODUCT_BASE_PRICE"].Value.ToString();
                                    productRetailPrice    = dataGridView1.Rows[i].Cells["PRODUCT_RETAIL_PRICE"].Value.ToString();
                                    productBulkPrice      = dataGridView1.Rows[i].Cells["PRODUCT_BULK_PRICE"].Value.ToString();
                                    productWholesalePrice = dataGridView1.Rows[i].Cells["PRODUCT_WHOLESALE_PRICE"].Value.ToString();
                                    productService        = dataGridView1.Rows[i].Cells["PRODUCT_IS_SERVICE"].Value.ToString();
                                    productUnitID         = dataGridView1.Rows[i].Cells["UNIT_ID"].Value.ToString();
                                    sqlCommand            = "INSERT INTO MASTER_PRODUCT (PRODUCT_ID, PRODUCT_BARCODE, PRODUCT_NAME, PRODUCT_DESCRIPTION, PRODUCT_BASE_PRICE, PRODUCT_RETAIL_PRICE, PRODUCT_BULK_PRICE, PRODUCT_WHOLESALE_PRICE, UNIT_ID, PRODUCT_STOCK_QTY, PRODUCT_LIMIT_STOCK, PRODUCT_SHELVES, PRODUCT_ACTIVE, PRODUCT_IS_SERVICE) VALUES (" +
                                                            "'" + productID + "', '" + productBarcode + "', '" + productName + "', '" + productDescription + "', " + productBasePrice + ", " + productRetailPrice + ", " + productBulkPrice + ", " + productWholesalePrice + ", " + productUnitID + ", 0, 0, '--00', 1, " + productService + ")";

                                    if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX))
                                    {
                                        throw internalEX;
                                    }

                                    i++;
                                }
                            }
                        }

                        DS.commit();
                    }
                }

                result = true;
            }
            catch (Exception e)
            {
                gutil.saveSystemDebugLog(globalConstants.MENU_SINKRONISASI_INFORMASI, "EXCEPTION THROWN [" + e.Message + "]");

                try
                {
                    DS.rollBack();
                }
                catch (MySqlException ex)
                {
                    if (DS.getMyTransConnection() != null)
                    {
                        gutil.showDBOPError(ex, "ROLLBACK");
                    }
                }

                gutil.showDBOPError(e, "ROLLBACK");
                result = false;
            }
            finally
            {
                DS.mySqlClose();
            }

            return(result);
        }
Exemple #11
0
        private bool pullDetailMessageAndSaveToTable(int moduleID, string sqlCommand)
        {
            MySqlDataReader rdr;
            string          param1;
            string          param2;
            bool            newData = false;
            double          jumlahPembayaran;
            string          deskripsiPembayaran;

            string messageContent   = "";
            string insertSQLCommand = "";
            string todayDate        = String.Format(culture, "{0:dd-MM-yyyy}", DateTime.Now);

            DS.beginTransaction();

            try
            {
                DS.mySqlConnect();

                using (rdr = DS.getData(sqlCommand))
                {
                    if (rdr.HasRows)
                    {
                        newData = true;
                        while (rdr.Read())
                        {
                            param1 = rdr.GetString("PARAM_1");
                            param2 = rdr.GetString("PARAM_2");

                            switch (moduleID)
                            {
                            case globalConstants.MENU_TRANSAKSI_PENJUALAN:
                                messageContent = "SALES INVOICE [" + param1 + "] JATUH TEMPO TGL " + param2;
                                break;

                            case globalConstants.MENU_PURCHASE_ORDER:
                                messageContent = "PURCHASE ORDER [" + param1 + "] JATUH TEMPO TGL " + param2;
                                break;

                            case globalConstants.MENU_PEMBAYARAN_PIUTANG:
                                jumlahPembayaran    = rdr.GetDouble("JUMLAH");
                                deskripsiPembayaran = rdr.GetString("DESCRIPTION");

                                messageContent = "PEMBAYARAN SALES INVOICE [" + param1 + "] [" + deskripsiPembayaran + "] SEBESAR " + jumlahPembayaran.ToString("C2", culture) + " JATUH TEMPO";
                                break;

                            case globalConstants.MENU_PEMBAYARAN_HUTANG_SUPPLIER:
                                jumlahPembayaran    = rdr.GetDouble("JUMLAH");
                                deskripsiPembayaran = rdr.GetString("DESCRIPTION");

                                messageContent = "PEMBAYARAN PURCASE ORDER [" + param1 + "] [" + deskripsiPembayaran + "] SEBESAR " + jumlahPembayaran.ToString("C2", culture) + " JATUH TEMPO";
                                break;

                            case globalConstants.MENU_REQUEST_ORDER:
                                messageContent = "REQUEST ORDER [" + param1 + "] EXPIRED PADA TGL " + param2;
                                break;

                            case globalConstants.MENU_PRODUK:
                                messageContent = "PRODUCT_ID [" + param1 + "] SUDAH MENDEKATI LIMIT";
                                break;

                            case globalConstants.MENU_SALES_QUOTATION:
                                messageContent = "QUOTATION [" + param1 + "] DIPESAN TGL " + param2;
                                break;
                            }

                            insertSQLCommand = "INSERT INTO MASTER_MESSAGE (STATUS, MODULE_ID, IDENTIFIER_NO, MSG_DATETIME_CREATED, MSG_CONTENT) " +
                                               "VALUES " +
                                               "(0, " + moduleID + ", '" + param1 + "', STR_TO_DATE('" + todayDate + "', '%d-%m-%Y'), '" + messageContent + "')";

                            DS.executeNonQueryCommand(insertSQLCommand);
                        }

                        DS.commit();
                    }
                }
            }
            catch (Exception e)
            {
                try
                {
                    DS.rollBack();
                }
                catch (MySqlException ex)
                {
                    if (DS.getMyTransConnection() != null)
                    {
                        gutil.showDBOPError(ex, "ROLLBACK");
                    }
                }

                gutil.showDBOPError(e, "INSERT");
            }
            finally
            {
                DS.mySqlClose();
            }
            return(newData);
        }
Exemple #12
0
        private void loadDataStockTake()
        {
            string          selectedDate = String.Format(culture, "{0:yyyyMMdd}", Convert.ToDateTime(stockTakeDateTimePicker.Value));
            string          sqlCommand   = "";
            MySqlDataReader rdr;

            //DataTable dt = new DataTable();

            if (detailDataGrid.ColumnCount <= 0)
            {
                return;
            }

            // CHECK WHETHER DATA HAS BEEN SAVED BEFORE OR NOT
            sqlCommand = "SELECT COUNT(1) FROM PRODUCT_DAILY_ADJUSTMENT_HEADER WHERE DATE_FORMAT(PRODUCT_ADJUSTMENT_DATE, '%Y%m%d')  = '" + selectedDate + "'";
            if (Convert.ToInt32(DS.getDataSingleValue(sqlCommand)) > 0)
            {
                int userAccessOption = DS.getUserAccessRight(globalConstants.REVISI_STOCK_TAKE_HARIAN, gUtil.getUserGroupID());

                if (userAccessOption == 1)
                {
                    sqlCommand = "SELECT PRODUCT_ADJUSTMENT_ID FROM PRODUCT_DAILY_ADJUSTMENT_HEADER WHERE DATE_FORMAT(PRODUCT_ADJUSTMENT_DATE, '%Y%m%d')  = '" + selectedDate + "'";
                    globalProductAdjustmentID = DS.getDataSingleValue(sqlCommand).ToString();

                    moduleID = EDIT_DAILY_STOCK_TAKE;

                    sqlCommand           = "SELECT STOCK_TAKE_CLOSED FROM PRODUCT_DAILY_ADJUSTMENT_HEADER WHERE DATE_FORMAT(PRODUCT_ADJUSTMENT_DATE, '%Y%m%d')  = '" + selectedDate + "'";
                    stockTakeCloseStatus = Convert.ToInt32(DS.getDataSingleValue(sqlCommand));

                    if (stockTakeCloseStatus == 1)
                    {
                        allowToEdit             = false;
                        detailDataGrid.ReadOnly = true;
                        label3.Text             = "CLOSED";
                        label3.Visible          = true;
                    }
                    else
                    {
                        allowToEdit = true;
                    }
                }
                else
                {
                    moduleID                = EDIT_DAILY_STOCK_TAKE;
                    allowToEdit             = false;
                    detailDataGrid.ReadOnly = true;
                }
            }

            switch (moduleID)
            {
            case NEW_DAILY_STOCK_TAKE:
                sqlCommand = "SELECT MP.PRODUCT_ID, MP.PRODUCT_NAME AS ROTI, MP.PRODUCT_STOCK_AWAL AS AWAL, (IFNULL(TAB1.TOTAL_RECEIVED, 0)-IFNULL(TAB4.TOTAL_RECEIVED_RETURN, 0)) AS PRODUKSI, '' AS REMARK, '0' AS BS, MP.PRODUCT_STOCK_QTY AS AKHIR, (IFNULL(TAB2.TOTAL_SALES, 0) + IFNULL(TAB_DO.TOTAL_DELIVERED, 0) - IFNULL(TAB3.TOTAL_SALES_RETURN, 0)) AS LAKU, " +
                             "IF(MP.PRODUCT_STOCK_AWAL > 0, MP.PRODUCT_STOCK_QTY - (MP.PRODUCT_STOCK_AWAL+ (IFNULL(TAB1.TOTAL_RECEIVED, 0)-IFNULL(TAB4.TOTAL_RECEIVED_RETURN, 0)) - (IFNULL(TAB2.TOTAL_SALES, 0)-IFNULL(TAB3.TOTAL_SALES_RETURN, 0))), '0') AS PENYESUAIAN, " +
                             "'0' AS RIILQTY " +
                             "FROM MASTER_PRODUCT MP LEFT OUTER JOIN " +
                             "(SELECT PRODUCT_ID, SUM(PRODUCT_ACTUAL_QTY) AS TOTAL_RECEIVED FROM PRODUCTS_RECEIVED_HEADER PRH, PRODUCTS_RECEIVED_DETAIL PRD WHERE PRD.PR_INVOICE = PRH.PR_INVOICE AND DATE_FORMAT(PRH.PR_DATE , '%Y%m%d')  = '" + selectedDate + "' GROUP BY PRODUCT_ID) TAB1 ON TAB1.PRODUCT_ID = MP.PRODUCT_ID ";

                if (gUtil.isServerApp() == 0)
                {
                    sqlCommand = sqlCommand + "LEFT OUTER JOIN (SELECT PRODUCT_ID, SUM(PRODUCT_QTY) AS TOTAL_SALES FROM SALES_HEADER SH, SALES_DETAIL SD WHERE SH.SALES_TOP = 1 AND SD.SALES_INVOICE = SH.SALES_INVOICE AND DATE_FORMAT(SH.SALES_DATE, '%Y%m%d')  = '" + selectedDate + "' GROUP BY PRODUCT_ID) TAB2 ON TAB2.PRODUCT_ID = MP.PRODUCT_ID " +
                                 "LEFT OUTER JOIN (SELECT '0' AS PRODUCT_ID, 0 AS TOTAL_DELIVERED) TAB_DO ON TAB_DO.PRODUCT_ID = MP.PRODUCT_ID ";
                }
                else
                {
                    sqlCommand = sqlCommand + "LEFT OUTER JOIN(SELECT PRODUCT_ID, SUM(PRODUCT_QTY) AS TOTAL_SALES FROM SALES_HEADER SH, SALES_DETAIL SD WHERE SH.SALES_TOP = 1 AND SD.SALES_INVOICE = SH.SALES_INVOICE AND DATE_FORMAT(SH.SALES_DATE, '%Y%m%d') = '" + selectedDate + "' GROUP BY PRODUCT_ID) TAB2 ON TAB2.PRODUCT_ID = MP.PRODUCT_ID " +
                                 "LEFT OUTER JOIN(SELECT DD.PRODUCT_ID, SUM(DD.PRODUCT_QTY) AS TOTAL_DELIVERED FROM DELIVERY_ORDER_HEADER DH, DELIVERY_ORDER_DETAIL DD WHERE DATE_FORMAT(DH.DO_DATE, '%Y%m%d') = '" + selectedDate + "' GROUP BY PRODUCT_ID) TAB_DO ON TAB_DO.PRODUCT_ID = MP.PRODUCT_ID ";
                }

                sqlCommand = sqlCommand + "LEFT OUTER JOIN(SELECT PRODUCT_ID, SUM(PRODUCT_RETURN_QTY) AS TOTAL_SALES_RETURN FROM RETURN_SALES_HEADER RSH, RETURN_SALES_DETAIL RSD WHERE RSD.RS_INVOICE = RSH.RS_INVOICE AND DATE_FORMAT(RSH.RS_DATETIME, '%Y%m%d')  = '" + selectedDate + "' GROUP BY PRODUCT_ID) TAB3 ON TAB3.PRODUCT_ID = MP.PRODUCT_ID " +
                             "LEFT OUTER JOIN(SELECT PRODUCT_ID, SUM(PRODUCT_QTY) AS TOTAL_RECEIVED_RETURN FROM RETURN_PURCHASE_HEADER RPH, RETURN_PURCHASE_DETAIL RPD WHERE RPD.RP_ID = RPH.RP_ID AND DATE_FORMAT(RPH.RP_DATE, '%Y%m%d')  = '" + selectedDate + "' GROUP BY PRODUCT_ID) TAB4 ON TAB4.PRODUCT_ID = MP.PRODUCT_ID " +
                             "WHERE MP.PRODUCT_ACTIVE = 1";
                break;

            case EDIT_DAILY_STOCK_TAKE:
                sqlCommand = "SELECT PAD.PRODUCT_ID, MP.PRODUCT_NAME AS ROTI, PAD.PRODUCT_LAST_STOCK_QTY AS AWAL, PAD.PRODUCT_RECEIVED_QTY AS PRODUKSI, PAD.REMARKS AS REMARK, PAD.PRODUCT_BS_QTY AS BS, PAD.PRODUCT_LEFTOVER_QTY AS AKHIR, PAD.PRODUCT_SOLD_QTY AS LAKU, PAD.PRODUCT_ADJUSTMENT_QTY AS PENYESUAIAN, PAD.PRODUCT_RIIL_QTY AS RIILQTY " +
                             "FROM PRODUCT_DAILY_ADJUSTMENT_HEADER PAH, PRODUCT_DAILY_ADJUSTMENT_DETAIL AS PAD, MASTER_PRODUCT MP " +
                             "WHERE PAD.PRODUCT_ADJUSTMENT_ID = PAH.PRODUCT_ADJUSTMENT_ID AND PAD.PRODUCT_ID = MP.PRODUCT_ID AND DATE_FORMAT(PAH.PRODUCT_ADJUSTMENT_DATE, '%Y%m%d')  = '" + selectedDate + "'";
                break;
            }

            using (rdr = DS.getData(sqlCommand))
            {
                detailDataGrid.Rows.Clear();
                if (rdr.HasRows)
                {
                    while (rdr.Read())
                    {
                        detailDataGrid.Rows.Add(rdr.GetString("PRODUCT_ID"),
                                                detailDataGrid.Rows.Count + 1,
                                                rdr.GetString("ROTI"),
                                                rdr.GetString("AWAL"),
                                                rdr.GetString("PRODUKSI"),
                                                rdr.GetString("LAKU"),
                                                rdr.GetString("PENYESUAIAN"),
                                                rdr.GetString("BS"),
                                                rdr.GetString("AKHIR"),
                                                rdr.GetString("RIILQTY"),
                                                rdr.GetString("REMARK"),
                                                rdr.GetString("AKHIR")
                                                );
                        BSQty.Add(rdr.GetString("BS"));
                        //calculateAkhirValue(detailDataGrid.Rows.Count-1);
                    }
                }
            }
            rdr.Close();
        }
        private void loadProdukData()
        {
            MySqlDataReader rdr;
            DataTable       dt             = new DataTable();
            string          productShelves = "";
            string          fileName       = "";

            DS.mySqlConnect();

            // LOAD PRODUCT DATA
            using (rdr = DS.getData("SELECT * FROM MASTER_PRODUCT WHERE ID =  " + selectedInternalProductID))
            {
                if (rdr.HasRows)
                {
                    while (rdr.Read())
                    {
                        kodeProdukTextBox.Text = rdr.GetString("PRODUCT_ID");
                        barcodeTextBox.Text    = rdr.GetString("PRODUCT_BARCODE");
                        namaProdukTextBox.Text = rdr.GetString("PRODUCT_NAME");
                        produkDescTextBox.Text = rdr.GetString("PRODUCT_DESCRIPTION");
                        hppTextBox.Text        = rdr.GetString("PRODUCT_BASE_PRICE");
                        hargaEcerTextBox.Text  = rdr.GetString("PRODUCT_RETAIL_PRICE");
                        //hargaBSTextBox.Text = rdr.GetString("PRODUCT_BULK_PRICE");
                        //hargaGrosirTextBox.Text = rdr.GetString("PRODUCT_WHOLESALE_PRICE"); ;
                        merkTextBox.Text      = rdr.GetString("PRODUCT_BRAND");
                        stokAwalTextBox.Text  = rdr.GetString("PRODUCT_STOCK_QTY");
                        limitStokTextBox.Text = rdr.GetString("PRODUCT_LIMIT_STOCK");

                        productShelves = rdr.GetString("PRODUCT_SHELVES");

                        noRakBarisTextBox.Text = productShelves.Substring(0, 2);
                        noRakKolomTextBox.Text = productShelves.Substring(2);

                        selectedUnitID = rdr.GetInt32("UNIT_ID");
                        if (rdr.GetString("PRODUCT_ACTIVE").Equals("1"))
                        {
                            nonAktifCheckbox.Checked = false;
                        }
                        else
                        {
                            nonAktifCheckbox.Checked = true;
                        }

                        if (rdr.GetString("PRODUCT_IS_SERVICE").Equals("1"))
                        {
                            produkJasaCheckbox.Checked = true;
                            stokAwalTextBox.Enabled    = false;
                            limitStokTextBox.Enabled   = false;
                        }
                        else
                        {
                            produkJasaCheckbox.Checked = false;
                            stokAwalTextBox.Enabled    = true;
                            limitStokTextBox.Enabled   = true;
                        }

                        fileName = rdr.GetString("PRODUCT_PHOTO_1").Trim();

                        if (!fileName.Equals(""))
                        {
                            try
                            {
                                panelImage.BackgroundImageLayout = ImageLayout.Stretch;
                                panelImage.BackgroundImage       = Image.FromFile("PRODUCT_PHOTO/" + fileName);

                                selectedPhoto = "PRODUCT_PHOTO/" + fileName;
                                photoFileName = fileName;
                            }
                            catch (Exception ex)
                            {
                            }
                        }
                    }
                }
            }
        }