/// <summary>
/// This method is used for setting the Session variable for userId and
/// after that filling the required dropdowns with database values
/// and also check accessing priviledges for particular user
/// and generate the next ID also.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Page_Load(object sender, System.EventArgs e)
{
try
{
string pass;
pass = (Session["User_Name"].ToString());
}
catch
{
Response.Redirect("../../Sysitem/ErrorPage.aspx", false);
return;
}
if (!IsPostBack)
{
ColumnName = "";
View = 0;
//GetNextID();
DBOperations.DBUtil dbobj = new DBOperations.DBUtil(System.Configuration.ConfigurationSettings.AppSettings["Servosms"], true);
#region Check Privileges
int i;
string View_flag = "0", Add_Flag = "0", Edit_Flag = "0", Del_Flag = "0";
string Module = "4";
string SubModule = "11";
string[,] Priv = (string[, ])Session["Privileges"];
for (i = 0; i < Priv.GetLength(0); i++)
{
if (Priv[i, 0] == Module && Priv[i, 1] == SubModule)
{
View_flag = Priv[i, 2];
Add_Flag = Priv[i, 3];
Edit_Flag = Priv[i, 4];
Del_Flag = Priv[i, 5];
break;
}
}
if (Add_Flag == "0" && Edit_Flag == "0" && View_flag == "0")
{
//string msg="UnAthourized Visit to Price Updation Page";
// dbobj.LogActivity(msg,Session["User_Name"].ToString());
Response.Redirect("../../Sysitem/AccessDeny.aspx", false);
return;
}
if (Add_Flag == "0")
{
btnSubmit.Enabled = false;
}
//if(Add_Flag =="0" && Edit_Flag == "0")
// Btnsubmit1.Enabled = false;
#endregion
#region Testing Purpose
InventoryClass obj = new InventoryClass();
SqlConnection con = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["Servosms"]);
ArrayList arrHeaderName = new ArrayList();
ArrayList arrColName = new ArrayList();
ArrayList arrRemName = new ArrayList();
object ob = null;
dbobj.ExecProc(DBOperations.OprType.Insert, "ProUpdateCustomerType", ref ob, "@Cust_ID", "");
SqlDataReader rdr = null;
SqlCommand cmd;
//rdr = obj.GetRecordSet("select distinct case when customertypename like 'oe%' then 'Oe' else customertypename end as customertypename from customertype order by customertypename");
rdr = obj.GetRecordSet("select distinct custtype,custtypeid from tempcustomertype order by custtypeid");
if (rdr.HasRows)
{
while (rdr.Read())
{
string name = rdr.GetValue(0).ToString().ToLower();
name = name.Replace("/", "");
name = name.Replace(" ", "");
name = name.Replace("-", "");
if (rdr.GetValue(0).ToString().ToLower().StartsWith("ro"))
{
//arrColName.Add(rdr.GetValue(0).ToString().ToLower()+"lube");
//arrColName.Add(rdr.GetValue(0).ToString().ToLower()+"2t4t");
arrColName.Add(name + "lube");
arrColName.Add(name + "2t4t");
}
else if (rdr.GetValue(0).ToString().ToLower().StartsWith("bazar") || rdr.GetValue(0).ToString().ToLower().StartsWith("bazzar"))
{
//arrColName.Add(rdr.GetValue(0).ToString().ToLower()+"lube");
//arrColName.Add(rdr.GetValue(0).ToString().ToLower()+"2t4t");
arrColName.Add(name + "lube");
arrColName.Add(name + "2t4t");
}
else
{
//arrColName.Add(rdr.GetValue(0).ToString().ToLower());
arrColName.Add(name);
}
}
}
rdr.Close();
rdr = obj.GetRecordSet("select * from ly_ps_sale");
int n = rdr.FieldCount;
//ColumnName="ly_ps_sales,month,tot_pur,pur_foc,gen_oil,grease,";
//for(int p=0,m=7;m<n;m++,p++)
for (int p = 0, m = 9; m < n; m++, p++)
{
arrHeaderName.Add(rdr.GetName(m));
//ColumnName+=rdr.GetName(m)+",";
}
//ColumnName=ColumnName.Substring(0,ColumnName.Length-1);
rdr.Close();
if (arrColName.Count == arrHeaderName.Count)
{
rdr = obj.GetRecordSet("select * from ly_ps_sale");
n = rdr.FieldCount;
ColumnName = "ly_ps_sales,discription,month,tot_pur,pur_foc,gen_oil,grease,total_purchase,";
for (int p = 0, m = 9; m < n; m++, p++)
{
ColumnName += rdr.GetName(m) + ",";
}
ColumnName += "total_sales,";
ColumnName = ColumnName.Substring(0, ColumnName.Length - 1);
rdr.Close();
return;
}
if (arrColName.Count >= arrHeaderName.Count)
{
for (int r = 0; r < arrColName.Count; r++)
{
arrRemName.Add(arrColName[r]);
}
for (int q = 0; q < arrHeaderName.Count; q++)
{
arrRemName.Remove(arrHeaderName[q]);
}
if (arrRemName.Count > 0)
{
for (int k = 0; k < arrRemName.Count; k++)
{
con.Open();
string name = arrRemName[k].ToString();
name = name.Replace("/", "");
name = name.Replace(" ", "");
name = name.Replace("-", "");
string str = "alter table ly_ps_sale add " + name + " float";
cmd = new SqlCommand(str, con);
cmd.ExecuteNonQuery();
cmd.Dispose();
con.Close();
}
}
}
else
{
for (int q = 0; q < arrHeaderName.Count; q++)
{
arrRemName.Add(arrHeaderName[q]);
}
for (int q = 0; q < arrColName.Count; q++)
{
arrRemName.Remove(arrColName[q]);
}
if (arrRemName.Count > 0)
{
for (int k = 0; k < arrRemName.Count; k++)
{
con.Open();
string name = arrRemName[k].ToString();
name = name.Replace("/", "");
name = name.Replace(" ", "");
name = name.Replace("-", "");
string str = "alter table ly_ps_sale drop column " + name + "";
cmd = new SqlCommand(str, con);
cmd.ExecuteNonQuery();
cmd.Dispose();
con.Close();
}
}
}
rdr = obj.GetRecordSet("select * from ly_ps_sale");
n = rdr.FieldCount;
ColumnName = "ly_ps_sales,discription,month,tot_pur,pur_foc,gen_oil,grease,total_purchase,";
for (int p = 0, m = 9; m < n; m++, p++)
{
ColumnName += rdr.GetName(m) + ",";
}
ColumnName += "total_sales,";
ColumnName = ColumnName.Substring(0, ColumnName.Length - 1);
rdr.Close();
#endregion
}
}
/// <summary>
/// This method is used to update the all Ledger balance of AccountsLedgerTable and CustomerLedgerTable in sequencialy.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnUpdate_Click(object sender, System.EventArgs e)
{
string Cust_ID = "0";
string Ledger_ID = "0";
try
{
if (chkCustBal.Checked != false || chkStock.Checked != false)
{
InventoryClass obj = new InventoryClass();
InventoryClass obj1 = new InventoryClass();
SqlCommand cmd;
int Flag = 0;
string str = "", str1 = "";
SqlConnection Con = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["Servosms"]);
SqlDataReader rdr1 = null, rdr = null;
if (chkStock.Checked)
{
str = "select Prod_ID from Products";
rdr = obj.GetRecordSet(str);
while (rdr.Read())
{
str1 = "select * from Stock_Master where Productid='" + rdr["Prod_ID"].ToString() + "' order by Stock_date";
rdr1 = obj1.GetRecordSet(str1);
double OS = 0, CS = 0, k = 0;
while (rdr1.Read())
{
Flag = 1;
if (k == 0)
{
OS = double.Parse(rdr1["opening_stock"].ToString());
k++;
}
else
{
OS = CS;
}
CS = OS + double.Parse(rdr1["receipt"].ToString()) - (double.Parse(rdr1["sales"].ToString()) + double.Parse(rdr1["salesfoc"].ToString()));
Con.Open();
cmd = new SqlCommand("update Stock_Master set opening_stock='" + OS.ToString() + "', Closing_Stock='" + CS.ToString() + "' where ProductID='" + rdr1["Productid"].ToString() + "' and Stock_Date=Convert(datetime,'" + GenUtil.str2DDMMYYYY(rdr1["stock_date"].ToString()) + "',103)", Con);
cmd.ExecuteNonQuery();
cmd.Dispose();
Con.Close();
}
rdr1.Close();
}
rdr.Close();
}
if (chkCustBal.Checked)
{
/*16.4.2013 str1 = "select Ledger_ID from Ledger_Master";
* rdr1 = obj1.GetRecordSet(str1);
* while(rdr1.Read())
* {
* str = "select * from AccountsLedgerTable where Ledger_ID='"+rdr1["Ledger_ID"].ToString()+"' order by entry_date";
* rdr = obj.GetRecordSet(str);
* double Bal=0;
* string BalType="";
* int i=0;
* Flag=1;
* while(rdr.Read())
* {
* if(i==0)
* {
* BalType=rdr["Bal_Type"].ToString();
* i++;
* }
* if(double.Parse(rdr["Credit_Amount"].ToString())!=0)
* {
* if(BalType=="Cr")
* {
* Bal+=double.Parse(rdr["Credit_Amount"].ToString());
* BalType="Cr";
* }
* else
* {
* Bal-=double.Parse(rdr["Credit_Amount"].ToString());
* if(Bal<0)
* {
* Bal=double.Parse(Bal.ToString().Substring(1));
* BalType="Cr";
* }
* else
* BalType="Dr";
* }
* }
* else if(double.Parse(rdr["Debit_Amount"].ToString())!=0)
* {
* if(BalType=="Dr")
* Bal+=double.Parse(rdr["Debit_Amount"].ToString());
* else
* {
* Bal-=double.Parse(rdr["Debit_Amount"].ToString());
* if(Bal<0)
* {
* Bal=double.Parse(Bal.ToString().Substring(1));
* BalType="Dr";
* }
* else
* BalType="Cr";
* }
* }
* Con.Open();
* cmd = new SqlCommand("update AccountsLedgerTable set Balance='"+Bal.ToString()+"',Bal_Type='"+BalType+"' where Ledger_ID='"+rdr["Ledger_ID"].ToString()+"' and Particulars='"+rdr["Particulars"].ToString()+"' ",Con);
* cmd.ExecuteNonQuery();
* cmd.Dispose();
* Con.Close();
* }
* rdr.Close();
* }
* rdr1.Close();
*
* str1 = "select Cust_ID from Customer";
* rdr1 = obj1.GetRecordSet(str1);
* while(rdr1.Read())
* {
* //dbobj.SelectQuery("select * from CustomerLedgerTable where CustID='"+rdr1["Cust_ID"].ToString()+"' order by entrydate",ref rdr);
* str = "select * from CustomerLedgerTable where CustID='"+rdr1["Cust_ID"].ToString()+"' order by entrydate";
* rdr = obj.GetRecordSet(str);
* double Bal=0;
* string BalType="";
* int i=0;
* while(rdr.Read())
* {
* if(i==0)
* {
* BalType=rdr["BalanceType"].ToString();
* i++;
* }
* if(double.Parse(rdr["CreditAmount"].ToString())!=0)
* {
* if(BalType=="Cr.")
* {
* Bal+=double.Parse(rdr["CreditAmount"].ToString());
* BalType="Cr.";
* }
* else
* {
* Bal-=double.Parse(rdr["CreditAmount"].ToString());
* if(Bal<0)
* {
* Bal=double.Parse(Bal.ToString().Substring(1));
* BalType="Cr.";
* }
* else
* BalType="Dr.";
* }
* }
* else if(double.Parse(rdr["DebitAmount"].ToString())!=0)
* {
* if(BalType=="Dr.")
* Bal+=double.Parse(rdr["DebitAmount"].ToString());
* else
* {
* Bal-=double.Parse(rdr["DebitAmount"].ToString());
* if(Bal<0)
* {
* Bal=double.Parse(Bal.ToString().Substring(1));
* BalType="Dr.";
* }
* else
* BalType="Cr.";
* }
* }
* Con.Open();
* cmd = new SqlCommand("update CustomerLedgerTable set Balance='"+Bal.ToString()+"',BalanceType='"+BalType+"' where CustID='"+rdr["CustID"].ToString()+"' and Particular='"+rdr["Particular"].ToString()+"' ",Con);
* cmd.ExecuteNonQuery();
* cmd.Dispose();
* Con.Close();
* }
* rdr.Close();
* }
* rdr1.Close(); End*/
/******************Add by vikas 6.3.2013**********************/
object op = null;
int x = 0;
System.Data.SqlClient.SqlDataReader rd = null;
string sql1 = "";
str1 = "select * from Ledger_Master order by ledger_id";
rdr1 = obj1.GetRecordSet(str1);
while (rdr1.Read())
{
str = "select * from AccountsLedgerTable where Ledger_ID='" + rdr1["Ledger_ID"].ToString() + "' and particulars='Opening Balance'";
rdr = obj.GetRecordSet(str);
double Bal = 0;
string BalType = "";
int i = 0;
Flag = 1;
while (rdr.Read())
{
try
{
Ledger_ID = rdr1["Ledger_ID"].ToString() + " : " + rdr1["Ledger_Name"].ToString();
BalType = rdr["Bal_Type"].ToString();
if (BalType == "Cr")
{
Bal = double.Parse(rdr["Credit_Amount"].ToString());
}
else if (BalType == "Dr")
{
Bal = double.Parse(rdr["Debit_Amount"].ToString());
}
dbobj.ExecProc(OprType.Update, "UpdateAccountsLedger", ref op, "@Ledger_ID", rdr["Ledger_ID"].ToString(), "@Amount", Bal.ToString(), "@Type", BalType.ToString());
}
catch (Exception ex)
{
//MessageBox.Show("Ledger Name : "+Ledger_ID.ToString());
}
}
rdr.Close();
}
rdr1.Close();
/*sql1="select Ledger_ID from Ledger_Master";
* dbobj.SelectQuery(sql1,ref rd);
* while(rd.Read())
* dbobj.ExecProc(OprType.Insert,"UpdateAccountsLedger ",ref op,"@Ledger_ID",rd["Ledger_ID"].ToString(),"@Amount","0","@Type","Dr");
* //exec UpdateAccountsLedger @Ledger_ID,@Op_Balance,@Balance_Type
* rdr.Close();*/
str1 = "select * from Customer";
rdr1 = obj1.GetRecordSet(str1);
while (rdr1.Read())
{
Cust_ID = rdr1["Cust_ID"].ToString() + ":" + rdr1["Cust_Name"].ToString();
str = "select * from CustomerLedgerTable where CustID='" + rdr1["Cust_ID"].ToString() + "' and particular='Opening Balance'";
rdr = obj.GetRecordSet(str);
double Bal = 0;
string BalType = "";
int i = 0;
while (rdr.Read())
{
try
{
BalType = rdr["BalanceType"].ToString();
if (BalType == "Cr.")
{
Bal = double.Parse(rdr["CreditAmount"].ToString());
}
else if (BalType == "Dr.")
{
Bal = double.Parse(rdr["DebitAmount"].ToString());
}
dbobj.ExecProc(OprType.Update, "UpdateBalance", ref op, "@Cust_ID", rdr["custid"].ToString(), "@Balance", Bal.ToString(), "@Bal_Type", BalType.ToString());
}
catch (Exception ex)
{
//MessageBox.Show("Cust Name : "+Cust_ID.ToString());
}
//exec UpdateBalance @Cust_ID,@Op_Balance,@Balance_Type
}
rdr.Close();
}
rdr1.Close();
//sql1="select * from Customer order by cust_name,city,cust_type";
//dbobj.SelectQuery(sql1,ref rd);
//while(rd.Read())
// dbobj.ExecProc(OprType.Insert,"UpdateBalance",ref op,"@Cust_Name",rd["cust_Name"].ToString(),"@Place",rd["City"].ToString(),"@Cr_Plus","0","@Dr_Plus","0");
//exec UpdateBalance @Cust_ID,@Op_Balance,@Balance_Type
//rdr.Close();
Flag = 1;
/******************Add by vikas **********************/
}
if (Flag == 1)
{
MessageBox.Show("Stock Variation Updated Successfully");
chkCustBal.Checked = false;
chkStock.Checked = false;
}
}
else
{
MessageBox.Show("Please Select Atleast One CheckBox");
}
CreateLogFiles.ErrorLog("Form:ModuleManage.aspx,Class:InventoryClass.cs,Method:btnUpdate_Click, User_ID: " + uid);
}
catch (Exception ex)
{
//MessageBox.Show("Ledger ID : "+Ledger_ID.ToString()+"Cust ID : "+Cust_ID.ToString());
CreateLogFiles.ErrorLog("Form:ModuleManage.aspx,Class:InventoryClass.cs,Method:btnUpdate_Click, Exception : " + ex.Message + " User_ID: " + uid);
}
}
/// <summary>
/// This method is used to save the record.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnSave_Click(object sender, System.EventArgs e)
{
try
{
SqlDataReader SqlDtr = null;
string SubGrp = "";
string Group = "";
if (!checkAcc_Period())
{
MessageBox.Show("Please enter the Accounts Period from Organization Details ");
fetchGroup();
return;
}
dbobj.SelectQuery("select * from ledger_master where ledger_name='" + TxtLedger.Text + "'", ref SqlDtr);
if (SqlDtr.HasRows)
{
MessageBox.Show("Ledger Name '" + TxtLedger.Text + "' is Allready Exist");
return;
}
SqlDtr.Close();
if (DropSub.SelectedItem.Text.Equals("Cash in hand"))
{
dbobj.SelectQuery("select count(*) from ledger_master where sub_grp_id=118", ref SqlDtr);
if (SqlDtr.Read())
{
if (int.Parse(SqlDtr.GetValue(0).ToString()) > 0)
{
MessageBox.Show("Cash Account Allready Exist");
return;
}
}
SqlDtr.Close();
}
// Check & Fetch The sub Group
if (DropSub.SelectedIndex == 0)
{
MessageBox.Show("Please select Sub Group");
fetchGroup();
return;
}
else
{
if (DropSub.SelectedItem.Text == "Other")
{
if (TxtSub.Text.Trim() == "")
{
MessageBox.Show("Please specify Other Sub Group");
TxtSub.Enabled = true;
fetchGroup();
return;
}
else
{
SubGrp = TxtSub.Text.Trim();
}
}
else
{
SubGrp = DropSub.SelectedItem.Text.Trim();
}
}
// Check & Fetch The Group
if (txtTempGrp.Value == "Select")
{
MessageBox.Show("Please select Group");
fetchGroup();
return;
}
else
{
if (txtTempGrp.Value == "Other")
{
if (TxtGroup.Text.Trim() == "")
{
MessageBox.Show("Please specify Other Group");
TxtGroup.Enabled = true;
fetchGroup();
DropGroup.SelectedIndex = DropGroup.Items.IndexOf(DropGroup.Items.FindByText(txtTempGrp.Value));
return;
}
else
{
Group = TxtGroup.Text.Trim();
}
}
else
{
Group = txtTempGrp.Value;
}
}
string ledgname = "";
string nature = "";
double Op_bal = 0;
string bal_type = "";
ledgname = TxtLedger.Text.Trim();
string op_bal = "0";
if (TxtOpeningBal.Text.Trim() != "")
{
op_bal = TxtOpeningBal.Text.Trim();
}
Op_bal = System.Convert.ToDouble(op_bal);
bal_type = DropBalType.SelectedItem.Text.Trim();
if (RadioAsset.Checked)
{
nature = "Assets";
}
else if (RadioLiab.Checked)
{
nature = "Liabilities";
}
else if (RadioExp.Checked)
{
nature = "Expenses";
}
else
{
nature = "Income";
}
int subgrpid = 0;
// Get the sub_grp_id for selected Sub_Group Name and Nature Of Payment
dbobj.SelectQuery("select sub_grp_Id from Ledger_Master_Sub_grp where sub_grp_name = '" + SubGrp + "' and Nature_Of_group ='" + nature + "' and grp_id = (select top 1 grp_id from mgroup where grp_name = '" + Group + "')", ref SqlDtr);
if (SqlDtr.Read())
{
subgrpid = System.Convert.ToInt32(SqlDtr["sub_grp_id"].ToString());
}
SqlDtr.Close();
int count = 0;
// check the Ledger Name is already present for the selected sub group
dbobj.ExecuteScalar("Select count(Ledger_ID) from Ledger_Master where Ledger_Name = '" + ledgname + "' and Sub_grp_ID = " + subgrpid, ref count);
if (count > 0)
{
MessageBox.Show("Ledger Name is already exist for selected Sub Group ");
fetchGroup();
DropGroup.SelectedIndex = DropGroup.Items.IndexOf(DropGroup.Items.FindByText(Group));
return;
}
else
{
object op = null;
dbobj.ExecProc(OprType.Insert, "ProInsertLedger", ref op, "@Ledger_Name", ledgname, "@SubGrp_Name", SubGrp, "@Group_Name", Group, "@Grp_Nature", nature, "@Op_Bal", Op_bal, "@Bal_Type", bal_type);
}
MessageBox.Show("Ledger Saved");
CreateLogFiles.ErrorLog("Form:Ledger Creation,Method:btnSave_Click New Ledger of name " + ledgname + " Saved. User: "******"Form:Ledger Creation,Method:btnSave_Click Exception: " + ex.Message + " User: " + uid);
}
}