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();
}
}
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();
}
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);
}
}
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);
}
}
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);
}
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);
}
}
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);
}
}
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);
}
}
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);
}
}
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);
}
}
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);
}
}
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 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);
}
}