public TrnInvoicePrint(long InvoiceHeaderId, string InvoiceDetails) { InitializeComponent(); dmlService = new DMLService(); cmnService = new CommonService(); rptService = new ReportService(); lngInvoiceHeaderId = InvoiceHeaderId; string[] strInvoiceDetails = InvoiceDetails.Split('^'); InvoiceNO = strInvoiceDetails[0].Trim(); //SerialNo = strInvoiceDetails[1].Trim(); EmailID = strInvoiceDetails[2].Trim(); MobileNo = strInvoiceDetails[3].Trim(); //-- if (strInvoiceDetails[1].Trim() == "") { lblOfflineSerialNumber.Visible = false; label6.Visible = false; } else { lblOfflineSerialNumber.Visible = true; label6.Visible = true; SerialNo = Convert.ToString(dmlService.J_ExecSqlReturnScalar("SELECT OFFLINE_CODE FROM MST_OFFLINE_SERIAL WHERE OFFLINE_SERIAL_CODE ='" + strInvoiceDetails[1].Trim() + "'")); } }
string strSQL; //For Storing the Local SQL Query #endregion #region Event Handlers #region CmnChangePassword_Load private void CmnChangePassword_Load(object sender, EventArgs e) { string strLoginId = string.Empty; strSQL = "SELECT LOGIN_ID " + "FROM MST_USER " + "WHERE USER_ID = " + J_Var.J_pUserId + " "; if (J_Var.J_pLoginId.Trim().ToUpper() != "ADMIN") { strSQL = strSQL + "AND BRANCH_ID = " + J_Var.J_pBranchId + " "; } strLoginId = Convert.ToString(dmlService.J_ExecSqlReturnScalar(strSQL)); if (strLoginId == null) { return; } txtLoginId.Text = strLoginId; lblTitle.Text = "Change User Password"; txtOldPassword.Select(); }
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); } }
private void cmbFAYear_SelectedIndexChanged(object sender, EventArgs e) { J_Var.J_pFAYearId = Support.GetItemData(cmbFAYear, cmbFAYear.SelectedIndex); J_Var.J_pFABegDate = cmnService.J_Mid(cmbFAYear.Text, 0, 10); J_Var.J_pFAEndDate = cmnService.J_Mid(cmbFAYear.Text, 13, 10); J_Var.J_pCollectionPostFlag = cmnService.J_ReturnInt32Value(Convert.ToString(dmlService.J_ExecSqlReturnScalar("SELECT COLLECTION_POST_FLAG FROM MST_FAYEAR WHERE FAYEAR_ID = " + J_Var.J_pFAYearId))); if (J_Var.J_pCollectionPostFlag > 0) { J_Var.J_pReconFlag = 0; } else { J_Var.J_pReconFlag = 1; } }
private void cmbCompany_SelectedIndexChanged(object sender, EventArgs e) { //ADDED BY DHRUB ON 28/05/2014 if (cmbCompany.SelectedIndex > 0) { strSQL = "SELECT MAX_DAYS_PERMIT FROM MST_COMPANY WHERE COMPANY_ID = " + cmnService.J_GetComboBoxItemId(ref cmbCompany, cmbCompany.SelectedIndex); lngMaxDaysPermit = Convert.ToInt64(dmlService.J_ExecSqlReturnScalar(strSQL)); } //1. HeaderText, //2. Width, //3. Format, //4. Alignment, //5. NullToText, //6. Visible, //7. AutoSizeMode string[,] strMatrix1 = { { "Invoice Header Id", "0", "", "Right", "", "false", "" }, { "Invoice No.", "110", "", "", "", "", "" }, { "Invoice Date", "70", "dd/MM/yyyy", "", "", "", "" }, { "Party", "190", "", "", "", "", "fill" }, { "Category", "50", "", "", "", "", "" }, { "Net Amount.", "80", "0.00", "Right", "", "", "" }, { "Payment Type.", "100", "", "", "", "", "" }, { "Bank", "60", "", "", "", "", "" }, { "Reference No", "70", "", "", "", "", "" }, { "A/c Entry Date", "80", "dd/MM/yyyy", "", "", "", "" }, { "Bank Date", "80", "dd/MM/yyyy", "", "", "", "" }, { "Max Diff", "50", "", "CENTER", "", "", "" } }; strMatrix = strMatrix1; strQuery = @"SELECT TRN_INVOICE_HEADER.INVOICE_HEADER_ID AS INVOICE_HEADER_ID, TRN_INVOICE_HEADER.INVOICE_NO AS INVOICE_NO, " + cmnService.J_SQLDBFormat("TRN_INVOICE_HEADER.INVOICE_DATE", J_SQLColFormat.DateFormatDDMMYYYY) + @" AS INVOICE_DATE, " + cmnService.J_SQLDBFormat("MST_PARTY.PARTY_NAME", J_ColumnType.String, J_SQLColFormat.NullCheck, null, "********* UNKNOWN DEPOSIT *********") + @" AS PARTY_NAME, MST_PARTY_CATEGORY.PARTY_CATEGORY_DESCRIPTION AS PARTY_CATEGORY_DESCRIPTION, TRN_INVOICE_HEADER.NET_AMOUNT AS NET_AMOUNT, MST_PAYMENT_TYPE.PAYMENT_TYPE_DESCRIPTION AS PAYMENT_TYPE_DESCRIPTION, MST_BANK.BANK_NAME AS BANK_NAME, TRN_INVOICE_HEADER.REFERENCE_NO AS REFERENCE_NO, " + cmnService.J_SQLDBFormat("TRN_INVOICE_HEADER.ACCOUNT_ENTRY_DATE", J_SQLColFormat.DateFormatDDMMYYYY) + @" AS ACCOUNT_ENTRY_DATE, " + cmnService.J_SQLDBFormat("TRN_INVOICE_HEADER.BANK_STATEMENT_DATE", J_SQLColFormat.DateFormatDDMMYYYY) + @" AS BANK_STATEMENT_DATE, CASE WHEN (CASE WHEN ABS(DATEDIFF(DAY,TRN_INVOICE_HEADER.INVOICE_DATE,TRN_INVOICE_HEADER.ACCOUNT_ENTRY_DATE))> " + lngMaxDaysPermit + @" THEN '*' ELSE '' END) = '*' OR (CASE WHEN ABS(DATEDIFF(DAY,TRN_INVOICE_HEADER.INVOICE_DATE,TRN_INVOICE_HEADER.BANK_STATEMENT_DATE))> " + lngMaxDaysPermit + @" THEN '*' ELSE '' END) = '*' THEN '*' ELSE '' END STARED FROM (((((TRN_INVOICE_HEADER LEFT JOIN (SELECT INVOICE_HEADER_ID FROM TRN_INVOICE_HEADER WHERE PAYMENT_TYPE_ID = 4 AND ACCOUNT_ENTRY_DATE IS NOT NULL) AS CASH_RECONCILED ON TRN_INVOICE_HEADER.INVOICE_HEADER_ID = CASH_RECONCILED.INVOICE_HEADER_ID) LEFT JOIN MST_PARTY ON TRN_INVOICE_HEADER.PARTY_ID = MST_PARTY.PARTY_ID) LEFT JOIN MST_PARTY_CATEGORY ON MST_PARTY.PARTY_CATEGORY_ID = MST_PARTY_CATEGORY.PARTY_CATEGORY_ID) LEFT JOIN MST_PAYMENT_TYPE ON TRN_INVOICE_HEADER.PAYMENT_TYPE_ID = MST_PAYMENT_TYPE.PAYMENT_TYPE_ID) LEFT JOIN MST_BANK ON TRN_INVOICE_HEADER.BANK_ID = MST_BANK.BANK_ID)"; if (cmbCompany.SelectedIndex <= 0) { strQuery += "WHERE 1 = 2 "; } else { strQuery += "WHERE TRN_INVOICE_HEADER.COMPANY_ID = " + cmnService.J_GetComboBoxItemId(ref cmbCompany, cmbCompany.SelectedIndex) + " " + "AND TRN_INVOICE_HEADER.FAYEAR_ID = " + J_Var.J_pFAYearId + " " + "AND CANCELLATION_FLAG = 0 "; if (cmbFilterPaymentType.SelectedIndex > 0) { strQuery += "AND TRN_INVOICE_HEADER.PAYMENT_TYPE_ID = " + Support.GetItemData(cmbFilterPaymentType, cmbFilterPaymentType.SelectedIndex) + " "; } if (chkIncompleteRecords.Checked == true) { strQuery += "AND ( TRN_INVOICE_HEADER.ACCOUNT_ENTRY_DATE IS NULL " + " OR TRN_INVOICE_HEADER.BANK_STATEMENT_DATE IS NULL ) " + "AND CASH_RECONCILED.INVOICE_HEADER_ID IS NULL "; } } strOrderBy = "TRN_INVOICE_HEADER.INVOICE_HEADER_ID DESC"; //----------------------------------------------------------- strSQL = strQuery + "ORDER BY " + strOrderBy; //----------------------------------------------------------- if (dsetGridClone != null) { dsetGridClone.Clear(); } dsetGridClone = dmlService.J_ShowDataInGrid(ref ViewGrid, strSQL, strMatrix); //Show Data into the Grid //int j = 0; //for (int i = j; i <= ViewGrid.VisibleRowCount - 1; i++) //{ // //if (cmnService.J_ReturnInt32Value(Convert.ToString(dgvDeductees.Rows[i].Cells[intVerifyId].Value)) > 0) // if (Convert.ToString(ViewGrid.CurrentCell)== "*") // { // } // j = j + 1; //} }