예제 #1
0
        private void BtnOK_Click(object sender, EventArgs e)
        {
            if (ValidateFields() == false)
            {
                return;
            }

            if (MessageBox.Show(this,
                                "Are you sure to change your Password ?",
                                J_Var.J_pProjectName,
                                MessageBoxButtons.YesNo,
                                MessageBoxIcon.Question,
                                MessageBoxDefaultButton.Button2) == DialogResult.Yes)
            {
                dmlService.J_BeginTransaction();
                strSQL = "UPDATE MST_USER " +
                         "SET    USER_PASSWORD = '******'" +
                         "WHERE  BRANCH_ID     =  " + J_Var.J_pBranchId + " " +
                         "AND    USER_ID       =  " + J_Var.J_pUserId + "";
                if (dmlService.J_ExecSql(strSQL) == false)
                {
                    dmlService.J_Rollback();
                    return;
                }
                dmlService.J_Commit();
                cmnService.J_PanelMessage(J_PanelIndex.e00_DisplayText, "Password Updated to :: ******* ");

                dmlService.Dispose();
                Application.Restart();
            }
        }
예제 #2
0
        private void BtnBackup_Click(object sender, EventArgs e)
        {
            try
            {
                txtPath.Visible = false;

                if (strLocalMachineName.Trim().ToUpper() != J_Var.J_pServerName.Trim().ToUpper())
                {
                    cmn.J_UserMessage("Please take the BACKUP from server itself !!");
                    BtnCancel.Select();
                    return;
                }

                string strFolderPath = cmn.J_OpenFolderDialog();
                if (strFolderPath == "")
                {
                    return;
                }

                cmn.J_BusyMode();
                dml.J_ClearDatabaseLog(J_Var.J_pDatabaseName);

                string strSQL = "EXEC SP_MSFOREACHTABLE " +
                                "@COMMAND1 = \"DROP TABLE ?\"," +
                                "@WHEREAND = \"AND ID IN (SELECT ID FROM SYSOBJECTS WHERE XTYPE = 'U' AND (NAME LIKE 'TMP%' OR NAME LIKE 'TEMP%'))\"";
                dml.J_ExecSql(strSQL);

                strPath = strFolderPath + "/" + J_Var.J_pBranchCode + "-" + string.Format("{0:yyyyMMdd}", System.DateTime.Now.Date) + "-" + string.Format("{0:HHmmss}", System.DateTime.Now) + ".BAK";
                strSQL  = "BACKUP DATABASE [" + J_Var.J_pDatabaseName + "] " +
                          "       TO DISK = '" + strPath + "'";
                dml.J_ExecSql(strSQL);

                txtPath.Visible = true;
                txtPath.Text    = "Collect the backup from : " + strPath + "";

                cmn.J_NormalMode();
            }
            catch (Exception err_handler)
            {
                cmn.J_UserMessage(err_handler.Message);
            }
        }
        private bool InsertMenuData(long MenuId,
                                    string GroupCode,
                                    string MenuGroup,
                                    string SLNo,
                                    string MenuName,
                                    string MenuDesc,
                                    string MenuSubDesc_1,
                                    bool MenuVisibility)
        {
            if (dmlService.J_IsRecordExist("MST_MENU", "MENU_ID = " + MenuId + "") == true)
            {
                return(true);
            }

            int intMenuVisibility = 0;

            if (MenuVisibility == false)
            {
                intMenuVisibility = 1;
            }

            strSQL = "INSERT INTO MST_MENU (" +
                     "            MENU_ID," +
                     "            MENU_GROUP_CODE," +
                     "            MENU_GROUP_NAME," +
                     "            MENU_SLNO," +
                     "            MENU_NAME," +
                     "            MENU_DESC," +
                     "            MENU_SUB_DESC_1," +
                     "            MENU_VISIBILITY) " +
                     "VALUES    ( " + MenuId + "," +
                     "           '" + GroupCode + "'," +
                     "           '" + MenuGroup + "'," +
                     "           '" + SLNo + "'," +
                     "           '" + MenuName + "'," +
                     "           '" + MenuDesc + "'," +
                     "           '" + MenuSubDesc_1 + "'," +
                     "            " + intMenuVisibility + ")";
            if (dmlService.J_ExecSql(strSQL) == false)
            {
                return(false);
            }
            return(true);
        }
        private void BtnSubmit_Click(object sender, EventArgs e)
        {
            // Ending Date should be on & after Begining Date
            if (dtService.J_IsDateGreater(ref mskBeginingDate, ref mskEndingDate, "Begining Date", "Ending Date", "Ending Date should be on & after Begining Date !!", J_ShowMessage.YES) == false)
            {
                return;
            }

            // Transaction is begining
            dmlService.J_BeginTransaction();

            string strSQL = "INSERT INTO MST_FAYEAR (" +
                            "            FAYEAR_ID," +
                            "            FA_BEG_DATE," +
                            "            FA_END_DATE) " +
                            "     VALUES(" +
                            "            1," +
                            "            " + cmnService.J_DateOperator() + dtService.J_ConvertMMddyyyy(mskBeginingDate.Text) + cmnService.J_DateOperator() + "," +
                            "            " + cmnService.J_DateOperator() + dtService.J_ConvertMMddyyyy(mskEndingDate.Text) + cmnService.J_DateOperator() + ")";

            if (dmlService.J_ExecSql(strSQL) == false)
            {
                mskBeginingDate.Select();
                return;
            }

            // Transaction has been commited
            dmlService.J_Commit();

            // Close & Dispose the MstLogin Class
            this.dmlService.Dispose();
            this.Close();
            this.Dispose();

            // Call the login form
            CmnLogin frm = new CmnLogin();

            frm.ShowDialog();
            frm.Dispose();
        }
예제 #5
0
        private void Insert_Update_Delete_Data()
        {
            try
            {
                switch (lblMode.Text)
                {
                case J_Mode.Add:

                    // all validation
                    if (ValidateFields() == false)
                    {
                        return;
                    }

                    // Save Confirmation Message
                    if (cmnService.J_SaveConfirmationMessage(ref txtPartyName) == true)
                    {
                        return;
                    }

                    // set the transaction as begin
                    dmlService.J_BeginTransaction();

                    // insert query & execution
                    strSQL = "INSERT INTO MST_PARTY (" +
                             "            BRANCH_ID," +
                             "            PARTY_CATEGORY_ID," +
                             "            PARTY_NAME," +
                             "            ADDRESS1," +
                             "            ADDRESS2," +
                             "            ADDRESS3," +
                             "            CITY," +
                             "            PIN," +
                             "            CONTACT_PERSON," +
                             "            MOBILE_NO," +
                             "            PHONE_NO," +
                             "            FAX," +
                             "            EMAIL_ID," +
                             "            VAT_NO," +
                             "            USER_ID," +
                             "            CREATE_DATE) " +
                             "    VALUES( " + J_Var.J_pBranchId + "," +
                             "            " + Support.GetItemData(cmbPartyCategory, cmbPartyCategory.SelectedIndex) + "," +
                             "           '" + cmnService.J_ReplaceQuote(txtPartyName.Text.Trim()) + "'," +
                             "           '" + cmnService.J_ReplaceQuote(txtAddress1.Text.Trim()) + "'," +
                             "           '" + cmnService.J_ReplaceQuote(txtAddress2.Text.Trim()) + "'," +
                             "           '" + cmnService.J_ReplaceQuote(txtAddress3.Text.Trim()) + "'," +
                             "           '" + cmnService.J_ReplaceQuote(txtCity.Text.Trim()) + "'," +
                             "           '" + cmnService.J_ReplaceQuote(txtPin.Text.Trim()) + "'," +
                             "           '" + cmnService.J_ReplaceQuote(txtContactPersonName.Text.Trim()) + "'," +
                             "           '" + cmnService.J_ReplaceQuote(txtMobileNo.Text.Trim()) + "'," +
                             "           '" + cmnService.J_ReplaceQuote(txtPhone.Text.Trim()) + "'," +
                             "           '" + cmnService.J_ReplaceQuote(txtFaxNo.Text.Trim()) + "'," +
                             "           '" + cmnService.J_ReplaceQuote(txtEmailID.Text.Trim()) + "'," +
                             "           '" + cmnService.J_ReplaceQuote(txtVatNo.Text.Trim()) + "'," +
                             "            " + J_Var.J_pUserId + "," +
                             "            " + cmnService.J_DateOperator() + dtService.J_ConvertMMddyyyy(dmlService.J_ReturnServerDate()) + cmnService.J_DateOperator() + ")";
                    if (dmlService.J_ExecSql(strSQL) == false)
                    {
                        txtPartyName.Select();
                        return;
                    }

                    // get last inserted area id as per branch & user
                    lngSearchId = dmlService.J_ReturnMaxValue("MST_PARTY", "PARTY_ID",
                                                              "    BRANCH_ID         = " + J_Var.J_pBranchId + " " +
                                                              "AND USER_ID           = " + J_Var.J_pUserId + "");
                    if (lngSearchId == 0)
                    {
                        return;
                    }

                    // Transaction is commited
                    dmlService.J_Commit();

                    // after insert data, the message is displayed
                    cmnService.J_PanelMessage(J_PanelIndex.e00_DisplayText, J_Msg.AddModeSave);

                    // all controls are cleared
                    ClearControls();
                    txtPartyName.Select();

                    break;

                case J_Mode.Edit:

                    // all validation
                    if (ValidateFields() == false)
                    {
                        return;
                    }

                    // Save Confirmation Message
                    if (cmnService.J_SaveConfirmationMessage(ref txtPartyName) == true)
                    {
                        return;
                    }

                    // set the transaction as begin
                    dmlService.J_BeginTransaction();

                    // update query & execution
                    strSQL = "UPDATE MST_PARTY " +
                             "SET    PARTY_CATEGORY_ID =  " + Support.GetItemData(cmbPartyCategory, cmbPartyCategory.SelectedIndex) + ", " +
                             "       PARTY_NAME        = '" + cmnService.J_ReplaceQuote(txtPartyName.Text.Trim()) + "'," +
                             "       ADDRESS1          = '" + cmnService.J_ReplaceQuote(txtAddress1.Text.Trim()) + "'," +
                             "       ADDRESS2          = '" + cmnService.J_ReplaceQuote(txtAddress2.Text.Trim()) + "'," +
                             "       ADDRESS3          = '" + cmnService.J_ReplaceQuote(txtAddress3.Text.Trim()) + "'," +
                             "       CITY              = '" + cmnService.J_ReplaceQuote(txtCity.Text.Trim()) + "'," +
                             "       PIN               = '" + cmnService.J_ReplaceQuote(txtPin.Text.Trim()) + "'," +
                             "       CONTACT_PERSON    = '" + cmnService.J_ReplaceQuote(txtContactPersonName.Text.Trim()) + "'," +
                             "       MOBILE_NO         = '" + cmnService.J_ReplaceQuote(txtMobileNo.Text.Trim()) + "'," +
                             "       PHONE_NO          = '" + cmnService.J_ReplaceQuote(txtPhone.Text.Trim()) + "'," +
                             "       FAX               = '" + cmnService.J_ReplaceQuote(txtFaxNo.Text.Trim()) + "'," +
                             "       EMAIL_ID          = '" + cmnService.J_ReplaceQuote(txtEmailID.Text.Trim()) + "'," +
                             "       VAT_NO            = '" + cmnService.J_ReplaceQuote(txtVatNo.Text.Trim()) + "'" +
                             "WHERE  PARTY_ID          =  " + lngSearchId + "";
                    if (dmlService.J_ExecSql(strSQL) == false)
                    {
                        txtPartyName.Select();
                        return;
                    }

                    // Transaction is commited
                    dmlService.J_Commit();

                    // after insert data, the message is displayed
                    cmnService.J_PanelMessage(J_PanelIndex.e00_DisplayText, J_Msg.EditModeSave);

                    // all controls are cleared
                    ClearControls();

                    // Refresh the view grid
                    strSQL = strQuery + "ORDER BY " + strOrderBy;
                    if (dsetGridClone != null)
                    {
                        dsetGridClone.Clear();
                    }
                    dsetGridClone = dmlService.J_ShowDataInGrid(ref ViewGrid, strSQL, strMatrix);           //Show Data into the Grid
                    if (dsetGridClone == null)
                    {
                        return;
                    }

                    // change the buttons status
                    lblMode.Text = J_Mode.View;
                    cmnService.J_StatusButton(this, lblMode.Text);

                    BtnDelete.BackColor = Color.LightGray;
                    BtnDelete.Enabled   = false;

                    ControlVisible(false);

                    dmlService.J_setGridPosition(ref this.ViewGrid, dsetGridClone, "PARTY_ID", lngSearchId);
                    break;

                case J_Mode.Delete:
                    break;
                }
            }
            catch (Exception err_handler)
            {
                dmlService.J_Rollback();
                cmnService.J_UserMessage(err_handler.Message);
            }
        }
예제 #6
0
        private void Insert_Update_Delete_Data()
        {
            try
            {
                string strLastDate = string.Empty;
                //-----------------------------------------------------------
                if (mskLastDate.Text == "  /  /")
                {
                    strLastDate = "null";
                }
                else
                {
                    strLastDate = cmnService.J_DateOperator() + dtService.J_ConvertMMddyyyy(mskLastDate.Text) + cmnService.J_DateOperator();
                }

                switch (lblMode.Text)
                {
                case J_Mode.Add:

                    // all validation
                    if (ValidateFields() == false)
                    {
                        return;
                    }

                    // Save Confirmation Message
                    if (cmnService.J_SaveConfirmationMessage(ref txtPrefix) == true)
                    {
                        return;
                    }

                    // set the transaction as begin
                    dmlService.J_BeginTransaction();


                    // Area Code Logic
                    //mf.MF_GetAreaCode(dmlService.J_pCommand, J_Var.J_pBranchCode)

                    // insert query & execution
                    strSQL = "INSERT INTO MST_INVOICE_SERIES (" +
                             "            BRANCH_ID," +
                             "            COMPANY_ID," +
                             "            PREFIX," +
                             "            START_NO," +
                             "            LAST_NO," +
                             "            LAST_DATE," +
                             "            HEADER_DISPLAY_TEXT," +
                             "            USER_ID," +
                             "            CREATE_DATE) " +
                             "    VALUES( " + J_Var.J_pBranchId + "," +
                             "            " + Convert.ToInt32(Support.GetItemData(cmbCompanyName, cmbCompanyName.SelectedIndex)) + "," +
                             "           '" + cmnService.J_ReplaceQuote(txtPrefix.Text.Trim()) + "'," +
                             "            " + Convert.ToInt32(cmnService.J_NumericData(txtStartNo)) + "," +
                             "            " + Convert.ToInt32(cmnService.J_NumericData(txtStartNo)) + "," +
                             "            " + strLastDate + "," +
                             "           '" + cmnService.J_ReplaceQuote(txtDisplayText.Text.Trim()) + "'," +
                             "            " + J_Var.J_pUserId + "," +
                             "            " + cmnService.J_DateOperator() + dtService.J_ConvertMMddyyyy(dmlService.J_ReturnServerDate()) + cmnService.J_DateOperator() + ")";
                    if (dmlService.J_ExecSql(strSQL) == false)
                    {
                        cmbCompanyName.Select();
                        return;
                    }

                    // get last inserted area id as per branch & user
                    lngSearchId = dmlService.J_ReturnMaxValue("MST_INVOICE_SERIES", "INVOICE_SERIES_ID",
                                                              "    BRANCH_ID         = " + J_Var.J_pBranchId + " " +
                                                              "AND USER_ID           = " + J_Var.J_pUserId + "");
                    if (lngSearchId == 0)
                    {
                        return;
                    }

                    // Transaction is commited
                    dmlService.J_Commit();

                    // after insert data, the message is displayed
                    cmnService.J_PanelMessage(J_PanelIndex.e00_DisplayText, J_Msg.AddModeSave);

                    // all controls are cleared
                    ClearControls();
                    cmbCompanyName.Select();

                    break;

                case J_Mode.Edit:

                    // all validation
                    if (ValidateFields() == false)
                    {
                        return;
                    }

                    // Save Confirmation Message
                    if (cmnService.J_SaveConfirmationMessage(ref txtPrefix) == true)
                    {
                        return;
                    }

                    // set the transaction as begin
                    dmlService.J_BeginTransaction();

                    // update query & execution
                    strSQL = "UPDATE MST_INVOICE_SERIES " +
                             "SET    COMPANY_ID          =  " + Convert.ToInt32(Support.GetItemData(cmbCompanyName, cmbCompanyName.SelectedIndex)) + "," +
                             "       PREFIX              = '" + cmnService.J_ReplaceQuote(txtPrefix.Text.Trim()) + "'," +
                             "       START_NO            =  " + Convert.ToInt32(cmnService.J_NumericData(txtStartNo)) + "," +
                             "       LAST_NO             =  " + Convert.ToInt32(cmnService.J_NumericData(txtStartNo)) + "," +
                             "       LAST_DATE           = " + strLastDate + "," +
                             "       HEADER_DISPLAY_TEXT = '" + cmnService.J_ReplaceQuote(txtDisplayText.Text.Trim()) + "' " +
                             "WHERE  INVOICE_SERIES_ID   =  " + lngSearchId + "";
                    if (dmlService.J_ExecSql(strSQL) == false)
                    {
                        cmbCompanyName.Select();
                        return;
                    }

                    // Transaction is commited
                    dmlService.J_Commit();

                    // after insert data, the message is displayed
                    cmnService.J_PanelMessage(J_PanelIndex.e00_DisplayText, J_Msg.EditModeSave);

                    // all controls are cleared
                    ClearControls();

                    // Refresh the view grid
                    strSQL = strQuery + "ORDER BY " + strOrderBy;
                    if (dsetGridClone != null)
                    {
                        dsetGridClone.Clear();
                    }
                    dsetGridClone = dmlService.J_ShowDataInGrid(ref ViewGrid, strSQL, strMatrix);           //Show Data into the Grid
                    if (dsetGridClone == null)
                    {
                        return;
                    }

                    // change the buttons status
                    lblMode.Text = J_Mode.View;
                    cmnService.J_StatusButton(this, lblMode.Text);

                    BtnDelete.BackColor = Color.LightGray;
                    BtnDelete.Enabled   = false;

                    ControlVisible(false);

                    dmlService.J_setGridPosition(ref this.ViewGrid, dsetGridClone, "INVOICE_SERIES_ID", lngSearchId);
                    break;

                case J_Mode.Delete:
                    break;
                }
            }
            catch (Exception err_handler)
            {
                dmlService.J_Rollback();
                cmnService.J_UserMessage(err_handler.Message);
            }
        }
예제 #7
0
        private void BtnSave_Click(object sender, EventArgs e)
        {
            // Added by Ripan Paul on 23-07-2011
            if (dmlService.J_IsRecordExist("MST_PARTY",
                                           "    BRANCH_ID  =  " + J_Var.J_pBranchId + " " +
                                           "AND PARTY_NAME = '" + cmnService.J_ReplaceQuote(txtPartyName.Text.Trim().ToUpper()) + "'") == true)
            {
                cmnService.J_UserMessage("Duplicate Party found ...");
                txtPartyName.Select();
                return;
            }

            // Save Confirmation Message
            if (cmnService.J_SaveConfirmationMessage(ref txtAddress1) == true)
            {
                return;
            }

            // Transaction is started
            dmlService.J_BeginTransaction();

            // insert query & execution
            strSQL = "INSERT INTO MST_PARTY (" +
                     "            BRANCH_ID," +
                     "            PARTY_CATEGORY_ID," +
                     "            PARTY_NAME," +
                     "            ADDRESS1," +
                     "            ADDRESS2," +
                     "            ADDRESS3," +
                     "            CITY," +
                     "            PIN," +
                     "            CONTACT_PERSON," +
                     "            MOBILE_NO," +
                     "            PHONE_NO," +
                     "            FAX," +
                     "            EMAIL_ID," +
                     "            USER_ID," +
                     "            CREATE_DATE) " +
                     "    VALUES( " + J_Var.J_pBranchId + "," +
                     "            " + Support.GetItemData(cmbPartyCategory, cmbPartyCategory.SelectedIndex) + ", " +
                     "           '" + cmnService.J_ReplaceQuote(txtPartyName.Text.Trim()) + "'," +
                     "           '" + cmnService.J_ReplaceQuote(txtAddress1.Text.Trim()) + "'," +
                     "           '" + cmnService.J_ReplaceQuote(txtAddress2.Text.Trim()) + "'," +
                     "           '" + cmnService.J_ReplaceQuote(txtAddress3.Text.Trim()) + "'," +
                     "           '" + cmnService.J_ReplaceQuote(txtCity.Text.Trim()) + "'," +
                     "           '" + cmnService.J_ReplaceQuote(txtPin.Text.Trim()) + "'," +
                     "           '" + cmnService.J_ReplaceQuote(txtContactPersonName.Text.Trim()) + "'," +
                     "           '" + cmnService.J_ReplaceQuote(txtMobileNo.Text.Trim()) + "'," +
                     "           '" + cmnService.J_ReplaceQuote(txtPhone.Text.Trim()) + "'," +
                     "           '" + cmnService.J_ReplaceQuote(txtFaxNo.Text.Trim()) + "'," +
                     "           '" + cmnService.J_ReplaceQuote(txtEmailID.Text.Trim()) + "'," +
                     "            " + J_Var.J_pUserId + "," +
                     "            " + cmnService.J_DateOperator() + dtService.J_ConvertMMddyyyy(dmlService.J_ReturnServerDate()) + cmnService.J_DateOperator() + ")";
            if (dmlService.J_ExecSql(strSQL) == false)
            {
                txtPartyName.Select();
                return;
            }

            // Transaction is commited
            dmlService.J_Commit();

            // Added by Ripan Paul on 23-07-2011
            BS.BS_PartyName = txtPartyName.Text.Trim();

            // after data is inserted, the message is displayed
            cmnService.J_PanelMessage(J_PanelIndex.e00_DisplayText, J_Msg.AddModeSave);

            // exit from this form
            BtnExit_Click(sender, e);
        }
예제 #8
0
        private void BtnSave_Click(object sender, EventArgs e)
        {
            // Transaction is started
            dmlService.J_BeginTransaction();

            // report file object
            ReportClass rptcls;
            crInvoice   rptInvoice = new crInvoice();

            rptcls = (ReportClass)rptInvoice;

            // Temp Table Name
            string strTableName = "TMP_InvoiceType_" + J_Var.J_pUserId + "_" + string.Format("{0:ddMMyy}", System.DateTime.Now.Date) + "_" + string.Format("{0:HHmmss}", System.DateTime.Now);

            // Create the report temp table
            strSQL = "CREATE TABLE " + strTableName + "(" +
                     "             " + cmnService.J_GetDataType("INVOICE_TYPE_ID", J_ColumnType.Long) + "," +
                     "             " + cmnService.J_GetDataType("INVOICE_TYPE", J_ColumnType.String, 50) + "," +
                     "             " + cmnService.J_GetDataType("PRINT_TYPE", J_ColumnType.Integer) + ")";
            if (dmlService.J_ExecSql(strSQL, J_SQLType.DDL) == false)
            {
                return;
            }

            // print type
            int intPrintType = 0;

            if (rbnSort1_2.Checked == true)
            {
                intPrintType = 1;
            }

            // invoice type
            //Added by Shrey Kejriwal on 05/08/2011
            if (chkBox1.Checked == true)
            {
                strSQL = "INSERT INTO " + strTableName + " (INVOICE_TYPE_ID, INVOICE_TYPE                                                    , PRINT_TYPE) " +
                         "                          VALUES (1              , '" + cmnService.J_ReplaceQuote("(Original - Buyer's Copy)") + "', " + intPrintType + ")";
                if (dmlService.J_ExecSql(strSQL) == false)
                {
                    return;
                }
            }

            if (chkBox2.Checked == true)
            {
                strSQL = "INSERT INTO " + strTableName + " (INVOICE_TYPE_ID, INVOICE_TYPE      , PRINT_TYPE) " +
                         "                          VALUES (2              , '(Duplicate Copy)', " + intPrintType + ")";
                if (dmlService.J_ExecSql(strSQL) == false)
                {
                    return;
                }
            }

            if (chkBox3.Checked == true)
            {
                strSQL = "INSERT INTO " + strTableName + " (INVOICE_TYPE_ID, INVOICE_TYPE     , PRINT_TYPE) " +
                         "                          VALUES (3              , '(Office Copy)', " + intPrintType + ")";
                if (dmlService.J_ExecSql(strSQL) == false)
                {
                    return;
                }
            }

            #region COMMENTED
            //Commented by Shrey Kejriwal on 05/08/2011
            //if (rbnSort1.Checked == true)
            //{
            //    strSQL = "INSERT INTO " + strTableName + " (INVOICE_TYPE_ID, INVOICE_TYPE                                                    , PRINT_TYPE) " +
            //             "                          VALUES (1              , '" + cmnService.J_ReplaceQuote("(Original - Buyer's Copy)") + "', " + intPrintType + ")";
            //    if (dmlService.J_ExecSql(strSQL) == false) return;

            //    strSQL = "INSERT INTO " + strTableName + " (INVOICE_TYPE_ID, INVOICE_TYPE      , PRINT_TYPE) " +
            //             "                          VALUES (2              , '(Duplicate Copy)', " + intPrintType + ")";
            //    if (dmlService.J_ExecSql(strSQL) == false) return;

            //    strSQL = "INSERT INTO " + strTableName + " (INVOICE_TYPE_ID, INVOICE_TYPE     , PRINT_TYPE) " +
            //             "                          VALUES (3              , '(Office Copy)', " + intPrintType + ")";
            //    if (dmlService.J_ExecSql(strSQL) == false) return;
            //}
            //else if (rbnSort2.Checked == true)
            //{
            //    strSQL = "INSERT INTO " + strTableName + " (INVOICE_TYPE_ID, INVOICE_TYPE                                                    , PRINT_TYPE) " +
            //             "                          VALUES (1              , '" + cmnService.J_ReplaceQuote("(Original - Buyer's Copy)") + "', " + intPrintType + ")";
            //    if (dmlService.J_ExecSql(strSQL) == false) return;
            //}
            //else if (rbnSort3.Checked == true)
            //{
            //    strSQL = "INSERT INTO " + strTableName + " (INVOICE_TYPE_ID, INVOICE_TYPE      , PRINT_TYPE) " +
            //             "                          VALUES (2              , '(Duplicate Copy)', " + intPrintType + ")";
            //    if (dmlService.J_ExecSql(strSQL) == false) return;
            //}
            //else if (rbnSort4.Checked == true)
            //{
            //    strSQL = "INSERT INTO " + strTableName + " (INVOICE_TYPE_ID, INVOICE_TYPE     , PRINT_TYPE) " +
            //             "                          VALUES (3              , '(Office Copy)', " + intPrintType + ")";
            //    if (dmlService.J_ExecSql(strSQL) == false) return;
            //}
            #endregion
            //
            strSQL = "SELECT " + strTableName + ".INVOICE_TYPE_ID           AS INVOICE_TYPE_ID," +
                     "       " + strTableName + ".INVOICE_TYPE              AS INVOICE_TYPE," +
                     "       " + strTableName + ".PRINT_TYPE                AS PRINT_TYPE," +
                     "       TRN_INVOICE_HEADER.INVOICE_HEADER_ID           AS INVOICE_HEADER_ID," +
                     "       MST_INVOICE_SERIES.INVOICE_SERIES_ID           AS INVOICE_SERIES_ID," +
                     "       MST_INVOICE_SERIES.HEADER_DISPLAY_TEXT         AS HEADER_DISPLAY_TEXT," +
                     "       MST_PARTY.PARTY_NAME                           AS PARTY_NAME," +
                     "       MST_PARTY.ADDRESS1                             AS ADDRESS1," +
                     "       MST_PARTY.ADDRESS2                             AS ADDRESS2," +
                     "       MST_PARTY.ADDRESS3                             AS ADDRESS3," +
                     "       MST_PARTY.CITY                                 AS CITY," +
                     "       MST_PARTY.PIN                                  AS PIN," +
                     "       MST_PARTY.MOBILE_NO                            AS MOBILE_NO," +
                     "       MST_PARTY.PHONE_NO                             AS PHONE_NO," +
                     "       MST_PARTY.FAX                                  AS FAX," +
                     "       MST_PARTY.CONTACT_PERSON                       AS CONTACT_PERSON," +
                     "       TRN_INVOICE_HEADER.INVOICE_NO                  AS INVOICE_NO," +
                     "       TRN_INVOICE_HEADER.INVOICE_DATE                AS INVOICE_DATE," +
                     "       TRN_INVOICE_HEADER.CHALLAN_REF_NO              AS CHALLAN_REF_NO," +
                     "       TRN_INVOICE_HEADER.ORDER_NO                    AS ORDER_NO," +
                     "       TRN_INVOICE_HEADER.TOTAL_AMOUNT                AS TOTAL_AMOUNT," +
                     "       TRN_INVOICE_HEADER.DISCOUNT_TEXT               AS DISCOUNT_TEXT," +
                     "       TRN_INVOICE_HEADER.DISCOUNT_RATE               AS DISCOUNT_RATE," +
                     "       TRN_INVOICE_HEADER.DISCOUNT_AMOUNT             AS DISCOUNT_AMOUNT," +
                     "       TRN_INVOICE_HEADER.AMOUNT_WITH_DISCOUNT        AS AMOUNT_WITH_DISCOUNT," +
                     "       TRN_INVOICE_HEADER.TAX_TOTAL_AMOUNT            AS TAX_TOTAL_AMOUNT," +
                     "       TRN_INVOICE_HEADER.AMOUNT_WITH_TAX             AS AMOUNT_WITH_TAX," +
                     "       TRN_INVOICE_HEADER.ADDITIONAL_COST_TEXT        AS ADDITIONAL_COST_TEXT," +
                     "       TRN_INVOICE_HEADER.ADDITIONAL_COST             AS ADDITIONAL_COST," +
                     "       TRN_INVOICE_HEADER.AMOUNT_WITH_ADDITIONAL_COST AS AMOUNT_WITH_ADDITIONAL_COST," +
                     "       TRN_INVOICE_HEADER.ROUNDED_OFF                 AS ROUNDED_OFF," +
                     "       TRN_INVOICE_HEADER.NET_AMOUNT                  AS NET_AMOUNT," +
                     "       TRN_INVOICE_HEADER.NET_AMOUNT_INWORDS          AS NET_AMOUNT_INWORDS," +
                     "       MST_ITEM.ITEM_ID                               AS ITEM_ID," +
                     "       MST_ITEM.ITEM_NAME                             AS ITEM_NAME," +
                     "       TRN_INVOICE_DETAIL.QUANTITY                    AS QUANTITY," +
                     "       MST_ITEM.UNIT                                  AS UNIT," +
                     "       TRN_INVOICE_DETAIL.RATE                        AS RATE," +
                     "       TRN_INVOICE_DETAIL.AMOUNT                      AS AMOUNT," +
                     "       TRN_INVOICE_DETAIL.REMARKS                     AS REMARKS," +
                     "       MST_COMPANY.COMPANY_ID                         AS COMPANY_ID," +
                     "       MST_COMPANY.COMPANY_NAME                       AS COMPANY_NAME," +
                     "       MST_COMPANY.ADDRESS1                           AS COMPANY_ADDRESS1," +
                     "       MST_COMPANY.ADDRESS2                           AS COMPANY_ADDRESS2," +
                     "       MST_COMPANY.ADDRESS3                           AS COMPANY_ADDRESS3," +
                     "       MST_COMPANY.PIN                                AS COMPANY_PIN," +
                     "       MST_COMPANY.CONTACT_NO                         AS COMPANY_CONTACT_NO," +
                     "       MST_COMPANY.FAX                                AS COMPANY_FAX," +
                     "       MST_COMPANY.EMAIL_ID                           AS COMPANY_EMAIL_ID," +
                     "       MST_COMPANY.WEB_SITE                           AS COMPANY_WEB_SITE," +
                     "       MST_COMPANY.VAT_NO                             AS VAT_NO," +
                     "       MST_COMPANY.CST_NO                             AS CST_NO," +
                     "       MST_COMPANY.SERVICE_TAX_NO                     AS SERVICE_TAX_NO," +
                     "       MST_COMPANY.PAN                                AS PAN, " +
                     "       MST_COMPANY.CIN_NO                             AS CIN_NO," +
                     "       TRN_INVOICE_HEADER.DELIVERY_MARK               AS DELIVERY_MARK," +
                     "       MST_COMPANY.BANK_DETAIL1                       AS BANK_DETAIL1," +
                     "       MST_COMPANY.BANK_DETAIL2                       AS BANK_DETAIL2," +
                     "       MST_COMPANY.BANK_DETAIL3                       AS BANK_DETAIL3," +
                     "       MST_COMPANY.BANK_DETAIL4                       AS BANK_DETAIL4," +
                     "       MST_COMPANY.BANK_DETAIL5                       AS BANK_DETAIL5 " +
                     "FROM   TRN_INVOICE_HEADER," +
                     "       TRN_INVOICE_DETAIL," +
                     "       MST_INVOICE_SERIES," +
                     "       MST_COMPANY," +
                     "       MST_PARTY," +
                     "       MST_ITEM," +
                     "       " + strTableName + " " +
                     "WHERE  TRN_INVOICE_HEADER.INVOICE_HEADER_ID = TRN_INVOICE_DETAIL.INVOICE_HEADER_ID " +
                     "AND    TRN_INVOICE_HEADER.INVOICE_SERIES_ID = MST_INVOICE_SERIES.INVOICE_SERIES_ID " +
                     "AND    TRN_INVOICE_HEADER.COMPANY_ID        = MST_COMPANY.COMPANY_ID " +
                     "AND    TRN_INVOICE_HEADER.PARTY_ID          = MST_PARTY.PARTY_ID " +
                     "AND    TRN_INVOICE_DETAIL.ITEM_ID           = MST_ITEM.ITEM_ID " +
                     "AND    TRN_INVOICE_HEADER.INVOICE_HEADER_ID = " + lngInvoiceHeaderId + " " +
                     "AND    TRN_INVOICE_HEADER.BRANCH_ID         = " + J_Var.J_pBranchId + " " +
                     "ORDER BY " + strTableName + ".INVOICE_TYPE_ID," +
                     "       TRN_INVOICE_HEADER.INVOICE_NO";

            // SUB REPORTS
            // FOR SUMMARY OF TAX DETAILS
            string strSubRptTaxDetails = "SELECT MST_TAX.TAX_ID                    AS TAX_ID," +
                                         "       MST_TAX.TAX_DESC                  AS TAX_DESC," +
                                         "       TRN_INVOICE_TAX.INVOICE_HEADER_ID AS INVOICE_HEADER_ID," +
                                         "       TRN_INVOICE_TAX.TAX_RATE          AS TAX_RATE," +
                                         "       TRN_INVOICE_TAX.TAX_AMOUNT        AS TAX_AMOUNT " +
                                         "FROM   MST_TAX," +
                                         "       TRN_INVOICE_TAX " +
                                         "WHERE  MST_TAX.TAX_ID                    = TRN_INVOICE_TAX.TAX_ID " +
                                         "AND    TRN_INVOICE_TAX.INVOICE_HEADER_ID = " + lngInvoiceHeaderId + " " +
                                         "AND    MST_TAX.BRANCH_ID                 = " + J_Var.J_pBranchId + " " +
                                         "ORDER BY TRN_INVOICE_TAX.INVOICE_TAX_ID";

            // Transaction is commited
            dmlService.J_Commit();

            // POPULATE & DISPLAY SUB REPORT
            rptcls.OpenSubreport("crSubRptTaxSummary").SetDataSource(dmlService.J_ExecSqlReturnDataSet(strSubRptTaxDetails).Tables[0]);

            PictureObject objBlobFieldObject;
            objBlobFieldObject = (PictureObject)rptcls.ReportDefinition.Sections[2].ReportObjects["imgSignature"];
            objBlobFieldObject.ObjectFormat.EnableSuppress = true;

            // report is executed
            DataSet ds = dmlService.J_ExecSqlReturnDataSet(strSQL);
            if (ds == null)
            {
                return;
            }
            rptService.J_PreviewReport(ref rptcls, this, ds, J_Var.J_pCompanyName, J_Var.J_pBranchAddress, "INVOICE");

            // Drop the report temp table
            if (dmlService.J_ExecSql("DROP TABLE " + strTableName) == false)
            {
                return;
            }
            //--
        }
        private void BtnSave_Click(object sender, EventArgs e)
        {
            try
            {
                // All validation
                if (ValidateFields() == false)
                {
                    return;
                }

                // Transaction is started
                dmlService.J_BeginTransaction();
                //---------------------------------------------
                strSQL = @" UPDATE MST_PARTY
                            SET    MOBILE_NO = '" + cmnService.J_ReplaceQuote(txtMobileNo.Text.Trim()) + @"',
                                   EMAIL_ID  = '" + cmnService.J_ReplaceQuote(txtEmailID.Text.Trim()) + @"',
                                   CONTACT_PERSON  = '" + cmnService.J_ReplaceQuote(txtContactPersonName.Text.Trim()) + @"'
                            WHERE  PARTY_ID  = " + lngPartyId;

                if (dmlService.J_ExecSql(strSQL) == false)
                {
                    //Rollback Transaction
                    dmlService.J_Rollback();
                    return;
                }
                //---------------------------------------------
                //Commit Transaction
                //---------------------------------------------
                dmlService.J_Commit();
                //---------------------------------------------
                BS.BS_PartyContactPerson = cmnService.J_ReplaceQuote(txtContactPersonName.Text.Trim());
                BS.BS_PartyMobNo         = cmnService.J_ReplaceQuote(txtMobileNo.Text.Trim());
                BS.BS_PartyEmailId       = cmnService.J_ReplaceQuote(txtEmailID.Text.Trim());
                //
                if (chkSMS.Checked == true)
                {
                    BS.BS_PartySendSMS = true;
                }
                else
                {
                    BS.BS_PartySendSMS = false;
                }
                //
                if (chkEmail.Checked == true)
                {
                    BS.BS_PartySendEmail = true;
                }
                else
                {
                    BS.BS_PartySendEmail = false;
                }
                //---------------------------------------------
                BS.BS_SaveInvoiceStatus = true;

                dmlService.Dispose();
                this.Close();
                this.Dispose();
            }
            catch (Exception err)
            {
                //Rollback Transaction
                dmlService.J_Rollback();

                cmnService.J_UserMessage(err.Message);
            }
        }
        private void Insert_Update_Delete_Data()
        {
            try
            {
                switch (lblMode.Text)
                {
                case J_Mode.Add:

                    // all validations
                    if (ValidateFields() == false)
                    {
                        return;
                    }

                    // save confirmation message
                    if (cmnService.J_SaveConfirmationMessage(ref cmbUserId) == true)
                    {
                        return;
                    }

                    // begin the transaction
                    dmlService.J_BeginTransaction();

                    // delete query & execution
                    strSQL = "DELETE FROM MST_USER_RIGHTS " +
                             "     WHERE  USER_ID = " + Convert.ToInt64(Support.GetItemData(cmbUserId, cmbUserId.SelectedIndex)) + " ";
                    if (dmlService.J_ExecSql(strSQL) == false)
                    {
                        cmbUserId.Select();
                        return;
                    }

                    // selected menu is inserted
                    foreach (DataGridViewRow row in grdvDescription.Rows)
                    {
                        if (row.Cells[0].Value != null && (bool)row.Cells[0].Value == true)
                        {
                            strSQL = "INSERT INTO MST_USER_RIGHTS(" +
                                     "            USER_ID," +
                                     "            MENU_ID)" +
                                     "VALUES(" +
                                     "            " + Convert.ToInt64(Support.GetItemData(cmbUserId, cmbUserId.SelectedIndex)) + "," +
                                     "            " + Convert.ToInt64(Convert.ToString(row.Cells[1].Value)) + ")";
                            if (dmlService.J_ExecSql(strSQL) == false)
                            {
                                cmbUserId.Select();
                                return;
                            }
                        }
                    }

                    // transaction is commited
                    dmlService.J_Commit();

                    // display the message in status bur
                    cmnService.J_PanelMessage(J_PanelIndex.e00_DisplayText, "Menu rights are successfully completed");

                    // all controls are cleared
                    ClearControls();
                    cmbUserId.Select();

                    break;

                case J_Mode.Edit:
                    break;

                case J_Mode.Delete:
                    break;
                }
            }
            catch (Exception err_handler)
            {
                dmlService.J_Rollback();
                cmnService.J_UserMessage(err_handler.Message);
            }
        }
예제 #11
0
        private void Insert_Update_Delete_Data()
        {
            try
            {
                switch (lblMode.Text)
                {
                case J_Mode.Add:

                    break;

                case J_Mode.Edit:
                    //*****  For Modify
                    //-----------------------------------------------------------
                    if (ValidateFields() == false)
                    {
                        return;
                    }

                    //--Assigning Cancellation Status to a variable

                    //-----------------------------------------------------------
                    dmlService.J_BeginTransaction();
                    //-----------------------------------------------------------

                    string strAccountEntryDate = "null";
                    string strBankDate         = "null";

                    if (!dtService.J_IsBlankDateCheck(ref mskAccountEntryDate, J_ShowMessage.NO))
                    {
                        strAccountEntryDate = cmnService.J_DateOperator() + dtService.J_ConvertMMddyyyy(mskAccountEntryDate.Text) + cmnService.J_DateOperator();
                    }

                    if (!dtService.J_IsBlankDateCheck(ref mskBankDate, J_ShowMessage.NO))
                    {
                        strBankDate = cmnService.J_DateOperator() + dtService.J_ConvertMMddyyyy(mskBankDate.Text) + cmnService.J_DateOperator();
                    }
                    //UPDATING HEADER RECORD
                    strSQL = "UPDATE TRN_INVOICE_HEADER " +
                             "SET    PAYMENT_TYPE_ID     =  " + Support.GetItemData(cmbPaymentType, cmbPaymentType.SelectedIndex) + ", " +
                             "       BANK_ID             =  " + Support.GetItemData(cmbBank, cmbBank.SelectedIndex) + ", " +
                             "       REFERENCE_NO        = '" + cmnService.J_ReplaceQuote(txtReference.Text) + "', " +
                             "       ACCOUNT_ENTRY_DATE  =  " + strAccountEntryDate + ", " +
                             "       BANK_STATEMENT_DATE =  " + strBankDate + " " +
                             "WHERE  INVOICE_HEADER_ID = " + lngSearchId;
                    //----------------------------------------------------------
                    if (dmlService.J_ExecSql(dmlService.J_pCommand, strSQL) == false)
                    {
                        cmbPaymentType.Select();
                        return;
                    }

                    if (lngPartyId > 0)
                    {
                        //Updating the Party Category
                        strSQL = "UPDATE MST_PARTY " +
                                 "SET    PARTY_CATEGORY_ID   =  " + Support.GetItemData(cmbPartyCategory, cmbPartyCategory.SelectedIndex) + " " +
                                 "WHERE  PARTY_ID = " + lngPartyId;
                        //----------------------------------------------------------
                        if (dmlService.J_ExecSql(dmlService.J_pCommand, strSQL) == false)
                        {
                            cmbPaymentType.Select();
                            return;
                        }
                    }



                    //-----------------------------------------------------------
                    dmlService.J_Commit();
                    cmnService.J_PanelMessage(0, J_Msg.EditModeSave);
                    //-----------------------------------------------------------
                    ClearControls();

                    //-----------------------------------------------------------
                    strSQL = strQuery + "ORDER BY " + strOrderBy;
                    //-----------------------------------------------------------
                    if (dsetGridClone != null)
                    {
                        dsetGridClone.Clear();
                    }
                    dsetGridClone = dmlService.J_ShowDataInGrid(ref ViewGrid, strSQL, strMatrix);           //Show Data into the Grid
                    if (dsetGridClone == null)
                    {
                        return;
                    }
                    //-----------------------------------------------------------
                    lblMode.Text = J_Mode.View;
                    cmnService.J_StatusButton(this, lblMode.Text);

                    //-----------------------------------------------------------
                    ControlVisible(false);
                    //-----------------------------------------------------------
                    dmlService.J_setGridPosition(ref this.ViewGrid, dsetGridClone, "INVOICE_HEADER_ID", lngSearchId);
                    break;

                case J_Mode.Delete:

                    break;
                }
            }
            catch (Exception err_handler)
            {
                dmlService.J_Rollback();
                cmnService.J_UserMessage(err_handler.Message);
            }
        }
예제 #12
0
        private void Insert_Update_Delete_Data()
        {
            try
            {
                switch (lblMode.Text)
                {
                case J_Mode.Add:

                    // all validation
                    if (ValidateFields() == false)
                    {
                        return;
                    }

                    // Save Confirmation Message
                    if (cmnService.J_SaveConfirmationMessage(ref txtTaxDesc) == true)
                    {
                        return;
                    }

                    // set the transaction as begin
                    dmlService.J_BeginTransaction();

                    // Area Code Logic
                    //mf.MF_GetAreaCode(dmlService.J_pCommand, J_Var.J_pBranchCode)

                    // insert query & execution
                    strSQL = "INSERT INTO MST_TAX (" +
                             "            BRANCH_ID," +
                             "            TAX_DESC," +
                             "            TAX_RATE," +
                             "            USER_ID," +
                             "            CREATE_DATE) " +
                             "    VALUES( " + J_Var.J_pBranchId + "," +
                             "           '" + cmnService.J_ReplaceQuote(txtTaxDesc.Text.Trim().ToUpper()) + "'," +
                             "            " + Convert.ToDouble(cmnService.J_ReplaceQuote(txtTaxPercentage.Text.Trim())) + "," +
                             "            " + J_Var.J_pUserId + "," +
                             "            " + cmnService.J_DateOperator() + dtService.J_ConvertMMddyyyy(dmlService.J_ReturnServerDate()) + cmnService.J_DateOperator() + ")";
                    if (dmlService.J_ExecSql(strSQL) == false)
                    {
                        txtTaxDesc.Select();
                        return;
                    }

                    // get last inserted tax id as per branch & user
                    lngSearchId = dmlService.J_ReturnMaxValue("MST_TAX", "TAX_ID",
                                                              "    BRANCH_ID         = " + J_Var.J_pBranchId + " " +
                                                              "AND USER_ID           = " + J_Var.J_pUserId + "");
                    if (lngSearchId == 0)
                    {
                        return;
                    }

                    // Transaction is commited
                    dmlService.J_Commit();

                    // after insert data, the message is displayed
                    cmnService.J_PanelMessage(J_PanelIndex.e00_DisplayText, J_Msg.AddModeSave);

                    // all controls are cleared
                    ClearControls();
                    txtTaxDesc.Select();

                    break;

                case J_Mode.Edit:

                    // all validation
                    if (ValidateFields() == false)
                    {
                        return;
                    }

                    // Save Confirmation Message
                    if (cmnService.J_SaveConfirmationMessage(ref txtTaxDesc) == true)
                    {
                        return;
                    }

                    // set the transaction as begin
                    dmlService.J_BeginTransaction();

                    // update query & execution
                    strSQL = "UPDATE MST_TAX " +
                             "SET    TAX_DESC     = '" + cmnService.J_ReplaceQuote(txtTaxDesc.Text.Trim().ToUpper()) + "'," +
                             "       TAX_RATE     =  " + Convert.ToDouble(cmnService.J_ReplaceQuote(txtTaxPercentage.Text.Trim())) + " " +
                             "WHERE  TAX_ID       =  " + lngSearchId + "";
                    if (dmlService.J_ExecSql(strSQL) == false)
                    {
                        txtTaxDesc.Select();
                        return;
                    }

                    // Transaction is commited
                    dmlService.J_Commit();

                    // after insert data, the message is displayed
                    cmnService.J_PanelMessage(J_PanelIndex.e00_DisplayText, J_Msg.EditModeSave);

                    // all controls are cleared
                    ClearControls();

                    // Refresh the view grid
                    strSQL = strQuery + "ORDER BY " + strOrderBy;
                    if (dsetGridClone != null)
                    {
                        dsetGridClone.Clear();
                    }
                    dsetGridClone = dmlService.J_ShowDataInGrid(ref ViewGrid, strSQL, strMatrix);           //Show Data into the Grid
                    if (dsetGridClone == null)
                    {
                        return;
                    }

                    // change the buttons status
                    lblMode.Text = J_Mode.View;
                    cmnService.J_StatusButton(this, lblMode.Text);

                    BtnDelete.BackColor = Color.LightGray;
                    BtnDelete.Enabled   = false;

                    ControlVisible(false);

                    dmlService.J_setGridPosition(ref this.ViewGrid, dsetGridClone, "TAX_ID", lngSearchId);
                    break;

                case J_Mode.Delete:
                    break;
                }
            }
            catch (Exception err_handler)
            {
                dmlService.J_Rollback();
                cmnService.J_UserMessage(err_handler.Message);
            }
        }
예제 #13
0
        private void Insert_Update_Delete_Data()
        {
            try
            {
                switch (lblMode.Text)
                {
                case J_Mode.Add:

                    // all validation
                    if (ValidateFields() == false)
                    {
                        return;
                    }

                    // Save Confirmation Message
                    if (cmnService.J_SaveConfirmationMessage(ref txtCompanyName) == true)
                    {
                        return;
                    }

                    // set the transaction as begin
                    dmlService.J_BeginTransaction();

                    // insert query & execution
                    strSQL = "INSERT INTO MST_COMPANY (" +
                             "            BRANCH_ID," +
                             "            COMPANY_NAME," +
                             "            ADDRESS1," +
                             "            ADDRESS2," +
                             "            ADDRESS3," +
                             "            CITY," +
                             "            PIN," +
                             "            CONTACT_NO," +
                             "            FAX," +
                             "            EMAIL_ID," +
                             "            WEB_SITE," +
                             "            CONTACT_PERSON," +
                             "            SIGNATORY," +
                             "            VAT_NO," +
                             "            CST_NO," +
                             "            SERVICE_TAX_NO," +
                             "            PAN," +
                             "            USER_ID," +
                             "            CREATE_DATE, " +
                             "            CIN_NO, " +
                             "            MAX_DAYS_PERMIT, " +
                             "            BANK_DETAIL1, " +     //-- 2016/02/18 ANIK
                             "            BANK_DETAIL2, " +
                             "            BANK_DETAIL3, " +
                             "            BANK_DETAIL4, " +
                             "            BANK_DETAIL5)" +
                             "    VALUES( " + J_Var.J_pBranchId + "," +
                             "           '" + cmnService.J_ReplaceQuote(txtCompanyName.Text.Trim()) + "'," +
                             "           '" + cmnService.J_ReplaceQuote(txtAddress1.Text.Trim()) + "'," +
                             "           '" + cmnService.J_ReplaceQuote(txtAddress2.Text.Trim()) + "'," +
                             "           '" + cmnService.J_ReplaceQuote(txtAddress3.Text.Trim()) + "'," +
                             "           '" + cmnService.J_ReplaceQuote(txtCity.Text.Trim()) + "'," +
                             "           '" + cmnService.J_ReplaceQuote(txtPin.Text.Trim()) + "'," +
                             "           '" + cmnService.J_ReplaceQuote(txtContactNo.Text.Trim()) + "'," +
                             "           '" + cmnService.J_ReplaceQuote(txtFax.Text.Trim()) + "'," +
                             "           '" + cmnService.J_ReplaceQuote(txtEmailID.Text.Trim()) + "'," +
                             "           '" + cmnService.J_ReplaceQuote(txtWebsite.Text.Trim()) + "'," +
                             "           '" + cmnService.J_ReplaceQuote(txtContactPersonName.Text.Trim()) + "'," +
                             "           '" + cmnService.J_ReplaceQuote(txtSignatory.Text.Trim()) + "'," +
                             "           '" + cmnService.J_ReplaceQuote(txtVATNo.Text.Trim()) + "'," +
                             "           '" + cmnService.J_ReplaceQuote(txtCSTNo.Text.Trim()) + "'," +
                             "           '" + cmnService.J_ReplaceQuote(txtServiceTaxNo.Text.Trim()) + "'," +
                             "           '" + cmnService.J_ReplaceQuote(txtPAN.Text.Trim()) + "'," +
                             "            " + J_Var.J_pUserId + "," +
                             "            " + cmnService.J_DateOperator() + dtService.J_ConvertMMddyyyy(dmlService.J_ReturnServerDate()) + cmnService.J_DateOperator() + ", " +
                             "           '" + cmnService.J_ReplaceQuote(txtCINNo.Text.Trim()) + "'," +
                             "            " + cmnService.J_ReplaceQuote(txtMaxPermitedDays.Text.Trim()) + ", " +
                             "           '" + cmnService.J_ReplaceQuote(txtBankDetails1.Text.Trim()) + "', " +     //-- 2016/02/18 ANIK
                             "           '" + cmnService.J_ReplaceQuote(txtBankDetails2.Text.Trim()) + "', " +
                             "           '" + cmnService.J_ReplaceQuote(txtBankDetails3.Text.Trim()) + "', " +
                             "           '" + cmnService.J_ReplaceQuote(txtBankDetails4.Text.Trim()) + "', " +
                             "           '" + cmnService.J_ReplaceQuote(txtBankDetails5.Text.Trim()) + "')";

                    if (dmlService.J_ExecSql(strSQL) == false)
                    {
                        txtCompanyName.Select();
                        return;
                    }

                    // get last inserted area id as per branch & user
                    lngSearchId = dmlService.J_ReturnMaxValue("MST_COMPANY", "COMPANY_ID",
                                                              "    BRANCH_ID         = " + J_Var.J_pBranchId + " " +
                                                              "AND USER_ID           = " + J_Var.J_pUserId + "");
                    if (lngSearchId == 0)
                    {
                        return;
                    }

                    // Transaction is commited
                    dmlService.J_Commit();

                    // after insert data, the message is displayed
                    cmnService.J_PanelMessage(J_PanelIndex.e00_DisplayText, J_Msg.AddModeSave);

                    // all controls are cleared
                    ClearControls();
                    txtCompanyName.Select();

                    break;

                case J_Mode.Edit:

                    // all validation
                    if (ValidateFields() == false)
                    {
                        return;
                    }

                    // Save Confirmation Message
                    if (cmnService.J_SaveConfirmationMessage(ref txtCompanyName) == true)
                    {
                        return;
                    }

                    // set the transaction as begin
                    dmlService.J_BeginTransaction();

                    // update query & execution
                    strSQL = "UPDATE MST_COMPANY " +
                             "SET    COMPANY_NAME   = '" + cmnService.J_ReplaceQuote(txtCompanyName.Text.Trim()) + "'," +
                             "       ADDRESS1       = '" + cmnService.J_ReplaceQuote(txtAddress1.Text.Trim()) + "'," +
                             "       ADDRESS2       = '" + cmnService.J_ReplaceQuote(txtAddress2.Text.Trim()) + "'," +
                             "       ADDRESS3       = '" + cmnService.J_ReplaceQuote(txtAddress3.Text.Trim()) + "'," +
                             "       CITY           = '" + cmnService.J_ReplaceQuote(txtCity.Text.Trim()) + "'," +
                             "       PIN            = '" + cmnService.J_ReplaceQuote(txtPin.Text.Trim()) + "'," +
                             "       CONTACT_NO     = '" + cmnService.J_ReplaceQuote(txtContactNo.Text.Trim()) + "'," +
                             "       FAX            = '" + cmnService.J_ReplaceQuote(txtFax.Text.Trim()) + "'," +
                             "       EMAIL_ID       = '" + cmnService.J_ReplaceQuote(txtEmailID.Text.Trim()) + "'," +
                             "       WEB_SITE       = '" + cmnService.J_ReplaceQuote(txtWebsite.Text.Trim()) + "'," +
                             "       CONTACT_PERSON = '" + cmnService.J_ReplaceQuote(txtContactPersonName.Text.Trim()) + "'," +
                             "       SIGNATORY      = '" + cmnService.J_ReplaceQuote(txtSignatory.Text.Trim()) + "'," +
                             "       VAT_NO         = '" + cmnService.J_ReplaceQuote(txtVATNo.Text.Trim()) + "'," +
                             "       CST_NO         = '" + cmnService.J_ReplaceQuote(txtCSTNo.Text.Trim()) + "'," +
                             "       SERVICE_TAX_NO = '" + cmnService.J_ReplaceQuote(txtServiceTaxNo.Text.Trim()) + "'," +
                             "       PAN            = '" + cmnService.J_ReplaceQuote(txtPAN.Text.Trim()) + "', " +
                             "       CIN_NO         = '" + cmnService.J_ReplaceQuote(txtCINNo.Text.Trim()) + "', " +
                             "       MAX_DAYS_PERMIT = " + cmnService.J_ReplaceQuote(txtMaxPermitedDays.Text.Trim()) + ", " +
                             "       BANK_DETAIL1    = '" + cmnService.J_ReplaceQuote(txtBankDetails1.Text.Trim()) + "', " +     //-- 2016/02/18 ANIK
                             "       BANK_DETAIL2    = '" + cmnService.J_ReplaceQuote(txtBankDetails2.Text.Trim()) + "', " +
                             "       BANK_DETAIL3    = '" + cmnService.J_ReplaceQuote(txtBankDetails3.Text.Trim()) + "', " +
                             "       BANK_DETAIL4    = '" + cmnService.J_ReplaceQuote(txtBankDetails4.Text.Trim()) + "', " +
                             "       BANK_DETAIL5    = '" + cmnService.J_ReplaceQuote(txtBankDetails5.Text.Trim()) + "' " +
                             "WHERE  COMPANY_ID     =  " + lngSearchId + "";
                    if (dmlService.J_ExecSql(strSQL) == false)
                    {
                        txtCompanyName.Select();
                        return;
                    }

                    // Transaction is commited
                    dmlService.J_Commit();

                    // after insert data, the message is displayed
                    cmnService.J_PanelMessage(J_PanelIndex.e00_DisplayText, J_Msg.EditModeSave);

                    // all controls are cleared
                    ClearControls();

                    // Refresh the view grid
                    strSQL = strQuery + "ORDER BY " + strOrderBy;
                    if (dsetGridClone != null)
                    {
                        dsetGridClone.Clear();
                    }
                    dsetGridClone = dmlService.J_ShowDataInGrid(ref ViewGrid, strSQL, strMatrix);           //Show Data into the Grid
                    if (dsetGridClone == null)
                    {
                        return;
                    }

                    // change the buttons status
                    lblMode.Text = J_Mode.View;
                    cmnService.J_StatusButton(this, lblMode.Text);

                    BtnDelete.BackColor = Color.LightGray;
                    BtnDelete.Enabled   = false;

                    ControlVisible(false);

                    dmlService.J_setGridPosition(ref this.ViewGrid, dsetGridClone, "COMPANY_ID", lngSearchId);
                    break;

                case J_Mode.Delete:
                    break;
                }
            }
            catch (Exception err_handler)
            {
                dmlService.J_Rollback();
                cmnService.J_UserMessage(err_handler.Message);
            }
        }
        private void Insert_Update_Delete_Data()
        {
            try
            {
                switch (lblMode.Text)
                {
                case J_Mode.Add:

                    if (ValidateFields() == false)
                    {
                        return;
                    }
                    //-----------------------------------------------------------
                    if (cmnService.J_SaveConfirmationMessage(ref mskInvoiceDate) == true)
                    {
                        return;
                    }
                    //-----------------------------------------------------------

                    //Generating inwords for the amounts
                    string strAccountEntryDate = "null";
                    string strBankDate         = "null";

                    if (!dtService.J_IsBlankDateCheck(ref mskAccountEntryDate, J_ShowMessage.NO))
                    {
                        strAccountEntryDate = cmnService.J_DateOperator() + dtService.J_ConvertMMddyyyy(mskAccountEntryDate.Text) + cmnService.J_DateOperator();
                    }

                    if (!dtService.J_IsBlankDateCheck(ref mskBankDate, J_ShowMessage.NO))
                    {
                        strBankDate = cmnService.J_DateOperator() + dtService.J_ConvertMMddyyyy(mskBankDate.Text) + cmnService.J_DateOperator();
                    }


                    //-----------------------------------------------------------
                    dmlService.J_BeginTransaction();
                    //-----------------------------------------------------------

                    strSQL = @"INSERT INTO TRN_INVOICE_HEADER
                                             (BRANCH_ID,
                                              FAYEAR_ID,
                                              COMPANY_ID,
                                              INVOICE_DATE,
                                              TRAN_TYPE,
                                              NET_AMOUNT,
                                              BANK_STATEMENT_DATE,
                                              ACCOUNT_ENTRY_DATE,
                                              BANK_ID,
                                              PAYMENT_TYPE_ID,
                                              REFERENCE_NO,
                                              REMARKS,
                                              USER_ID) " +
                             "    VALUES " +
                             "           (" + J_Var.J_pBranchId + "," +
                             "            " + J_Var.J_pFAYearId + "," +
                             "            " + Support.GetItemData(cmbCompany, cmbCompany.SelectedIndex) + ", " +
                             "            " + cmnService.J_DateOperator() + dtService.J_ConvertMMddyyyy(mskInvoiceDate.Text) + cmnService.J_DateOperator() + ", " +
                             "            'UN', " +
                             "            " + Convert.ToDouble(txtAmount.Text) + ", " +
                             "            " + strBankDate + ", " +
                             "            " + strAccountEntryDate + ", " +
                             "            " + Support.GetItemData(cmbBank, cmbBank.SelectedIndex) + ", " +
                             "            " + Support.GetItemData(cmbPaymentType, cmbPaymentType.SelectedIndex) + ", " +
                             "           '" + cmnService.J_ReplaceQuote(txtReference.Text) + "', " +
                             "           '" + cmnService.J_ReplaceQuote(txtRemarks.Text) + "', " +
                             "            " + Convert.ToInt32(J_Var.J_pUserId) + " )";
                    //-----------------------------------------------------------
                    if (dmlService.J_ExecSql(strSQL) == false)
                    {
                        mskInvoiceDate.Select();
                        return;
                    }

                    //-----------------------------------------------------------

                    dmlService.J_Commit();
                    cmnService.J_PanelMessage(J_PanelIndex.e00_DisplayText, J_Msg.AddModeSave);
                    //-----------------------------------------------------------
                    ClearControls();
                    mskInvoiceDate.Text = dmlService.J_ReturnServerDate();

                    //-----------------------------------------------------------
                    cmnService.J_UserMessage("Record Saved");
                    //-----------------------------------------------------------

                    mskInvoiceDate.Select();

                    break;

                case J_Mode.Edit:
                    //*****  For Modify
                    //-----------------------------------------------------------
                    if (ValidateFields() == false)
                    {
                        return;
                    }

                    //--Assigning Cancellation Status to a variable

                    //-----------------------------------------------------------
                    dmlService.J_BeginTransaction();
                    //-----------------------------------------------------------

                    strAccountEntryDate = "null";
                    strBankDate         = "null";

                    if (!dtService.J_IsBlankDateCheck(ref mskAccountEntryDate, J_ShowMessage.NO))
                    {
                        strAccountEntryDate = cmnService.J_DateOperator() + dtService.J_ConvertMMddyyyy(mskAccountEntryDate.Text) + cmnService.J_DateOperator();
                    }

                    if (!dtService.J_IsBlankDateCheck(ref mskBankDate, J_ShowMessage.NO))
                    {
                        strBankDate = cmnService.J_DateOperator() + dtService.J_ConvertMMddyyyy(mskBankDate.Text) + cmnService.J_DateOperator();
                    }

                    int intCancellationFlag = 0;

                    if (chkCancelled.Checked == true)
                    {
                        intCancellationFlag = 1;
                    }

                    //UPDATING HEADER RECORD

                    strSQL = "UPDATE TRN_INVOICE_HEADER " +
                             "SET    PAYMENT_TYPE_ID     =  " + Support.GetItemData(cmbPaymentType, cmbPaymentType.SelectedIndex) + ", " +
                             "       BANK_ID             =  " + Support.GetItemData(cmbBank, cmbBank.SelectedIndex) + ", " +
                             "       REFERENCE_NO        = '" + cmnService.J_ReplaceQuote(txtReference.Text) + "', " +
                             "       ACCOUNT_ENTRY_DATE  =  " + strAccountEntryDate + ", " +
                             "       BANK_STATEMENT_DATE =  " + strBankDate + ", " +
                             "       INVOICE_DATE        =  " + cmnService.J_DateOperator() + dtService.J_ConvertMMddyyyy(mskInvoiceDate.Text) + cmnService.J_DateOperator() + ", " +
                             "       NET_AMOUNT          =  " + Convert.ToDouble(txtAmount.Text) + ", " +
                             "       REMARKS             = '" + cmnService.J_ReplaceQuote(txtRemarks.Text) + "', " +
                             "       CANCELLATION_FLAG   =  " + intCancellationFlag + " " +
                             "WHERE  INVOICE_HEADER_ID = " + lngSearchId;
                    //----------------------------------------------------------
                    if (dmlService.J_ExecSql(dmlService.J_pCommand, strSQL) == false)
                    {
                        cmbPaymentType.Select();
                        return;
                    }

                    //-----------------------------------------------------------
                    dmlService.J_Commit();
                    cmnService.J_PanelMessage(0, J_Msg.EditModeSave);
                    //-----------------------------------------------------------
                    ClearControls();

                    //-----------------------------------------------------------
                    strSQL = strQuery + "ORDER BY " + strOrderBy;
                    //-----------------------------------------------------------
                    if (dsetGridClone != null)
                    {
                        dsetGridClone.Clear();
                    }
                    dsetGridClone = dmlService.J_ShowDataInGrid(ref ViewGrid, strSQL, strMatrix);           //Show Data into the Grid
                    if (dsetGridClone == null)
                    {
                        return;
                    }
                    //-----------------------------------------------------------
                    lblMode.Text = J_Mode.View;
                    cmnService.J_StatusButton(this, lblMode.Text);

                    cmbCompany.Enabled = true;

                    //-----------------------------------------------------------
                    ControlVisible(false);
                    //-----------------------------------------------------------
                    dmlService.J_setGridPosition(ref this.ViewGrid, dsetGridClone, "INVOICE_HEADER_ID", lngSearchId);
                    break;

                case J_Mode.Delete:

                    if (cmnService.J_UserMessage("Are you sure you want to delete the selected Payment entry?", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button2) == DialogResult.No)
                    {
                        return;
                    }

                    dmlService.J_BeginTransaction();

                    strSQL = "DELETE FROM TRN_INVOICE_HEADER WHERE INVOICE_HEADER_ID = " + lngSearchId;

                    dmlService.J_ExecSql(strSQL);


                    dmlService.J_Commit();
                    cmnService.J_PanelMessage(0, J_Msg.DeleteMode);
                    //-----------------------------------------------------------
                    strSQL = strQuery + "ORDER BY " + strOrderBy;
                    //-----------------------------------------------------------
                    if (dsetGridClone != null)
                    {
                        dsetGridClone.Clear();
                    }
                    dsetGridClone = dmlService.J_ShowDataInGrid(ref ViewGrid, strSQL, strMatrix);           //Show Data into the Grid
                    if (dsetGridClone == null)
                    {
                        return;
                    }
                    //-----------------------------------------------------------
                    lblMode.Text = J_Mode.View;
                    cmnService.J_StatusButton(this, lblMode.Text);
                    //-----------------------------------------------------------
                    dmlService.J_setGridPosition(ref this.ViewGrid, dsetGridClone, "INVOICE_HEADER_ID", lngSearchId);


                    break;
                }
            }
            catch (Exception err_handler)
            {
                dmlService.J_Rollback();
                cmnService.J_UserMessage(err_handler.Message);
            }
        }
예제 #15
0
        private void Insert_Update_Delete_Data()
        {
            try
            {
                string strLoginId  = string.Empty;
                int    intCategory = 0;

                switch (lblMode.Text)
                {
                case J_Mode.Add:

                    // all validations
                    if (ValidateFields() == false)
                    {
                        return;
                    }

                    // Save Confirmation Message
                    if (cmnService.J_SaveConfirmationMessage(ref txtUserName) == true)
                    {
                        return;
                    }

                    intCategory = Convert.ToInt32(chkActiveInactive.Checked == true ? "0" : "1");

                    // Begin Transaction
                    dmlService.J_BeginTransaction();

                    strSQL = "INSERT INTO MST_USER (" +
                             "            BRANCH_ID," +
                             "            USER_CODE," +
                             "            DISPLAY_NAME," +
                             "            LOGIN_ID," +
                             "            USER_PASSWORD," +
                             "            USER_CATEGORY) " +
                             "    VALUES( " + J_Var.J_pBranchId + "," +
                             "           ''," +
                             "           '" + cmnService.J_ReplaceQuote(txtUserName.Text.Trim()) + "'," +
                             "           '" + cmnService.J_ReplaceQuote(txtLoginId.Text.Trim()) + "'," +
                             "           '" + cmnService.J_ReplaceQuote(txtPassword.Text.Trim()) + "'," +
                             "            " + intCategory + ")";

                    if (dmlService.J_ExecSql(strSQL) == false)
                    {
                        txtUserName.Select();
                        return;
                    }

                    lngSearchId = dmlService.J_ReturnMaxValue("MST_USER", "USER_ID",
                                                              "    BRANCH_ID = " + J_Var.J_pBranchId + " " +
                                                              "AND " + cmnService.J_SQLDBFormat("LOGIN_ID", J_SQLColFormat.UCase) + " = '" + cmnService.J_ReplaceQuote(txtLoginId.Text.Trim().ToUpper()) + "'");
                    if (lngSearchId == 0)
                    {
                        return;
                    }

                    dmlService.J_Commit();
                    cmnService.J_PanelMessage(J_PanelIndex.e00_DisplayText, J_Msg.AddModeSave);

                    ClearControls();
                    txtUserName.Select();

                    break;

                case J_Mode.Edit:
                    // For Modify
                    if (ValidateFields() == false)
                    {
                        return;
                    }
                    if (cmnService.J_SaveConfirmationMessage(ref txtUserName) == true)
                    {
                        return;
                    }

                    intCategory = Convert.ToInt32(chkActiveInactive.Checked == true ? "0" : "1");

                    // Begin Transaction
                    dmlService.J_BeginTransaction();

                    strSQL = "UPDATE MST_USER " +
                             "SET    DISPLAY_NAME  = '" + cmnService.J_ReplaceQuote(txtUserName.Text.Trim()) + "'," +
                             "       USER_CATEGORY =  " + intCategory + " ";

                    // If reset password is checked then update password too

                    if (chkResetPassword.Checked == true)
                    {
                        strSQL = strSQL + ", USER_PASSWORD = '******' ";
                    }
                    strSQL = strSQL + "WHERE USER_ID = " + lngSearchId + "";

                    if (dmlService.J_ExecSql(strSQL) == false)
                    {
                        txtUserName.Select();
                        return;
                    }

                    dmlService.J_Commit();
                    cmnService.J_PanelMessage(J_PanelIndex.e00_DisplayText, J_Msg.EditModeSave);

                    ClearControls();

                    strSQL = strQuery + "ORDER BY " + strOrderBy;
                    if (dsetGridClone != null)
                    {
                        dsetGridClone.Clear();
                    }
                    dsetGridClone = dmlService.J_ShowDataInGrid(ref ViewGrid, strSQL, strMatrix);           //Show Data into the Grid
                    if (dsetGridClone == null)
                    {
                        return;
                    }

                    lblMode.Text = J_Mode.View;
                    cmnService.J_StatusButton(this, lblMode.Text);

                    BtnDelete.BackColor = Color.LightGray;
                    BtnDelete.Enabled   = false;

                    ControlVisible(false);

                    dmlService.J_setGridPosition(ref ViewGrid, dsetGridClone, "USER_ID", lngSearchId);
                    break;

                case J_Mode.Delete:
                    break;
                }
            }
            catch (Exception err_handler)
            {
                dmlService.J_Rollback();
                cmnService.J_UserMessage(err_handler.Message);
            }
        }
예제 #16
0
        private void Insert_Update_Delete_Data()
        {
            try
            {
                int    intSaveConfirmationMessage  = 0;
                string strLast_Reconciliation_Date = "";

                // Save Confirmation Message
                if (chkSaveConfirmationMessage.Checked == true)
                {
                    intSaveConfirmationMessage = 1;
                }
                else if (chkSaveConfirmationMessage.Checked == false)
                {
                    intSaveConfirmationMessage = 0;
                }


                if (dtService.J_IsBlankDateCheck(ref mskLastReconcileDate, J_ShowMessage.NO) == false)
                {
                    strLast_Reconciliation_Date = cmnService.J_DateOperator() + dtService.J_ConvertMMddyyyy(mskLastReconcileDate) + cmnService.J_DateOperator();
                }
                else
                {
                    strLast_Reconciliation_Date = "null";
                }

                switch (lblMode.Text)
                {
                case J_Mode.Add:

                    if (ValidateFields() == false)
                    {
                        return;
                    }
                    // save confirmation message
                    if (cmnService.J_SaveConfirmationMessage(ref txtAddress) == true)
                    {
                        return;
                    }



                    // begin transaction
                    dmlService.J_BeginTransaction();

                    strSQL = "UPDATE MST_SETUP " +
                             "     SET    ADDRESS                      = '" + cmnService.J_ReplaceQuote(txtAddress.Text.Trim()) + "'," +
                             "            CITY                         = '" + cmnService.J_ReplaceQuote(txtCity.Text.Trim()) + "'," +
                             "            PIN                          = '" + cmnService.J_ReplaceQuote(txtPin.Text.Trim()) + "'," +
                             "            CONTACT_NO                   = '" + cmnService.J_ReplaceQuote(txtContactNo.Text.Trim()) + "'," +
                             "            EMAIL_ID                     = '" + cmnService.J_ReplaceQuote(txtEmailId.Text.Trim()) + "'," +
                             "            WEB_SITE                     = '" + cmnService.J_ReplaceQuote(txtWebsite.Text.Trim()) + "'," +
                             "            OPENING_SUNDRY_OUTSTANDING   =  " + cmnService.J_ReplaceQuote(txtSundryOpening.Text.Trim()) + "," +
                             "            SUNDRY_CUTOFF_DATE           =  " + cmnService.J_DateOperator() + dtService.J_ConvertMMddyyyy(mskSundryCutoffDate) + cmnService.J_DateOperator() + "," +
                             "            SAVE_CONFIRMATION_MSG        =  " + intSaveConfirmationMessage + "," +
                             "            NETWORK_CREDENTIAL_USERNAME  = '******'," +
                             "            NETWORK_CREDENTIAL_PASSWORD  = '******'," +
                             "            NETWORK_CREDENTIAL_PORT      = '" + cmnService.J_ReplaceQuote(txtNetPort.Text.Trim()) + "'," +
                             "            NETWORK_CREDENTIAL_HOST      = '" + cmnService.J_ReplaceQuote(txtNetHost.Text.Trim()) + "', " +
                             "            SMS_WORKING_KEY              = '" + cmnService.J_ReplaceQuote(txtSmsWorkingKey.Text.Trim()) + "', " +
                             "            SMS_OFFLINE_INVOICE_MESSAGE  = '" + cmnService.J_ReplaceQuote(txtOfflineSmsMessage.Text.Trim()) + "', " +
                             "            SMS_ONLINE_INVOICE_MESSAGE   = '" + cmnService.J_ReplaceQuote(txtOnlineSmsMessage.Text.Trim()) + "', " +
                             "            SMS_SENDER_NAME              = '" + cmnService.J_ReplaceQuote(txtSmsSenderName.Text.Trim()) + "', " +
                             "            LAST_RECONCILIATION_DATE     =  " + strLast_Reconciliation_Date + "" +
                             "     WHERE  BRANCH_ID                    =  " + J_Var.J_pBranchId + " ";
                    if (dmlService.J_ExecSql(strSQL) == false)
                    {
                        return;
                    }

                    J_Var.J_pBranchAddress  = txtAddress.Text.Trim();
                    J_Var.J_pSaveConfirmMsg = intSaveConfirmationMessage;

                    // Transaction is commited
                    dmlService.J_Commit();
                    cmnService.J_PanelMessage(0, "Setup Updated");

                    this.dmlService.Dispose();
                    this.Close();
                    this.Dispose();

                    break;

                case J_Mode.Edit:
                    break;

                case J_Mode.Delete:
                    break;
                }
            }
            catch (Exception err_handler)
            {
                dmlService.J_Rollback();
                cmnService.J_UserMessage(err_handler.Message);
            }
        }
예제 #17
0
        private void BtnSystemMaintainence_Click(object sender, EventArgs e)
        {
            try
            {
                if (cmnService.J_UserMessage("Do you want to Maintainence your Database??",
                                             J_Var.J_pProjectName,
                                             MessageBoxButtons.YesNo,
                                             MessageBoxIcon.Question,
                                             MessageBoxDefaultButton.Button1) == DialogResult.No)
                {
                    BtnCancel.Select();
                    return;
                }

                cmnService.J_BusyMode();
                dmlService.J_ClearDatabaseLog();

                // Added by Ripan Paul on 15-06-2011
                //if (dmlService.J_IsDatabaseObjectExist("TRN_INVOICE_HEADER", "DISCOUNT_TEXT") == false)
                //{
                //    strSQL = "ALTER TABLE TRN_INVOICE_HEADER ADD " + cmnService.J_GetDataType("DISCOUNT_TEXT", J_ColumnType.String, 200) + "";
                //    dmlService.J_ExecSql(strSQL);

                //    strSQL = "UPDATE TRN_INVOICE_HEADER " +
                //             "SET    DISCOUNT_TEXT = ''";
                //    dmlService.J_ExecSql(strSQL);
                //}


                //-- VIEW [VW_INVOICE_HEADER] -- ADDED BY DHRUB ON 29/04/2015
                #region VW_INVOICE_HEADER
                if (Convert.ToInt32(dmlService.J_ExecSqlReturnScalar("SELECT COUNT(*) FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME LIKE 'VW_INVOICE_HEADER'")) > 0)
                {
                    strSQL = @" DROP VIEW VW_INVOICE_HEADER ";

                    //---------------------------------------------
                    dmlService.J_ExecSql(strSQL);
                    //---------------------------------------------
                }

                strSQL = @" CREATE VIEW DBO.VW_INVOICE_HEADER
                            AS
                            (
                            SELECT TRN_INVOICE_HEADER.INVOICE_HEADER_ID,
                                   TRN_INVOICE_HEADER.FAYEAR_ID,
                                   TRN_INVOICE_HEADER.COMPANY_ID,
                                   TRN_INVOICE_HEADER.TRAN_TYPE,
                                   MST_COMPANY.COMPANY_NAME,
                                   TRN_INVOICE_HEADER.PARTY_ID,
                                   MST_PARTY.PARTY_NAME,
                                   MST_PARTY_CATEGORY.PARTY_CATEGORY_ID,
                                   MST_PARTY_CATEGORY.PARTY_CATEGORY_DESCRIPTION,
                                   TRN_INVOICE_HEADER.INVOICE_SERIES_ID,
                                   TRN_INVOICE_HEADER.INVOICE_NO                         AS INVOICE_NO,
                                   TRN_INVOICE_HEADER.INVOICE_DATE                       AS INVOICE_DATE,
                                   CONVERT(CHAR(10),TRN_INVOICE_HEADER.INVOICE_DATE,103) AS INVOICE_DATE_DDMMYYYY,
                                   CONVERT(CHAR(8),TRN_INVOICE_HEADER.INVOICE_DATE,112)  AS INVOICE_DATE_YYYYMMDD,
                                   TRN_INVOICE_HEADER.NET_AMOUNT                         AS NET_AMOUNT,
                                   TRN_INVOICE_HEADER.PAYMENT_TYPE_ID                    AS PAYMENT_TYPE_ID,
                                   MST_PAYMENT_TYPE.PAYMENT_TYPE_DESCRIPTION             AS PAYMENT_TYPE_DESCRIPTION,
                                   TRN_INVOICE_HEADER.BANK_ID                            AS BANK_ID,
                                   ISNULL(MST_BANK.BANK_NAME,'')                         AS BANK_NAME,
                                   TRN_INVOICE_HEADER.REFERENCE_NO                       AS REFERENCE_NO,
                                   TRN_INVOICE_HEADER.DELIVERY_MODE_ID                   AS DELIVERY_MODE_ID,
                                   PAR_DELIVERY_MODE.DELIVERY_MODE_DESC                  AS DELIVERY_MODE_DESC,
                                   TRN_INVOICE_HEADER.CONTACT_PERSON                     AS CONTACT_PERSON,
                                   TRN_INVOICE_HEADER.MOBILE_NO                          AS MOBILE_NO,
                                   TRN_INVOICE_HEADER.EMAIL_ID                           AS EMAIL_ID,
                                   TRN_INVOICE_HEADER.OFFLINE_SERIAL_ID                  AS OFFLINE_SERIAL_ID,
                                   ISNULL(TRNCOLLECT.COLLECTION_AMOUNT,0)                AS COLLECTION_AMOUNT,
                                   ISNULL(TRNCOLLECT.ADJUSTED_AMOUNT,0)                  AS ADJUSTED_AMOUNT,
                                   TRN_INVOICE_HEADER.NET_AMOUNT - ISNULL(TRNCOLLECT.COLLECTION_AMOUNT,0) - ISNULL(TRNCOLLECT.ADJUSTED_AMOUNT,0) AS PENDING_AMOUNT,
                                   ------- ADDED BY DHRUB ON 03-06-2015
                                   ---------------------------------------------------------------------------------------------------------------------------------------
                                   TRN_INVOICE_HEADER.AMOUNT_WITH_DISCOUNT               AS TAXABLE_AMOUNT,
                                   TRN_INVOICE_HEADER.TAX_TOTAL_AMOUNT                   AS TAX_TOTAL_AMOUNT,
                                   ISNULL(MST_TAX.TAX_TYPE,'')                           AS TAX_TYPE,
                                   ---------------------------------------------------------------------------------------------------------------------------------------
                                   TRN_INVOICE_HEADER.ADDITIONAL_COST                    AS ADDITIONAL_COST,
                                   TRN_INVOICE_HEADER.ROUNDED_OFF                        AS ROUNDED_OFF
                                   ---------------------------------------------------------------------------------------------------------------------------------------
                            FROM   TRN_INVOICE_HEADER
                                   INNER JOIN MST_PARTY          
                                   ON TRN_INVOICE_HEADER.PARTY_ID         = MST_PARTY.PARTY_ID
                                   INNER JOIN MST_COMPANY        
                                   ON TRN_INVOICE_HEADER.COMPANY_ID       = MST_COMPANY.COMPANY_ID
                                   INNER JOIN PAR_DELIVERY_MODE  
                                   ON TRN_INVOICE_HEADER.DELIVERY_MODE_ID = PAR_DELIVERY_MODE.DELIVERY_MODE_ID
                                   INNER JOIN MST_PAYMENT_TYPE   
                                   ON TRN_INVOICE_HEADER.PAYMENT_TYPE_ID  = MST_PAYMENT_TYPE.PAYMENT_TYPE_ID
                                   INNER JOIN MST_PARTY_CATEGORY 
                                   ON MST_PARTY.PARTY_CATEGORY_ID         = MST_PARTY_CATEGORY.PARTY_CATEGORY_ID
                                   LEFT  JOIN MST_BANK           
                                   ON TRN_INVOICE_HEADER.BANK_ID          = MST_BANK.BANK_ID
                                   ------- ADDED BY DHRUB  ON 03-06-2015
                                   -------------------------------------------------------------------------------
                                   LEFT JOIN TRN_INVOICE_TAX
                                   ON TRN_INVOICE_HEADER.INVOICE_HEADER_ID = TRN_INVOICE_TAX.INVOICE_HEADER_ID
                                   LEFT  JOIN MST_TAX 
                                   ON  TRN_INVOICE_TAX.TAX_ID = MST_TAX.TAX_ID
                                   -------------------------------------------------------------------------------
                                   LEFT  JOIN ( SELECT COLLADJ.INVOICE_HEADER_ID,
                                                       SUM(COLLADJ.COLLECTION_AMOUNT) AS COLLECTION_AMOUNT,
                                                       SUM(COLLADJ.ADJUSTED_AMOUNT)   AS ADJUSTED_AMOUNT
                                                FROM   (
                                                       ------------- INVOICE WISE TOTAL COLLECTION
                                                       SELECT TRN_COLLECTION_DETAIL.INVOICE_HEADER_ID,
                                                              SUM(TRN_COLLECTION_DETAIL.COLLECTION_AMOUNT) AS COLLECTION_AMOUNT,
                                                              0                                            AS ADJUSTED_AMOUNT
                                                       FROM   TRN_COLLECTION_DETAIL,
                                                              TRN_COLLECTION_HEADER
                                                       WHERE  TRN_COLLECTION_DETAIL.COLLECTION_HEADER_ID = TRN_COLLECTION_HEADER.COLLECTION_HEADER_ID            
                                                       AND    TRN_COLLECTION_HEADER.ADJUSTMENT_FLAG = 0
                                                       GROUP BY TRN_COLLECTION_DETAIL.INVOICE_HEADER_ID
                                                       UNION
                                                       ------------- INVOICE WISE TOTAL ADJUSTMENT
                                                       SELECT TRN_COLLECTION_DETAIL.INVOICE_HEADER_ID,
                                                              0                                            AS COLLECTION_AMOUNT,
                                                              SUM(TRN_COLLECTION_DETAIL.COLLECTION_AMOUNT) AS ADJUSTED_AMOUNT
                                                       FROM   TRN_COLLECTION_DETAIL,
                                                              TRN_COLLECTION_HEADER
                                                       WHERE  TRN_COLLECTION_DETAIL.COLLECTION_HEADER_ID = TRN_COLLECTION_HEADER.COLLECTION_HEADER_ID            
                                                       AND    TRN_COLLECTION_HEADER.ADJUSTMENT_FLAG = 1
                                                       GROUP BY TRN_COLLECTION_DETAIL.INVOICE_HEADER_ID
                                                      ) AS COLLADJ
	                                            GROUP BY COLLADJ.INVOICE_HEADER_ID
	                                          ) AS TRNCOLLECT 
                                      ON TRN_INVOICE_HEADER.INVOICE_HEADER_ID = TRNCOLLECT.INVOICE_HEADER_ID
                            WHERE    TRN_INVOICE_HEADER.RECON_FLAG = 0
                            AND     (TRN_INVOICE_HEADER.TRAN_TYPE = 'INV' OR TRN_INVOICE_HEADER.TRAN_TYPE = 'OINV')
                        )";
                //---------------------------------------------
                dmlService.J_ExecSql(strSQL);
                //---------------------------------------------

                #endregion

                //-- VIEW [VW_COLLECTION_HEADER] -- ADDED BY DHRUB ON 29/04/2015
                #region VW_COLLECTION_HEADER
                if (Convert.ToInt32(dmlService.J_ExecSqlReturnScalar("SELECT COUNT(*) FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME LIKE 'VW_COLLECTION_HEADER'")) > 0)
                {
                    strSQL = @" DROP VIEW VW_COLLECTION_HEADER ";

                    //---------------------------------------------
                    dmlService.J_ExecSql(strSQL);
                    //---------------------------------------------
                }
                strSQL = @" CREATE VIEW DBO.VW_COLLECTION_HEADER AS 
                            (
                               SELECT  TRN_COLLECTION_HEADER.COLLECTION_HEADER_ID,  
                                       TRN_COLLECTION_HEADER.COLLECTION_DATE,  
                                       CONVERT(CHAR(10),TRN_COLLECTION_HEADER.COLLECTION_DATE,103) AS COLLECTION_DATE_DDMMYYYY,  
                                       CONVERT(CHAR(8),TRN_COLLECTION_HEADER.COLLECTION_DATE,112) AS COLLECTION_DATE_YYYYMMDD,
                                       TRN_COLLECTION_HEADER.COMPANY_ID,  
                                       MST_COMPANY.COMPANY_NAME,  
                                       TRN_COLLECTION_HEADER.PAYMENT_TYPE_ID,  
                                       ISNULL(MST_PAYMENT_TYPE.PAYMENT_TYPE_DESCRIPTION,'') AS PAYMENT_TYPE_DESCRIPTION,  
                                       TRN_COLLECTION_HEADER.BANK_ID,  
                                       ISNULL(MST_BANK.BANK_NAME,'') AS BANK_NAME,  
                                       TRN_COLLECTION_HEADER.REFERENCE_NO,  
                                       TRN_COLLECTION_HEADER.GROSS_AMT,  
                                       TRN_COLLECTION_HEADER.LESS_AMT,  
                                       TRN_COLLECTION_HEADER.NET_AMT,  
                                       TRN_COLLECTION_HEADER.COLLECTION_REMARKS,  
                                       TRN_COLLECTION_HEADER.NET_INVOICE_AMT,  
                                       TRN_COLLECTION_HEADER.DUE_AMT,  
                                       TRN_COLLECTION_HEADER.RECONCILIATION_DATE,  
                                       ISNULL(CONVERT(CHAR(10),TRN_COLLECTION_HEADER.RECONCILIATION_DATE,103),'') AS RECONCILIATION_DATE_DDMMYYYY,  
                                       ISNULL(CONVERT(CHAR(8),TRN_COLLECTION_HEADER.RECONCILIATION_DATE,112),'') AS RECONCILIATION_DATE_YYYYMMDD,  
                                       TRN_COLLECTION_HEADER.AUTO_POST_FLAG,  
                                       CASE WHEN TRN_COLLECTION_HEADER.AUTO_POST_FLAG = 0 THEN 'M' ELSE '' END AS POST_TYPE,  
                                       TRN_COLLECTION_HEADER.ADJUSTMENT_FLAG,
                                       TRN_COLLECTION_HEADER.TALLIED  
                                FROM   TRN_COLLECTION_HEADER         
                                INNER JOIN MST_COMPANY      
                                ON TRN_COLLECTION_HEADER.COMPANY_ID      = MST_COMPANY.COMPANY_ID  
                                LEFT  JOIN MST_PAYMENT_TYPE 
                                ON TRN_COLLECTION_HEADER.PAYMENT_TYPE_ID = MST_PAYMENT_TYPE.PAYMENT_TYPE_ID  
                                LEFT  JOIN MST_BANK         
                                ON TRN_COLLECTION_HEADER.BANK_ID         = MST_BANK.BANK_ID   
                            )";
                //---------------------------------------------
                dmlService.J_ExecSql(strSQL);
                //---------------------------------------------
                #endregion

                //-- VIEW [VW_COLLECTION_DETAIL] -- ADDED BY DHRUB ON 29/04/2015
                #region VW_COLLECTION_DETAIL
                if (Convert.ToInt32(dmlService.J_ExecSqlReturnScalar("SELECT COUNT(*) FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME LIKE 'VW_COLLECTION_DETAIL'")) > 0)
                {
                    strSQL = @" DROP VIEW VW_COLLECTION_DETAIL ";

                    //---------------------------------------------
                    dmlService.J_ExecSql(strSQL);
                    //---------------------------------------------
                }
                strSQL = @" CREATE VIEW DBO.VW_COLLECTION_DETAIL 
                            AS 
                            (
                               SELECT  TRN_COLLECTION_HEADER.COLLECTION_HEADER_ID,  
                                       TRN_COLLECTION_HEADER.COLLECTION_DATE,  
                                       CONVERT(CHAR(10),TRN_COLLECTION_HEADER.COLLECTION_DATE,103) AS COLLECTION_DATE_DDMMYYYY,  
                                       CONVERT(CHAR(8),TRN_COLLECTION_HEADER.COLLECTION_DATE,112) AS COLLECTION_DATE_YYYYMMDD,  
                                       TRN_COLLECTION_HEADER.COMPANY_ID,  
                                       MST_COMPANY.COMPANY_NAME,  
                                       TRN_COLLECTION_HEADER.PAYMENT_TYPE_ID,  
                                       ISNULL(MST_PAYMENT_TYPE.PAYMENT_TYPE_DESCRIPTION,'') AS PAYMENT_TYPE_DESCRIPTION,  
                                       TRN_COLLECTION_HEADER.BANK_ID,  
                                       ISNULL(MST_BANK.BANK_NAME,'') AS BANK_NAME,  
                                       TRN_COLLECTION_HEADER.REFERENCE_NO,  
                                       TRN_COLLECTION_HEADER.GROSS_AMT,  
                                       TRN_COLLECTION_HEADER.LESS_AMT,  
                                       TRN_COLLECTION_HEADER.NET_AMT,  
                                       TRN_COLLECTION_HEADER.COLLECTION_REMARKS,  
                                       TRN_COLLECTION_HEADER.NET_INVOICE_AMT,  
                                       TRN_COLLECTION_HEADER.DUE_AMT,  
                                       TRN_COLLECTION_HEADER.RECONCILIATION_DATE,  
                                       ISNULL(CONVERT(CHAR(10),TRN_COLLECTION_HEADER.RECONCILIATION_DATE,103),'') AS RECONCILIATION_DATE_DDMMYYYY,  
                                       ISNULL(CONVERT(CHAR(8),TRN_COLLECTION_HEADER.RECONCILIATION_DATE,112),'')  AS RECONCILIATION_DATE_YYYYMMDD,  
                                       TRN_COLLECTION_HEADER.AUTO_POST_FLAG,  
                                       CASE WHEN TRN_COLLECTION_HEADER.AUTO_POST_FLAG = 0 THEN 'M' ELSE '' END AS POST_TYPE,  
                                       TRN_COLLECTION_HEADER.ADJUSTMENT_FLAG,  
                                       ISNULL(TRN_COLLECTION_DETAIL.INVOICE_HEADER_ID,0) AS INVOICE_HEADER_ID,  
                                       ISNULL(TRN_INVOICE_HEADER.INVOICE_NO,'') AS INVOICE_NO,  
                                       ISNULL(TRN_INVOICE_HEADER.REFERENCE_NO,'') AS INVOICE_REFERENCE_NO,  
                                       ISNULL(TRN_INVOICE_HEADER.NET_AMOUNT,0) AS INVOICE_NET_AMOUNT,  
                                       TRN_INVOICE_HEADER.INVOICE_DATE,  
                                       ISNULL(CONVERT(CHAR(10),TRN_INVOICE_HEADER.INVOICE_DATE,103),'') AS INVOICE_DATE_DDMMYYYY,  
                                       ISNULL(CONVERT(CHAR(8),TRN_INVOICE_HEADER.INVOICE_DATE,112),'')  AS INVOICE_DATE_YYYYMMDD,
                                       TRN_COLLECTION_HEADER.TALLIED,  
                                       ISNULL(TRN_INVOICE_HEADER.PARTY_ID,0) AS PARTY_ID,  
                                       ISNULL(MST_PARTY.PARTY_NAME,'') AS PARTY_NAME,  
                                       ISNULL(TRN_COLLECTION_DETAIL.COLLECTION_AMOUNT,0) AS COLLECTION_AMOUNT  
                                FROM   TRN_COLLECTION_HEADER         
                                INNER JOIN MST_COMPANY           
                                ON TRN_COLLECTION_HEADER.COMPANY_ID           = MST_COMPANY.COMPANY_ID  
                                LEFT  JOIN MST_PAYMENT_TYPE      
                                ON TRN_COLLECTION_HEADER.PAYMENT_TYPE_ID      = MST_PAYMENT_TYPE.PAYMENT_TYPE_ID  
                                LEFT  JOIN MST_BANK              
                                ON TRN_COLLECTION_HEADER.BANK_ID              = MST_BANK.BANK_ID  
                                LEFT  JOIN TRN_COLLECTION_DETAIL 
                                ON TRN_COLLECTION_HEADER.COLLECTION_HEADER_ID = TRN_COLLECTION_DETAIL.COLLECTION_HEADER_ID  
                                LEFT  JOIN TRN_INVOICE_HEADER    
                                ON TRN_COLLECTION_DETAIL.INVOICE_HEADER_ID    = TRN_INVOICE_HEADER.INVOICE_HEADER_ID  
                                LEFT  JOIN MST_PARTY             
                                ON TRN_INVOICE_HEADER.PARTY_ID                = MST_PARTY.PARTY_ID   
                            )";
                //---------------------------------------------
                dmlService.J_ExecSql(strSQL);
                //---------------------------------------------

                #endregion

                //-- VIEW [VW_INVOICE_DETAIL] -- ADDED BY DHRUB ON 29/04/2015
                #region VW_INVOICE_DETAIL
                if (Convert.ToInt32(dmlService.J_ExecSqlReturnScalar("SELECT COUNT(*) FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME LIKE 'VW_INVOICE_DETAIL'")) > 0)
                {
                    strSQL = @" DROP VIEW VW_INVOICE_DETAIL ";

                    //---------------------------------------------
                    dmlService.J_ExecSql(strSQL);
                    //---------------------------------------------
                }
                strSQL = @" CREATE VIEW DBO.VW_INVOICE_DETAIL 
                            AS 
                            (
                                SELECT TRN_INVOICE_HEADER.INVOICE_HEADER_ID,
                                       TRN_INVOICE_HEADER.FAYEAR_ID,
                                       TRN_INVOICE_HEADER.COMPANY_ID,
                                       TRN_INVOICE_HEADER.TRAN_TYPE,
                                       MST_COMPANY.COMPANY_NAME,
                                       TRN_INVOICE_HEADER.PARTY_ID,
                                       MST_PARTY.PARTY_NAME,
                                       MST_PARTY_CATEGORY.PARTY_CATEGORY_ID,
                                       MST_PARTY_CATEGORY.PARTY_CATEGORY_DESCRIPTION,
                                       TRN_INVOICE_HEADER.INVOICE_SERIES_ID,
                                       TRN_INVOICE_HEADER.INVOICE_NO                         AS INVOICE_NO,
                                       TRN_INVOICE_HEADER.INVOICE_DATE                       AS INVOICE_DATE,
                                       CONVERT(CHAR(10),TRN_INVOICE_HEADER.INVOICE_DATE,103) AS INVOICE_DATE_DDMMYYYY,
                                       CONVERT(CHAR(8),TRN_INVOICE_HEADER.INVOICE_DATE,112)  AS INVOICE_DATE_YYYYMMDD,
                                       TRN_INVOICE_HEADER.NET_AMOUNT                         AS NET_AMOUNT,
                                       TRN_INVOICE_HEADER.PAYMENT_TYPE_ID                    AS PAYMENT_TYPE_ID,
                                       MST_PAYMENT_TYPE.PAYMENT_TYPE_DESCRIPTION             AS PAYMENT_TYPE_DESCRIPTION,
                                       TRN_INVOICE_HEADER.BANK_ID                            AS BANK_ID,
                                       ISNULL(MST_BANK.BANK_NAME,'')                         AS BANK_NAME,
                                       TRN_INVOICE_HEADER.REFERENCE_NO                       AS REFERENCE_NO,
                                       TRN_INVOICE_HEADER.DELIVERY_MODE_ID                   AS DELIVERY_MODE_ID,
                                       PAR_DELIVERY_MODE.DELIVERY_MODE_DESC                  AS DELIVERY_MODE_DESC,
                                       TRN_INVOICE_HEADER.CONTACT_PERSON                     AS CONTACT_PERSON,
                                       TRN_INVOICE_HEADER.MOBILE_NO                          AS MOBILE_NO,
                                       TRN_INVOICE_HEADER.EMAIL_ID                           AS EMAIL_ID,
                                       TRN_INVOICE_HEADER.OFFLINE_SERIAL_ID                  AS OFFLINE_SERIAL_ID,
                                       ISNULL(TRNCOLLECT.COLLECTION_AMOUNT,0)                AS COLLECTION_AMOUNT,
                                       ISNULL(TRNCOLLECT.ADJUSTED_AMOUNT,0)                  AS ADJUSTED_AMOUNT,
                                       TRN_INVOICE_HEADER.NET_AMOUNT - ISNULL(TRNCOLLECT.COLLECTION_AMOUNT,0) - ISNULL(TRNCOLLECT.ADJUSTED_AMOUNT,0) AS PENDING_AMOUNT,
                                       TRN_COLLECTION_HEADER.COLLECTION_DATE,
                                       ISNULL(CONVERT(CHAR(10),TRN_COLLECTION_HEADER.COLLECTION_DATE,103),'') AS COLLECTION_DATE_DDMMYYYY,
                                       ISNULL(CONVERT(CHAR(8),TRN_COLLECTION_HEADER.COLLECTION_DATE,112),'')  AS COLLECTION_DATE_YYYYMMDD,
                                       ISNULL(TRN_COLLECTION_HEADER.ADJUSTMENT_FLAG,0)                        AS ADJUSTMENT_FLAG,
                                       ISNULL(MST_PAYMENT_TYPE_COLLECTION.PAYMENT_TYPE_DESCRIPTION,'') AS PAYMENT_TYPE_DESCRIPTION_COLLECTION,
                                       ISNULL(MST_BANK_COLLECTION.BANK_NAME,'') AS BANK_NAME_COLLECTION,
                                       ISNULL(TRN_COLLECTION_HEADER.REFERENCE_NO,'') AS REFERENCE_NO_COLLECTION,
                                       ISNULL(TRN_COLLECTION_HEADER.COLLECTION_REMARKS,'') AS COLLECTION_REMARKS,
                                       ISNULL(TRN_COLLECTION_DETAIL.COLLECTION_AMOUNT,0) AS COLLECTION_ADJUSTED_AMOUNT,
                                       TRN_COLLECTION_HEADER.RECONCILIATION_DATE,
                                       ISNULL(CONVERT(CHAR(10),TRN_COLLECTION_HEADER.RECONCILIATION_DATE,103),'') AS RECONCILIATION_DATE_DDMMYYYY,
                                       ISNULL(CONVERT(CHAR(8),TRN_COLLECTION_HEADER.RECONCILIATION_DATE,112),'')  AS RECONCILIATION_DATE_YYYYMMDD
                                FROM   TRN_INVOICE_HEADER
                                       INNER JOIN MST_PARTY          
                                       ON TRN_INVOICE_HEADER.PARTY_ID         = MST_PARTY.PARTY_ID
                                       INNER JOIN MST_COMPANY        
                                       ON TRN_INVOICE_HEADER.COMPANY_ID       = MST_COMPANY.COMPANY_ID
                                       INNER JOIN PAR_DELIVERY_MODE  
                                       ON TRN_INVOICE_HEADER.DELIVERY_MODE_ID = PAR_DELIVERY_MODE.DELIVERY_MODE_ID
                                       INNER JOIN MST_PAYMENT_TYPE   
                                       ON TRN_INVOICE_HEADER.PAYMENT_TYPE_ID  = MST_PAYMENT_TYPE.PAYMENT_TYPE_ID
                                       INNER JOIN MST_PARTY_CATEGORY 
                                       ON MST_PARTY.PARTY_CATEGORY_ID         = MST_PARTY_CATEGORY.PARTY_CATEGORY_ID
                                       LEFT  JOIN MST_BANK           
                                       ON TRN_INVOICE_HEADER.BANK_ID          = MST_BANK.BANK_ID
                                       LEFT  JOIN TRN_COLLECTION_DETAIL
                                       ON TRN_INVOICE_HEADER.INVOICE_HEADER_ID = TRN_COLLECTION_DETAIL.INVOICE_HEADER_ID
                                       LEFT  JOIN TRN_COLLECTION_HEADER
                                       ON TRN_COLLECTION_DETAIL.COLLECTION_HEADER_ID = TRN_COLLECTION_HEADER.COLLECTION_HEADER_ID
                                       LEFT JOIN MST_PAYMENT_TYPE AS MST_PAYMENT_TYPE_COLLECTION
                                       ON TRN_COLLECTION_HEADER.PAYMENT_TYPE_ID = MST_PAYMENT_TYPE_COLLECTION.PAYMENT_TYPE_ID
                                       LEFT JOIN MST_BANK AS MST_BANK_COLLECTION
                                       ON TRN_COLLECTION_HEADER.BANK_ID = MST_BANK_COLLECTION.BANK_ID
                                       LEFT  JOIN ( SELECT COLLADJ.INVOICE_HEADER_ID,
                                                           SUM(COLLADJ.COLLECTION_AMOUNT) AS COLLECTION_AMOUNT,
                                                           SUM(COLLADJ.ADJUSTED_AMOUNT)   AS ADJUSTED_AMOUNT
                                                    FROM   (
                                                           ------------- INVOICE WISE TOTAL COLLECTION
                                                           SELECT TRN_COLLECTION_DETAIL.INVOICE_HEADER_ID,
                                                                  SUM(TRN_COLLECTION_DETAIL.COLLECTION_AMOUNT) AS COLLECTION_AMOUNT,
                                                                  0                                            AS ADJUSTED_AMOUNT
                                                           FROM   TRN_COLLECTION_DETAIL,
                                                                  TRN_COLLECTION_HEADER
                                                           WHERE  TRN_COLLECTION_DETAIL.COLLECTION_HEADER_ID = TRN_COLLECTION_HEADER.COLLECTION_HEADER_ID            
                                                           AND    TRN_COLLECTION_HEADER.ADJUSTMENT_FLAG = 0
                                                           GROUP BY TRN_COLLECTION_DETAIL.INVOICE_HEADER_ID
                                                           UNION
                                                           ------------- INVOICE WISE TOTAL ADJUSTMENT
                                                           SELECT TRN_COLLECTION_DETAIL.INVOICE_HEADER_ID,
                                                                  0                                            AS COLLECTION_AMOUNT,
                                                                  SUM(TRN_COLLECTION_DETAIL.COLLECTION_AMOUNT) AS ADJUSTED_AMOUNT
                                                           FROM   TRN_COLLECTION_DETAIL,
                                                                  TRN_COLLECTION_HEADER
                                                           WHERE  TRN_COLLECTION_DETAIL.COLLECTION_HEADER_ID = TRN_COLLECTION_HEADER.COLLECTION_HEADER_ID            
                                                           AND    TRN_COLLECTION_HEADER.ADJUSTMENT_FLAG = 1
                                                           GROUP BY TRN_COLLECTION_DETAIL.INVOICE_HEADER_ID
                                                          ) AS COLLADJ
				                                    GROUP BY COLLADJ.INVOICE_HEADER_ID
				                                  ) AS TRNCOLLECT 
                                          ON TRN_INVOICE_HEADER.INVOICE_HEADER_ID = TRNCOLLECT.INVOICE_HEADER_ID
                                WHERE    TRN_INVOICE_HEADER.RECON_FLAG = 0  
                                AND     (TRN_INVOICE_HEADER.TRAN_TYPE = 'INV' OR TRN_INVOICE_HEADER.TRAN_TYPE = 'OINV')
                            )";
                //---------------------------------------------
                dmlService.J_ExecSql(strSQL);
                //---------------------------------------------

                #endregion

                //--
                #region INDEXES

                #region MST_PARTY [IDX_PARTY_NAME]
                //---------- MST_PARTY
                if (Convert.ToInt32(dmlService.J_ExecSqlReturnScalar("SELECT COUNT(*) FROM SYS.INDEXES WHERE NAME LIKE 'IDX_PARTY_NAME'")) > 0)
                {
                    strSQL = @" DROP INDEX MST_PARTY.IDX_PARTY_NAME";
                    dmlService.J_ExecSql(strSQL);
                }
                strSQL = "CREATE INDEX IDX_PARTY_NAME        ON MST_PARTY (PARTY_NAME)";
                dmlService.J_ExecSql(strSQL);

                #endregion

                #region MST_PARTY [IDX_PARTY_CATEGORY_ID]
                //---------- MST_PARTY
                if (Convert.ToInt32(dmlService.J_ExecSqlReturnScalar("SELECT COUNT(*) FROM SYS.INDEXES WHERE NAME LIKE 'IDX_PARTY_CATEGORY_ID'")) > 0)
                {
                    strSQL = @" DROP INDEX MST_PARTY.IDX_PARTY_CATEGORY_ID";
                    dmlService.J_ExecSql(strSQL);
                }
                strSQL = "CREATE INDEX IDX_PARTY_CATEGORY_ID ON MST_PARTY (PARTY_CATEGORY_ID)";
                dmlService.J_ExecSql(strSQL);

                #endregion

                #region TRN_INVOICE_HEADER [IDX_INVOICE_NO]
                //---------- MST_PARTY
                if (Convert.ToInt32(dmlService.J_ExecSqlReturnScalar("SELECT COUNT(*) FROM SYS.INDEXES WHERE NAME LIKE 'IDX_INVOICE_NO'")) > 0)
                {
                    strSQL = @" DROP INDEX TRN_INVOICE_HEADER.IDX_INVOICE_NO";
                    dmlService.J_ExecSql(strSQL);
                }
                strSQL = "CREATE INDEX IDX_INVOICE_NO   ON TRN_INVOICE_HEADER (INVOICE_NO)";
                dmlService.J_ExecSql(strSQL);

                #endregion

                #region TRN_INVOICE_HEADER [IDX_INVOICE_DATE]
                if (Convert.ToInt32(dmlService.J_ExecSqlReturnScalar("SELECT COUNT(*) FROM SYS.INDEXES WHERE NAME LIKE 'IDX_INVOICE_DATE'")) > 0)
                {
                    strSQL = @" DROP INDEX TRN_INVOICE_HEADER.IDX_INVOICE_DATE";
                    dmlService.J_ExecSql(strSQL);
                }
                strSQL = "CREATE INDEX IDX_INVOICE_DATE ON TRN_INVOICE_HEADER (INVOICE_DATE)";
                dmlService.J_ExecSql(strSQL);

                #endregion

                #region TRN_INVOICE_HEADER [IDX_PARTY_ID]
                if (Convert.ToInt32(dmlService.J_ExecSqlReturnScalar("SELECT COUNT(*) FROM SYS.INDEXES WHERE NAME LIKE 'IDX_PARTY_ID'")) > 0)
                {
                    strSQL = @" DROP INDEX TRN_INVOICE_HEADER.IDX_PARTY_ID";
                    dmlService.J_ExecSql(strSQL);
                }
                strSQL = "CREATE INDEX IDX_PARTY_ID     ON TRN_INVOICE_HEADER (PARTY_ID)";
                dmlService.J_ExecSql(strSQL);

                #endregion

                #region TRN_INVOICE_HEADER [IDX_FAYEAR_ID]
                if (Convert.ToInt32(dmlService.J_ExecSqlReturnScalar("SELECT COUNT(*) FROM SYS.INDEXES WHERE NAME LIKE 'IDX_FAYEAR_ID'")) > 0)
                {
                    strSQL = @" DROP INDEX TRN_INVOICE_HEADER.IDX_FAYEAR_ID";
                    dmlService.J_ExecSql(strSQL);
                }
                strSQL = "CREATE INDEX IDX_FAYEAR_ID    ON TRN_INVOICE_HEADER (FAYEAR_ID)";
                dmlService.J_ExecSql(strSQL);

                #endregion

                #region TRN_COLLECTION_HEADER [IDX_COLLECTION_DATE]
                if (Convert.ToInt32(dmlService.J_ExecSqlReturnScalar("SELECT COUNT(*) FROM SYS.INDEXES WHERE NAME LIKE 'IDX_COLLECTION_DATE'")) > 0)
                {
                    strSQL = @" DROP INDEX TRN_COLLECTION_HEADER.IDX_COLLECTION_DATE";
                    dmlService.J_ExecSql(strSQL);
                }
                strSQL = "CREATE INDEX IDX_COLLECTION_DATE     ON TRN_COLLECTION_HEADER (COLLECTION_DATE)";
                dmlService.J_ExecSql(strSQL);

                #endregion

                #region TRN_COLLECTION_HEADER [IDX_RECONCILIATION_DATE]
                if (Convert.ToInt32(dmlService.J_ExecSqlReturnScalar("SELECT COUNT(*) FROM SYS.INDEXES WHERE NAME LIKE 'IDX_RECONCILIATION_DATE'")) > 0)
                {
                    strSQL = @" DROP INDEX TRN_COLLECTION_HEADER.IDX_RECONCILIATION_DATE";
                    dmlService.J_ExecSql(strSQL);
                }
                strSQL = "CREATE INDEX IDX_RECONCILIATION_DATE ON TRN_COLLECTION_HEADER (RECONCILIATION_DATE)";
                dmlService.J_ExecSql(strSQL);

                #endregion

                #region TRN_COLLECTION_DETAIL [IDX_COLLECTION_HEADER_ID]
                if (Convert.ToInt32(dmlService.J_ExecSqlReturnScalar("SELECT COUNT(*) FROM SYS.INDEXES WHERE NAME LIKE 'IDX_COLLECTION_HEADER_ID'")) > 0)
                {
                    strSQL = @" DROP INDEX TRN_COLLECTION_DETAIL.IDX_COLLECTION_HEADER_ID";
                    dmlService.J_ExecSql(strSQL);
                }
                strSQL = "CREATE INDEX IDX_COLLECTION_HEADER_ID ON TRN_COLLECTION_DETAIL(COLLECTION_HEADER_ID)";
                dmlService.J_ExecSql(strSQL);

                #endregion

                #region TRN_COLLECTION_DETAIL [IDX_INVOICE_HEADER_ID]
                if (Convert.ToInt32(dmlService.J_ExecSqlReturnScalar("SELECT COUNT(*) FROM SYS.INDEXES WHERE NAME LIKE 'IDX_INVOICE_HEADER_ID'")) > 0)
                {
                    strSQL = @" DROP INDEX TRN_COLLECTION_DETAIL.IDX_INVOICE_HEADER_ID";
                    dmlService.J_ExecSql(strSQL);
                }
                strSQL = "CREATE INDEX IDX_INVOICE_HEADER_ID    ON TRN_COLLECTION_DETAIL(INVOICE_HEADER_ID)";
                dmlService.J_ExecSql(strSQL);

                #endregion

                #endregion

                // cursor will be normal mode
                cmnService.J_NormalMode();

                txtInfo.Visible = true;
                txtInfo.Text    = "Database Maintainence is completed.";
            }
            catch (Exception err_handler)
            {
                cmnService.J_UserMessage(err_handler.Message);
            }
        }
예제 #18
0
        private void Insert_Update_Delete_Data()
        {
            try
            {
                string strLoginId  = string.Empty;
                int    intInactive = 0;

                switch (lblMode.Text)
                {
                    #region ADD
                case J_Mode.Add:

                    //// all validations
                    //if (ValidateFields() == false) return;

                    //// Save Confirmation Message
                    //if (cmnService.J_SaveConfirmationMessage(ref txtOnlineMailBody) == true) return;

                    ////intCategory = Convert.ToInt32(chkActiveInactive.Checked == true ? "0" : "1");

                    //// Begin Transaction
                    //dmlService.J_BeginTransaction();

                    //strSQL = "INSERT INTO MST_EMAIL_CATEGORY (" +
                    //         "            BRANCH_ID," +
                    //         "            USER_CODE," +
                    //         "            DISPLAY_NAME," +
                    //         "            LOGIN_ID," +
                    //         "            USER_PASSWORD," +
                    //         "            USER_CATEGORY) " +
                    //         "    VALUES( " + J_Var.J_pBranchId + "," +
                    //         "           ''," +
                    //         "           '" + cmnService.J_ReplaceQuote(txtUserName.Text.Trim()) + "'," +
                    //         "           '" + cmnService.J_ReplaceQuote(txtLoginId.Text.Trim()) + "'," +
                    //         "           '" + cmnService.J_ReplaceQuote(txtPassword.Text.Trim()) + "'," +
                    //         "            " + intCategory + ")";

                    //if (dmlService.J_ExecSql(strSQL) == false)
                    //{
                    //    txtUserName.Select();
                    //    return;
                    //}

                    //lngSearchId = dmlService.J_ReturnMaxValue("MST_USER", "USER_ID",
                    //    "    BRANCH_ID = " + J_Var.J_pBranchId + " " +
                    //    "AND " + cmnService.J_SQLDBFormat("LOGIN_ID", J_SQLColFormat.UCase) + " = '" + cmnService.J_ReplaceQuote(txtLoginId.Text.Trim().ToUpper()) + "'");
                    //if (lngSearchId == 0) return;

                    //dmlService.J_Commit();
                    //cmnService.J_PanelMessage(J_PanelIndex.e00_DisplayText, J_Msg.AddModeSave);

                    //ClearControls();
                    //txtUserName.Select();

                    break;

                    #endregion
                    //
                    #region EDIT
                case J_Mode.Edit:
                    // For Modify
                    if (ValidateFields() == false)
                    {
                        return;
                    }
                    if (cmnService.J_SaveConfirmationMessage(ref txtOnlineMailBody) == true)
                    {
                        return;
                    }

                    intInactive = Convert.ToInt32(chkInactive.Checked == true ? "1" : "0");

                    // Begin Transaction
                    dmlService.J_BeginTransaction();

                    strSQL = "UPDATE MST_EMAIL_CATEGORY " +
                             "SET    EMAIL_SUBJECT_ONLINE  ='" + cmnService.J_ReplaceQuote(txtEmailSubjectOnline.Text.Trim()) + "'," +
                             "       EMAIL_SUBJECT_OFFLINE ='" + cmnService.J_ReplaceQuote(txtEmailSubjectOffline.Text.Trim()) + "'," +
                             "       EMAIL_SUBJECT_DESPATCH ='" + cmnService.J_ReplaceQuote(txtEmailSubjectItemDispatched.Text.Trim()) + "'," +
                             "       EMAIL_BODY_ONLINE     ='" + cmnService.J_ReplaceQuote(txtOnlineMailBody.Text.Trim()) + "'," +
                             "       EMAIL_BODY_OFFLINE    ='" + cmnService.J_ReplaceQuote(txtOfflineMailBody.Text.Trim()) + "'," +
                             "       EMAIL_BODY_DESPATCH   ='" + cmnService.J_ReplaceQuote(txtItemsDispatchedMailBody.Text.Trim()) + "'," +
                             "       FROM_EMAIL            ='" + cmnService.J_ReplaceQuote(txtFromEmail.Text.Trim()) + "'," +
                             "       DISPLAY_NAME          ='" + cmnService.J_ReplaceQuote(txtDisplayName.Text.Trim()) + "'," +
                             "       EMAIL_BCC             ='" + cmnService.J_ReplaceQuote(txtBCCEmail.Text.Trim()) + "'," +
                             "       INACTIVE_FLAG         = " + intInactive + " " +
                             "WHERE  EMAIL_TYPE_ID         = " + lngSearchId + "";

                    // If reset password is checked then update password too

                    //if (chkResetPassword.Checked == true)
                    //    strSQL = strSQL + ", USER_PASSWORD = '******' ";
                    //strSQL = strSQL + "WHERE EMAIL_TYPE_ID = " + lngSearchId + "";

                    if (dmlService.J_ExecSql(strSQL) == false)
                    {
                        txtOnlineMailBody.Select();
                        return;
                    }

                    dmlService.J_Commit();
                    cmnService.J_PanelMessage(J_PanelIndex.e00_DisplayText, J_Msg.EditModeSave);

                    ClearControls();

                    strSQL = strQuery + "ORDER BY " + strOrderBy;
                    if (dsetGridClone != null)
                    {
                        dsetGridClone.Clear();
                    }
                    dsetGridClone = dmlService.J_ShowDataInGrid(ref ViewGrid, strSQL, strMatrix);           //Show Data into the Grid
                    if (dsetGridClone == null)
                    {
                        return;
                    }

                    lblMode.Text = J_Mode.View;
                    cmnService.J_StatusButton(this, lblMode.Text);

                    BtnDelete.BackColor = Color.LightGray;
                    BtnDelete.Enabled   = false;
                    BtnAdd.BackColor    = Color.LightGray;
                    BtnAdd.Enabled      = false;
                    BtnSort.BackColor   = Color.LightGray;
                    BtnSort.Enabled     = false;
                    BtnSearch.BackColor = Color.LightGray;
                    BtnSearch.Enabled   = false;

                    ControlVisible(false);

                    dmlService.J_setGridPosition(ref ViewGrid, dsetGridClone, "EMAIL_TYPE_ID", lngSearchId);
                    break;

                    #endregion
                //
                case J_Mode.Delete:
                    break;
                }
            }
            catch (Exception err_handler)
            {
                dmlService.J_Rollback();
                cmnService.J_UserMessage(err_handler.Message);
            }
        }