protected void btnApprove_Click(object sender, EventArgs e)
{
try
{
vdm = new VehicleDBMgr();
DateTime ServerDateCurrentdate = VehicleDBMgr.GetTime(vdm.conn);
string empid = Session["UserSno"].ToString();
string buttontext = btnApprove.Text;
if (buttontext == "Approval")
{
// cmd = new MySqlCommand("UPDATE incentive_structure SET ApprovalStatus = 'A' WHERE (sno = @incentivesno)");
cmd = new MySqlCommand("UPDATE incentive_structure SET ApprovalStatus = 'A', ApprovedDate = @dt, ApprovedBy = @empid WHERE (sno = @incentivesno)");
cmd.Parameters.AddWithValue("@incentivesno", ddlIncentiveStructure.SelectedValue);
cmd.Parameters.AddWithValue("@dt", ServerDateCurrentdate);
cmd.Parameters.AddWithValue("@empid", empid);
vdm.Update(cmd);
lblmsg.Text = "Selected Structure Aprroved";
}
if (buttontext == "Disable")
{
cmd = new MySqlCommand("UPDATE incentive_structure SET ApprovalStatus = 'P', ApprovedDate = @dt, ApprovedBy = @empid WHERE (sno = @incentivesno)");
cmd.Parameters.AddWithValue("@incentivesno", ddlIncentiveStructure.SelectedValue);
cmd.Parameters.AddWithValue("@dt", ServerDateCurrentdate);
cmd.Parameters.AddWithValue("@empid", empid);
vdm.Update(cmd);
lblmsg.Text = "Selected Structure Aprroval Cancelled";
}
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
}
}
protected void btnSubmitt_Click(object sender, EventArgs e)
{
try
{
if (Session["field1"] != null)
{
lblError.Text = "";
UserName = Session["field1"].ToString();
vdm = new VehicleDBMgr();
vdm.InitializeDB();
cmd = new MySqlCommand("SELECT pwd FROM loginstable WHERE loginid = @UN");
cmd.Parameters.Add("@UN", UserName);
DataTable dt = vdm.SelectQuery(cmd).Tables[0];//"ManageData", "UserName", new string[] { "UserName=@UserName" }, new string[] { UserName }, new string[] { "" }).Tables[0];
vdm = null;
if (dt.Rows.Count > 0)
{
string password = dt.Rows[0]["pwd"].ToString();
if (password == txtOldPassWord.Text)
{
if (txtNewPassWord.Text == txtConformPassWord.Text)
{
txtNewPassWord.Text = txtConformPassWord.Text;
vdm = new VehicleDBMgr();
vdm.InitializeDB();
cmd = new MySqlCommand("Update loginstable set pwd=@pwd where loginid=@loginid ");
cmd.Parameters.Add("@loginid", UserName);
cmd.Parameters.Add("@pwd", txtNewPassWord.Text.Trim());
vdm.Update(cmd);
vdm = null;
lblMessage.Text = "Your Password has been Changed successfully";
}
else
{
lblError.Text = "Conform password not match";
}
}
else
{
lblError.Text = "Entered password is incorrect";
}
}
else
{
lblError.Text = "Entered username is incorrect";
}
}
else
{
Response.Redirect("Login.aspx");
}
}
catch (Exception ex)
{
lblError.Text = "Password Changed Failed";
}
}
protected void btnSave_OnClick(object sender, EventArgs e)
{
try
{
cmd = new MySqlCommand("SELECT sno, frombranchsno, tobranchsno, kms, expectedtime FROM locatondistances where username=@username");
cmd.Parameters.Add("@username", UserName);
DataTable branchdata = vdm.SelectQuery(cmd).Tables[0];
if (branchdata.Rows.Count > 0)
{
for (int i = 0; i < GridView1.Rows.Count; i++)
{
string frombranchsno = ((TextBox)GridView1.Rows[i].FindControl("hbnFrombranch")).Text.Trim();
string tobranchsno = ((TextBox)GridView1.Rows[i].FindControl("hbnTobranch")).Text.Trim();
string txttime = ((TextBox)GridView1.Rows[i].FindControl("txttime")).Text.Trim();
DataRow[] distancerow = branchdata.Select("frombranchsno=" + frombranchsno + " and tobranchsno=" + tobranchsno + "");
if (distancerow.Length > 0)
{
cmd = new MySqlCommand("update locatondistances set expectedtime=@expectedtime where frombranchsno=@frombranchsno and tobranchsno=@tobranchsno and username=@username");
cmd.Parameters.Add("@frombranchsno", frombranchsno);
cmd.Parameters.Add("@tobranchsno", tobranchsno);
cmd.Parameters.Add("@expectedtime", txttime);
cmd.Parameters.Add("@username", UserName);
vdm.Update(cmd);
}
else
{
cmd = new MySqlCommand("insert into locatondistances (frombranchsno, tobranchsno,expectedtime,username) values (@frombranchsno, @tobranchsno,@expectedtime,@username)");
cmd.Parameters.Add("@frombranchsno", frombranchsno);
cmd.Parameters.Add("@tobranchsno", tobranchsno);
cmd.Parameters.Add("@expectedtime", txttime);
cmd.Parameters.Add("@username", UserName);
vdm.insert(cmd);
}
}
lblmsg.Text = "Data Updated Successfully";
}
else
{
for (int i = 0; i < GridView1.Rows.Count; i++)
{
string frombranchsno = ((TextBox)GridView1.Rows[i].FindControl("hbnFrombranch")).Text.Trim();
string tobranchsno = ((TextBox)GridView1.Rows[i].FindControl("hbnTobranch")).Text.Trim();
string txttime = ((TextBox)GridView1.Rows[i].FindControl("txttime")).Text.Trim();
cmd = new MySqlCommand("insert into locatondistances (frombranchsno, tobranchsno,expectedtime,username) values (@frombranchsno, @tobranchsno,@expectedtime,@username)");
cmd.Parameters.Add("@frombranchsno", frombranchsno);
cmd.Parameters.Add("@tobranchsno", tobranchsno);
cmd.Parameters.Add("@expectedtime", txttime);
cmd.Parameters.Add("@username", UserName);
vdm.insert(cmd);
}
lblmsg.Text = "Data Saved Successfully";
}
}
catch
{
}
}
protected void btn_DeiselVal_Add_Click(object sender, EventArgs e)
{
try
{
DateTime fromdate = DateTime.Now;
string[] datestrig = txtDate.Text.Split(' ');
if (datestrig.Length > 1)
{
if (datestrig[0].Split('/').Length > 0)
{
string[] dates = datestrig[0].Split('/');
string[] times = datestrig[1].Split(':');
fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
if (btn_DeiselVal_Add.Text == "Add")
{
cmd = new MySqlCommand("insert into inventory (VehicleNo,PartNo,PartName,Qty,UnitPrice,TotalAmount,Datetime,UserName)values (@VehicleNo,@PartNo,@PartName,@Qty,@UnitPrice,@TotalAmount,@Datetime,@UserName)");
cmd.Parameters.Add("@VehicleNo", ddlVehicleNo.SelectedValue);
cmd.Parameters.Add("@PartNo", txtPartNo.Text.Trim());
cmd.Parameters.Add("@PartName", ddlPartName.SelectedValue);
cmd.Parameters.Add("@Qty", txtQty.Text.Trim());
cmd.Parameters.Add("@UnitPrice", txtUnitPrice.Text.Trim());
cmd.Parameters.Add("@TotalAmount", txtTotalAmount.Text.Trim());
// cmd.Parameters.Add("@Datetime", txtDate.Text.Trim());
cmd.Parameters.Add("@Datetime", fromdate);
cmd.Parameters.Add("@UserName", UserName);
vdm.insert(cmd);
Refresh();
UpdateDeisel();
lblStatus.Text = "Record added Successfully";
}
else
{
cmd = new MySqlCommand("update inventory set VehicleNo=@VehicleNo,PartNo=@PartNo,PartName=@PartName,Qty=@Qty,UnitPrice=@UnitPrice,TotalAmount=@TotalAmount,Datetime=@Datetime where UserName=UserName and Sno=@Sno");
cmd.Parameters.Add("@VehicleNo", ddlVehicleNo.SelectedValue);
cmd.Parameters.Add("@PartNo", txtPartNo.Text.Trim());
cmd.Parameters.Add("@PartName", ddlPartName.SelectedValue);
cmd.Parameters.Add("@Qty", txtQty.Text.Trim());
cmd.Parameters.Add("@UnitPrice", txtUnitPrice.Text.Trim());
cmd.Parameters.Add("@TotalAmount", txtTotalAmount.Text.Trim());
cmd.Parameters.Add("@Datetime", fromdate);
cmd.Parameters.Add("@UserName", UserName);
cmd.Parameters.Add("@Sno", Sno);
vdm.Update(cmd);
Refresh();
UpdateDeisel();
btn_DeiselVal_Add.Text = "Add";
lblStatus.Text = "Record Updated Successfully";
}
}
catch
{
}
}
protected void BtnSave_Click(object sender, EventArgs e)
{
try
{
vdm = new VehicleDBMgr();
DateTime CreateDate = VehicleDBMgr.GetTime(vdm.conn);
DateTime fromdate = DateTime.Now;
DataTable dt = (DataTable)Session["xportdata"];
int i = 1;
foreach (DataRow dr in dt.Rows)
{
string Ledgername = dr["Ledger Name"].ToString();
string Ledgercode = dr["Ledger Code"].ToString();
if (Ledgername == "" || Ledgercode == "")
{
}
else
{
sqlcmd = new SqlCommand("SELECT sno FROM headofaccounts_master WHERE ((accountname = @accountname) OR (accountcode = @accountcode))");
sqlcmd.Parameters.Add("@accountcode", dr["Ledger Code"].ToString());
sqlcmd.Parameters.Add("@accountname", dr["Ledger Name"].ToString());
DataTable dtledgers = vdm.SelectQuery(sqlcmd).Tables[0];
if (dtledgers.Rows.Count > 0)
{
string sno = dtledgers.Rows[0]["sno"].ToString();
sqlcmd = new SqlCommand("update headofaccounts_master set accountname=@accountname,accountcode=@accountcode where sno=@sno");
sqlcmd.Parameters.Add("@accountcode", dr["Ledger Code"].ToString());
sqlcmd.Parameters.Add("@accountname", dr["Ledger Name"].ToString());
sqlcmd.Parameters.Add("@doe", fromdate);
sqlcmd.Parameters.Add("@sno", sno);
vdm.Update(sqlcmd);
}
else
{
sqlcmd = new SqlCommand("Insert into headofaccounts_master (accountname, accountcode,doe) values (@accountname, @accountcode,@doe)");
sqlcmd.Parameters.Add("@accountcode", dr["Ledger Code"].ToString());
sqlcmd.Parameters.Add("@accountname", dr["Ledger Name"].ToString());
sqlcmd.Parameters.Add("@doe", fromdate);
vdm.insert(sqlcmd);
}
}
}
pnlHide.Visible = false;
DataTable dtempty = new DataTable();
grdReports.DataSource = dtempty;
grdReports.DataBind();
lbl_msg.Text = "Successfully Saved";
}
catch (Exception ex)
{
lbl_msg.Text = ex.ToString();
}
}
protected void btn_WIDB_Click(object sender, EventArgs e)
{
try
{
vdm = new VehicleDBMgr();
////SAPdbmanger SAPvdm = new SAPdbmanger();
DateTime ServerDateCurrentdate = VehicleDBMgr.GetTime(vdm.conn);
DataTable dt = (DataTable)Session["dtImport"];
foreach (DataRow dr in dt.Rows)
{
////////string ledger_code = dr["BP Code"].ToString();
////////string ledgername = dr["BP Name"].ToString();
////////cmd = new MySqlCommand("Update vehicel_master set ledger_code=@ledger_code where ledgername=@ledgername");
////////cmd.Parameters.Add("@ledger_code", ledger_code);
////////cmd.Parameters.Add("@ledgername", ledgername);
////////vdm.Update(cmd);
////////cmd = new MySqlCommand("Update vehicel_master set Petroledger_code=@Petroledger_code where petroledgername=@petroledgername");
////////cmd.Parameters.Add("@Petroledger_code", ledger_code);
////////cmd.Parameters.Add("@petroledgername", ledgername);
////////vdm.Update(cmd);
////////cmd = new MySqlCommand("Update termloanentry set ledger_code=@ledger_code where ledgername=@ledgername");
////////cmd.Parameters.Add("@ledger_code", ledger_code);
////////cmd.Parameters.Add("@ledgername", ledgername);
////////vdm.Update(cmd);
string sno = dr["sno"].ToString();
string seriesno = dr["newno"].ToString();
cmd = new MySqlCommand("Update new_tyres_sub set seriesno=@seriesno where sno=@sno");
cmd.Parameters.Add("@seriesno", seriesno);
cmd.Parameters.Add("@sno", sno);
vdm.Update(cmd);
}
lblmsg.Text = "Saved";
}
catch (Exception ex)
{
if (ex.Message == "Object reference not set to an instance of an object.")
{
lblmsg.Text = "Session Expired";
Response.Redirect("Login.aspx");
}
else
{
lblmsg.Text = ex.Message;
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
DateTime Currentdate = VehicleDBMgr.GetTime(vdm.conn);
Session["UserName"] = null;
Session["userdata_sno"] = null;
Session["Owner"] = null;
Session["LevelType"] = null;
Session["Plant"] = null;
Session["SalesOffice"] = null;
Session["Distributors"] = null;
Session["getcategorynames"] = null;
Session["getsalesofficenames"] = null;
Session["getbranchcategorynames"] = null;
Session["getproductsnames"] = null;
Session["getsubregionsnames"] = null;
Session["getsalesofficenames"] = null;
Session["getbranchnames"] = null;
Session["getrates_categorynames"] = null;
Session["getrates_productsnames"] = null;
Session["getrates_subregion_subregionsnames"] = null;
Session["getrates_subregions_categorynames"] = null;
Session["getrates_subregions_productsnames"] = null;
Session["getrates_routes_subregionsnames"] = null;
Session["getrates_routes_categorynames"] = null;
Session["getrates_routes_productsnames"] = null;
Session["getrates_routes_routename"] = null;
Session["Branches"] = null;
Session["PlantSno"] = null;
Session["SalesoffSno"] = null;
Session["branchSno"] = null;
if (Session["EmpSno"] != null)
{
string sno = Session["EmpSno"].ToString();
cmd = new MySqlCommand("Select max(sno) as transno from logininfo where UserId=@userid");
cmd.Parameters.AddWithValue("@userid", sno);
DataTable dttime = vdm.SelectQuery(cmd).Tables[0];
if (dttime.Rows.Count > 0)
{
string transno = dttime.Rows[0]["transno"].ToString();
cmd = new MySqlCommand("UPDATE logininfo set logouttime=@logouttime,status=@status where sno=@sno");
cmd.Parameters.AddWithValue("@logouttime", Currentdate);
cmd.Parameters.AddWithValue("@status", "0");
cmd.Parameters.AddWithValue("@sno", transno);
vdm.Update(cmd);
}
}
Session.Abandon();
Response.Redirect("login.aspx");
}
protected void btn_WIDB_Click(object sender, EventArgs e)
{
try
{
vdm = new VehicleDBMgr();
DateTime ServerDateCurrentdate = VehicleDBMgr.GetTime(vdm.conn);
DataTable dt = (DataTable)Session["dtImport"];
int i = 0;
foreach (DataRow dr in dt.Rows)
{
string AgentCode = dr["Agent Code"].ToString();
int j = 3;
foreach (DataColumn dc in dt.Columns)
{
var cell = dc.ColumnName;
if (cell == "SNo" || cell == "Agent Code" || cell == "Agent Name")
{
}
else
{
cmd = new MySqlCommand("Select sno from invmaster where InvName=@InvName");
cmd.Parameters.AddWithValue("@InvName", dc.ColumnName);
DataTable dtProduct = vdm.SelectQuery(cmd).Tables[0];
string invsno = dtProduct.Rows[0]["Sno"].ToString();
string Qty = dt.Rows[i][j].ToString();
cmd = new MySqlCommand("update inventory_monitor set Qty=@Qty where BranchId=@BranchId and Inv_Sno=@Inv_Sno");
cmd.Parameters.AddWithValue("@Qty", Qty);
cmd.Parameters.AddWithValue("@BranchId", AgentCode);
cmd.Parameters.AddWithValue("@Inv_Sno", invsno);
vdm.Update(cmd);
}
j++;
}
i++;
}
lblmsg.Text = "Updated Successfully";
}
catch (Exception ex)
{
if (ex.Message == "Object reference not set to an instance of an object.")
{
lblmsg.Text = "Session Expired";
Response.Redirect("Login.aspx");
}
else
{
lblmsg.Text = ex.Message;
}
}
}
protected void sessionsclick_click(object sender, EventArgs e)
{
try
{
string username = lbl_username.Text.ToString();
string password = lbl_passwords.Text.ToString();
cmd = new SqlCommand("update employe_login set loginstatus=@log where username=@username and passward=@passward");
cmd.Parameters.Add("@log", "0");
cmd.Parameters.Add("@username", username);
cmd.Parameters.Add("@passward", password);
vdm.Update(cmd);
DateTime ServerDateCurrentdate = VehicleDBMgr.GetTime(vdm.conn);
cmd = new SqlCommand("SELECT sno FROM employe_login where username=@username and passward=@passward");
cmd.Parameters.Add("@username", username);
cmd.Parameters.Add("@passward", password);
DataTable dtEMP = vdm.SelectQuery(cmd).Tables[0];
if (dtEMP.Rows.Count > 0)
{
string empid = dtEMP.Rows[0]["sno"].ToString();
cmd = new SqlCommand("Select max(sno) as transno from logininfo where userid=@userid");
cmd.Parameters.Add("@userid", empid);
DataTable dttime = vdm.SelectQuery(cmd).Tables[0];
if (dttime.Rows.Count > 0)
{
string transno = dttime.Rows[0]["transno"].ToString();
cmd = new SqlCommand("UPDATE logininfo set logouttime=@logouttime where sno=@sno");
cmd.Parameters.Add("@logouttime", ServerDateCurrentdate);
cmd.Parameters.Add("@sno", transno);
vdm.Update(cmd);
}
}
this.AlertBox.Visible = false;
}
catch
{
}
}
protected void Page_Load(object sender, EventArgs e)
{
try
{
string sno = Session["Employ_Sno"].ToString();
cmd = new MySqlCommand("update employdata set loginstatus=@log where emp_sno=@sno");
cmd.Parameters.Add("@log", "0");
cmd.Parameters.Add("@sno", sno);
vdm.Update(cmd);
DateTime ServerDateCurrentdate = VehicleDBMgr.GetTime(vdm.conn);
cmd = new MySqlCommand("Select max(sno) as transno from login_info where empid=@userid");
cmd.Parameters.Add("@userid", Session["Employ_Sno"]);
cmd.Parameters.Add("@UserName", Session["UserName"]);
DataTable dttime = vdm.SelectQuery(cmd).Tables[0];
if (dttime.Rows.Count > 0)
{
string transno = dttime.Rows[0]["transno"].ToString();
cmd = new MySqlCommand("UPDATE login_info set logouttime=@logouttime where sno=@sno");
cmd.Parameters.Add("@logouttime", ServerDateCurrentdate);
cmd.Parameters.Add("@sno", transno);
vdm.Update(cmd);
}
Session.Clear();
Session.RemoveAll();
Session.Abandon();
// window.localStorage.clear();
//ClearCache();
//clearchachelocalall();
Response.Redirect("Default.aspx");
}
catch
{
}
}
protected void btnReject_Click(object sender, EventArgs e)
{
try
{
vdm = new VehicleDBMgr();
cmd = new MySqlCommand("UPDATE incentive_structure SET ApprovalStatus = 'R' WHERE (sno = @incentivesno)");
cmd.Parameters.AddWithValue("@incentivesno", ddlIncentiveStructure.SelectedValue);
vdm.Update(cmd);
lblmsg.Text = "Selected Structure Rejected";
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
}
}
protected void BtnSave_Click(object sender, EventArgs e)
{
if (txt_Geofencename.Text != "" && txt_GeoFence_latitude.Text != "" && txt_Geofence_longitude.Text != "" && txt_Radious.Text != "")
{
try
{
if (BtnSave.Text == "Save")
{
cmd = new MySqlCommand("insert into GeofenseTable (Name,Latitude,Longitude,Radious,UserName) values (@Name,@Latitude,@Longitude,@Radious,@UserName)");
cmd.Parameters.Add("@Name", txt_Geofencename.Text.Trim());
cmd.Parameters.Add("@Latitude", txt_GeoFence_latitude.Text.Trim());
cmd.Parameters.Add("@Longitude", txt_Geofence_longitude.Text.Trim());
cmd.Parameters.Add("@Radious", txt_Radious.Text.Trim());
cmd.Parameters.Add("@UserName", UserName);
vdm.insert(cmd);
MessageBox.Show("Successfully Geofence added", this);
getGeofenseData();
resetall();
}
else
{
cmd = new MySqlCommand("update GeofenseTable set Name=@Name,Latitude=@Latitude,Longitude=@Longitude,Radious=@Radious where UserName=@UserName and Name=@Nme");
cmd.Parameters.Add("@Name", txt_Geofencename.Text);
cmd.Parameters.Add("@Latitude", txt_GeoFence_latitude.Text);
cmd.Parameters.Add("@Longitude", txt_Geofence_longitude.Text);
cmd.Parameters.Add("@Radious", txt_Radious.Text);
cmd.Parameters.Add("@Nme", geofenseName);
cmd.Parameters.Add("@UserName", UserName);
vdm.Update(cmd);
MessageBox.Show("Successfully Geofence Modified", this);
getGeofenseData();
resetall();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, this);
// Response.Write(@"<script language='javascript'>alert('"+ex.Message+"')</script>");
}
}
else
{
// Response.Write(@"<script language='javascript'>alert('Please fill all required filelds')</script>");
MessageBox.Show("Please fill all required filelds", this);
}
}
protected void save_head_click(object sender, EventArgs e)
{
vdm = new VehicleDBMgr();
DateTime CreateDate = VehicleDBMgr.GetTime(vdm.conn);
DateTime fromdate = DateTime.Now;
DataTable dtmainaccount = (DataTable)Session["dtImport"];
foreach (DataRow dr in dtmainaccount.Rows)
{
string Ledgername = dr["ledgername"].ToString();
string Ledgercode = dr["accountcode"].ToString();
if (Ledgername == "" || Ledgercode == "")
{
}
else
{
sqlcmd = new SqlCommand("SELECT sno FROM headofaccounts_master WHERE (accountname = @accountname)");
sqlcmd.Parameters.Add("@accountcode", Ledgercode);
sqlcmd.Parameters.Add("@accountname", Ledgername);
DataTable dtledgers = vdm.SelectQuery(sqlcmd).Tables[0];
if (dtledgers.Rows.Count > 0)
{
string sno = dtledgers.Rows[0]["sno"].ToString();
sqlcmd = new SqlCommand("update headofaccounts_master set accountname=@accountname,accountcode=@accountcode where sno=@sno");
sqlcmd.Parameters.Add("@accountcode", Ledgercode);
sqlcmd.Parameters.Add("@accountname", Ledgername);
sqlcmd.Parameters.Add("@doe", fromdate);
sqlcmd.Parameters.Add("@sno", sno);
vdm.Update(sqlcmd);
}
else
{
sqlcmd = new SqlCommand("Insert into headofaccounts_master (accountname, accountcode,doe) values (@accountname, @accountcode,@doe)");
sqlcmd.Parameters.Add("@accountcode", Ledgercode);
sqlcmd.Parameters.Add("@accountname", Ledgername);
sqlcmd.Parameters.Add("@doe", fromdate);
vdm.insert(sqlcmd);
}
}
}
DataTable dtempty = new DataTable();
grdReports.DataSource = dtempty;
grdReports.DataBind();
lblmsg.Text = "Successfully Saved";
}
protected void btnTripend_OnClick(object sender, EventArgs e)
{
try
{
DateTime CompleteDate = DateTime.Now;
DateTime Assigndate = DateTime.Now;
string[] datestrig = txtCompleteDate.Text.Split(' ');
if (datestrig.Length > 1)
{
if (datestrig[0].Split('-').Length > 0)
{
string[] dates = datestrig[0].Split('-');
string[] times = datestrig[1].Split(':');
CompleteDate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
datestrig = txtAssigndate.Text.Split(' ');
if (datestrig.Length > 1)
{
if (datestrig[0].Split('-').Length > 0)
{
string[] dates = datestrig[0].Split('-');
string[] times = datestrig[1].Split(':');
Assigndate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
cmd = new MySqlCommand("update tripdata set completdate=@completdate,Status=@Status,assigndate=@assigndate where Tripid=@Tripid and UserID=@UserID and Refno=@Refno");
cmd.Parameters.Add("@Tripid", txtTripName.Text);
cmd.Parameters.Add("@Status", ddlStatus.SelectedValue);
cmd.Parameters.Add("@completdate", CompleteDate);
cmd.Parameters.Add("@assigndate", Assigndate);
cmd.Parameters.Add("@UserID", UserName);
cmd.Parameters.Add("@Refno", Refno);
vdm.Update(cmd);
updateTripend();
refresh();
lblmsg.Text = "Saved Successfully";
}
catch
{
}
}
protected void btnSubmitt_Click(object sender, EventArgs e)
{
try
{
if (Session["UserSno"] != null)
{
lblError.Text = "";
UserName = Session["UserSno"].ToString();
vdm = new VehicleDBMgr();
cmd = new SqlCommand("SELECT Password FROM employe_login WHERE sno = @Sno");
cmd.Parameters.Add("@Sno", UserName);
DataTable dt = vdm.SelectQuery(cmd).Tables[0];//"ManageData", "UserName", new string[] { "UserName=@UserName" }, new string[] { UserName }, new string[] { "" }).Tables[0];
if (dt.Rows.Count > 0)
{
if (txtNewPassWord.Text == txtConformPassWord.Text)
{
txtNewPassWord.Text = txtConformPassWord.Text;
cmd = new SqlCommand("Update employe_login set passward=@Password where sno=@Sno ");
cmd.Parameters.Add("@Sno", UserName);
cmd.Parameters.Add("@Password", txtNewPassWord.Text.Trim());
vdm.Update(cmd);
lblMessage.Text = "Your Password has been Changed successfully";
Response.Redirect("Login.aspx", false);
}
else
{
lblError.Text = "Conform password not match";
}
}
else
{
lblError.Text = "Entered username is incorrect";
}
}
else
{
Response.Redirect("Login.aspx", false);
}
}
catch (Exception ex)
{
lblError.Text = "Password Changed Failed";
}
}
protected void BtnSave_Click(object sender, EventArgs e)
{
try
{
vdm = new VehicleDBMgr();
DateTime fromdate = DateTime.Now;
DateTime todate = DateTime.Now;
string[] datestrig = dtp_FromDate.Text.Split(' ');
if (datestrig.Length > 1)
{
if (datestrig[0].Split('-').Length > 0)
{
string[] dates = datestrig[0].Split('-');
string[] times = datestrig[1].Split(':');
fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
datestrig = dtp_Todate.Text.Split(' ');
if (datestrig.Length > 1)
{
if (datestrig[0].Split('-').Length > 0)
{
string[] dates = datestrig[0].Split('-');
string[] times = datestrig[1].Split(':');
todate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
cmd = new MySqlCommand("update tripdata set DieselCost=@DieselCost where enddate between @d1 and @d2 and userid=@userid and vehicleno=@vehicleno");
cmd.Parameters.Add("@DieselCost", txt_fuel.Text);
cmd.Parameters.Add("@d1", GetLowDate(fromdate));
cmd.Parameters.Add("@d2", GetHighDate(todate));
cmd.Parameters.Add("@userid", BranchID);
cmd.Parameters.Add("@vehicleno", ddlvehicles.SelectedValue);
vdm.Update(cmd);
lblmsg.Text = "Fuel updated successfully";
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
}
}
protected void btn_ML_Add_Click(object sender, EventArgs e)
{
if (btn_ML_Add.Text == "Add")
{
//if (rb_ML_vehicletype.SelectedItem.Text == "Vehicles")
//{
cmd = new MySqlCommand("insert into MovingLoc (VehicleNo,UserName,VehicleType,ConditionType,MaxSpeed,MinSpeed) values (@VehicleNo,@UN,@VT,@CT,@MxS,@MiS)");
cmd.Parameters.Add("@VehicleNo", ddl_ML_VehicleNo.Text);
cmd.Parameters.Add("@UN", UserName);
cmd.Parameters.Add("@VT", rb_ML_vehicletype.SelectedItem.Text);
cmd.Parameters.Add("@CT", ddl_ml_type.Text);
cmd.Parameters.Add("@MxS", txt_ml_MinSpeed.Text);
cmd.Parameters.Add("@MiS", txt_ml_MaxSpeed.Text);
vdm.insert(cmd);
updateML_gridview();
ML_Refresh();
//}
//else
//{
//}
}
else
{
cmd = new MySqlCommand("update MovingLoc set VehicleNo=@VehicleNo, VehicleType=@VT, ConditionType=@CT, MaxSpeed=@MxS,MinSpeed=@MiS where sno=@sno ");
cmd.Parameters.Add("@VehicleNo", ddl_ML_VehicleNo.Text);
cmd.Parameters.Add("@VT", rb_ML_vehicletype.SelectedItem.Text);
cmd.Parameters.Add("@CT", ddl_ml_type.Text);
cmd.Parameters.Add("@MxS", txt_ml_MinSpeed.Text);
cmd.Parameters.Add("@MiS", txt_ml_MaxSpeed.Text);
cmd.Parameters.Add("@sno", ml_sno);
vdm.Update(cmd);
updateML_gridview();
ML_Refresh();
}
}
protected void btnSave_Click(object sender, EventArgs e)
{
try
{
DataTable dt = (DataTable)Session["btnImport"];
vdm = new VehicleDBMgr();
DateTime CreateDate = VehicleDBMgr.GetTime(vdm.conn);
DateTime fromdate = DateTime.Now;
int i = 1;
foreach (DataRow dr in dt.Rows)
{
string Ledgername = dr["Account Name"].ToString();
string Ledgercode = dr["Account Number"].ToString();
if (Ledgername == "" || Ledgercode == "")
{
}
else
{
sqlcmd = new SqlCommand("update headofaccounts_master set accountcode=@accountcode where accountname=@accountname");
sqlcmd.Parameters.Add("@accountcode", Ledgercode);
sqlcmd.Parameters.Add("@accountname", Ledgername);
vdm.Update(sqlcmd);
i++;
}
}
DataTable dtempty = new DataTable();
grdReports.DataSource = dtempty;
grdReports.DataBind();
lblMessage.Text = "Successfully Saved";
}
catch (Exception ex)
{
lblMessage.Text = ex.ToString();
}
}
protected void BtnAddAssignGeofence_Click(object sender, EventArgs e)
{
try
{
if (BtnAddAssignGeofence.Text == "Save")
{
if (txtDateofUnAssign.Text != "")
{
cmd = new MySqlCommand("insert into AssignGeofence (VehicleID, Geofencename, GeofenceType,DateOfAssign,DateofUnAssign,Latitude,Longitude,Radius,UserName ) values (@VehicleID, @Geofencename, @GeofenceType,@DateOfAssign,@DateofUnAssign,@Latitude,@Longitude,@Radius, @UserName)");
}
else
{
cmd = new MySqlCommand("insert into AssignGeofence (VehicleID, Geofencename, GeofenceType,DateOfAssign,Latitude,Longitude,Radius,UserName ) values (@VehicleID, @Geofencename, @GeofenceType,@DateOfAssign,@Latitude,@Longitude,@Radius, @UserName)");
}
DateTime fromdate = DateTime.Now; //System.Convert.ToDateTime(startdate.Text);//startdate_CalendarExtender.SelectedDate ?? DateTime.Now;// DateTime.Now.AddMonths(-3);//DateTime.Parse(startdate.Text); ;
DateTime todate = DateTime.Now; //System.Convert.ToDateTime(enddate.Text);//enddate_CalendarExtender.SelectedDate ?? DateTime.Now; //DateTime.Parse(enddate.Text);
// d/M/yyyy HH:mm
string[] datestrig = txtDateofAssign.Text.Split(' ');
if (datestrig.Length > 1)
{
if (datestrig[0].Split('/').Length > 0)
{
string[] dates = datestrig[0].Split('/');
string[] times = datestrig[1].Split(':');
fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
else
{
// MessageBox.Show("Date Time Format Wrong");
lbl_nofifier.Text = "From Date Time Format Wrong";
return;
}
if (txtDateofUnAssign.Text != "")
{
datestrig = txtDateofUnAssign.Text.Split(' ');
if (datestrig.Length > 1)
{
if (datestrig[0].Split('/').Length > 0)
{
string[] dates = datestrig[0].Split('/');
string[] times = datestrig[1].Split(':');
todate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
else
{
// MessageBox.Show("Date Time Format Wrong");
lbl_nofifier.Text = "To Date Time Format Wrong";
return;
}
}
cmd.Parameters.Add("@VehicleID", ddlSelectVehicle.SelectedValue.Trim());
cmd.Parameters.Add("@Geofencename", ddlSelectGeofence.SelectedValue.Trim());
cmd.Parameters.Add("@GeofenceType", ddlGeofenceType.SelectedValue.Trim());
cmd.Parameters.Add("@DateOfAssign", fromdate);
if (txtDateofUnAssign.Text != "")
{
cmd.Parameters.Add("@DateofUnAssign", todate);
}
DataRow[] drAssignGeofence = GeofenceDataTable.Select("Name='" + ddlSelectGeofence.SelectedValue + "'");
cmd.Parameters.Add("@Latitude", drAssignGeofence[0]["Latitude"].ToString());
cmd.Parameters.Add("@Longitude", drAssignGeofence[0]["Longitude"].ToString());
cmd.Parameters.Add("@Radius", drAssignGeofence[0]["Radious"].ToString());
cmd.Parameters.Add("@UserName", UserName);
vdm.insert(cmd);
MessageBox.Show("Data Added Successfully..", this);
UpdateAssignGeofence();
RefreshAssignGeofence();
}
else
{
if (txtDateofUnAssign.Text != "")
{
cmd = new MySqlCommand("Update AssignGeofence set VehicleID=@VehicleID,Geofencename=@Geofencename,GeofenceType=@GeofenceType,DateOfAssign=@DateOfAssign,DateofUnAssign=@DateofUnAssign,Latitude=@Latitude,Longitude=@Longitude,Radius=@Radius where SlNo=@SlNo and UserName=@UserName");
}
else
{
cmd = new MySqlCommand("Update AssignGeofence set VehicleID=@VehicleID,Geofencename=@Geofencename,GeofenceType=@GeofenceType,DateOfAssign=@DateOfAssign,Latitude=@Latitude,Longitude=@Longitude,Radius=@Radius where SlNo=@SlNo and UserName=@UserName");
}
DateTime fromdate = DateTime.Now; //System.Convert.ToDateTime(startdate.Text);//startdate_CalendarExtender.SelectedDate ?? DateTime.Now;// DateTime.Now.AddMonths(-3);//DateTime.Parse(startdate.Text); ;
DateTime todate = DateTime.Now; //System.Convert.ToDateTime(enddate.Text);//enddate_CalendarExtender.SelectedDate ?? DateTime.Now; //DateTime.Parse(enddate.Text);
// d/M/yyyy HH:mm
string[] datestrig = txtDateofAssign.Text.Split(' ');
if (datestrig.Length > 1)
{
if (datestrig[0].Split('/').Length > 0)
{
string[] dates = datestrig[0].Split('/');
string[] times = datestrig[1].Split(':');
fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
else
{
// MessageBox.Show("Date Time Format Wrong");
lbl_nofifier.Text = "From Date Time Format Wrong";
return;
}
datestrig = txtDateofUnAssign.Text.Split(' ');
if (datestrig.Length > 1)
{
if (datestrig[0].Split('/').Length > 0)
{
string[] dates = datestrig[0].Split('/');
string[] times = datestrig[1].Split(':');
todate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
else
{
// MessageBox.Show("Date Time Format Wrong");
lbl_nofifier.Text = "To Date Time Format Wrong";
return;
}
cmd.Parameters.Add("@VehicleID", ddlSelectVehicle.Text.Trim());
cmd.Parameters.Add("@Geofencename", ddlSelectGeofence.Text.Trim());
cmd.Parameters.Add("@GeofenceType", ddlGeofenceType.Text.Trim());
cmd.Parameters.Add("@DateOfAssign", fromdate);
cmd.Parameters.Add("@DateofUnAssign", todate);
DataRow[] drAssignGeofence = GeofenceDataTable.Select("Name='" + ddlSelectGeofence.SelectedValue + "'");
cmd.Parameters.Add("@Latitude", drAssignGeofence[0]["Latitude"].ToString());
cmd.Parameters.Add("@Longitude", drAssignGeofence[0]["Longitude"].ToString());
cmd.Parameters.Add("@Radius", drAssignGeofence[0]["Radious"].ToString());
cmd.Parameters.Add("@UserName", UserName);
cmd.Parameters.Add("@SlNo", SlNo);
vdm.Update(cmd);
BtnAddAssignGeofence.Text = "Save";
MessageBox.Show("Successfully Data Modified", this);
UpdateAssignGeofence();
RefreshAssignGeofence();
}
}
catch (Exception ex)
{
if (ex.Message == "-2146232060")
{
MessageBox.Show(" is already Existed", this);
}
else
{
MessageBox.Show(ex.Message, this);
}
}
}
protected void btn_WIDB_Click(object sender, EventArgs e)
{
try
{
vdm = new VehicleDBMgr();
DateTime ServerDateCurrentdate = VehicleDBMgr.GetTime(vdm.conn);
DataTable dt = (DataTable)Session["dtImport"];
cmd = new MySqlCommand("SELECT branch_sno, product_sno, unitprice FROM branchproducts ");
//cmd = new MySqlCommand("SELECT branch_sno, product_sno, unitprice FROM branchproducts WHERE (branch_sno = @branchsno) UNION SELECT branchproducts_1.branch_sno, branchproducts_1.product_sno, branchproducts_1.unitprice FROM branchmappingtable INNER JOIN branchproducts branchproducts_1 ON branchmappingtable.SubBranch = branchproducts_1.branch_sno WHERE (branchmappingtable.SuperBranch = @branchsno)");
//cmd.Parameters.AddWithValue("@branchsno", Session["branch"]);
DataTable dtBrnchPrdt = vdm.SelectQuery(cmd).Tables[0];
int i = 0;
foreach (DataRow dr in dt.Rows)
{
string AgentCode = dr["Agent Code"].ToString();
DataTable dtAgentprdt = new DataTable();
dtAgentprdt.Columns.Add("branch_sno");
dtAgentprdt.Columns.Add("product_sno");
dtAgentprdt.Columns.Add("unitprice");
DataRow[] drBp = dtBrnchPrdt.Select("branch_sno='" + dr["Agent Code"].ToString() + "'");
for (int k = 0; k < drBp.Length; k++)
{
DataRow newrow = dtAgentprdt.NewRow();
newrow["branch_sno"] = drBp[k][0].ToString();
newrow["product_sno"] = drBp[k][1].ToString();
newrow["unitprice"] = drBp[k][2].ToString();
dtAgentprdt.Rows.Add(newrow);
}
int j = 3;
foreach (DataColumn dc in dt.Columns)
{
var cell = dc.ColumnName;
if (cell == "SNo" || cell == "Agent Code" || cell == "Agent Name")
{
}
else
{
string UnitPrice = dt.Rows[i][j].ToString();
if (UnitPrice == " ")
{
UnitPrice = "0";
}
cmd = new MySqlCommand("Select Sno from productsdata where ProductName=@ProductName");
cmd.Parameters.AddWithValue("@ProductName", dc.ColumnName);
DataTable dtProduct = vdm.SelectQuery(cmd).Tables[0];
string ProductID = dtProduct.Rows[0]["Sno"].ToString();
DataTable oldunitprice = new DataTable();
oldunitprice.Columns.Add("unitprice");
DataRow[] drAp = dtAgentprdt.Select("product_sno='" + ProductID + "'");
if (drAp.Length == 0)
{
if (UnitPrice == "0")
{
}
else
{
cmd = new MySqlCommand("insert into branchproducts (branch_sno,product_sno,unitprice,userdata_sno,DTarget,WTarget,MTarget) values (@branchname,@productname,@unitprice, @username,@DTarget,@WTarget,@MTarget)");
cmd.Parameters.AddWithValue("@branchname", AgentCode);
cmd.Parameters.AddWithValue("@productname", ProductID);
float UntCost = 0;
float.TryParse(UnitPrice, out UntCost);
cmd.Parameters.AddWithValue("@unitprice", UntCost);
//cmd.Parameters.AddWithValue("@unitprice", 0);
cmd.Parameters.AddWithValue("@username", Session["userdata_sno"]);
int productDaytarget = 0;
int productWeektarget = 0;
int productMonthtarget = 0;
cmd.Parameters.AddWithValue("@DTarget", productDaytarget);
cmd.Parameters.AddWithValue("@WTarget", productWeektarget);
cmd.Parameters.AddWithValue("@MTarget", productMonthtarget);
vdm.insert(cmd);
}
}
else
{
for (int ap = 0; ap < drAp.Length; ap++)
{
DataRow newaprow = oldunitprice.NewRow();
newaprow["unitprice"] = drAp[ap][2].ToString();
oldunitprice.Rows.Add(newaprow);
}
string oldprice = "0";
if (oldunitprice.Rows.Count > 0)
{
oldprice = oldunitprice.Rows[0]["unitprice"].ToString();
}
float UnitCost = 0;
float.TryParse(UnitPrice, out UnitCost);
float oldUnitCost = 0;
float.TryParse(oldprice, out oldUnitCost);
if (UnitCost == oldUnitCost)
{
}
else
{
cmd = new MySqlCommand("Update branchproducts set UnitPrice=@UnitPrice where Branch_sno=@Branch_sno and Product_sno=@Product_sno");
cmd.Parameters.AddWithValue("@UnitPrice", UnitCost);
cmd.Parameters.AddWithValue("@Branch_sno", AgentCode);
cmd.Parameters.AddWithValue("@Product_sno", ProductID);
vdm.Update(cmd);
cmd = new MySqlCommand("insert into productsrateslogs (PrdtSno,BranchId,OldPrice,EditedPrice,EditedUserid,DateOfEdit) values (@PrdtSno,@BranchId,@OldPrice,@EditedPrice,@EditedUserid,@DateOfEdit)");
cmd.Parameters.AddWithValue("@PrdtSno", ProductID);
cmd.Parameters.AddWithValue("@BranchId", AgentCode);
cmd.Parameters.AddWithValue("@OldPrice", oldUnitCost);
cmd.Parameters.AddWithValue("@EditedPrice", UnitCost);
cmd.Parameters.AddWithValue("@EditedUserid", Session["UserSno"]);
cmd.Parameters.AddWithValue("@DateOfEdit", ServerDateCurrentdate);
vdm.insert(cmd);
}
}
j++;
}
}
i++;
}
lblmsg.Text = "Updated Successfully";
}
catch (Exception ex)
{
if (ex.Message == "Object reference not set to an instance of an object.")
{
lblmsg.Text = "Session Expired";
Response.Redirect("Login.aspx");
}
else
{
lblmsg.Text = ex.Message;
}
}
}
protected void gvDetails_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
vdm = new VehicleDBMgr();
DateTime fromdate = DateTime.Now;
string[] fromdatestrig = txtdate.Text.Split(' ');
if (fromdatestrig.Length > 1)
{
if (fromdatestrig[0].Split('-').Length > 0)
{
string[] dates = fromdatestrig[0].Split('-');
string[] times = fromdatestrig[1].Split(':');
fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
GridViewRow row = grdReports.Rows[e.RowIndex];
string UserName = (row.FindControl("txtUserName") as TextBox).Text;
string DispName = (row.FindControl("txtDispName") as TextBox).Text;
string Sno = (row.FindControl("txtSno") as TextBox).Text;
string Status = (row.FindControl("txtStatus") as TextBox).Text;
string Canceldate = (row.FindControl("txtCanceldate") as TextBox).Text;
string CollectedAmount = (row.FindControl("txtCollectedAmount") as TextBox).Text;
string SubmittedAmount = (row.FindControl("txtSubmittedAmount") as TextBox).Text;
string ReceivedAmount = (row.FindControl("txtReceivedAmount") as TextBox).Text;
cmd = new MySqlCommand("SELECT Sno, EmpId, AssignDate, Status, Cdate, ReceivedAmount, RecieptNo FROM tripdata WHERE (Sno = @tripid) AND (BranchID = @branchid)");
cmd.Parameters.AddWithValue("@tripid", Sno);
cmd.Parameters.AddWithValue("@branchid", Session["branch"].ToString());
DataTable dtreceiptno = vdm.SelectQuery(cmd).Tables[0];
DateTime Receiptdate = new DateTime();
string RecieptNo = "";
if (dtreceiptno.Rows.Count > 0)
{
string Tripenddate = dtreceiptno.Rows[0]["Cdate"].ToString();
RecieptNo = dtreceiptno.Rows[0]["RecieptNo"].ToString();
if (Tripenddate != "")
{
Receiptdate = Convert.ToDateTime(Tripenddate);
}
}
//int customerId = Convert.ToInt32(grdReports.DataKeys[e.RowIndex].Values[0]);
cmd = new MySqlCommand("Update Tripdata set Status=@Status,SyncStatus=@SyncStatus,Cdate=@Cdate,ReceivedAmount=@ReceivedAmount where Sno=@sno");
cmd.Parameters.AddWithValue("@Status", Status);
cmd.Parameters.AddWithValue("@Sno", Sno);
cmd.Parameters.AddWithValue("@Cdate", fromdate);
cmd.Parameters.AddWithValue("@SyncStatus", "1");
cmd.Parameters.AddWithValue("@ReceivedAmount", ReceivedAmount);
vdm.Update(cmd);
cmd = new MySqlCommand("SELECT Sno, BranchId, ReceivedFrom, AgentID, Empid, Amountpayable, AmountPaid, DOE, Create_by, Modified_by, Remarks, OppBal, dispatchid, Receipt, PaymentStatus, ChequeNo, Tripid, GroupRecieptNo, GroupRef,TransactionType, AmountDebited, newreceipt FROM cashreceipts WHERE (Receipt = @receipt) AND (BranchId = @branchid) AND (ReceivedFrom = @ReceivedFrom) AND (DOE BETWEEN @d1 AND @d2)");
cmd.Parameters.AddWithValue("@receipt", RecieptNo);
cmd.Parameters.AddWithValue("@branchid", Session["branch"].ToString());
cmd.Parameters.AddWithValue("@ReceivedFrom", "SalesMen");
cmd.Parameters.AddWithValue("@d1", GetLowDate(Receiptdate));
cmd.Parameters.AddWithValue("@d2", GetHighDate(Receiptdate));
DataTable dtreceiptdetails = vdm.SelectQuery(cmd).Tables[0];
if (dtreceiptdetails.Rows.Count > 0)
{
string receiptsno = dtreceiptdetails.Rows[0]["Sno"].ToString();
cmd = new MySqlCommand("Update cashreceipts set DOE=@DOE where Sno=@sno");
cmd.Parameters.AddWithValue("@DOE", fromdate);
cmd.Parameters.AddWithValue("@sno", receiptsno);
vdm.Update(cmd);
}
lblmsg.Text = "Assign Successfully";
grdReports.EditIndex = -1;
GetReport();
}
public void save()
{
VehicleDBMgr vdm = new VehicleDBMgr();
dtmissing.Columns.Add("Ledgername");
DateTime CreateDate = VehicleDBMgr.GetTime(vdm.conn);
DateTime fromdate = DateTime.Now;
DataTable dtmainaccount = (DataTable)Session["dtmainImport"];
DataTable dtsubaccount = (DataTable)Session["dtImport"];
string UserName = Session["UserSno"].ToString();
string branchname = "";
string doe = txtFromdate.Text;
DateTime paymentdate = Convert.ToDateTime(doe);
string payto = "";
string acno = ddlaccountno.SelectedItem.Value;
string subbranch = "";
string sapimport = "2";
string remarks = "";
string approvedby = "";
double totalamount = 0;
double totalsubamount = 0;
cmd = new SqlCommand("insert into paymentdetails (name,accountno,remarks,approvedby,doe,createdby,status,paymentdate,branch,sub_branch,sapimport) values (@name,@accountno,@remarks,@approvedby,@doe,@createdby,'P',@paymentdate,@branch,@sub_branch,@sapimport)");
cmd.Parameters.Add("@name", payto);
cmd.Parameters.Add("@accountno", acno);
//cmd.Parameters.Add("@totalamount", totalamount);
cmd.Parameters.Add("@remarks", remarks);
cmd.Parameters.Add("@approvedby", approvedby);
cmd.Parameters.Add("@doe", CreateDate);
cmd.Parameters.Add("@createdby", UserName);
cmd.Parameters.Add("@paymentdate", doe);
cmd.Parameters.Add("@branch", branchname);
cmd.Parameters.Add("@sub_branch", subbranch);
cmd.Parameters.Add("@sapimport", sapimport);
vdm.insert(cmd);
cmd = new SqlCommand("select MAX(sno) AS sno from paymentdetails ");
DataTable routes = vdm.SelectQuery(cmd).Tables[0];
string paymentrefno = routes.Rows[0]["sno"].ToString();
//string paymentrefno = "7277";
if (dtmainaccount != null && dtmainaccount.Rows.Count > 0)
{
try
{
foreach (DataRow drmain in dtmainaccount.Rows)
{
double mainamount = 0;
string headofaccount = drmain["ledgername"].ToString();
cmd = new SqlCommand("SELECT sno FROM headofaccounts_master WHERE accountname=@acname");
cmd.Parameters.Add("@acname", headofaccount);
DataTable dtheadofaccount = vdm.SelectQuery(cmd).Tables[0];
if (dtheadofaccount.Rows.Count > 0)
{
string accountid = dtheadofaccount.Rows[0]["sno"].ToString();
string amount = drmain["amount"].ToString();
mainamount = Convert.ToDouble(amount);
totalamount += mainamount;
cmd = new SqlCommand("insert into paymentsubdetails (paymentrefno, headofaccount, amount) values (@paymentrefno, @headofaccount, @amount)");
cmd.Parameters.Add("@paymentrefno", paymentrefno);
cmd.Parameters.Add("@headofaccount", accountid);
cmd.Parameters.Add("@amount", amount);
vdm.insert(cmd);
}
else
{
DataRow newrow = dtmissing.NewRow();
newrow["Ledgername"] = headofaccount;
dtmissing.Rows.Add(newrow);
}
}
cmd = new SqlCommand("UPDATE paymentdetails SET totalamount=@totalamount WHERE sno=@refno");
cmd.Parameters.Add("@totalamount", totalamount);
cmd.Parameters.Add("@refno", paymentrefno);
vdm.Update(cmd);
}
catch (Exception ex)
{
}
}
if (dtsubaccount != null && dtsubaccount.Rows.Count > 0)
{
try
{
foreach (DataRow dr in dtsubaccount.Rows)
{
double subamount = 0;
string subheadofaccount = dr["ledgername"].ToString();
cmd = new SqlCommand("SELECT sno FROM headofaccounts_master WHERE accountname=@acname");
cmd.Parameters.Add("@acname", subheadofaccount);
DataTable dtsubheadofaccount = vdm.SelectQuery(cmd).Tables[0];
if (dtsubheadofaccount.Rows.Count > 0)
{
string subaccountid = dtsubheadofaccount.Rows[0]["sno"].ToString();
string amount = dr["amount"].ToString();
subamount = Convert.ToDouble(amount);
totalsubamount += subamount;
cmd = new SqlCommand("insert into subaccount_payment (paymentrefno, headofaccount, amount, branchid) values (@paymentrefno, @headofaccount, @amount,@branchid)");
cmd.Parameters.Add("@paymentrefno", paymentrefno);
cmd.Parameters.Add("@headofaccount", subaccountid);
cmd.Parameters.Add("@amount", amount);
cmd.Parameters.Add("@branchid", subbranch);
vdm.insert(cmd);
}
else
{
DataRow newrow = dtmissing.NewRow();
newrow["Ledgername"] = subheadofaccount;
dtmissing.Rows.Add(newrow);
}
}
cmd = new SqlCommand("UPDATE paymentdetails SET total_subamount=@total_subamount WHERE sno=@prefno");
cmd.Parameters.Add("@total_subamount", totalsubamount);
cmd.Parameters.Add("@prefno", paymentrefno);
vdm.Update(cmd);
}
catch (Exception ex)
{
}
}
}
protected void btnLogin_Click(object sender, EventArgs e)
{
try
{
vdm = new VehicleDBMgr();
String UserName = txtUserName.Text, PassWord = txtPassword.Text;
lbl_username.Text = UserName;
lbl_passwords.Text = PassWord;
cmd = new SqlCommand("SELECT sno, name, username, passward, branchid, doe, createdby, leveltype,loginstatus FROM employe_login WHERE (username = @UN) AND (passward = @Pwd)");
cmd.Parameters.Add("@UN", UserName);
cmd.Parameters.Add("@Pwd", PassWord);
DataTable dtemp = vdm.SelectQuery(cmd).Tables[0];
if (dtemp.Rows.Count > 0)
{
//string loginstatus = dtemp.Rows[0]["loginstatus"].ToString();
//if (loginstatus == "True")
//{
lbl_validation.Text = "Already Some one Login With This User Name";
// this.AlertBoxMessage.InnerText = "Already Some one Login With This User Name";
// this.AlertBox.Visible = true;
//}
//else
//{
Session["TitleName"] = "SRI VYSHNAVI DAIRY SPECIALITIES (P) LTD";
Session["Address"] = "R.S.No:381/2,Punabaka village Post,Pellakuru Mandal,Nellore District -524129., ANDRAPRADESH (State).Phone: 9440622077, Fax: 044 – 26177799.";
Session["TinNo"] = "37921042267";
Session["UserSno"] = dtemp.Rows[0]["Sno"].ToString();
Session["EmpName"] = dtemp.Rows[0]["name"].ToString();
Session["UserName"] = dtemp.Rows[0]["username"].ToString();
Session["passward"] = dtemp.Rows[0]["passward"].ToString();
Session["branch"] = "svds";
Session["branchname"] = "svds";
Session["FA_branchid"] = dtemp.Rows[0]["branchid"].ToString();
Session["LevelType"] = dtemp.Rows[0]["leveltype"].ToString();
Response.Cookies["userInfo"]["userName"] = Session["UserName"].ToString();
Response.Cookies["userInfo"]["UserSno"] = Session["UserSno"].ToString();
Response.Cookies["userInfo"]["branch"] = Session["branch"].ToString();
Response.Cookies["userInfo"]["lastVisit"] = DateTime.Now.ToString();
Response.Cookies["userInfo"].Expires = DateTime.Now.AddDays(1);
HttpCookie aCookie = new HttpCookie("userInfo");
aCookie.Values["userName"] = Session["UserName"].ToString();
aCookie.Values["lastVisit"] = DateTime.Now.ToString();
aCookie.Expires = DateTime.Now.AddDays(1);
Response.Cookies.Add(aCookie);
//get ip address and device type
string ipaddress;
ipaddress = Request.ServerVariables["HTTP_X_FORWARDED_FOR"];
if (ipaddress == "" || ipaddress == null)
{
ipaddress = Request.ServerVariables["REMOTE_ADDR"];
}
DateTime ServerDateCurrentdate = VehicleDBMgr.GetTime(vdm.conn);
HttpBrowserCapabilities browser = Request.Browser;
string devicetype = "";
string userAgent = Request.ServerVariables["HTTP_USER_AGENT"];
Regex OS = new Regex(@"(android|bb\d+|meego).+mobile|avantgo|bada\/|blackberry|blazer|compal|elaine|fennec|hiptop|iemobile|ip(hone|od)|iris|kindle|lge |maemo|midp|mmp|mobile.+firefox|netfront|opera m(ob|in)i|palm( os)?|phone|p(ixi|re)\/|plucker|pocket|psp|series(4|6)0|symbian|treo|up\.(browser|link)|vodafone|wap|windows ce|xda|xiino", RegexOptions.IgnoreCase | RegexOptions.Multiline);
Regex device = new Regex(@"1207|6310|6590|3gso|4thp|50[1-6]i|770s|802s|a wa|abac|ac(er|oo|s\-)|ai(ko|rn)|al(av|ca|co)|amoi|an(ex|ny|yw)|aptu|ar(ch|go)|as(te|us)|attw|au(di|\-m|r |s )|avan|be(ck|ll|nq)|bi(lb|rd)|bl(ac|az)|br(e|v)w|bumb|bw\-(n|u)|c55\/|capi|ccwa|cdm\-|cell|chtm|cldc|cmd\-|co(mp|nd)|craw|da(it|ll|ng)|dbte|dc\-s|devi|dica|dmob|do(c|p)o|ds(12|\-d)|el(49|ai)|em(l2|ul)|er(ic|k0)|esl8|ez([4-7]0|os|wa|ze)|fetc|fly(\-|_)|g1 u|g560|gene|gf\-5|g\-mo|go(\.w|od)|gr(ad|un)|haie|hcit|hd\-(m|p|t)|hei\-|hi(pt|ta)|hp( i|ip)|hs\-c|ht(c(\-| |_|a|g|p|s|t)|tp)|hu(aw|tc)|i\-(20|go|ma)|i230|iac( |\-|\/)|ibro|idea|ig01|ikom|im1k|inno|ipaq|iris|ja(t|v)a|jbro|jemu|jigs|kddi|keji|kgt( |\/)|klon|kpt |kwc\-|kyo(c|k)|le(no|xi)|lg( g|\/(k|l|u)|50|54|\-[a-w])|libw|lynx|m1\-w|m3ga|m50\/|ma(te|ui|xo)|mc(01|21|ca)|m\-cr|me(rc|ri)|mi(o8|oa|ts)|mmef|mo(01|02|bi|de|do|t(\-| |o|v)|zz)|mt(50|p1|v )|mwbp|mywa|n10[0-2]|n20[2-3]|n30(0|2)|n50(0|2|5)|n7(0(0|1)|10)|ne((c|m)\-|on|tf|wf|wg|wt)|nok(6|i)|nzph|o2im|op(ti|wv)|oran|owg1|p800|pan(a|d|t)|pdxg|pg(13|\-([1-8]|c))|phil|pire|pl(ay|uc)|pn\-2|po(ck|rt|se)|prox|psio|pt\-g|qa\-a|qc(07|12|21|32|60|\-[2-7]|i\-)|qtek|r380|r600|raks|rim9|ro(ve|zo)|s55\/|sa(ge|ma|mm|ms|ny|va)|sc(01|h\-|oo|p\-)|sdk\/|se(c(\-|0|1)|47|mc|nd|ri)|sgh\-|shar|sie(\-|m)|sk\-0|sl(45|id)|sm(al|ar|b3|it|t5)|so(ft|ny)|sp(01|h\-|v\-|v )|sy(01|mb)|t2(18|50)|t6(00|10|18)|ta(gt|lk)|tcl\-|tdg\-|tel(i|m)|tim\-|t\-mo|to(pl|sh)|ts(70|m\-|m3|m5)|tx\-9|up(\.b|g1|si)|utst|v400|v750|veri|vi(rg|te)|vk(40|5[0-3]|\-v)|vm40|voda|vulc|vx(52|53|60|61|70|80|81|83|85|98)|w3c(\-| )|webc|whit|wi(g |nc|nw)|wmlb|wonu|x700|yas\-|your|zeto|zte\-", RegexOptions.IgnoreCase | RegexOptions.Multiline);
string device_info = string.Empty;
if (OS.IsMatch(userAgent))
{
device_info = OS.Match(userAgent).Groups[0].Value;
}
if (device.IsMatch(userAgent.Substring(0, 4)))
{
device_info += device.Match(userAgent).Groups[0].Value;
}
if (!string.IsNullOrEmpty(device_info))
{
devicetype = device_info;
string[] words = devicetype.Split(')');
devicetype = words[0].ToString();
}
else
{
devicetype = "Desktop";
}
cmd = new SqlCommand("INSERT INTO logininfo(userid, username, logintime, ipaddress, devicetype) values (@userid, @UserName, @logintime, @ipaddress, @device)");
cmd.Parameters.Add("@userid", dtemp.Rows[0]["Sno"].ToString());
cmd.Parameters.Add("@UserName", dtemp.Rows[0]["name"].ToString());
cmd.Parameters.Add("@logintime", ServerDateCurrentdate);
cmd.Parameters.Add("@ipaddress", ipaddress);
cmd.Parameters.Add("@device", devicetype);
//cmd.Parameters.Add("@otp", otp);
vdm.insert(cmd);
cmd = new SqlCommand("update employe_login set loginstatus=@log where sno=@sno ");
cmd.Parameters.Add("@log", "1");
cmd.Parameters.Add("@sno", Session["UserSno"]);
vdm.Update(cmd);
Response.Redirect("BankMasterDetails.aspx", false);
//string SalesType = dtemp.Rows[0]["SalesType"].ToString();
//}
}
else
{
lbl_validation.Text = "Invalid username and password";
}
}
catch (Exception ex)
{
lbl_validation.Text = ex.Message;
}
}
protected void btnSave_Click(object sender, EventArgs e)
{
long refno = 0;
try
{
if (btnSave.Text == "Save")
{
cmd = new MySqlCommand("insert into loginstable (main_user,loginid, pwd,usertype,emailid,phonenumber,main_user_id) values (@main_user,@loginid,@pwd,@usertype,@emailid,@phonenumber,@main_user_id)");
cmd.Parameters.Add("@main_user", UserName);
cmd.Parameters.Add("@loginid", txtLoginID.Text);
cmd.Parameters.Add("@pwd", txtPassword.Text.Trim());
cmd.Parameters.Add("@usertype", ddlUserType.SelectedValue);
cmd.Parameters.Add("@emailid", txt_emailid.Text.Trim());
cmd.Parameters.Add("@phonenumber", txt_mobno.Text.Trim());
cmd.Parameters.Add("@main_user_id", mainusrsno);
refno = vdm.insertgetrefno(cmd);
foreach (ListItem obj in ckbVehicles.Items)
{
if (obj.Selected)
{
cmd = new MySqlCommand("insert into loginsconfigtable (Refno,VehicleID) values(@Refno,@VehicleID)");
cmd.Parameters.Add("@Refno", refno);
cmd.Parameters.Add("@VehicleID", obj.Text);
vdm.insert(cmd);
}
}
Refresh();
UpdateLogins();
lbl_nofifier.Text = "Record added Successfully";
}
else
{
DataTable prev_data = (DataTable)Session["dtallVehicles"];
//cmd = new MySqlCommand("Delete from loginsconfigtable where Refno=@Refno");
//cmd.Parameters.Add("@Refno", Sno);
//vdm.Delete(cmd);
cmd = new MySqlCommand("Update loginstable set main_user=@main_user,pwd=@pwd,usertype=@usertype,emailid=@emailid,phonenumber=@phonenumber,main_user_id=@main_user_id where loginid=@loginid ");
cmd.Parameters.Add("@main_user", UserName);
cmd.Parameters.Add("@loginid", txtLoginID.Text);
cmd.Parameters.Add("@pwd", txtPassword.Text.Trim());
cmd.Parameters.Add("@usertype", ddlUserType.SelectedValue);
cmd.Parameters.Add("@emailid", txt_emailid.Text.Trim());
cmd.Parameters.Add("@phonenumber", txt_mobno.Text.Trim());
cmd.Parameters.Add("@main_user_id", mainusrsno);
vdm.Update(cmd);
foreach (ListItem obj in ckbVehicles.Items)
{
if (prev_data.Select("VehicleID='" + obj.Text + "'").Length > 0)
{
if (!obj.Selected)
{
cmd = new MySqlCommand("Delete from loginsconfigtable where Refno=@Refno and VehicleID=@vid");
cmd.Parameters.Add("@Refno", Sno);
cmd.Parameters.Add("@vid", obj.Text);
vdm.Delete(cmd);
}
}
else
{
if (obj.Selected)
{
cmd = new MySqlCommand("insert into loginsconfigtable (Refno,VehicleID) values(@Refno,@VehicleID)");
cmd.Parameters.Add("@Refno", Sno);
cmd.Parameters.Add("@VehicleID", obj.Text);
vdm.insert(cmd);
}
}
}
//foreach (ListItem obj in ckbVehicles.Items)
//{
// if (obj.Selected)
// {
// cmd = new MySqlCommand("insert into loginsconfigtable (Refno,VehicleID) values(@Refno,@VehicleID)");
// cmd.Parameters.Add("@Refno", Sno);
// cmd.Parameters.Add("@VehicleID", obj.Text);
// vdm.insert(cmd);
// }
//}
Refresh();
UpdateLogins();
btnSave.Text = "Save";
lbl_nofifier.Text = "Record Updated Successfully";
}
}
catch (Exception ex)
{
if (ex.Message.Contains("PRIMARY"))
{
lbl_nofifier.Text = "Oops..! Username alredy existed";
}
else
{
lbl_nofifier.Text = "Oops..! Error occurred please try again";
}
}
}
protected void btnGenerate_Click(object sender, EventArgs e)
{
vdm = new VehicleDBMgr();
DateTime fromdate = DateTime.Now;
string[] datestrig = txtdate.Text.Split(' ');
if (datestrig.Length > 1)
{
if (datestrig[0].Split('-').Length > 0)
{
string[] dates = datestrig[0].Split('-');
string[] times = datestrig[1].Split(':');
fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
try
{
string BranchID = "";
// string BranchID = dr["sno"].ToString();
if (BranchID == "572")
{
BranchID = "158";
}
DateTime ServerDateCurrentdate = VehicleDBMgr.GetTime(vdm.conn);
cmd = new MySqlCommand("SELECT branchdata.BranchName, branchdata.sno, branchroutes.Sno AS routesno, branchroutes.RouteName FROM branchdata INNER JOIN branchdata branchdata_1 ON branchdata.sno = branchdata_1.sno INNER JOIN branchroutes ON branchdata.sno = branchroutes.BranchID WHERE (branchdata_1.SalesOfficeID = @SOID) AND (branchdata.SalesType IS NOT NULL) AND (branchroutes.flag <> 0) OR (branchdata.SalesType IS NOT NULL) AND (branchdata.sno = @BranchID) AND (branchroutes.flag <> 0) ORDER BY branchdata.sno");
cmd.Parameters.AddWithValue("@SOID", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
DataTable dtroutes = vdm.SelectQuery(cmd).Tables[0];
cmd = new MySqlCommand("SELECT modifiedroutes.RouteName, SUM(indents_subtable.DeliveryQty) AS saleQty, SUM(indents_subtable.DeliveryQty * indents_subtable.UnitCost) AS salevalue,modifiedroutes.Sno AS routesno, modifidroutssubtab.BranchID, branchdata_2.BranchName, branchdata_2.flag FROM branchdata branchdata_2 RIGHT OUTER JOIN branchdata INNER JOIN branchdata branchdata_1 ON branchdata.sno = branchdata_1.sno INNER JOIN modifiedroutes ON branchdata.sno = modifiedroutes.BranchID INNER JOIN (SELECT RefNo, Rank, LevelType, BranchID, CDate, EDate FROM modifiedroutesubtable WHERE (EDate IS NULL) AND (CDate <= @starttime) OR (EDate > @starttime) AND (CDate <= @starttime)) modifidroutssubtab ON modifiedroutes.Sno = modifidroutssubtab.RefNo ON branchdata_2.sno = modifidroutssubtab.BranchID LEFT OUTER JOIN indents_subtable INNER JOIN (SELECT IndentNo, I_date, Branch_id FROM indents WHERE (I_date BETWEEN @starttime AND @endtime)) indt ON indents_subtable.IndentNo = indt.IndentNo ON modifidroutssubtab.BranchID = indt.Branch_id WHERE (branchdata_1.SalesOfficeID = @SOID) AND (branchdata.SalesType IS NOT NULL) OR (branchdata.SalesType IS NOT NULL) AND (branchdata.sno = @BranchID) GROUP BY modifidroutssubtab.BranchID ORDER BY branchdata.sno, routesno");
cmd.Parameters.AddWithValue("@SOID", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@starttime", GetLowDate(fromdate.AddDays(-1)));
cmd.Parameters.AddWithValue("@endtime", GetHighDate(fromdate.AddDays(-1)));
DataTable dtroutecollection = vdm.SelectQuery(cmd).Tables[0];
DataTable dtrouteamount = new DataTable();
DataTable dtsalescollection = new DataTable();
//cmd = new MySqlCommand("SELECT branchdata.BranchName, branchdata.sno, modifiedroutes.RouteName, modifidroutssubtab.BranchID, modifiedroutes.Sno AS routesno, SUM(colltion.AmountPaid) AS amtpaid FROM branchdata INNER JOIN branchdata branchdata_1 ON branchdata.sno = branchdata_1.sno INNER JOIN modifiedroutes ON branchdata.sno = modifiedroutes.BranchID INNER JOIN (SELECT RefNo, Rank, LevelType, BranchID, CDate, EDate FROM modifiedroutesubtable WHERE (EDate IS NULL) AND (CDate <= @starttime) OR (EDate > @starttime) AND (CDate <= @starttime)) modifidroutssubtab ON modifiedroutes.Sno = modifidroutssubtab.RefNo INNER JOIN (SELECT Branchid, AmountPaid, PaidDate FROM collections WHERE (PaymentType = 'CASH') AND (PaidDate BETWEEN @d1 AND @d2)) colltion ON modifidroutssubtab.BranchID = colltion.Branchid WHERE (branchdata_1.SalesOfficeID = @SOID) AND (branchdata.SalesType IS NOT NULL) OR (branchdata.SalesType IS NOT NULL) AND (branchdata.sno = @BranchID) GROUP BY modifiedroutes.Sno ORDER BY branchdata.sno, routesno");
if (BranchID == "306")
{
cmd = new MySqlCommand("SELECT branchdata.tbranchname AS BranchName,branchdata.sno AS BranchID,cashreceipts.Remarks, cashreceipts.Receipt, DATE_FORMAT(tripdata.I_Date, '%d %b %y') AS DOE, SUM(cashreceipts.AmountPaid) AS amtpaid, cashreceipts.PaymentStatus, tripdata.Sno FROM branchmappingtable INNER JOIN branchdata ON branchmappingtable.SubBranch = branchdata.sno INNER JOIN cashreceipts ON branchdata.sno = cashreceipts.AgentID INNER JOIN tripdata ON cashreceipts.Tripid = tripdata.Sno WHERE (tripdata.I_Date BETWEEN @d1 AND @d2) AND (branchmappingtable.SuperBranch = @BranchID) AND (cashreceipts.PaymentStatus = 'Cash') AND (cashreceipts.AmountPaid > 0) Group by branchdata.tbranchname ORDER BY branchdata.sno");
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@SOID", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate).AddDays(-1));
cmd.Parameters.AddWithValue("@d2", GetHighDate(fromdate).AddDays(-1));
cmd.Parameters.AddWithValue("@Type", "Agent");
dtrouteamount = vdm.SelectQuery(cmd).Tables[0];
cmd = new MySqlCommand("SELECT branchdata.tBranchName,branchdata.sno AS BranchID,collections.receiptno as rec,collections.Remarks, collections.Sno as ReceiptNo,DATE_FORMAT(collections.PaidDate, '%d %b %y') AS DOE , collections.AmountPaid, collections.PaymentType FROM collections INNER JOIN branchdata ON collections.Branchid = branchdata.sno INNER JOIN branchmappingtable ON branchdata.sno = branchmappingtable.SubBranch INNER JOIN branchdata branchdata_1 ON branchmappingtable.SuperBranch = branchdata_1.sno WHERE (collections.PaidDate BETWEEN @d1 AND @d2) AND (branchmappingtable.SuperBranch = @BranchID) AND (collections.PaymentType = 'Cash') AND (collections.AmountPaid > 0) AND (collections.tripid is NULL) OR (collections.PaidDate BETWEEN @d1 AND @d2) AND (collections.PaymentType = 'Cash') AND (branchdata_1.SalesOfficeID = @SOID) AND (collections.tripid is NULL) AND (collections.AmountPaid > 0)");
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@SOID", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate).AddDays(1));
cmd.Parameters.AddWithValue("@d2", GetHighDate(fromdate).AddDays(1));
cmd.Parameters.AddWithValue("@Type", "Agent");
dtsalescollection = vdm.SelectQuery(cmd).Tables[0];
//dtrouteamount = vdm.SelectQuery(cmd).Tables[0];
}
else
{
cmd = new MySqlCommand("SELECT branchdata.BranchName, branchdata.sno, modifiedroutes.RouteName, modifidroutssubtab.BranchID, modifiedroutes.Sno AS routesno, SUM(colltion.AmountPaid) AS amtpaid FROM branchdata INNER JOIN branchdata branchdata_1 ON branchdata.sno = branchdata_1.sno INNER JOIN modifiedroutes ON branchdata.sno = modifiedroutes.BranchID INNER JOIN (SELECT RefNo, Rank, LevelType, BranchID, CDate, EDate FROM modifiedroutesubtable WHERE (EDate IS NULL) AND (CDate <= @starttime) OR (EDate > @starttime) AND (CDate <= @starttime)) modifidroutssubtab ON modifiedroutes.Sno = modifidroutssubtab.RefNo INNER JOIN (SELECT Branchid, AmountPaid, PaidDate FROM collections WHERE (PaymentType <> 'Cheque') AND (PaymentType <> 'Bank Transfer') AND (PaidDate BETWEEN @d1 AND @d2)) colltion ON modifidroutssubtab.BranchID = colltion.Branchid WHERE (branchdata_1.SalesOfficeID = @SOID) AND (branchdata.SalesType IS NOT NULL) OR (branchdata.SalesType IS NOT NULL) AND (branchdata.sno = @BranchID) GROUP BY modifidroutssubtab.BranchID ORDER BY branchdata.sno");
cmd.Parameters.AddWithValue("@starttime", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@d2", GetHighDate(fromdate));
cmd.Parameters.AddWithValue("@SOID", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
dtrouteamount = vdm.SelectQuery(cmd).Tables[0];
}
cmd = new MySqlCommand("SELECT branchdata.BranchName, branchdata.sno, modifiedroutes.RouteName, modifidroutssubtab.BranchID, modifiedroutes.Sno AS routesno, SUM(colltion.AmountPaid) AS amtpaid FROM branchdata INNER JOIN branchdata branchdata_1 ON branchdata.sno = branchdata_1.sno INNER JOIN modifiedroutes ON branchdata.sno = modifiedroutes.BranchID INNER JOIN (SELECT RefNo, Rank, LevelType, BranchID, CDate, EDate FROM modifiedroutesubtable WHERE (EDate IS NULL) AND (CDate <= @starttime) OR (EDate > @starttime) AND (CDate <= @starttime)) modifidroutssubtab ON modifiedroutes.Sno = modifidroutssubtab.RefNo INNER JOIN (SELECT Branchid, AmountPaid, PaidDate FROM collections WHERE (PaymentType = 'Cheque') AND (VarifyDate BETWEEN @d1 AND @d2) AND (CheckStatus = 'V')) colltion ON modifidroutssubtab.BranchID = colltion.Branchid WHERE (branchdata_1.SalesOfficeID = @SOID) AND (branchdata.SalesType IS NOT NULL) OR (branchdata.SalesType IS NOT NULL) AND (branchdata.sno = @BranchID) GROUP BY modifidroutssubtab.BranchID ORDER BY modifidroutssubtab.BranchID");
cmd.Parameters.AddWithValue("@starttime", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@d2", GetHighDate(fromdate));
cmd.Parameters.AddWithValue("@SOID", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
DataTable dtrouteChequeamount = vdm.SelectQuery(cmd).Tables[0];
cmd = new MySqlCommand("SELECT branchdata.BranchName, branchdata.sno, modifiedroutes.RouteName, modifidroutssubtab.BranchID, modifiedroutes.Sno AS routesno, SUM(colltion.AmountPaid) AS amtpaid FROM branchdata INNER JOIN branchdata branchdata_1 ON branchdata.sno = branchdata_1.sno INNER JOIN modifiedroutes ON branchdata.sno = modifiedroutes.BranchID INNER JOIN (SELECT RefNo, Rank, LevelType, BranchID, CDate, EDate FROM modifiedroutesubtable WHERE (EDate IS NULL) AND (CDate <= @starttime) OR (EDate > @starttime) AND (CDate <= @starttime)) modifidroutssubtab ON modifiedroutes.Sno = modifidroutssubtab.RefNo INNER JOIN (SELECT Branchid, AmountPaid, PaidDate FROM collections WHERE (PaymentType = 'Bank Transfer') AND (VarifyDate BETWEEN @d1 AND @d2) AND (banktransferstatus = 'V')) colltion ON modifidroutssubtab.BranchID = colltion.Branchid WHERE (branchdata_1.SalesOfficeID = @SOID) AND (branchdata.SalesType IS NOT NULL) OR (branchdata.SalesType IS NOT NULL) AND (branchdata.sno = @BranchID) GROUP BY modifidroutssubtab.BranchID ORDER BY modifidroutssubtab.BranchID");
cmd.Parameters.AddWithValue("@starttime", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@d2", GetHighDate(fromdate));
cmd.Parameters.AddWithValue("@SOID", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
DataTable dtrouteBankTransferamount = vdm.SelectQuery(cmd).Tables[0];
cmd = new MySqlCommand("SELECT Branchid, AmountPaid, Remarks, DATE_FORMAT(PaidDate, '%d/%b/%y') AS PDate, PayTime, EmpID, ReceiptNo, VarifyDate, TransactionType, AmountDebited, DiffAmount, SalesOfficeID, Status FROM collections WHERE (SalesOfficeID = @BranchID) AND (TransactionType = @type) AND (Status = @status) AND (PayTime BETWEEN @d1 AND @d2)");
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@d2", GetHighDate(fromdate));
cmd.Parameters.AddWithValue("@type", "Debit");
cmd.Parameters.AddWithValue("@status", "1");
DataTable dtAgent_Debits = vdm.SelectQuery(cmd).Tables[0];
cmd = new MySqlCommand("SELECT Branchid, AmountPaid, Remarks, DATE_FORMAT(PaidDate, '%d/%b/%y') AS PDate, PayTime, EmpID, ReceiptNo, VarifyDate, TransactionType, AmountDebited, DiffAmount, SalesOfficeID, Status FROM collections WHERE (SalesOfficeID = @BranchID) AND (TransactionType = @type) AND (Status = @status) AND (PayTime BETWEEN @d1 AND @d2)");
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@d2", GetHighDate(fromdate));
cmd.Parameters.AddWithValue("@type", "Credit");
cmd.Parameters.AddWithValue("@status", "1");
DataTable dtAgent_Credits = vdm.SelectQuery(cmd).Tables[0];
cmd = new MySqlCommand("SELECT sno, OppBalance, SaleValue, paidamount, ClosingBalance, IndentDate, EntryDate, agentid, salesofficeid, SaleQty, ReceivedAmount, DiffAmount, RouteId FROM tempduetrasactions where IndentDate between @d1 AND @d2 AND salesofficeid=@SOID");
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate.AddDays(-2)));
cmd.Parameters.AddWithValue("@d2", GetHighDate(fromdate.AddDays(-2)));
cmd.Parameters.AddWithValue("@SOID", ddlSalesOffice.SelectedValue);
DataTable dtOpp = vdm.SelectQuery(cmd).Tables[0];
DataTable Report = new DataTable();
Report.Columns.Add("Sno");
Report.Columns.Add("SalesOfficeId");
Report.Columns.Add("Route Code");
Report.Columns.Add("Agent Code");
Report.Columns.Add("Agent Name");
Report.Columns.Add("Oppening Balance");
Report.Columns.Add("Sale Qty").DataType = typeof(Double);
Report.Columns.Add("Sale Value").DataType = typeof(Double);
Report.Columns.Add("Received Amount").DataType = typeof(Double);
Report.Columns.Add("Debit Amount").DataType = typeof(Double);
Report.Columns.Add("Closing Amount").DataType = typeof(Double);
foreach (DataRow drroutes in dtroutes.Rows)
{
double totalsaleqty = 0;
double totalopp = 0;
double totalsalevalue = 0;
double totalamountpaid = 0;
double totalclosingbalance = 0;
int i = 1;
foreach (DataRow drsale in dtroutecollection.Select("routesno='" + drroutes["routesno"].ToString() + "'"))
{
DataRow newrow1 = Report.NewRow();
newrow1["Sno"] = i++;
newrow1["Route Code"] = drsale["routesno"].ToString();
newrow1["Agent Code"] = drsale["BranchID"].ToString();
newrow1["Agent Name"] = drsale["BranchName"].ToString();
newrow1["SalesOfficeId"] = ddlSalesOffice.SelectedValue;
double saleqty = 0;
double salevalue = 0;
double amountpaid = 0;
double chequeamountpaid = 0;
double banktrasferamountpaid = 0;
double oppbalance = 0;
double debitamount = 0;
double creditamount = 0;
foreach (DataRow dropp in dtOpp.Select("AgentId='" + drsale["BranchID"].ToString() + "'"))
{
double.TryParse(dropp["ClosingBalance"].ToString(), out oppbalance);
newrow1["Oppening Balance"] = oppbalance;
}
foreach (DataRow drdeb in dtAgent_Debits.Select("Branchid='" + drsale["BranchID"].ToString() + "'"))
{
double amountDebited = 0;
double.TryParse(drdeb["AmountDebited"].ToString(), out amountDebited);
debitamount += amountDebited;
newrow1["Debit Amount"] = amountDebited;
}
foreach (DataRow drcre in dtAgent_Credits.Select("Branchid='" + drsale["BranchID"].ToString() + "'"))
{
double amountcredited = 0;
double.TryParse(drcre["AmountPaid"].ToString(), out amountcredited);
creditamount += amountcredited;
}
double.TryParse(drsale["saleQty"].ToString(), out saleqty);
double.TryParse(drsale["salevalue"].ToString(), out salevalue);
newrow1["Sale Qty"] = Math.Round(saleqty, 2);
newrow1["Sale Value"] = Math.Round(salevalue, 2);
totalopp += Math.Round(oppbalance, 2);
totalsaleqty += Math.Round(saleqty, 2);
totalsalevalue += Math.Round(salevalue, 2);
foreach (DataRow drcoll in dtrouteamount.Select("BranchID='" + drsale["BranchID"].ToString() + "'"))
{
double.TryParse(drcoll["amtpaid"].ToString(), out amountpaid);
totalamountpaid += Math.Round(amountpaid, 2);
}
if (dtsalescollection.Rows.Count > 0)
{
foreach (DataRow drcoll in dtsalescollection.Select("BranchID='" + drsale["BranchID"].ToString() + "'"))
{
double.TryParse(drcoll["AmountPaid"].ToString(), out amountpaid);
totalamountpaid += Math.Round(amountpaid, 2);
}
}
foreach (DataRow drChequecoll in dtrouteChequeamount.Select("BranchID='" + drsale["BranchID"].ToString() + "'"))
{
double.TryParse(drChequecoll["amtpaid"].ToString(), out chequeamountpaid);
totalamountpaid += Math.Round(chequeamountpaid, 2);
}
foreach (DataRow drBankTransfer in dtrouteBankTransferamount.Select("BranchID='" + drsale["BranchID"].ToString() + "'"))
{
double.TryParse(drBankTransfer["amtpaid"].ToString(), out banktrasferamountpaid);
totalamountpaid += Math.Round(banktrasferamountpaid, 2);
}
double totamt = 0;
totamt = amountpaid + chequeamountpaid + banktrasferamountpaid;
newrow1["Received Amount"] = Math.Round(totamt, 2);
double closing = 0;
closing = (oppbalance + salevalue) - (totamt);
closing = closing + debitamount;
newrow1["Closing Amount"] = Math.Round(closing, 2);
totalclosingbalance += Math.Round(closing, 2);
if (saleqty == 0 && salevalue == 0 && totamt == 0 && closing == 0)
{
}
else
{
Report.Rows.Add(newrow1);
}
}
}
foreach (DataRow drroutes in Report.Rows)
{
if (drroutes["Route Code"].ToString() == "")
{
}
else
{
double oppeningbal1 = 0;
double saleqty1 = 0;
double salevalue1 = 0;
double receivedamt1 = 0;
double closingamt1 = 0;
double differenceamount1 = 0;
double debitamount = 0;
double.TryParse(drroutes["Oppening Balance"].ToString(), out oppeningbal1);
double.TryParse(drroutes["Sale Qty"].ToString(), out saleqty1);
double.TryParse(drroutes["Sale Value"].ToString(), out salevalue1);
double.TryParse(drroutes["Received Amount"].ToString(), out receivedamt1);
double.TryParse(drroutes["Closing Amount"].ToString(), out closingamt1);
double.TryParse(drroutes["Debit Amount"].ToString(), out debitamount);
cmd = new MySqlCommand("UPDATE tempbranchaccounts SET Amount=@Amount where Agentid=@BranchId");
cmd.Parameters.AddWithValue("@Amount", closingamt1);
cmd.Parameters.AddWithValue("@BranchId", drroutes["Agent Code"].ToString());
//vdm.Update(cmd);
cmd = new MySqlCommand("UPDATE tempduetrasactions SET salesofficeid = @SalesOfficeId, RouteId = @RouteId, agentid = @AgentId, IndentDate = @IndentDate, EntryDate = @EntryDate, OppBalance = @OppBalance,SaleQty = @SaleQty, SaleValue = @SaleValue, ReceivedAmount = @ReceivedAmount, ClosingBalance = @ClosingBalance, DiffAmount = @DiffAmount WHERE (IndentDate = @IndentDate) AND (agentid = @AgentId)");
cmd.Parameters.AddWithValue("@SalesOfficeId", drroutes["SalesOfficeId"].ToString());
cmd.Parameters.AddWithValue("@RouteId", drroutes["Route Code"].ToString());
cmd.Parameters.AddWithValue("@AgentId", drroutes["Agent Code"].ToString());
cmd.Parameters.AddWithValue("@IndentDate", GetLowDate(fromdate).AddDays(-1));
cmd.Parameters.AddWithValue("@EntryDate", ServerDateCurrentdate);
cmd.Parameters.AddWithValue("@OppBalance", Math.Round(oppeningbal1, 2));
cmd.Parameters.AddWithValue("@SaleQty", Math.Round(saleqty1, 2));
cmd.Parameters.AddWithValue("@SaleValue", Math.Round(salevalue1, 2));
cmd.Parameters.AddWithValue("@ReceivedAmount", Math.Round(receivedamt1, 2));
cmd.Parameters.AddWithValue("@ClosingBalance", Math.Round(closingamt1, 2));
cmd.Parameters.AddWithValue("@DiffAmount", Math.Round(debitamount, 2));
if (vdm.Update(cmd) == 0)
{
cmd = new MySqlCommand("insert into tempduetrasactions (salesofficeid,RouteId,agentid,IndentDate,EntryDate,OppBalance,SaleQty,SaleValue,ReceivedAmount,ClosingBalance,DiffAmount) values (@SalesOfficeId,@RouteId,@AgentId,@IndentDate,@EntryDate,@OppBalance,@SaleQty,@SaleValue,@ReceivedAmount,@ClosingBalance,@DiffAmount)");
cmd.Parameters.AddWithValue("@SalesOfficeId", drroutes["SalesOfficeId"].ToString());
cmd.Parameters.AddWithValue("@RouteId", drroutes["Route Code"].ToString());
cmd.Parameters.AddWithValue("@AgentId", drroutes["Agent Code"].ToString());
cmd.Parameters.AddWithValue("@IndentDate", GetLowDate(fromdate).AddDays(-1));
cmd.Parameters.AddWithValue("@EntryDate", ServerDateCurrentdate);
double oppeningbal = 0;
double saleqty = 0;
double salevalue = 0;
double receivedamt = 0;
double closingamt = 0;
double differenceamount = 0;
double debitamount1 = 0;
double.TryParse(drroutes["Oppening Balance"].ToString(), out oppeningbal);
double.TryParse(drroutes["Sale Qty"].ToString(), out saleqty);
double.TryParse(drroutes["Sale Value"].ToString(), out salevalue);
double.TryParse(drroutes["Received Amount"].ToString(), out receivedamt);
double.TryParse(drroutes["Closing Amount"].ToString(), out closingamt);
double.TryParse(drroutes["Debit Amount"].ToString(), out debitamount1);
cmd.Parameters.AddWithValue("@OppBalance", Math.Round(oppeningbal, 2));
cmd.Parameters.AddWithValue("@SaleQty", Math.Round(saleqty, 2));
cmd.Parameters.AddWithValue("@SaleValue", Math.Round(salevalue, 2));
cmd.Parameters.AddWithValue("@ReceivedAmount", Math.Round(receivedamt, 2));
cmd.Parameters.AddWithValue("@ClosingBalance", Math.Round(closingamt, 2));
cmd.Parameters.AddWithValue("@DiffAmount", Math.Round(debitamount1, 2));
vdm.insert(cmd);
}
}
}
//BranchID = "172";
cmd = new MySqlCommand("SELECT sno, invsno, openinginv, isuue_invqty, receive_invqty, closing_invqty, doe, closing_date, branchid FROM inventarytransactions where branchid=@branchid AND closing_date between @d1 and @d2");
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate).AddDays(-1));
cmd.Parameters.AddWithValue("@d2", GetHighDate(fromdate).AddDays(-1));
cmd.Parameters.AddWithValue("@branchid", ddlSalesOffice.SelectedValue);
DataTable dtinvopp = vdm.SelectQuery(cmd).Tables[0];
cmd = new MySqlCommand("SELECT triproutes.Tripdata_sno, SUM(tripinvdata.Qty) AS DispatchQty, SUM(tripinvdata.Remaining) AS ReceivedQty, invmaster.tempinvname AS InvName, invmaster.sno AS InvSno, dispatch.Branch_Id FROM tripdata INNER JOIN triproutes ON tripdata.Sno = triproutes.Tripdata_sno INNER JOIN dispatch ON triproutes.RouteID = dispatch.sno INNER JOIN tripinvdata ON tripdata.Sno = tripinvdata.Tripdata_sno INNER JOIN invmaster ON tripinvdata.invid = invmaster.sno WHERE (tripdata.I_Date BETWEEN @d1 AND @d2) AND (dispatch.Branch_Id = @branchid) GROUP BY invmaster.sno");
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@d2", GetHighDate(fromdate));
cmd.Parameters.AddWithValue("@branchid", ddlSalesOffice.SelectedValue);
DataTable dtinventransaction = vdm.SelectQuery(cmd).Tables[0];
double invissue = 0;
double invreceive = 0;
double openinvqty = 0;
foreach (DataRow drdaycoll in dtinventransaction.Rows)
{
double issuedqty = 0; double ReceivedQty = 0;
double.TryParse(drdaycoll["DispatchQty"].ToString(), out invissue);
double.TryParse(drdaycoll["ReceivedQty"].ToString(), out invreceive);
double presentopening = 0; double closinginvqty = 0;
foreach (DataRow drtripinv in dtinvopp.Select("invsno='" + drdaycoll["InvSno"].ToString() + "'AND branchid='" + ddlSalesOffice.SelectedValue + "'"))
{
double.TryParse(drtripinv["closing_invqty"].ToString(), out presentopening);
double oppening = presentopening + invreceive;
closinginvqty = oppening - invissue;
}
cmd = new MySqlCommand("UPDATE inventarytransactions SET openinginv = @openinginv, isuue_invqty = @isuue_invqty, receive_invqty = @receive_invqty, closing_invqty = @closing_invqty, doe = @doe WHERE (closing_date = @closing_date) AND (branchid = @branchid) AND (invsno=@invsno)");
cmd.Parameters.AddWithValue("@invsno", drdaycoll["InvSno"].ToString());
cmd.Parameters.AddWithValue("@openinginv", presentopening);
cmd.Parameters.AddWithValue("@isuue_invqty", invissue);
cmd.Parameters.AddWithValue("@receive_invqty", invreceive);
cmd.Parameters.AddWithValue("@closing_invqty", closinginvqty);
cmd.Parameters.AddWithValue("@doe", ServerDateCurrentdate);
cmd.Parameters.AddWithValue("@closing_date", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@branchid", ddlSalesOffice.SelectedValue);
if (vdm.Update(cmd) == 0)
{
cmd = new MySqlCommand("insert into inventarytransactions (invsno,openinginv,isuue_invqty,receive_invqty,closing_invqty,doe,closing_date,branchid) values (@invsno,@openinginv,@isuue_invqty,@receive_invqty,@closing_invqty,@doe,@closing_date,@branchid)");
cmd.Parameters.AddWithValue("@invsno", drdaycoll["InvSno"].ToString());
cmd.Parameters.AddWithValue("@openinginv", presentopening);
cmd.Parameters.AddWithValue("@isuue_invqty", invissue);
cmd.Parameters.AddWithValue("@receive_invqty", invreceive);
cmd.Parameters.AddWithValue("@closing_invqty", closinginvqty);
cmd.Parameters.AddWithValue("@doe", ServerDateCurrentdate);
cmd.Parameters.AddWithValue("@closing_date", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@branchid", ddlSalesOffice.SelectedValue);
vdm.insert(cmd);
}
}
}
catch (Exception ex)
{
}
}
protected void btnalertSave_Click(object sender, EventArgs e)
{
long refno = 0;
try
{
if (btnalertSave.Text == "Save")
{
cmd = new MySqlCommand("insert into alerts (AlertType,EmailID,username) values (@alerttype,@emailid,@username)");
cmd.Parameters.Add("@username", UserName);
cmd.Parameters.Add("@alerttype", "Email");
cmd.Parameters.Add("@emailid", ddlSelectEmail.SelectedItem.Value);
//cmd.Parameters.Add("@usertype", ddlUserType.SelectedValue);
refno = vdm.insertgetrefno(cmd);
foreach (ListItem obj in ckbVehicles.Items)
{
if (obj.Selected)
{
cmd = new MySqlCommand("insert into alert_subinfo (alert_sno,routecode) values(@Refno,@routeID)");
cmd.Parameters.Add("@Refno", refno);
cmd.Parameters.Add("@routeID", obj.Text);
vdm.insert(cmd);
}
}
Refresh();
//foreach (ListItem li in chblZones.Items)
//{
// if (li.Selected)
// {
// plants += li.Text + ",";
// }
//}
UpdateLogins();
lblmsg.Text = "Record added Successfully";
}
else
{
cmd = new MySqlCommand("Delete from alert_subinfo where alert_sno=@Refno");
cmd.Parameters.Add("@Refno", Session["reffno"]);
vdm.Delete(cmd);
cmd = new MySqlCommand("Update alerts set EmailID=@emailid where sno=@Refno ");
cmd.Parameters.Add("@Refno", Session["reffno"]);
cmd.Parameters.Add("@emailid", ddlSelectEmail.SelectedItem.Value);
//cmd.Parameters.Add("@loginid", txtLoginID.Text);
//cmd.Parameters.Add("@pwd", txtPassword.Text.Trim());
//cmd.Parameters.Add("@usertype", ddlUserType.SelectedValue);
vdm.Update(cmd);
foreach (ListItem obj in ckbVehicles.Items)
{
if (obj.Selected)
{
cmd = new MySqlCommand("insert into alert_subinfo (alert_sno,routecode) values(@Refno,@routeID)");
cmd.Parameters.Add("@Refno", Session["reffno"]);
cmd.Parameters.Add("@routeID", obj.Text);
vdm.insert(cmd);
}
}
Refresh();
UpdateLogins();
btnalertSave.Text = "Save";
lblmsg.Text = "Record Updated Successfully";
}
}
catch (Exception ex)
{
if (ex.Message.Contains("PRIMARY"))
{
//lbl_nofifier.Text = "Oops..! Username alredy existed";
}
else
{
//lbl_nofifier.Text = "Oops..! Error occurred please try again";
}
}
}
protected void BtnCategory_Save_Click(object sender, EventArgs e)
{
if (btn_save.Text == "Save")
{
// StringBuilder object
StringBuilder str = new StringBuilder();
Dictionary <string, string> selectedvalues = new Dictionary <string, string>();
cmd = new MySqlCommand("insert into location_groups (location_group_name,user_sno) values (@location_group_name,@user_sno)");
cmd.Parameters.Add("@location_group_name", txt_groupname.Text);
cmd.Parameters.Add("@user_sno", User_sno);
long sno = vdm.insertScalar(cmd);
// Select the checkboxes from the GridView control
for (int i = 0; i < grdMylocation.Rows.Count; i++)
{
GridViewRow row = grdMylocation.Rows[i];
bool isChecked = ((CheckBox)row.FindControl("chkSelect")).Checked;
if (isChecked)
{
cmd = new MySqlCommand("insert into location_groups_mapping (location_group_sno,branch_sno) values (@location_group_sno,@branch_sno) ");
cmd.Parameters.Add("@location_group_sno", sno);
cmd.Parameters.Add("@branch_sno", grdMylocation.Rows[i].Cells[9].Text);
vdm.insert(cmd);
// Column 2 is the name column]
//selectedvalues.Add(grdMylocation.Rows[i].Cells[11].Text, grdMylocation.Rows[i].Cells[10].Text);
// str.Append(grdMylocation.Rows[i].Cells[2].Text);
}
}
}
else
{
GridViewRow gvr = list_groups.SelectedRow;
string group_id = gvr.Cells[1].Text;
if (group_id != "")
{
cmd = new MySqlCommand("update location_groups set location_group_name=@location_group_name,user_sno=@user_sno where sno=@sno");
cmd.Parameters.Add("@location_group_name", txt_groupname.Text);
cmd.Parameters.Add("@user_sno", User_sno);
cmd.Parameters.Add("@sno", group_id);
vdm.Update(cmd);
cmd = new MySqlCommand("delete from location_groups_mapping where location_group_sno=@lgs");
cmd.Parameters.Add("@lgs", group_id);
vdm.Delete(cmd);
for (int i = 0; i < grdMylocation.Rows.Count; i++)
{
GridViewRow row = grdMylocation.Rows[i];
bool isChecked = ((CheckBox)row.FindControl("chkSelect")).Checked;
if (isChecked)
{
cmd = new MySqlCommand("insert into location_groups_mapping (location_group_sno,branch_sno) values (@location_group_sno,@branch_sno) ");
cmd.Parameters.Add("@location_group_sno", group_id);
cmd.Parameters.Add("@branch_sno", grdMylocation.Rows[i].Cells[9].Text);
vdm.insert(cmd);
// Column 2 is the name column]
//selectedvalues.Add(grdMylocation.Rows[i].Cells[11].Text, grdMylocation.Rows[i].Cells[10].Text);
// str.Append(grdMylocation.Rows[i].Cells[2].Text);
}
}
}
}
FillCategoryName();
BtnMyLocatoinRefresh_Click(null, null);
// prints out the result
// Response.Write(str.ToString());
}
void GetReport()
{
try
{
lblmsg.Text = "";
pnlHide.Visible = true;
Report = new DataTable();
Session["RouteName"] = ddlSalesOffice.SelectedItem.Text;
Session["xporttype"] = "TallySales";
Session["IDate"] = DateTime.Now.AddDays(1).ToString("dd/MM/yyyy");
vdm = new VehicleDBMgr();
DateTime fromdate = DateTime.Now;
string[] dateFromstrig = txtFromdate.Text.Split(' ');
if (dateFromstrig.Length > 1)
{
if (dateFromstrig[0].Split('-').Length > 0)
{
string[] dates = dateFromstrig[0].Split('-');
string[] times = dateFromstrig[1].Split(':');
fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
lbl_selfromdate.Text = fromdate.ToString("dd/MM/yyyy");
lblRoutName.Text = ddlSalesOffice.SelectedItem.Text;
Session["filename"] = ddlSalesOffice.SelectedItem.Text + " Tally Sales " + fromdate.ToString("dd/MM/yyyy");
DateTime ReportDate = VehicleDBMgr.GetTime(vdm.conn);
DateTime dtapril = new DateTime();
DateTime dtmarch = new DateTime();
int currentyear = ReportDate.Year;
int nextyear = ReportDate.Year + 1;
if (ReportDate.Month > 3)
{
string apr = "4/1/" + currentyear;
dtapril = DateTime.Parse(apr);
string march = "3/31/" + nextyear;
dtmarch = DateTime.Parse(march);
}
if (ReportDate.Month <= 3)
{
string apr = "4/1/" + (currentyear - 1);
dtapril = DateTime.Parse(apr);
string march = "3/31/" + (nextyear - 1);
dtmarch = DateTime.Parse(march);
}
TimeSpan datespan = ReportDate.Subtract(fromdate);
int NoOfdays = datespan.Days;
Report = new DataTable();
if (ddltaxtype.SelectedValue == "Non Tax")
{
Report.Columns.Add("Ledger Type");
Report.Columns.Add("Customer Name");
Report.Columns.Add("Invoce No.");
Report.Columns.Add("Invoice Date");
Report.Columns.Add("HSN CODE");
Report.Columns.Add("Item Name");
Report.Columns.Add("Qty");
Report.Columns.Add("Rate");
Report.Columns.Add("Taxable Value");
Report.Columns.Add("SGST%");
Report.Columns.Add("SGST Amount");
Report.Columns.Add("CGST%");
Report.Columns.Add("CGST Amount");
Report.Columns.Add("IGST%");
Report.Columns.Add("IGST Amount");
Report.Columns.Add("Net Value");
Report.Columns.Add("Narration");
Report.Columns.Add("ProductID");
int i = 1;
cmd = new MySqlCommand("SELECT branchdata.sno,branchdata.Branchcode,branchdata.companycode, branchdata.incentivename, branchdata.BranchName,branchdata.stateid, statemastar.statename, statemastar.statecode , statemastar.gststatecode FROM branchdata INNER JOIN statemastar ON branchdata.stateid = statemastar.sno WHERE (branchdata.sno = @BranchID)");
if (Session["salestype"].ToString() == "Plant")
{
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
}
else
{
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
}
DataTable dtstate = vdm.SelectQuery(cmd).Tables[0];
string statename = "";
string branchcode = "";
string statecode = "";
string fromstateid = "";
string Branchcode = "";
string gststatecode = "";
string companycode = "";
if (dtstate.Rows.Count > 0)
{
statename = dtstate.Rows[0]["statename"].ToString();
branchcode = dtstate.Rows[0]["branchcode"].ToString();
statecode = dtstate.Rows[0]["statecode"].ToString();
fromstateid = dtstate.Rows[0]["stateid"].ToString();
gststatecode = dtstate.Rows[0]["gststatecode"].ToString();
companycode = dtstate.Rows[0]["companycode"].ToString();
}
cmd = new MySqlCommand("SELECT dispatch.DispName, dispatch.sno, dispatch.BranchID, tripdata.I_Date,tripdata.dcno, tripdata.Sno AS TripSno, dispatch.DispMode, branchmappingtable.SuperBranch, triproutes.Tripdata_sno FROM dispatch INNER JOIN triproutes ON dispatch.sno = triproutes.RouteID INNER JOIN tripdata ON triproutes.Tripdata_sno = tripdata.Sno INNER JOIN branchmappingtable ON dispatch.BranchID = branchmappingtable.SubBranch WHERE (dispatch.BranchID = @BranchID) AND (tripdata.I_Date BETWEEN @d1 AND @d2)and (dispatch.DispType='SO') and (tripdata.Status<>'C') OR (tripdata.I_Date BETWEEN @d1 AND @d2) AND (branchmappingtable.SuperBranch = @SuperBranch) and (dispatch.DispType='SO')and (tripdata.Status<>'C') GROUP BY tripdata.Sno ORDER BY dispatch.sno");
cmd.Parameters.AddWithValue("@SuperBranch", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate.AddDays(-1)));
cmd.Parameters.AddWithValue("@d2", GetHighDate(fromdate.AddDays(-1)));
DataTable dtDispnames = vdm.SelectQuery(cmd).Tables[0];
string DCNO = "";
foreach (DataRow dr in dtDispnames.Rows)
{
string tripID = ""; string soid = "";
cmd = new MySqlCommand("SELECT SUM(leakages.ShortQty) AS ShortQty, SUM(leakages.FreeMilk) AS FreeMilk, productsdata.tproduct, branchproducts.unitprice, leakages.invoiceno, products_category.tcategory, productsdata.hsncode, productsdata.igst, productsdata.cgst, productsdata.sgst, leakages.ProductID FROM leakages INNER JOIN tripdata ON leakages.TripID = tripdata.Sno INNER JOIN productsdata ON leakages.ProductID = productsdata.sno INNER JOIN branchproducts ON productsdata.sno = branchproducts.product_sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno WHERE (tripdata.ATripid = @TripID) AND (branchproducts.branch_sno = @BranchID) AND (productsdata.igst = 0) GROUP BY productsdata.tproduct, products_category.tcategory, leakages.ProductID");
//cmd = new MySqlCommand("SELECT SUM(leakages.ShortQty) AS ShortQty, SUM(leakages.FreeMilk) AS FreeMilk, productsdata.tproduct, branchproducts.unitprice, leakages.invoiceno, products_category.tcategory,productsdata.hsncode, productsdata.igst, productsdata.cgst, productsdata.sgst FROM leakages INNER JOIN tripdata ON leakages.TripID = tripdata.Sno INNER JOIN productsdata ON leakages.ProductID = productsdata.sno INNER JOIN branchproducts ON productsdata.sno = branchproducts.product_sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno WHERE (tripdata.ATripid = @TripID) AND (branchproducts.branch_sno = @BranchID) GROUP BY productsdata.tproduct, products_category.tcategory");
cmd.Parameters.AddWithValue("@TripID", dr["TripSno"].ToString());
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
DataTable Dtfreemilk = vdm.SelectQuery(cmd).Tables[0];
cmd = new MySqlCommand("SELECT branchleaktrans.ShortQty, branchleaktrans.FreeQty AS FreeMilk, productsdata.tproduct, branchproducts.unitprice, branchleaktrans.invoiceno, products_category.tcategory, productsdata.hsncode,productsdata.igst, productsdata.cgst, productsdata.sgst FROM branchproducts INNER JOIN productsdata ON branchproducts.product_sno = productsdata.sno INNER JOIN branchleaktrans ON productsdata.sno = branchleaktrans.ProdId INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno WHERE (branchproducts.branch_sno = @BranchID) AND (branchleaktrans.TripId = @TripID) AND (productsdata.igst = 0) GROUP BY productsdata.tproduct, products_category.tcategory");
cmd.Parameters.AddWithValue("@TripID", dr["TripSno"].ToString());
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
DataTable dtsalesofficeshortfree = vdm.SelectQuery(cmd).Tables[0];
DataTable newdt = new DataTable();
newdt = Dtfreemilk.Copy();
newdt.Merge(dtsalesofficeshortfree, true, MissingSchemaAction.Ignore);
int new_countdc = 0;
if (newdt.Rows.Count > 0)
{
int.TryParse(newdt.Rows[0]["invoiceno"].ToString(), out new_countdc);
}
if (new_countdc > 0)
{
}
else
{
if (Dtfreemilk.Rows.Count > 0)
{
//Hardcore this are the subbranches of sales offices.so for GST invoice purpose we need to maintain below processs
if (ddlSalesOffice.SelectedValue == "527" || ddlSalesOffice.SelectedValue == "2948" || ddlSalesOffice.SelectedValue == "282")
{
if (ddlSalesOffice.SelectedValue == "2948")
{
soid = "285";
}
if (ddlSalesOffice.SelectedValue == "527")
{
soid = "174";
}
if (ddlSalesOffice.SelectedValue == "282")
{
soid = "172";
}
}
else
{
if (ddlSalesOffice.SelectedValue == "572" || ddlSalesOffice.SelectedValue == "458")
{
soid = "158";
}
soid = ddlSalesOffice.SelectedValue;
}
double freeqty = 0; double taxfreeqty = 0;
foreach (DataRow branch in newdt.Rows)
{
//Free
if (branch["sgst"].ToString() == "0")
{
double Qty = 0;
double.TryParse(branch["FreeMilk"].ToString(), out Qty);
freeqty += Qty;
}
}
if (freeqty > 0)
{
cmd = new MySqlCommand("SELECT IFNULL(MAX(agentdcno), 0) + 1 AS Sno FROM agentdc WHERE (soid = @soid) AND (IndDate BETWEEN @d1 AND @d2)");
cmd.Parameters.AddWithValue("@soid", soid);
cmd.Parameters.AddWithValue("@d1", GetLowDate(dtapril));
cmd.Parameters.AddWithValue("@d2", GetHighDate(dtmarch));
DataTable dtadcno = vdm.SelectQuery(cmd).Tables[0];
string invno = dtadcno.Rows[0]["Sno"].ToString();
cmd = new MySqlCommand("Insert Into Agentdc (BranchId,IndDate,agentdcno,soid,stateid,companycode,moduleid,doe,invoicetype) Values(@BranchId,@IndDate,@agentdcno,@soid,@stateid,@companycode,@moduleid,@doe,@invoicetype)");
cmd.Parameters.AddWithValue("@BranchId", dr["sno"].ToString());
cmd.Parameters.AddWithValue("@IndDate", fromdate.AddDays(-1));
cmd.Parameters.AddWithValue("@agentdcno", invno);
cmd.Parameters.AddWithValue("@soid", soid);
cmd.Parameters.AddWithValue("@stateid", gststatecode);
cmd.Parameters.AddWithValue("@companycode", companycode);
cmd.Parameters.AddWithValue("@doe", ReportDate);
cmd.Parameters.AddWithValue("@moduleid", "4");// Module 4 is Credit Note (Ex...Leaks)
cmd.Parameters.AddWithValue("@invoicetype", "TOSales");
vdm.insert(cmd);
cmd = new MySqlCommand("UPDATE leakages t1 JOIN tripdata t2 ON t1.TripID=t2.Sno JOIN productsdata as t3 ON t3.sno=t1.ProductID SET t1.Invoiceno = @invoiceno WHERE t2.ATripid=@TripID and t3.igst=0");
//cmd = new MySqlCommand("UPDATE leakages t1 JOIN tripdata t2 ON t1.TripID=t2 .Sno SET t1.Invoiceno = @invoiceno WHERE t2 .ATripid=@TripID");
cmd.Parameters.AddWithValue("@invoiceno", invno);
cmd.Parameters.AddWithValue("@TripID", dr["TripSno"].ToString());
vdm.Update(cmd);
cmd = new MySqlCommand("Update branchleaktrans t1 join productsdata AS t2 ON t1.ProdId=t2.sno set t1.invoiceno=@invoiceno where t1.TripID=@TripID and t2.igst=0");
//cmd = new MySqlCommand("Update branchleaktrans set invoiceno=@invoiceno where TripID=@TripID");
cmd.Parameters.AddWithValue("@invoiceno", invno);
cmd.Parameters.AddWithValue("@TripID", dr["TripSno"].ToString());
vdm.Update(cmd);
int.TryParse(invno, out new_countdc);
}
}
}
DCNO = "";
if (new_countdc <= 10)
{
DCNO = "0000" + new_countdc;
}
if (new_countdc >= 10 && new_countdc <= 99)
{
DCNO = "000" + new_countdc;
}
if (new_countdc >= 99 && new_countdc <= 999)
{
DCNO = "00" + new_countdc;
}
if (new_countdc > 999)
{
DCNO = "0" + new_countdc;
}
if (ddlSalesOffice.SelectedValue == "306")
{
if (fromdate.AddDays(1).Month > 3)
{
DCNO = branchcode + "/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "N/" + DCNO;
}
else
{
if (fromdate.AddDays(1).Month <= 3)
{
DCNO = branchcode + "/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "N/" + DCNO;
}
else
{
DCNO = branchcode + "/" + dtapril.AddYears(-1).ToString("yy") + "-" + dtmarch.AddYears(-1).ToString("yy") + "N/" + DCNO;
}
}
}
else
{
if (fromdate.Month > 3)
{
DCNO = branchcode + "/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "N/" + DCNO;
}
else
{
if (fromdate.Month <= 3)
{
DCNO = branchcode + "/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "N/" + DCNO;
}
else
{
DCNO = branchcode + "/" + dtapril.AddYears(-1).ToString("yy") + "-" + dtmarch.AddYears(-1).ToString("yy") + "N/" + DCNO;
}
}
//if (fromdate.Month > 3)
//{
// DCNO = branchcode + "/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "N/" + DCNO;
//}
//else
//{
// DCNO = branchcode + "/" + dtapril.AddYears(-1).ToString("yy") + "-" + dtmarch.AddYears(-1).ToString("yy") + "N/" + DCNO;
//}
}
double totfreeamount = 0;
foreach (DataRow branch in newdt.Rows)
{
if (branch["sgst"].ToString() == "0")
{
//Free
DataRow newrow = Report.NewRow();
double Qty = 0;
double.TryParse(branch["FreeMilk"].ToString(), out Qty);
double freeqty = 0;
freeqty += Qty;
if (Qty == 0.0)
{
}
else
{
double rate = 0;
double.TryParse(branch["unitprice"].ToString(), out rate);
Qty = Math.Round(Qty, 2);
newrow["Customer Name"] = "Free Sales-Milk-" + dtstate.Rows[0]["incentivename"].ToString();
newrow["Invoce No."] = DCNO;
if (ddlSalesOffice.SelectedValue == "306")
{
newrow["Invoice Date"] = fromdate.AddDays(1).ToString("dd-MMM-yyyy");
}
else
{
newrow["Invoice Date"] = fromdate.ToString("dd-MMM-yyyy");
}
newrow["Item Name"] = branch["tproduct"].ToString();
newrow["HSN CODE"] = branch["hsncode"].ToString();
newrow["Qty"] = Qty;
newrow["Rate"] = rate;
double invval = 0;
double sgst = 0;
double sgstamount = 0;
double cgst = 0;
double cgstamount = 0;
double taxval = 0;
double Igst = 0;
double Igstamount = 0;
double totRate = 0;
double tot_vatamount = 0;
double PAmount = 0;
double.TryParse(branch["Igst"].ToString(), out Igst);
double Igstcon = 100 + Igst;
Igstamount = (rate / Igstcon) * Igst;
Igstamount = Math.Round(Igstamount, 2);
totRate = Igstamount;
double igst = 0;
double.TryParse(branch["igst"].ToString(), out igst);
string tcategory = "";
if (igst == null || igst == 0.0)
{
tcategory = branch["tcategory"].ToString();
}
else
{
tcategory = branch["tcategory"].ToString() + "-CGST/SGST";
}
newrow["Ledger Type"] = tcategory.ToString();
double Vatrate = rate - totRate;
Vatrate = Math.Round(Vatrate, 2);
newrow["Rate"] = Vatrate.ToString();
PAmount = Qty * Vatrate;
newrow["Taxable Value"] = Math.Round(PAmount, 2);
tot_vatamount = (PAmount * Igst) / 100;
sgstamount = (tot_vatamount / 2);
sgstamount = Math.Round(sgstamount, 2);
newrow["sgst%"] = "'" + branch["sgst"].ToString();
newrow["sgst amount"] = sgstamount.ToString();
cgstamount = (tot_vatamount / 2);
cgstamount = Math.Round(cgstamount, 2);
newrow["cgst%"] = "'" + branch["cgst"].ToString();
newrow["cgst amount"] = cgstamount.ToString();
newrow["Igst%"] = "'" + 0;
newrow["Igst amount"] = 0;
newrow["ProductID"] = branch["ProductID"].ToString();
invval = Math.Round(invval, 2);
double netvalue = 0;
netvalue = invval + taxval;
netvalue = Math.Round(netvalue, 2);
double tot_amount = PAmount + tot_vatamount;
tot_amount = Math.Round(tot_amount, 2);
newrow["Net Value"] = tot_amount;
newrow["Narration"] = "Being the Sale Of Milk Through " + ddlSalesOffice.SelectedItem.Text + ". This is Free Milk Vide JV No " + dr["dcno"].ToString() + ",Emp Name " + Session["EmpName"].ToString();
Report.Rows.Add(newrow);
}
}
}
}
grdReports.DataSource = Report;
grdReports.DataBind();
Session["xportdata"] = Report;
}
else
{
Report.Columns.Add("Ledger Type");
Report.Columns.Add("Customer Name");
Report.Columns.Add("Invoce No.");
Report.Columns.Add("Invoice Date");
Report.Columns.Add("HSN CODE");
Report.Columns.Add("Item Name");
Report.Columns.Add("Qty");
Report.Columns.Add("Rate");
Report.Columns.Add("Taxable Value");
Report.Columns.Add("SGST%");
Report.Columns.Add("SGST Amount");
Report.Columns.Add("CGST%");
Report.Columns.Add("CGST Amount");
Report.Columns.Add("IGST%");
Report.Columns.Add("IGST Amount");
Report.Columns.Add("Net Value");
Report.Columns.Add("Narration");
int i = 1;
cmd = new MySqlCommand("SELECT branchdata.sno,branchdata.Branchcode,branchdata.companycode, branchdata.incentivename, branchdata.BranchName,branchdata.stateid, statemastar.statename, statemastar.statecode , statemastar.gststatecode FROM branchdata INNER JOIN statemastar ON branchdata.stateid = statemastar.sno WHERE (branchdata.sno = @BranchID)");
if (Session["salestype"].ToString() == "Plant")
{
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
}
else
{
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
}
DataTable dtstate = vdm.SelectQuery(cmd).Tables[0];
string statename = "";
string branchcode = "";
string statecode = "";
string fromstateid = "";
string Branchcode = "";
string gststatecode = "";
string companycode = "";
if (dtstate.Rows.Count > 0)
{
statename = dtstate.Rows[0]["statename"].ToString();
branchcode = dtstate.Rows[0]["branchcode"].ToString();
statecode = dtstate.Rows[0]["statecode"].ToString();
fromstateid = dtstate.Rows[0]["stateid"].ToString();
gststatecode = dtstate.Rows[0]["gststatecode"].ToString();
companycode = dtstate.Rows[0]["companycode"].ToString();
}
cmd = new MySqlCommand("SELECT dispatch.DispName, dispatch.sno, dispatch.BranchID, tripdata.I_Date,tripdata.dcno, tripdata.Sno AS TripSno, dispatch.DispMode, branchmappingtable.SuperBranch, triproutes.Tripdata_sno FROM dispatch INNER JOIN triproutes ON dispatch.sno = triproutes.RouteID INNER JOIN tripdata ON triproutes.Tripdata_sno = tripdata.Sno INNER JOIN branchmappingtable ON dispatch.BranchID = branchmappingtable.SubBranch WHERE (dispatch.BranchID = @BranchID) AND (tripdata.I_Date BETWEEN @d1 AND @d2)and (dispatch.DispType='SO') and (tripdata.Status<>'C') OR (tripdata.I_Date BETWEEN @d1 AND @d2) AND (branchmappingtable.SuperBranch = @SuperBranch) and (dispatch.DispType='SO')and (tripdata.Status<>'C') GROUP BY tripdata.Sno ORDER BY dispatch.sno");
cmd.Parameters.AddWithValue("@SuperBranch", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate.AddDays(-1)));
cmd.Parameters.AddWithValue("@d2", GetHighDate(fromdate.AddDays(-1)));
DataTable dtDispnames = vdm.SelectQuery(cmd).Tables[0];
string DCNO = "";
foreach (DataRow dr in dtDispnames.Rows)
{
string tripID = ""; string soid = "";
cmd = new MySqlCommand("SELECT SUM(leakages.ShortQty) AS ShortQty, SUM(leakages.FreeMilk) AS FreeMilk, productsdata.tproduct, branchproducts.unitprice, leakages.invoiceno, products_category.tcategory, productsdata.hsncode, productsdata.igst, productsdata.cgst, productsdata.sgst FROM leakages INNER JOIN tripdata ON leakages.TripID = tripdata.Sno INNER JOIN productsdata ON leakages.ProductID = productsdata.sno INNER JOIN branchproducts ON productsdata.sno = branchproducts.product_sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno WHERE (tripdata.ATripid = @TripID) AND (branchproducts.branch_sno = @BranchID) AND (productsdata.igst > 0) GROUP BY productsdata.tproduct, products_category.tcategory");
//cmd = new MySqlCommand("SELECT SUM(leakages.ShortQty) AS ShortQty, SUM(leakages.FreeMilk) AS FreeMilk, productsdata.tproduct, branchproducts.unitprice, leakages.invoiceno, products_category.tcategory,productsdata.hsncode, productsdata.igst, productsdata.cgst, productsdata.sgst FROM leakages INNER JOIN tripdata ON leakages.TripID = tripdata.Sno INNER JOIN productsdata ON leakages.ProductID = productsdata.sno INNER JOIN branchproducts ON productsdata.sno = branchproducts.product_sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno WHERE (tripdata.ATripid = @TripID) AND (branchproducts.branch_sno = @BranchID) GROUP BY productsdata.tproduct, products_category.tcategory");
cmd.Parameters.AddWithValue("@TripID", dr["TripSno"].ToString());
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
DataTable Dtfreemilk = vdm.SelectQuery(cmd).Tables[0];
cmd = new MySqlCommand("SELECT branchleaktrans.ShortQty, branchleaktrans.FreeQty AS FreeMilk, productsdata.tproduct, branchproducts.unitprice, branchleaktrans.invoiceno, products_category.tcategory, productsdata.hsncode,productsdata.igst, productsdata.cgst, productsdata.sgst FROM branchproducts INNER JOIN productsdata ON branchproducts.product_sno = productsdata.sno INNER JOIN branchleaktrans ON productsdata.sno = branchleaktrans.ProdId INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno WHERE (branchproducts.branch_sno = @BranchID) AND (branchleaktrans.TripId = @TripID) AND (productsdata.igst > 0) GROUP BY productsdata.tproduct, products_category.tcategory");
cmd.Parameters.AddWithValue("@TripID", dr["TripSno"].ToString());
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
DataTable dtsalesofficeshortfree = vdm.SelectQuery(cmd).Tables[0];
DataTable newdt = new DataTable();
newdt = Dtfreemilk.Copy();
newdt.Merge(dtsalesofficeshortfree, true, MissingSchemaAction.Ignore);
int new_countdc = 0;
if (newdt.Rows.Count > 0)
{
int.TryParse(newdt.Rows[0]["invoiceno"].ToString(), out new_countdc);
}
if (new_countdc > 0)
{
}
else
{
if (newdt.Rows.Count > 0)
{
//Hardcore this are the subbranches of sales offices.so for GST invoice purpose we need to maintain below processs
if (ddlSalesOffice.SelectedValue == "527" || ddlSalesOffice.SelectedValue == "2948" || ddlSalesOffice.SelectedValue == "282")
{
if (ddlSalesOffice.SelectedValue == "2948")
{
soid = "285";
}
if (ddlSalesOffice.SelectedValue == "527")
{
soid = "174";
}
if (ddlSalesOffice.SelectedValue == "282")
{
soid = "172";
}
}
else
{
if (ddlSalesOffice.SelectedValue == "572" || ddlSalesOffice.SelectedValue == "458")
{
soid = "158";
}
soid = ddlSalesOffice.SelectedValue;
}
double freeqty = 0; double taxfreeqty = 0;
foreach (DataRow branch in newdt.Rows)
{
//Free
if (branch["sgst"].ToString() != "0")
{
double Qty = 0;
double.TryParse(branch["FreeMilk"].ToString(), out Qty);
freeqty += Qty;
}
}
if (freeqty > 0)
{
cmd = new MySqlCommand("SELECT IFNULL(MAX(agentdcno), 0) + 1 AS Sno FROM agenttaxdc WHERE (soid = @soid) AND (IndDate BETWEEN @d1 AND @d2)");
cmd.Parameters.AddWithValue("@soid", soid);
cmd.Parameters.AddWithValue("@d1", GetLowDate(dtapril));
cmd.Parameters.AddWithValue("@d2", GetHighDate(dtmarch));
DataTable dtadcno = vdm.SelectQuery(cmd).Tables[0];
string invno = dtadcno.Rows[0]["Sno"].ToString();
cmd = new MySqlCommand("Insert Into agenttaxdc (BranchId,IndDate,agentdcno,soid,stateid,companycode,moduleid,doe,invoicetype) Values(@BranchId,@IndDate,@agentdcno,@soid,@stateid,@companycode,@moduleid,@doe,@invoicetype)");
cmd.Parameters.AddWithValue("@BranchId", dr["sno"].ToString());
cmd.Parameters.AddWithValue("@IndDate", fromdate.AddDays(-1));
cmd.Parameters.AddWithValue("@agentdcno", invno);
cmd.Parameters.AddWithValue("@soid", soid);
cmd.Parameters.AddWithValue("@stateid", gststatecode);
cmd.Parameters.AddWithValue("@companycode", companycode);
cmd.Parameters.AddWithValue("@doe", ReportDate);
cmd.Parameters.AddWithValue("@moduleid", "4");// Module 4 is Credit Note (Ex...Leaks)
cmd.Parameters.AddWithValue("@invoicetype", "TOSales");
vdm.insert(cmd);
cmd = new MySqlCommand("UPDATE leakages t1 JOIN tripdata t2 ON t1.TripID=t2.Sno JOIN productsdata as t3 ON t3.sno=t1.ProductID SET t1.Invoiceno = @invoiceno WHERE t2.ATripid=@TripID and t3.igst>0");
//cmd = new MySqlCommand("UPDATE leakages t1 JOIN tripdata t2 ON t1.TripID=t2.Sno JOIN productsdata as t3 ON t3.productid=t1.productid ON SET t1.Invoiceno = @invoiceno WHERE t2 .ATripid=@TripID and t3.igst>0)");
cmd.Parameters.AddWithValue("@invoiceno", invno);
cmd.Parameters.AddWithValue("@TripID", dr["TripSno"].ToString());
vdm.Update(cmd);
cmd = new MySqlCommand("Update branchleaktrans t1 join productsdata AS t2 ON t1.ProdId=t2.sno set t1.invoiceno=@invoiceno where t1.TripID=@TripID and t2.igst>0");
cmd.Parameters.AddWithValue("@invoiceno", invno);
cmd.Parameters.AddWithValue("@TripID", dr["TripSno"].ToString());
vdm.Update(cmd);
int.TryParse(invno, out new_countdc);
}
}
}
DCNO = "";
if (new_countdc <= 10)
{
DCNO = "0000" + new_countdc;
}
if (new_countdc >= 10 && new_countdc <= 99)
{
DCNO = "000" + new_countdc;
}
if (new_countdc >= 99 && new_countdc <= 999)
{
DCNO = "00" + new_countdc;
}
if (new_countdc > 999)
{
DCNO = "0" + new_countdc;
}
if (ddlSalesOffice.SelectedValue == "306")
{
if (fromdate.AddDays(1).Month > 3)
{
DCNO = branchcode + "/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "T/" + DCNO;
}
else
{
if (fromdate.AddDays(1).Month <= 3)
{
DCNO = branchcode + "/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "T/" + DCNO;
}
else
{
DCNO = branchcode + "/" + dtapril.AddYears(-1).ToString("yy") + "-" + dtmarch.AddYears(-1).ToString("yy") + "T/" + DCNO;
}
}
}
else
{
if (fromdate.Month > 3)
{
DCNO = branchcode + "/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "T/" + DCNO;
}
else
{
if (fromdate.Month <= 3)
{
DCNO = branchcode + "/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "T/" + DCNO;
}
else
{
DCNO = branchcode + "/" + dtapril.AddYears(-1).ToString("yy") + "-" + dtmarch.AddYears(-1).ToString("yy") + "T/" + DCNO;
}
}
}
double totfreeamount = 0;
foreach (DataRow branch in newdt.Rows)
{
//Free
if (branch["sgst"].ToString() != "0")
{
DataRow newrow = Report.NewRow();
double Qty = 0;
double.TryParse(branch["FreeMilk"].ToString(), out Qty);
double freeqty = 0;
freeqty += Qty;
if (Qty == 0.0)
{
}
else
{
double rate = 0;
double.TryParse(branch["unitprice"].ToString(), out rate);
Qty = Math.Round(Qty, 2);
newrow["Customer Name"] = "Free Sales-Milk-" + dtstate.Rows[0]["incentivename"].ToString();
newrow["Invoce No."] = DCNO;
if (ddlSalesOffice.SelectedValue == "306")
{
newrow["Invoice Date"] = fromdate.AddDays(1).ToString("dd-MMM-yyyy");
}
else
{
newrow["Invoice Date"] = fromdate.ToString("dd-MMM-yyyy");
}
newrow["Item Name"] = branch["tproduct"].ToString();
newrow["HSN CODE"] = branch["hsncode"].ToString();
newrow["Qty"] = Qty;
newrow["Rate"] = rate;
double invval = 0;
double sgst = 0;
double sgstamount = 0;
double cgst = 0;
double cgstamount = 0;
double taxval = 0;
double Igst = 0;
double Igstamount = 0;
double totRate = 0;
double tot_vatamount = 0;
double PAmount = 0;
double.TryParse(branch["Igst"].ToString(), out Igst);
double Igstcon = 100 + Igst;
Igstamount = (rate / Igstcon) * Igst;
Igstamount = Math.Round(Igstamount, 2);
totRate = Igstamount;
double igst = 0;
double.TryParse(branch["igst"].ToString(), out igst);
string tcategory = "";
if (igst == null || igst == 0.0)
{
tcategory = branch["tcategory"].ToString();
}
else
{
tcategory = branch["tcategory"].ToString() + "-CGST/SGST";
}
newrow["Ledger Type"] = tcategory.ToString();
double Vatrate = rate - totRate;
Vatrate = Math.Round(Vatrate, 2);
newrow["Rate"] = Vatrate.ToString();
PAmount = Qty * Vatrate;
newrow["Taxable Value"] = Math.Round(PAmount, 2);
tot_vatamount = (PAmount * Igst) / 100;
sgstamount = (tot_vatamount / 2);
sgstamount = Math.Round(sgstamount, 2);
newrow["sgst%"] = "'" + branch["sgst"].ToString();
newrow["sgst amount"] = sgstamount.ToString();
cgstamount = (tot_vatamount / 2);
cgstamount = Math.Round(cgstamount, 2);
newrow["cgst%"] = "'" + branch["cgst"].ToString();
newrow["cgst amount"] = cgstamount.ToString();
newrow["Igst%"] = "'" + 0;
newrow["Igst amount"] = 0;
invval = Math.Round(invval, 2);
double netvalue = 0;
netvalue = invval + taxval;
netvalue = Math.Round(netvalue, 2);
double tot_amount = PAmount + tot_vatamount;
tot_amount = Math.Round(tot_amount, 2);
newrow["Net Value"] = tot_amount;
newrow["Narration"] = "Being the Sale Of Milk Through " + ddlSalesOffice.SelectedItem.Text + ". This is Free Milk Vide JV No " + dr["dcno"].ToString() + ",Emp Name " + Session["EmpName"].ToString();
Report.Rows.Add(newrow);
}
}
}
}
grdReports.DataSource = Report;
grdReports.DataBind();
Session["xportdata"] = Report;
}
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
grdReports.DataSource = Report;
grdReports.DataBind();
}
}
void GetReport()
{
try
{
lblmsg.Text = "";
pnlHide.Visible = true;
Session["RouteName"] = ddlSalesOffice.SelectedItem.Text;
Session["IDate"] = DateTime.Now.AddDays(1).ToString("dd/MM/yyyy");
vdm = new VehicleDBMgr();
DateTime fromdate = DateTime.Now;
string[] dateFromstrig = txtFromdate.Text.Split(' ');
if (dateFromstrig.Length > 1)
{
if (dateFromstrig[0].Split('-').Length > 0)
{
string[] dates = dateFromstrig[0].Split('-');
string[] times = dateFromstrig[1].Split(':');
fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
DataTable Report = new DataTable();
Report.Columns.Add("DOE");
Report.Columns.Add("Ref Receipt");
Report.Columns.Add("Receipt");
Report.Columns.Add("Type");
Report.Columns.Add("Name");
Report.Columns.Add("Amount").DataType = typeof(Double);
lbl_selfromdate.Text = fromdate.ToString("dd/MM/yyyy");
lblRoutName.Text = ddlSalesOffice.SelectedItem.Text;
DateTime ReportDate = VehicleDBMgr.GetTime(vdm.conn);
DateTime dtapril = new DateTime();
DateTime dtmarch = new DateTime();
int currentyear = ReportDate.Year;
int nextyear = ReportDate.Year + 1;
if (ReportDate.Month > 3)
{
string apr = "4/1/" + currentyear;
dtapril = DateTime.Parse(apr);
string march = "3/31/" + nextyear;
dtmarch = DateTime.Parse(march);
}
if (ReportDate.Month <= 3)
{
string apr = "4/1/" + (currentyear - 1);
dtapril = DateTime.Parse(apr);
string march = "3/31/" + (nextyear - 1);
dtmarch = DateTime.Parse(march);
}
Session["xporttype"] = "SapPayments";
string ledger = ""; string whcode = "";
string ladger_dr_code = "";
cmd = new MySqlCommand("SELECT tbranchname, ladger_dr,whcode,ladger_dr_code FROM branchdata WHERE (sno = @BranchID)");
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
DataTable dtledger = vdm.SelectQuery(cmd).Tables[0];
if (dtledger.Rows.Count > 0)
{
ledger = dtledger.Rows[0]["ladger_dr"].ToString();
whcode = dtledger.Rows[0]["whcode"].ToString();
ladger_dr_code = dtledger.Rows[0]["ladger_dr_code"].ToString();
}
Session["filename"] = ddlSalesOffice.SelectedItem.Text + " Sap Payments" + fromdate.ToString("dd/MM/yyyy");
//cmd = new MySqlCommand("SELECT cashpayables.VocherID, cashpayables.Remarks,subpayable.sno, subpayable.Amount, accountheads.HeadName FROM cashpayables INNER JOIN subpayable ON cashpayables.Sno = subpayable.RefNo INNER JOIN accountheads ON subpayable.HeadSno = accountheads.Sno WHERE (cashpayables.BranchID = @BranchID) AND (cashpayables.DOE BETWEEN @d1 AND @d2) AND (cashpayables.VoucherType = 'Debit') AND (cashpayables.Status = 'P')");
cmd = new MySqlCommand("SELECT cashpayables.VocherID, cashpayables.Remarks, subpayable.sno, subpayable.Amount,accountheads.accountcode,subpayable.HeadSno, accountheads.HeadName, cashpayables.Approvedby, cashpayables.Status,empmanage.EmpName, cashpayables.VoucherType, cashpayables.DOE, empmanage_1.EmpName AS createdby, empmanage_1.Sno AS createemp,empmanage.Sno AS approvedemp FROM cashpayables INNER JOIN subpayable ON cashpayables.Sno = subpayable.RefNo INNER JOIN accountheads ON subpayable.HeadSno = accountheads.Sno INNER JOIN empmanage ON cashpayables.Approvedby = empmanage.Sno INNER JOIN empmanage empmanage_1 ON cashpayables.Created_by = empmanage_1.Sno WHERE (cashpayables.BranchID = @BranchID) AND (cashpayables.DOE BETWEEN @d1 AND @d2) AND (cashpayables.VoucherType = 'Debit') AND (cashpayables.Status = 'P')");
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@d2", GetHighDate(fromdate));
DataTable dtAgent = vdm.SelectQuery(cmd).Tables[0];
if (dtAgent.Rows.Count > 0)
{
DataView view = new DataView(dtAgent);
dtReport = new DataTable();
dtReport.Columns.Add("Sno");
dtReport.Columns.Add("Voucher Date");
dtReport.Columns.Add("Voucher No");
dtReport.Columns.Add("Voucher Type");
dtReport.Columns.Add("whcode");
Report.Columns.Add("Series");
dtReport.Columns.Add("CreatedBy");
dtReport.Columns.Add("ApprovedBy");
dtReport.Columns.Add("Credit Code");
dtReport.Columns.Add("Ledger (Cr)");
dtReport.Columns.Add("Debit Code");
dtReport.Columns.Add("Ledger (Dr)");
dtReport.Columns.Add("Amount");
dtReport.Columns.Add("Narration");
//DataTable distincttable = view.ToTable(true, "BranchName", "BSno");
int i = 1;
foreach (DataRow branch in dtAgent.Rows)
{
string VoucherNo = "";
cmd = new MySqlCommand("SELECT vouchercode,RefNo, HeadDesc, Amount, HeadSno, sno, branchid, paiddate FROM subpayable WHERE (branchid = @BranchID) AND (paiddate BETWEEN @d1 AND @d2)");
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@d2", GetLowDate(fromdate));
DataTable dtVoucher = vdm.SelectQuery(cmd).Tables[0];
if (dtVoucher.Rows.Count > 0)
{
DataRow[] drvoucher = dtVoucher.Select("branchid='" + ddlSalesOffice.SelectedValue + "' and sno='" + branch["sno"].ToString() + "' and HeadSno='" + branch["HeadSno"].ToString() + "'");
if (drvoucher.Length > 0)
{
foreach (DataRow drv in drvoucher)
{
VoucherNo = drv.ItemArray[0].ToString();
}
}
else
{
cmd = new MySqlCommand("SELECT IFNULL(MAX(vouchercode), 0) + 1 AS Sno FROM subpayable WHERE (branchid = @branchid) AND (paiddate BETWEEN @d1 AND @d2)");
cmd.Parameters.AddWithValue("@branchid", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@HeadSno", branch["HeadSno"].ToString());
cmd.Parameters.AddWithValue("@d1", GetLowDate(dtapril.AddDays(-1)));
cmd.Parameters.AddWithValue("@d2", GetHighDate(dtmarch.AddDays(-1)));
DataTable dtvoucherno = vdm.SelectQuery(cmd).Tables[0];
VoucherNo = dtvoucherno.Rows[0]["Sno"].ToString();
cmd = new MySqlCommand("update subpayable set vouchercode=@vouchercode, paiddate=@paiddate,branchid=@branchid where (sno=@sno) AND (HeadSno = @HeadSno)");;
cmd.Parameters.AddWithValue("@vouchercode", VoucherNo);
cmd.Parameters.AddWithValue("@paiddate", fromdate);
cmd.Parameters.AddWithValue("@branchid", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@sno", branch["sno"].ToString());
cmd.Parameters.AddWithValue("@HeadSno", branch["HeadSno"].ToString());
vdm.Update(cmd);
}
}
else
{
cmd = new MySqlCommand("SELECT IFNULL(MAX(vouchercode), 0) + 1 AS Sno FROM subpayable WHERE (branchid = @branchid) AND (paiddate BETWEEN @d1 AND @d2)");
cmd.Parameters.AddWithValue("@branchid", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@HeadSno", branch["HeadSno"].ToString());
cmd.Parameters.AddWithValue("@d1", GetLowDate(dtapril.AddDays(-1)));
cmd.Parameters.AddWithValue("@d2", GetHighDate(dtmarch.AddDays(-1)));
DataTable dtvoucherno = vdm.SelectQuery(cmd).Tables[0];
VoucherNo = dtvoucherno.Rows[0]["Sno"].ToString();
cmd = new MySqlCommand("update subpayable set vouchercode=@vouchercode, paiddate=@paiddate,branchid=@branchid where (sno=@sno) AND (HeadSno = @HeadSno)");;
cmd.Parameters.AddWithValue("@vouchercode", VoucherNo);
cmd.Parameters.AddWithValue("@paiddate", fromdate);
cmd.Parameters.AddWithValue("@branchid", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@sno", branch["sno"].ToString());
cmd.Parameters.AddWithValue("@HeadSno", branch["HeadSno"].ToString());
vdm.Update(cmd);
}
if (VoucherNo == "0")
{
cmd = new MySqlCommand("SELECT IFNULL(MAX(vouchercode), 0) + 1 AS Sno FROM subpayable WHERE (branchid = @branchid) AND (paiddate BETWEEN @d1 AND @d2)");
cmd.Parameters.AddWithValue("@branchid", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@HeadSno", branch["HeadSno"].ToString());
cmd.Parameters.AddWithValue("@d1", GetLowDate(dtapril.AddDays(-1)));
cmd.Parameters.AddWithValue("@d2", GetHighDate(dtmarch.AddDays(-1)));
DataTable dtvoucherno = vdm.SelectQuery(cmd).Tables[0];
VoucherNo = dtvoucherno.Rows[0]["Sno"].ToString();
cmd = new MySqlCommand("update subpayable set vouchercode=@vouchercode, paiddate=@paiddate,branchid=@branchid where (RefNo=@RefNo) AND (HeadSno = @HeadSno)");;
cmd.Parameters.AddWithValue("@vouchercode", VoucherNo);
cmd.Parameters.AddWithValue("@paiddate", fromdate);
cmd.Parameters.AddWithValue("@branchid", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@RefNo", branch["sno"].ToString());
cmd.Parameters.AddWithValue("@HeadSno", branch["HeadSno"].ToString());
vdm.Update(cmd);
}
string NewVoucherNo = "0";
int countdc = 0;
int.TryParse(VoucherNo, out countdc);
if (countdc <= 10)
{
NewVoucherNo = "0000" + countdc;
}
if (countdc >= 10 && countdc <= 99)
{
NewVoucherNo = "000" + countdc;
}
if (countdc >= 99 && countdc <= 999)
{
NewVoucherNo = "00" + countdc;
}
if (countdc >= 999)
{
NewVoucherNo = "0" + countdc;
}
DataRow newrow = dtReport.NewRow();
newrow["Sno"] = i++;
newrow["Voucher Date"] = fromdate.ToString("dd-MMM-yyyy");
newrow["Voucher No"] = ddlSalesOffice.SelectedValue + dtapril.ToString("yy") + dtmarch.ToString("yy") + NewVoucherNo;
newrow["Voucher Type"] = "Cash Payment Import";
newrow["Credit Code"] = ladger_dr_code;
newrow["Ledger (Cr)"] = ledger;
newrow["Debit Code"] = branch["accountcode"].ToString();
newrow["Ledger (Dr)"] = branch["HeadName"].ToString();
newrow["Amount"] = branch["Amount"].ToString();
newrow["CreatedBy"] = branch["createdby"].ToString();
newrow["ApprovedBy"] = branch["EmpName"].ToString();
newrow["whcode"] = whcode;
newrow["Narration"] = branch["Remarks"].ToString() + ",VoucherID " + branch["VocherID"].ToString() + ",Emp Name " + Session["EmpName"].ToString();
dtReport.Rows.Add(newrow);
}
grdReports.DataSource = dtReport;
grdReports.DataBind();
Session["xportdata"] = dtReport;
}
else
{
pnlHide.Visible = false;
lblmsg.Text = "No Indent Found";
grdReports.DataSource = dtReport;
grdReports.DataBind();
}
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
grdReports.DataSource = dtReport;
grdReports.DataBind();
}
}
protected void BtnSave_Click(object sender, EventArgs e)
{
try
{
if (ddlreporttype.SelectedItem.Text == "Amount Balance")
{
vdm = new VehicleDBMgr();
string SalesOfficeId = ddlSalesOffice.SelectedValue;
if (SalesOfficeId == "572")
{
SalesOfficeId = "158";
}
string[] datestrig = txtdate.Text.Split(' ');
if (datestrig.Length > 1)
{
if (datestrig[0].Split('-').Length > 0)
{
string[] dates = datestrig[0].Split('-');
string[] times = datestrig[1].Split(':');
fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
fromdate = fromdate.AddDays(-1);
Prevdate = fromdate.AddDays(-1);
DateTime ServerDateCurrentdate = VehicleDBMgr.GetTime(vdm.conn);
cmd = new MySqlCommand("SELECT Sno, SalesOfficeId, RouteId, AgentId, IndentDate, EntryDate, OppBalance, SaleQty, SaleValue, ReceivedAmount, ClosingBalance, DiffAmount FROM duetransactions WHERE (SalesOfficeId = @SalesOfficeId) AND (IndentDate BETWEEN @d1 AND @d2) ORDER BY IndentDate");
cmd.Parameters.AddWithValue("@SalesOfficeId", SalesOfficeId);
cmd.Parameters.AddWithValue("@d1", GetLowDate(Prevdate));
cmd.Parameters.AddWithValue("@d2", GetHighDate(Prevdate));
DataTable dtprevdate = vdm.SelectQuery(cmd).Tables[0];
DataTable dt = (DataTable)Session["AmountClosing"];
if (dtprevdate.Rows.Count > 0)
{
foreach (DataRow drroutes in dt.Rows)
{
if (drroutes["Route Code"].ToString() == "")
{
}
else
{
cmd = new MySqlCommand("UPDATE duetransactions SET SalesOfficeId = @SalesOfficeId, RouteId = @RouteId, AgentId = @AgentId, IndentDate = @IndentDate, EntryDate = @EntryDate, OppBalance = @OppBalance,SaleQty = @SaleQty, SaleValue = @SaleValue, ReceivedAmount = @ReceivedAmount, ClosingBalance = @ClosingBalance, DiffAmount = @DiffAmount WHERE (IndentDate = @IndentDate) AND (AgentId = @AgentId)");
cmd.Parameters.AddWithValue("@SalesOfficeId", SalesOfficeId);
cmd.Parameters.AddWithValue("@RouteId", drroutes["Route Code"].ToString());
cmd.Parameters.AddWithValue("@AgentId", drroutes["Agent Code"].ToString());
cmd.Parameters.AddWithValue("@IndentDate", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@EntryDate", ServerDateCurrentdate);
double oppeningbal1 = 0;
double saleqty1 = 0;
double salevalue1 = 0;
double receivedamt1 = 0;
double closingamt1 = 0;
double differenceamount1 = 0;
double.TryParse(drroutes["Oppening Balance"].ToString(), out oppeningbal1);
double.TryParse(drroutes["Sale Qty"].ToString(), out saleqty1);
double.TryParse(drroutes["Sale Value"].ToString(), out salevalue1);
double.TryParse(drroutes["Received Amount"].ToString(), out receivedamt1);
double.TryParse(drroutes["Closing Amount"].ToString(), out closingamt1);
//double.TryParse(drroutes["Route Code"].ToString(), out differenceamount);
cmd.Parameters.AddWithValue("@OppBalance", Math.Round(oppeningbal1, 2));
cmd.Parameters.AddWithValue("@SaleQty", Math.Round(saleqty1, 2));
cmd.Parameters.AddWithValue("@SaleValue", Math.Round(salevalue1, 2));
cmd.Parameters.AddWithValue("@ReceivedAmount", Math.Round(receivedamt1, 2));
cmd.Parameters.AddWithValue("@ClosingBalance", Math.Round(closingamt1, 2));
cmd.Parameters.AddWithValue("@DiffAmount", Math.Round(differenceamount1, 2));
if (vdm.Update(cmd) == 0)
{
cmd = new MySqlCommand("insert into duetransactions (SalesOfficeId,RouteId,AgentId,IndentDate,EntryDate,OppBalance,SaleQty,SaleValue,ReceivedAmount,ClosingBalance,DiffAmount) values (@SalesOfficeId,@RouteId,@AgentId,@IndentDate,@EntryDate,@OppBalance,@SaleQty,@SaleValue,@ReceivedAmount,@ClosingBalance,@DiffAmount)");
cmd.Parameters.AddWithValue("@SalesOfficeId", SalesOfficeId);
cmd.Parameters.AddWithValue("@RouteId", drroutes["Route Code"].ToString());
cmd.Parameters.AddWithValue("@AgentId", drroutes["Agent Code"].ToString());
cmd.Parameters.AddWithValue("@IndentDate", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@EntryDate", ServerDateCurrentdate);
double oppeningbal = 0;
double saleqty = 0;
double salevalue = 0;
double receivedamt = 0;
double closingamt = 0;
double differenceamount = 0;
double.TryParse(drroutes["Oppening Balance"].ToString(), out oppeningbal);
double.TryParse(drroutes["Sale Qty"].ToString(), out saleqty);
double.TryParse(drroutes["Sale Value"].ToString(), out salevalue);
double.TryParse(drroutes["Received Amount"].ToString(), out receivedamt);
double.TryParse(drroutes["Closing Amount"].ToString(), out closingamt);
//double.TryParse(drroutes["Route Code"].ToString(), out differenceamount);
cmd.Parameters.AddWithValue("@OppBalance", Math.Round(oppeningbal, 2));
cmd.Parameters.AddWithValue("@SaleQty", Math.Round(saleqty, 2));
cmd.Parameters.AddWithValue("@SaleValue", Math.Round(salevalue, 2));
cmd.Parameters.AddWithValue("@ReceivedAmount", Math.Round(receivedamt, 2));
cmd.Parameters.AddWithValue("@ClosingBalance", Math.Round(closingamt, 2));
cmd.Parameters.AddWithValue("@DiffAmount", Math.Round(differenceamount, 2));
vdm.insert(cmd);
}
}
}
lblmsg.Text = "Agent Due Transactions Saved Successfully";
lblmessage.Text = "Agent Due Transactions Saved Successfully";
MessageBox.Show("Agent Due Transactions Saved Successfully", Page);
}
if (dtprevdate.Rows.Count <= 0)
{
string msg = "Previous Day Transaction Not Yet Closed";
MessageBox.Show("Previous Day Transaction Not Yet Closed", Page);
}
}
else
{
}
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
lblmessage.Text = ex.Message;
}
}