private void FrmDistributor_Load(object sender, EventArgs e)
{
cs.FrmStyle(this);
DataSet ds = db.GetTableData("select region_id, region_name || '(' || region_id || ')' region_name, action_type from mas_region");
CmbRegion.DataSource = ds.Tables[0];
CmbRegion.DisplayMember = "region_name";
CmbRegion.ValueMember = "region_id";
if (LblPK.Text.Trim().Length > 0)
{
DataSet ds1 = db.GetTableData("select * from mas_distributor where distributor_code=" + LblPK.Text);
if (ds1.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds1.Tables[0].Rows)
{
TxtName.Text = dr["distributor_name"].ToString();
TxtAddress.Text = dr["Distributor_address"].ToString();
TxtRemarks.Text = dr["Remarks"].ToString();
DataSet ds2 = db.GetTableData("Select * from mas_region where region_id=" + dr["region_id"].ToString());
if (ds2.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr1 in ds2.Tables[0].Rows)
{
CmbRegion.Text = dr1["region_name"].ToString() + "(" + dr1["region_id"].ToString() + ")";
}
}
}
}
}
}
private string GetROCode()
{
string RONo = "";
DataSet ds = db.GetTableData("select max(substr(ro_header_code,1,Instr(ro_header_code,'/',1,3))||to_char(sysdate,'MM')||'/'||lpad(substr(ro_header_code,instr(ro_header_code,'/',1,4)+1)+1,4,'0')) ro_code, decode(action_type,'U','I',action_type) action_type from hdr_ro where substr(ro_header_code, instr(ro_header_code,'/',1,3)+1,2) = to_char(sysdate,'MM') and substr(ro_header_code,8,2)||substr(ro_header_code,11,2)='" + GetFnYr().Replace("-", "") + "' group by decode(action_type,'U','I',action_type)");
if (ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
RONo = dr["Ro_code"].ToString();
}
}
else
{
DateTime DtSysDate = DateTime.Parse(DateTime.Now.ToShortDateString());
int Dts = Convert.ToInt32(DtSysDate.Month.ToString() + DtSysDate.ToString("dd"));
string DtMon = DtSysDate.ToString("MM");
string FY = "";
if (Dts >= 101 && Dts <= 331)
{
FY = DtSysDate.AddYears(-1).ToString("yy") + "-" + DtSysDate.ToString("yy");
}
else
{
FY = DtSysDate.ToString("yy") + "-" + DtSysDate.AddYears(1).ToString("yy");
}
RONo = "RO/HMZ/" + FY + "/" + DtMon + "/0001";
}
return(RONo);
}
private void button1_Click(object sender, EventArgs e)
{
if (TxtType.Text.Trim().Length <= 0)
{
MessageBox.Show("Invalid Subscription Type", "Hindudharmam Magazine", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (TxtIssues.Text.Trim().Length <= 0)
{
MessageBox.Show("Invalid No. of Subscriptions", "Hindudharmam Magazine", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
DataSet ds;
if (LblPK.Text.Trim().Length > 0)
{
ds = db.GetTableData("select * from mas_sub_type where sub_type_id<>" + LblPK.Text.Trim() + " and sub_type_name='" + TxtType.Text.Trim() + "'");
if (ds.Tables[0].Rows.Count > 0)
{
MessageBox.Show("This Subscription Type already exists", "Hindudharmam Magazine", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
else
{
ds = db.GetTableData("select * from mas_sub_type where sub_type_name='" + TxtType.Text.Trim() + "'");
if (ds.Tables[0].Rows.Count > 0)
{
MessageBox.Show("This Subscription Type already Exists", "Hindudharmam Magazine", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
string[] res;
if (LblPK.Text.Trim().Length > 0)
{
res = db.ExecuteQueries("Update mas_sub_type set sub_type_name='" + TxtType.Text.Trim() + "', issues=" + TxtIssues.Text.Trim() + ", Remarks='" + TxtRemarks.Text.Trim() + "', modified_by='" + GlobalClass.UserName + "', modified_date=sysdate where sub_type_id=" + LblPK.Text.Trim()).Split(',');
}
else
{
res = db.ExecuteQueries("Insert into mas_sub_type values (" + db.GetNewID("mas_sub_type", "sub_type_id") + ",'" + TxtType.Text.Trim() + "'," + TxtIssues.Text.Trim() + ",'" + TxtRemarks.Text.Trim() + "','" + GlobalClass.UserName + "',sysdate, null, null, 'I')").Split(',');
}
if (res[0].ToString() != "0")
{
MessageBox.Show("Insertion / Updation Failure..", "Hindudharmam Magazine", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
else
{
MessageBox.Show("Successfully Saved", "Hindudharmam Magazine", MessageBoxButtons.OK, MessageBoxIcon.Information);
TxtIssues.Text = "";
TxtRemarks.Text = "";
TxtType.Text = "";
LblPK.Text = "";
TxtType.Focus();
}
}
private void FrmMontring_Load(object sender, EventArgs e)
{
cs.FrmStyle(this);
DataSet ds = db.GetTableData("Select ad_category_code, ad_category_name || '('|| ad_Category_code || ')' ADCategoryName, Action_type from mas_ad_Category");
CmbADCategory.DataSource = ds.Tables[0];
CmbADCategory.DisplayMember = "ADCategoryName";
CmbADCategory.ValueMember = "AD_Category_Code";
}
private void FrmPartyLedger_Load(object sender, EventArgs e)
{
cs.FrmStyle(this);
DataSet ds = db.GetTableData("select party_type_id, party_type_name || '(' || party_type_id || ')' party_type_name, action_type from MAS_PARTY_TYPE");
CmbPType.DataSource = ds.Tables[0];
CmbPType.DisplayMember = "party_type_name";
CmbPType.ValueMember = "party_type_id";
}
private void FrmAddressBook_Load(object sender, EventArgs e)
{
cs.FrmStyle(this);
DataSet ds = db.GetTableData("select region_id, region_name||'('|| region_ID ||')' Region_Name, Action_type from mas_region order by region_name");
CmbRegion.DataSource = ds.Tables[0];
CmbRegion.DisplayMember = "Region_Name";
CmbRegion.ValueMember = "Region_ID";
}
private void FrmInvoiceGeneration_Load(object sender, EventArgs e)
{
cs.FrmStyle(this);
DataSet ds = db.GetTableData("select max(invoice_date) Invoice_Date, action_type from mas_acc_invoice group by action_type");
if (ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
DtpInvDt.MinDate = Convert.ToDateTime(dr["Invoice_Date"].ToString());
}
}
ds = db.GetTableData("Select max(invoice_code) Invoice_Code, action_type from mas_acc_invoice group by action_type");
if (ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
TxtLastInvoiceNo.Text = dr["Invoice_code"].ToString();
}
}
else
{
DateTime DtSysDate = DateTime.Parse(DateTime.Now.ToShortDateString());
int Dts = Convert.ToInt32(DtSysDate.Month.ToString() + DtSysDate.ToString("dd"));
string DtMon = DtSysDate.ToString("MM");
string FY = "";
if (Dts >= 101 && Dts <= 331)
{
FY = DtSysDate.AddYears(-1).ToString("yy") + "-" + DtSysDate.ToString("yy");
}
else
{
FY = DtSysDate.ToString("yy") + "-" + DtSysDate.AddYears(1).ToString("yy");
}
TxtLastInvoiceNo.Text = "IN/HMZ/" + FY + "/00001";
}
ds = db.GetTableData("Select min(tx_month) frmmnth, max(tx_month) tomnth, decode(MC.action_type,'U','I',MC.action_type) Action_type from mas_capsulling MC, DTL_RO DR where DR.RO_HEADER_CODE (+)= MC.RO_HEADER_CODE AND DR.RO_DETAIL_CODE (+)= MC.RO_DETAIL_CODE AND SPOT_CATEGORY_CODE=1 AND invoice_code is null group by decode(MC.action_type,'U','I',MC.action_type)");
if (ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
DtpMnthFrm.Value = Convert.ToDateTime(dr["frmmnth"].ToString());
DtpMnthTo.Value = Convert.ToDateTime(dr["tomnth"].ToString());
DtpMnthTo.MaxDate = Convert.ToDateTime(dr["tomnth"].ToString());
}
}
else
{
DtpMnthFrm.Value = Convert.ToDateTime("01-" + DateTime.Now.ToString("MMM-yyyy"));
DtpMnthTo.Value = Convert.ToDateTime("01-" + DateTime.Now.ToString("MMM-yyyy"));
DtpMnthTo.MaxDate = Convert.ToDateTime("01-" + DateTime.Now.ToString("MMM-yyyy"));
}
}
private void FrmGenerateMnthlySubscriptions_Load(object sender, EventArgs e)
{
cs.FrmStyle(this);
DataSet ds = db.GetTableData("select max(issue_month) mnth, action_type from mas_sub_issues group by action_type");
foreach (DataRow dr in ds.Tables[0].Rows)
{
DtpMnth.Value = Convert.ToDateTime("01-" + Convert.ToDateTime(dr["mnth"].ToString()).ToString("MMM-yy"));
DtpMnth.MinDate = Convert.ToDateTime("01-" + Convert.ToDateTime(dr["mnth"].ToString()).ToString("MMM-yy"));
}
DtpDspchDt.Value = DateTime.Now;
DtpDspchDt.MinDate = DateTime.Now;
}
private void button1_Click(object sender, EventArgs e)
{
DataSet ds;
if (TxtKeyword.Text.Trim().Length > 0)
{
ds = db.GetTableData("select sub_id, voucher_no, sub_name, address, city_name, state_name, district_name, region_name, pin_code, email_id, contact_no, executive_name, sub_type_name, to_char(period_frm,'dd-MON-yy') period_Frm, to_char(period_to,'dd-MON-yy') period_to from VEW_SUBSCRIPTIONS where voucher_no||sub_name||address||city_name||state_name||district_name||region_name||pin_code||email_id||contact_no||sub_Type_name||period_frm||period_to||executive_name like '%" + TxtKeyword.Text.Trim().ToUpper() + "%'");
}
else
{
ds = db.GetTableData("select sub_id, voucher_no, sub_name, address, city_name, state_name, district_name, region_name, pin_code, email_id, contact_no, executive_name, sub_type_name, to_char(period_frm,'dd-MON-yy') period_Frm, to_char(period_to,'dd-MON-yy') period_to from VEW_SUBSCRIPTIONS");
}
DgvList.DataSource = ds.Tables[0];
}
private void button1_Click(object sender, EventArgs e)
{
double OBDr = 0.0, OBCr = 0.0;
DataSet ds = db.GetTableData("select sum(credit_amount) cr, sum(debit_amount) dr, action_type from VEW_BANK_LEDGER where recon_date<'" + DtpFrm.Value.ToString("dd-MMM-yy") + "' group by action_type");
if (ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
double Cr = Convert.ToDouble(dr["Cr"].ToString());
double Dr = Convert.ToDouble(dr["Dr"].ToString());
if (Cr > Dr)
{
OBCr = Cr - Dr;
OBDr = 0;
}
if (Dr > Cr)
{
OBDr = Dr - Cr;
OBCr = 0;
}
}
}
cs.ShowRpt("Bank_Ledger.rpt", "{VEW_BANK_LEDGER.RECON_DATE} in DateTime (" + DtpFrm.Value.Year + ", " + DtpFrm.Value.Month + ", " + DtpFrm.Value.Day + ", 00, 00, 00) to DateTime (" + DtpTo.Value.Year + ", " + DtpTo.Value.Month + ", " + DtpTo.Value.Day + ", 00, 00, 00)", "Party Ledger", "FrmDt~" + DtpFrm.Value.ToString("dd-MMM-yyyy") + "@ToDt~" + DtpTo.Value.ToString("dd-MMM-yyyy") + "@OBDr~" + OBDr + "@OBCr~" + OBCr);
}
private void button3_Click(object sender, EventArgs e)
{
DataSet ds = db.GetTableData("select * from VEW_BILL_REGISTER where invoice_date between '" + DtpFrm.Value.ToString("dd-MMM-yy") + "' and '" + DtpTo.Value.ToString("dd-MMM-yy") + "'");
dataGridView1.DataSource = ds.Tables[0];
db.ExportToExcel(dataGridView1);
}
private void FrmReceipt_Load(object sender, EventArgs e)
{
cs.FrmStyle(this);
DataSet ds;
ds = db.GetTableData("Select pay_mode_id, pay_mode || '(' || pay_mode_id || ')' pay_mode, action_type from MAS_PAY_MODE");
CmbPayMode.DataSource = ds.Tables[0];
CmbPayMode.DisplayMember = "pay_mode";
CmbPayMode.ValueMember = "pay_mode_id";
ds = db.GetTableData("select region_id, region_name || '(' || region_id || ')' region_name, action_type from mas_region");
CmbRegion.DataSource = ds.Tables[0];
CmbRegion.DisplayMember = "region_Name";
CmbRegion.ValueMember = "region_id";
ds = db.GetTableData("select pay_mode_id, pay_mode || '('|| pay_mode_id || ')' Pay_mode, action_type from mas_pay_mode");
CmbPayMode.DataSource = ds.Tables[0];
CmbPayMode.DisplayMember = "pay_mode";
CmbPayMode.ValueMember = "pay_mode_id";
ds = db.GetTableData("Select bank_id, bank_name || '(' || bank_id ||')' Bank_name, action_type from mas_bank");
CmbBank.DataSource = ds.Tables[0];
CmbBank.DisplayMember = "Bank_Name";
CmbBank.ValueMember = "bank_id";
ds = db.GetTableData("select party_type_id, party_type_name || '(' || party_type_id || ')' party_type_name, action_type from MAS_PARTY_TYPE");
CmbPartyType.DataSource = ds.Tables[0];
CmbPartyType.DisplayMember = "party_type_name";
CmbPartyType.ValueMember = "party_type_id";
TxtRcptCode.Text = db.GetAccCode("RC", "mas_acc_receipt", "Receipt_code", DtpRcptDt.Value);
}
private void FrmExecutive_Load(object sender, EventArgs e)
{
cs.FrmStyle(this);
TxtID.Text = db.GetNewID("mas_executive", "executive_id").ToString();
if (LblPK.Text.Trim().Length > 0)
{
TxtID.Text = LblPK.Text;
DataSet ds = db.GetTableData("select * from mas_executive where executive_id=" + LblPK.Text);
if (ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
TxtName.Text = dr["executive_name"].ToString();
TxtRemarks.Text = dr["Remarks"].ToString();
}
}
}
}
private void FrmClient_Load(object sender, EventArgs e)
{
cs.FrmStyle(this);
db.TxtAutoFill(TxtName, "MAS_CLIENT", "CLIENT_NAME");
TxtCode.Text = db.GetNewID("Mas_CLIENT", "CLIENT_Code").ToString();
DataSet ds = db.GetTableData("Select state_code,state_name||'('||state_code||')' state_name, action_type from mas_gst_state");
CmbState.DataSource = ds.Tables[0];
CmbState.DisplayMember = "state_name";
CmbState.ValueMember = "state_code";
if (LblPK.Text.Trim().Length > 0)
{
ds = db.GetTableData("select * from mas_CLIENT where CLIENT_Code=" + LblPK.Text);
if (ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
TxtCode.Text = dr["CLIENT_Code"].ToString();
TxtName.Text = dr["CLIENT_Name"].ToString();
TxtComm.Text = dr["CLIENT_Commission"].ToString();
if (dr["active_flag"].ToString() == "Y")
{
ChkActive.Checked = true;
}
else
{
ChkActive.Checked = false;
}
DataSet ds1 = db.GetTableData("Select * from mas_gst_state where state_code=" + dr["state_code"].ToString());
foreach (DataRow dr1 in ds1.Tables[0].Rows)
{
CmbState.Text = dr1["State_name"].ToString() + "(" + dr1["State_Code"].ToString() + ")";
}
TxtGSTNo.Text = dr["Gst_No"].ToString();
TxtPANNo.Text = dr["PAN_No"].ToString();
TxtRemarks.Text = dr["Remarks"].ToString();
}
}
}
}
private void button1_Click(object sender, EventArgs e)
{
Cursor.Current = Cursors.WaitCursor;
Application.DoEvents();
string cnd = " Where 1=1 ";
if (checkBox1.Checked)
{
cnd = cnd + " And issue_month='01-" + DtpMnth.Value.ToString("MMM-yyyy") + "'";
}
if (checkBox2.Checked)
{
cnd = cnd + " And Issue_date='" + DtpIssueDate.Value.ToString("dd-MMM-yyyy") + "'";
}
if (TxtKeyword.Text.Trim().Length > 0)
{
cnd = cnd + " And to_char(issue_id)||voucher_no||to_char(sub_id)||state_name||district_Name||sub_name||address||pin_code||email_id||contact_no||to_char(current_issue) like '%" + TxtKeyword.Text.Trim().ToUpper() + "%'";
}
DataSet ds = db.GetTableData("select issue_id \"Book ID\", voucher_no \"Voucher No\", sub_id \"Subscription ID\", Sub_name \"Subcriber Name\", Address, State_name \"State\", District_name \"District\", Pin_Code \"PIN Code\",email_id \"E - Mail\", contact_no \"Contact No.\", to_char(issue_month,'MON-yy') \"Subscription Month\", to_char(issue_date,'dd-MON-yy') \"Dispatched Date\", current_issue \"Issued No\",SUB_TYPE_NAME \"Sub. Type\", EXECUTIVE_NAME \"Executive Name\", action_type from vew_labels " + cnd + " order by issue_id");
DgvList.DataSource = ds.Tables[0];
DgvList.Columns["action_type"].Visible = false;
DgvList.Refresh();
double qty = 0;
ds = db.GetTableData("select nvl(sum(quantity),0) qty, decode(action_type,'U','I',action_type) Action_type from vew_labels " + cnd + " group by decode(action_type,'U','I',action_type)");
foreach (DataRow dr in ds.Tables[0].Rows)
{
qty = Convert.ToDouble(dr[0].ToString());
}
label2.Text = "No. of Subscribers found : " + DgvList.Rows.Count.ToString() + " (No. of Books : " + qty.ToString() + ")";
Cursor.Current = Cursors.Default;
Application.DoEvents();
}
private void FrmSubBOS_Load(object sender, EventArgs e)
{
cs.FrmStyle(this);
DataSet ds = db.GetTableData("select max(invoice_date) Invoice_Date, action_type from HDR_ACC_BS_INVOICE group by action_type");
if (ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
DtpInvDt.MinDate = Convert.ToDateTime(dr["Invoice_Date"].ToString());
}
}
ds = db.GetTableData("Select max(invoice_code) Invoice_Code, action_type from hdr_acc_bs_invoice group by action_type");
if (ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
TxtLastInvoiceNo.Text = dr["Invoice_code"].ToString();
}
}
else
{
DateTime DtSysDate = DateTime.Parse(DateTime.Now.ToShortDateString());
int Dts = Convert.ToInt32(DtSysDate.Month.ToString() + DtSysDate.ToString("dd"));
string DtMon = DtSysDate.ToString("MM");
string FY = "";
if (Dts >= 101 && Dts <= 331)
{
FY = DtSysDate.AddYears(-1).ToString("yy") + "-" + DtSysDate.ToString("yy");
}
else
{
FY = DtSysDate.ToString("yy") + "-" + DtSysDate.AddYears(1).ToString("yy");
}
TxtLastInvoiceNo.Text = "IN/HBS/" + FY + "/00001";
}
}
private void button1_Click(object sender, EventArgs e)
{
if (TxtOPwd.Text.Trim().Length <= 0 || TxtNPwd.Text.Trim().Length <= 0 || TxtCPwd.Text.Trim().Length <= 0)
{
MessageBox.Show("All fields are mandatory", "Hindudharmam Magazine", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
string pwd = "";
DataSet ds = db.GetTableData("select * From mas_adm_user where user_name='" + TxtUserName.Text.Trim() + "'");
if (ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
pwd = dr["pwd"].ToString();
}
}
if (pwd != TxtOPwd.Text.Trim())
{
MessageBox.Show("Invalid Old Password", "Hindudharmam Magazine", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (TxtCPwd.Text.Trim() != TxtNPwd.Text.Trim())
{
MessageBox.Show("New Password and Confirm Password does not matched", "Hindudharmam Magazine", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (TxtNPwd.Text.Trim() == TxtOPwd.Text.Trim())
{
MessageBox.Show("Old Password and New Password is same", "Hindudharmam Magazine", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
string[] res = db.ExecuteQueries("Update mas_adm_user set pwd='" + TxtNPwd.Text.Trim() + "', modified_by='" + GlobalClass.UserName + "', modified_date=sysdate where user_name='" + TxtUserName.Text.Trim() + "'").Split(',');
if (res[0].ToString() != "0")
{
MessageBox.Show("Updation Failure..", "Hindudharmam Magazine", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
else
{
MessageBox.Show("Successfully Saved", "Hindudharmam Magazine", MessageBoxButtons.OK, MessageBoxIcon.Information);
TxtCPwd.Text = "";
TxtNPwd.Text = "";
TxtOPwd.Text = "";
TxtOPwd.Focus();
}
}
private void FrmCreateUser_Load(object sender, EventArgs e)
{
cs.FrmStyle(this);
if (LblPK.Text.Trim().Length > 0)
{
DataSet ds = db.GetTableData("select * from mas_adm_user where user_id=" + LblPK.Text.Trim());
if (ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
TxtUserName.Text = dr["user_name"].ToString();
TxtPwd.Text = dr["Pwd"].ToString();
if (dr["status"].ToString() == "U")
{
CmbStatus.Text = "Un-Lock";
}
else
{
CmbStatus.Text = "Lock";
}
}
}
}
}
private string vouchernumber()
{
string res = "1";
DataSet ds = db.GetTableData("Select max(voucher_no) vno, decode(spd.action_type,'U','I',spd.Action_type) Action_type from mas_sub_pay_details spd, mas_subscriptions ms where ms.sub_id (+)= spd.sub_id and state_id=" + db.ExtractCode(CmbState.Text) + " and district_id=" + db.ExtractCode(CmbDistrict.Text) + " group by decode(spd.action_type,'U','I',spd.Action_type)");
if (ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
try
{
int tt = Convert.ToInt32(dr["vno"].ToString());
tt++;
res = tt.ToString();
}
catch { }
}
}
return(res);
}
