protected void btn_VehicleGroup_Add_Click(object sender, EventArgs e)
{
try
{
if (!ValidateVehicleGroup())
{
return;
}
if (cblSelectVehicle.Items.Count > 0)
{
if (btn_VehicleGroup_Add.Text == "Add")
{
foreach (ListItem obj in cblSelectVehicle.Items)
{
if (obj.Selected)
{
cmd = new MySqlCommand("insert into VehicleGroup (GroupName,VehicleID,UserName) values (@GroupName,@VehicleID,@UserName) ");
cmd.Parameters.Add("@GroupName", txtGroupName.Text.Trim());
cmd.Parameters.Add("@VehicleID", obj.Text.Trim());
cmd.Parameters.Add("@UserName", UserName);
vdm.insert(cmd);
}
}
UpdateVehicleGroupData();
ResetVehicleGroup();
lblGroupStatus.Text = "Record Added Successfully";
}
else
{
cmd = new MySqlCommand("Delete from VehicleGroup where GroupName=@GroupName and UserName=@UN");
cmd.Parameters.Add("@GroupName", vehicleGroup);
cmd.Parameters.Add("@UN", UserName);
vdm.Delete(cmd);
foreach (ListItem obj in cblSelectVehicle.Items)
{
if (obj.Selected)
{
cmd = new MySqlCommand("insert into VehicleGroup (GroupName,VehicleID,UserName) values (@GroupName,@VehicleID,@UserName) ");
cmd.Parameters.Add("@GroupName", txtGroupName.Text);
cmd.Parameters.Add("@VehicleID", obj.Text);
cmd.Parameters.Add("@UserName", UserName);
}
}
vdm.insert(cmd);
btn_VehicleGroup_Add.Text = "Add";
MessageBox.Show("Successfully Groups Modified", this);
UpdateVehicleGroupData();
ResetVehicleGroup();
}
}
}
catch (Exception ex)
{
throw ex;
}
}
protected void btnSave_OnClick(object sender, EventArgs e)
{
try
{
if (btnSave.Text == "Save")
{
cmd = new MySqlCommand("insert into emailmgmt (PName , EmailAddress,Level,UserID) values (@PName, @EmailAddress,@Level,@UserID)");
cmd.Parameters.Add("@PName", txtName.Text);
cmd.Parameters.Add("@EmailAddress", txtEmail.Text);
cmd.Parameters.Add("@Level", ddlLevel.Text.Trim());
cmd.Parameters.Add("@UserID", UserName);
vdm.insert(cmd);
Refresh();
UpdateEmails();
lblmsg.Text = "Saved Successfully";
}
else
{
cmd = new MySqlCommand("update emailmgmt set PName=@PName,EmailAddress=@EmailAddress,Level=@Level where sno=@sno and UserID=@UserID");
cmd.Parameters.Add("@PName", txtName.Text);
cmd.Parameters.Add("@EmailAddress", txtEmail.Text);
cmd.Parameters.Add("@Level", ddlLevel.Text.Trim());
cmd.Parameters.Add("@UserID", UserName);
cmd.Parameters.Add("@sno", Sno);
vdm.insert(cmd);
Refresh();
UpdateEmails();
lblmsg.Text = "Updated Successfully";
}
}
catch (Exception ex)
{
lblmsg.Text = ex.ToString();
}
}
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 btnSave_Click(object sender, EventArgs e)
{
try
{
DataTable dt = (DataTable)Session["btnImport"];
foreach (DataRow dr in dt.Rows)
{
vdm = new VehicleDBMgr();
string groupcode = dr["groupcode"].ToString();
string accountcode = dr["accountcode"].ToString();
string description = dr["description"].ToString();
cmd = new SqlCommand("SELECT sno FROM fixed_assets_groups WHERE (groupname=@groupname)");
cmd.Parameters.Add("@groupname", groupcode);
DataTable dtaccounts = vdm.SelectQuery(cmd).Tables[0];
if (dtaccounts.Rows.Count > 0)
{
cmd = new SqlCommand("insert into accountmaster(groupcode,accountcode,description,createdby,doe) values(@groupcode,@accountcode,@description,@createdby,@doe)");
cmd.Parameters.Add("@groupcode", dtaccounts.Rows[0]["sno"].ToString());
cmd.Parameters.Add("@accountcode", accountcode);
cmd.Parameters.Add("@doe", DateTime.Now);
cmd.Parameters.Add("@description", description);
cmd.Parameters.Add("@createdby", Session["UserSno"].ToString());
vdm.insert(cmd);
}
}
}
catch (Exception ex)
{
lblmsg.Text = ex.ToString();
}
lblmsg.Text = "Records inserted successfully";
}
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 btnAdd_Click(object sender, EventArgs e)
{
string ItemType = "Expenses";
cmd = new MySqlCommand("insert into vehiclemanage (ItemType, ItemName,ItemCode,UserName) values (@ItemType, @ItemName,@ItemCode,@UserName)");
cmd.Parameters.Add("@ItemType", ItemType);
cmd.Parameters.Add("@ItemCode", txtPartNo.Text.Trim());
cmd.Parameters.Add("@ItemName", txtPartName.Text.Trim());
cmd.Parameters.Add("@UserName", UserName);
vdm.insert(cmd);
Refresh();
lblSuccess.Text = "Record added Successfully";
}
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 BtnSave_Click(object sender, EventArgs e)
{
try
{
vdm = new VehicleDBMgr();
string clobal = lblclosingbal.Text;
DateTime fromdate = new DateTime();
string[] datestrig = txtFromdate.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;
cmd = new SqlCommand("SELECT sno, accountid, amount, closedby, doe FROM accountnowiseclosingdetails WHERE (doe BETWEEN @d1 AND @d2) AND (accountid = @accountid)");
cmd.Parameters.Add("@accountid", ddlaccountno.SelectedValue);
cmd.Parameters.Add("@d1", GetLowDate(fromdate));
cmd.Parameters.Add("@d2", GetHighDate(fromdate));
DataTable dtroutes = vdm.SelectQuery(cmd).Tables[0];
string msg = "";
if (dtroutes.Rows.Count > 0)
{
lblbankmsg.Text = "This account no already closed";
}
else
{
cmd = new SqlCommand("insert into accountnowiseclosingdetails (accountid, amount, doe, closedby) values (@accountid, @amount, @doe, @userid)");
cmd.Parameters.Add("@accountid", ddlaccountno.SelectedValue);
cmd.Parameters.Add("@userid", Session["UserSno"].ToString());
cmd.Parameters.Add("@amount", clobal);
cmd.Parameters.Add("@doe", fromdate);
vdm.insert(cmd);
GetReport();
lblbankmsg.Text = "Banksheet saved successfully";
}
}
catch (Exception ex)
{
lblbankmsg.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 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;
}
}
}
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 BtnSave_Click(object sender, EventArgs e)
{
try
{
vdm = new VehicleDBMgr();
string SalesOfficeId = ddlSalesOffice.SelectedValue;
if (SalesOfficeId == "572")
{
SalesOfficeId = "158";
}
DateTime fromdate = new DateTime();
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);
}
}
DateTime ServerDateCurrentdate = VehicleDBMgr.GetTime(vdm.conn);
cmd = new MySqlCommand("SELECT sno, inv_sno, oppening, isuued, closing, due_trans_sno, branchid, agentid, received FROM due_trans_inventory WHERE (branchid = @BranchID) AND (doe between @d1 and @d2)");
cmd.Parameters.AddWithValue("@BranchID", SalesOfficeId);
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@d2", GetHighDate(fromdate));
DataTable dtprevdate = vdm.SelectQuery(cmd).Tables[0];
DataTable dt = (DataTable)Session["InvClosing"];
if (dtprevdate.Rows.Count > 0)
{
string msg = "Transaction Closed";
MessageBox.Show("Transaction Closed", Page);
}
if (dtprevdate.Rows.Count <= 0)
{
foreach (DataRow dr in dt.Rows)
{
cmd = new MySqlCommand("insert into due_trans_inventory (inv_sno,oppening,isuued,received,closing,branchid,agentid,opp10,issue10,rec10,clo10,opp20,issu20,rec20,clo20,opp40,issu40,rec40,clo40,doe) values (@inv_sno,@oppening,@isuued,@received,@closing,@branchid,@agentid,@opp10,@issue10,@rec10,@clo10,@opp20,@issu20,@rec20,@clo20,@opp40,@issu40,@rec40,@clo40,@doe)");
cmd.Parameters.AddWithValue("@inv_sno", 1);
cmd.Parameters.AddWithValue("@oppening", dr["Opp Crates "].ToString());
cmd.Parameters.AddWithValue("@isuued", dr["Issued Crates "].ToString());
cmd.Parameters.AddWithValue("@received", dr["Received Crates "].ToString());
cmd.Parameters.AddWithValue("@closing", dr["CB Crates "].ToString());
cmd.Parameters.AddWithValue("@branchid", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@agentid", dr["Branch Code "].ToString());
cmd.Parameters.AddWithValue("@opp10", dr["Opp Can 10ltr"].ToString());
cmd.Parameters.AddWithValue("@issue10", dr["Issued Can 10ltr"].ToString());
cmd.Parameters.AddWithValue("@rec10", dr["Received Can 10ltr"].ToString());
cmd.Parameters.AddWithValue("@clo10", dr["CB Can 10ltr"].ToString());
cmd.Parameters.AddWithValue("@opp20", dr["Opp Can 20ltr"].ToString());
cmd.Parameters.AddWithValue("@issu20", dr["Issued Can 20ltr"].ToString());
cmd.Parameters.AddWithValue("@rec20", dr["Received Can 20ltr"].ToString());
cmd.Parameters.AddWithValue("@clo20", dr["CB Can 20ltr"].ToString());
cmd.Parameters.AddWithValue("@opp40", dr["Opp Can 40ltr"].ToString());
cmd.Parameters.AddWithValue("@issu40", dr["Issued Can 40ltr"].ToString());
cmd.Parameters.AddWithValue("@rec40", dr["Received Can 40ltr"].ToString());
cmd.Parameters.AddWithValue("@clo40", dr["CB Can 40ltr"].ToString());
cmd.Parameters.AddWithValue("@doe", fromdate);
vdm.insert(cmd);
}
lblmsg.Text = "Agent Inventory Transactions Saved Successfully";
MessageBox.Show("Agent Inventory Transactions Saved Successfully", Page);
}
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
}
}
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 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;
}
}
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 btnSave_Click(object sender, EventArgs e)
{
try
{
DataTable dt = (DataTable)Session["dtImport"];
int i = 1;
foreach (DataRow dr in dt.Rows)
{
vdm = new VehicleDBMgr();
string Cardname = dr["Card name"].ToString();
string ACCOUNTID = dr["ACCOUNT ID"].ToString();
string DEALERNAME = dr["DEALER NAME"].ToString();
string DEALERCITY = dr["DEALER CITY"].ToString();
string TRANSACTIONDATE = dr["TRANSACTION DATE"].ToString();
string[] strtrans = TRANSACTIONDATE.Split('-');
string newdate = strtrans[1] + "/" + strtrans[0] + "/" + strtrans[2];
DateTime dttransactiondate = Convert.ToDateTime(newdate);
string ACCOUNTINGDAT = dr["ACCOUNTING DATE"].ToString();
string[] straccountingdat = ACCOUNTINGDAT.Split('-');
string newstraccountingdat = straccountingdat[1] + "/" + straccountingdat[0] + "/" + straccountingdat[2];
DateTime dtaccdate = Convert.ToDateTime(newstraccountingdat);
string TRANSACTIONTYPE = dr["TRANSACTION TYPE"].ToString();
string CURRENCY = dr["CURRENCY"].ToString();
string AMOUNT = dr["AMOUNT"].ToString();
string VOLUMEDOCNO = dr["VOLUME"].ToString();
string AMOUNTBALANCE = "0";// dr["AMOUNT BALANCE"].ToString();
string PETROMILESEARNED = dr["PETROMILES EARNED"].ToString();
string ODOMETERREADING = dr["ODOMETER READING"].ToString();
string vehicleno = dr["vehicle no"].ToString();
string Remarks = dr["Remarks"].ToString();
string vehicletype = dr["vehicle type"].ToString();
//DateTime ServerDateCurrentdate = VehicleDBMgr.GetTime(vdm.conn);
cmd = new MySqlCommand("insert into petrocard_details (cardname,accountid,dealername,dealercity,transactiondate,accountingdate,tranactiontype,currency,amount,volume,amountbalance,petromiles_earned,odometer,vehicleno,remarks,vehicletype ) values (@cardname,@accountid,@dealername,@dealercity,@transactiondate,@accountingdate,@tranactiontype,@currency,@amount,@volume,@amountbalance,@petromiles_earned,@odometer,@vehicleno,@remarks,@vehicletype)");
cmd.Parameters.Add("@cardname", Cardname);
cmd.Parameters.Add("@accountid", ACCOUNTID);
cmd.Parameters.Add("@dealername", DEALERNAME);
cmd.Parameters.Add("@dealercity", DEALERCITY);
cmd.Parameters.Add("@transactiondate", dttransactiondate);
cmd.Parameters.Add("@accountingdate", dtaccdate);
cmd.Parameters.Add("@tranactiontype", TRANSACTIONTYPE);
cmd.Parameters.Add("@currency", CURRENCY);
cmd.Parameters.Add("@amount", AMOUNT);
cmd.Parameters.Add("@volume", VOLUMEDOCNO);
cmd.Parameters.Add("@amountbalance", AMOUNTBALANCE);
cmd.Parameters.Add("@petromiles_earned", PETROMILESEARNED);
cmd.Parameters.Add("@odometer", ODOMETERREADING);
cmd.Parameters.Add("@vehicleno", vehicleno);
cmd.Parameters.Add("@remarks", Remarks);
cmd.Parameters.Add("@vehicletype", vehicletype);
vdm.insert(cmd);
}
lblmsg.Text = "Records inserted successfully";
lblMessage.Text = "Records inserted successfully";
DataTable dtempty = new DataTable();
grvExcelData.DataSource = dtempty;
grvExcelData.DataBind();
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
lblMessage.Text = ex.Message;
}
}
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 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 btnRouteSave_OnClick(object sender, EventArgs e)
{
try
{
lblmsg.Text = "";
vdm = new VehicleDBMgr();
vdm.InitializeDB();
string RouteName = txtRouteName.Text;
//string BrachNames = chblselected.Items;
//BrachNames = BrachNames.Substring(0, BrachNames.Length - 1);
//string Ranks = context.Request["Ranks"];
//string[] strBrachNames = BrachNames.Split('-');
//Ranks = Ranks.Substring(0, Ranks.Length - 1);
//string[] strRanks = Ranks.Split('-');
//string strSno = context.Request["Sno"];
//string[] SpltSno = strSno.Split('-');
//int rank = 0;
//int.TryParse(Ranks.ToString(), out rank);
//string Username = context.Session["field1"].ToString();
//string btnSave = context.Request["btnRouteSave"];
if (btnRouteSave.Text == "Save")
{
cmd = new MySqlCommand("Insert into Routetable(RouteName,UserID) values(@RouteName,@UserID)");
cmd.Parameters.Add("@RouteName", RouteName);
cmd.Parameters.Add("@UserID", UserName);
vdm.insert(cmd);
cmd = new MySqlCommand("Select SNo from Routetable where RouteName=@RouteName and UserID=@UserID ");
cmd.Parameters.Add("@RouteName", RouteName);
cmd.Parameters.Add("@UserID", UserName);
DataTable dt = vdm.SelectQuery(cmd).Tables[0];
int Sno = (int)dt.Rows[0]["Sno"];
DataTable dtSno = (DataTable)Session["Branchdata"];
int i = 0;
foreach (ListItem str in chblselected.Items)
{
foreach (DataRow dr in dtSno.Rows)
{
if (str.Text == dr["BranchID"].ToString())
{
//cmd = new MySqlCommand("Select BranchID from branchdata where UserName=@UserName and Sno=@Sno");
//cmd.Parameters.Add("@UserName", UserName);
//cmd.Parameters.Add("@Sno", SpltSno[i]);
//DataTable dtBranch = vdm.SelectQuery(cmd).Tables[0];
//string BranchID = dtBranch.Rows[0]["BranchID"].ToString();
cmd = new MySqlCommand("Insert into Routesubtable(SNo,LocationID,BranchName,Rank) values(@SNo,@LocationID,@BranchName,@Rank)");
cmd.Parameters.Add("@SNo", Sno);
cmd.Parameters.Add("@LocationID", dr["Sno"].ToString());
cmd.Parameters.Add("@BranchName", dr["BranchID"].ToString());
cmd.Parameters.Add("@Rank", i);
vdm.insert(cmd);
i++;
}
}
}
lblmsg.Text = "Route Saved Successfully";
UpdateRoute();
refresh();
}
else if (btnRouteSave.Text == "Edit")
{
vdm = new VehicleDBMgr();
vdm.InitializeDB();
cmd = new MySqlCommand("Update Routetable set RouteName=@RouteName where UserID=@UserID and SNo=@SNo");
cmd.Parameters.Add("@RouteName", txtRouteName.Text);
cmd.Parameters.Add("@UserID", UserName);
cmd.Parameters.Add("@SNo", RefSno);
vdm.insert(cmd);
cmd = new MySqlCommand("Select SNo from Routetable where RouteName=@RouteName and UserID=@UserID ");
cmd.Parameters.Add("@RouteName", txtRouteName.Text);
cmd.Parameters.Add("@UserID", UserName);
DataTable dt = vdm.SelectQuery(cmd).Tables[0];
int Sno = (int)dt.Rows[0]["Sno"];
cmd = new MySqlCommand("Delete from Routesubtable where SNo=@SNo");
cmd.Parameters.Add("@Sno", Sno);
vdm.Delete(cmd);
DataTable dtSno = (DataTable)Session["Branchdata"];
int i = 0;
foreach (ListItem str in chblselected.Items)
{
foreach (DataRow dr in dtSno.Rows)
{
if (str.Text == dr["BranchID"].ToString())
{
//cmd = new MySqlCommand("Select BranchID from branchdata where UserName=@UserName and Sno=@Sno");
//cmd.Parameters.Add("@UserName", UserName);
//cmd.Parameters.Add("@Sno", SpltSno[i]);
//DataTable dtBranch = vdm.SelectQuery(cmd).Tables[0];
//string BranchID = dtBranch.Rows[0]["BranchID"].ToString();
cmd = new MySqlCommand("Insert into Routesubtable(SNo,LocationID,BranchName,Rank) values(@SNo,@LocationID,@BranchName,@Rank)");
cmd.Parameters.Add("@SNo", Sno);
cmd.Parameters.Add("@LocationID", dr["Sno"].ToString());
cmd.Parameters.Add("@BranchName", dr["BranchID"].ToString());
cmd.Parameters.Add("@Rank", i);
vdm.insert(cmd);
i++;
}
}
}
lblmsg.Text = "Route Modified Successfully";
UpdateRoute();
refresh();
}
ScriptManager.RegisterStartupScript(Page, GetType(), "JsStatus", "ClearPolylines();", true);
}
catch
{
}
}
void GetReport()
{
try
{
lblmsg.Text = "";
pnlHide.Visible = true;
Report = new DataTable();
Session["RouteName"] = ddlRouteName.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);
}
}
lbl_selfromdate.Text = fromdate.ToString("dd/MM/yyyy");
lblRoutName.Text = ddlRouteName.SelectedItem.Text;
Session["filename"] = "AGENT WISE DELIVERY REPORT";
cmd = new MySqlCommand("select Route_id,IndentType from dispatch_sub where dispatch_sno=@dispsno");
cmd.Parameters.AddWithValue("@dispsno", ddlRouteName.SelectedValue);
DataTable dtrouteindenttype = vdm.SelectQuery(cmd).Tables[0];
foreach (DataRow drrouteitype in dtrouteindenttype.Rows)
{
routeid = drrouteitype["Route_id"].ToString();
routeitype = drrouteitype["IndentType"].ToString();
}
cmd = new MySqlCommand("SELECT branchdata.BranchName, modifiedroutes.RouteName, branchdata.sno AS BSno, indent.IndentType, ROUND(SUM(indents_subtable.DeliveryQty), 2) AS DeliveryQty,indents_subtable.UnitCost, productsdata.ProductName, productsdata.Units, productsdata.sno, products_category.Categoryname, invmaster.Qty,brnchprdt.Rank FROM modifiedroutes INNER JOIN modifiedroutesubtable ON modifiedroutes.Sno = modifiedroutesubtable.RefNo INNER JOIN branchdata ON modifiedroutesubtable.BranchID = branchdata.sno INNER JOIN (SELECT IndentNo, Branch_id, I_date, Status, IndentType FROM indents WHERE (I_date BETWEEN @starttime AND @endtime) AND (Status <> 'D')) indent ON branchdata.sno = indent.Branch_id INNER JOIN indents_subtable ON indent.IndentNo = indents_subtable.IndentNo INNER JOIN productsdata ON indents_subtable.Product_sno = productsdata.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno INNER JOIN invmaster ON productsdata.Inventorysno = invmaster.sno INNER JOIN (SELECT branch_sno, product_sno, Rank FROM branchproducts WHERE (branch_sno = @BranchID)) brnchprdt ON productsdata.sno = brnchprdt.product_sno WHERE (modifiedroutes.Sno = @TripID) AND (modifiedroutesubtable.EDate IS NULL) AND (modifiedroutesubtable.CDate <= @starttime) AND (indents_subtable.DeliveryQty > 0) OR (modifiedroutes.Sno = @TripID) AND (modifiedroutesubtable.EDate > @starttime) AND (modifiedroutesubtable.CDate <= @starttime) AND (indents_subtable.DeliveryQty > 0) GROUP BY productsdata.sno, BSno ORDER BY brnchprdt.Rank");
if (Session["salestype"].ToString() == "Plant")
{
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
}
else
{
cmd.Parameters.AddWithValue("@BranchID", Session["branch"]);
}
cmd.Parameters.AddWithValue("@TripID", routeid);
cmd.Parameters.AddWithValue("@starttime", GetLowDate(fromdate.AddDays(-1)));
cmd.Parameters.AddWithValue("@endtime", GetHighDate(fromdate.AddDays(-1)));
DataTable dtble = vdm.SelectQuery(cmd).Tables[0];
cmd = new MySqlCommand("SELECT branchdata.sno,branchdata.branchcode,branchdata.companycode, branchdata.BranchName, statemastar.statename ,statemastar.statecode, 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", Session["branch"]);
}
DataTable dtstatename = vdm.SelectQuery(cmd).Tables[0];
string statename = "";
string statecode = "";
string branchcode = "";
string gststatecode = "";
string companycode = "";
if (dtstatename.Rows.Count > 0)
{
statename = dtstatename.Rows[0]["statename"].ToString();
statecode = dtstatename.Rows[0]["statecode"].ToString();
branchcode = dtstatename.Rows[0]["branchcode"].ToString();
gststatecode = dtstatename.Rows[0]["gststatecode"].ToString();
companycode = dtstatename.Rows[0]["companycode"].ToString();
}
cmd = new MySqlCommand("SELECT branchdata.BranchName, modifiedroutes.RouteName, branchdata.sno AS BSno, indent.IndentType, ROUND(SUM(indents_subtable.DeliveryQty), 2) AS DeliveryQty,indents_subtable.UnitCost, productsdata.ProductName, productsdata.Units, productsdata.sno, products_category.Categoryname, invmaster.Qty,brnchprdt.Rank FROM modifiedroutes INNER JOIN modifiedroutesubtable ON modifiedroutes.Sno = modifiedroutesubtable.RefNo INNER JOIN branchdata ON modifiedroutesubtable.BranchID = branchdata.sno INNER JOIN (SELECT IndentNo, Branch_id, I_date, Status, IndentType FROM indents WHERE (I_date BETWEEN @starttime AND @endtime) AND (Status <> 'D')) indent ON branchdata.sno = indent.Branch_id INNER JOIN indents_subtable ON indent.IndentNo = indents_subtable.IndentNo INNER JOIN productsdata ON indents_subtable.Product_sno = productsdata.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno INNER JOIN invmaster ON productsdata.Inventorysno = invmaster.sno INNER JOIN (SELECT branch_sno, product_sno, Rank FROM branchproducts WHERE (branch_sno = @BranchID)) brnchprdt ON productsdata.sno = brnchprdt.product_sno WHERE (modifiedroutes.Sno = @TripID) AND (modifiedroutesubtable.EDate IS NULL) AND (modifiedroutesubtable.CDate <= @starttime) OR (modifiedroutes.Sno = @TripID) AND (modifiedroutesubtable.EDate > @starttime) AND (modifiedroutesubtable.CDate <= @starttime)");
if (Session["salestype"].ToString() == "Plant")
{
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
}
else
{
cmd.Parameters.AddWithValue("@BranchID", Session["branch"]);
}
cmd.Parameters.AddWithValue("@TripID", routeid);
cmd.Parameters.AddWithValue("@starttime", GetLowDate(fromdate.AddDays(-1)));
cmd.Parameters.AddWithValue("@endtime", GetHighDate(fromdate.AddDays(-1)));
DataTable dtsum = vdm.SelectQuery(cmd).Tables[0];
double totsum = 0;
if (dtsum.Rows.Count > 0)
{
string sum = dtsum.Rows[0]["DeliveryQty"].ToString();
double.TryParse(sum, out totsum);
}
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);
}
if (dtble.Rows.Count > 0)
{
DataView view = new DataView(dtble);
DataTable produtstbl = view.ToTable(true, "ProductName", "Categoryname");
Report = new DataTable();
Report.Columns.Add("SNo");
Report.Columns.Add("DC No");
Report.Columns.Add("Agent Name");
int count = 0;
foreach (DataRow dr in produtstbl.Rows)
{
Report.Columns.Add(dr["ProductName"].ToString()).DataType = typeof(Double);
count++;
}
Report.Columns.Add("Total Sale").DataType = typeof(Double);
Report.Columns.Add("Sale Value").DataType = typeof(Double);
DataTable distincttable = view.ToTable(true, "BranchName", "BSno");
int i = 1;
foreach (DataRow branch in distincttable.Rows)
{
DataRow newrow = Report.NewRow();
newrow["SNo"] = i;
string DCNO = "0";
cmd = new MySqlCommand("SELECT agentdcno FROM agentdc WHERE (BranchID = @BranchID) AND (IndDate BETWEEN @d1 AND @d2)");
cmd.Parameters.AddWithValue("@BranchID", branch["BSno"].ToString());
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate.AddDays(-1)));
cmd.Parameters.AddWithValue("@d2", GetHighDate(fromdate.AddDays(-1)));
DataTable dtDc = vdm.SelectQuery(cmd).Tables[0];
if (dtDc.Rows.Count > 0)
{
DCNO = dtDc.Rows[0]["agentdcno"].ToString();
}
else
{
DCNO = "0";
if (totsum > 0)
{
long DcNo = 0;
string socode = "";
if (ReportDate.ToString("dd/MM/yyyy") == fromdate.ToString("dd/MM/yyyy"))
{
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", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@d1", GetLowDate(dtapril.AddDays(-1)));
cmd.Parameters.AddWithValue("@d2", GetHighDate(dtmarch.AddDays(-1)));
DataTable dtadcno = vdm.SelectQuery(cmd).Tables[0];
string agentdcNo = dtadcno.Rows[0]["Sno"].ToString();
//cmd = new MySqlCommand("Insert Into Agentdc (BranchId,IndDate,soid,agentdcno,stateid,companycode,moduleid,doe,invoicetype) Values(@BranchId,@IndDate,@soid,@agentdcno,@stateid,@companycode,@moduleid,@doe,@invoicetype)");
//cmd.Parameters.AddWithValue("@BranchId", branch["BSno"].ToString());
//cmd.Parameters.AddWithValue("@IndDate", GetLowDate(fromdate.AddDays(-1)));
//cmd.Parameters.AddWithValue("@soid", ddlSalesOffice.SelectedValue);
//cmd.Parameters.AddWithValue("@agentdcno", agentdcNo);
//cmd.Parameters.AddWithValue("@stateid", gststatecode);
//cmd.Parameters.AddWithValue("@companycode", companycode);
//cmd.Parameters.AddWithValue("@doe", ReportDate);
//cmd.Parameters.AddWithValue("@moduleid", Session["moduleid"].ToString());
//cmd.Parameters.AddWithValue("@invoicetype", "RWDelivery");
//DcNo = vdm.insertScalar(cmd);
cmd = new MySqlCommand("SELECT IndentNo FROM indents WHERE (Branch_id = @BranchId) AND (I_date BETWEEN @d1 AND @d2)");
cmd.Parameters.AddWithValue("@BranchId", branch["BSno"].ToString());
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate.AddDays(-1)));
cmd.Parameters.AddWithValue("@d2", GetHighDate(fromdate.AddDays(-1)));
DataTable dtindentno = vdm.SelectQuery(cmd).Tables[0];
if (dtindentno.Rows.Count > 0)
{
foreach (DataRow dr in dtindentno.Rows)
{
cmd = new MySqlCommand("Insert Into dcsubTable (DcNo,IndentNo) Values(@DcNo,@IndentNo)");
cmd.Parameters.AddWithValue("@DcNo", DcNo);
cmd.Parameters.AddWithValue("@IndentNo", dr["IndentNo"].ToString());
vdm.insert(cmd);
}
}
cmd = new MySqlCommand("SELECT agentdcno FROM agentdc WHERE (BranchID = @BranchID) AND (IndDate BETWEEN @d1 AND @d2)");
cmd.Parameters.AddWithValue("@BranchID", branch["BSno"].ToString());
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate.AddDays(-1)));
cmd.Parameters.AddWithValue("@d2", GetHighDate(fromdate.AddDays(-1)));
DataTable dtagentdcnoDc = vdm.SelectQuery(cmd).Tables[0];
if (dtagentdcnoDc.Rows.Count > 0)
{
DCNO = dtagentdcnoDc.Rows[0]["agentdcno"].ToString();
}
}
}
}
int countdc = 0;
int.TryParse(DCNO, out countdc);
if (countdc <= 10)
{
DCNO = "0000" + countdc;
}
if (countdc >= 10 && countdc <= 99)
{
DCNO = "000" + countdc;
}
if (countdc >= 99 && countdc <= 999)
{
DCNO = "00" + countdc;
}
if (countdc > 999)
{
DCNO = "00" + countdc;
}
if (fromdate.Month > 3)
{
newrow["DC No"] = branchcode + "/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "N/" + DCNO;
}
else
{
newrow["DC No"] = branchcode + "/" + dtapril.AddYears(-1).ToString("yy") + "-" + dtmarch.AddYears(-1).ToString("yy") + "N/" + DCNO;
}
newrow["Agent Name"] = branch["BranchName"].ToString();
double total = 0;
double totalSale = 0;
foreach (DataRow dr in dtble.Rows)
{
if (branch["BranchName"].ToString() == dr["BranchName"].ToString())
{
double Amount = 0;
double qtyvalue = 0;
double DeliveryQty = 0;
double.TryParse(dr["DeliveryQty"].ToString(), out DeliveryQty);
double UnitCost = 0;
double.TryParse(dr["UnitCost"].ToString(), out UnitCost);
newrow[dr["ProductName"].ToString()] = DeliveryQty;
if (dr["Categoryname"].ToString() == "MILK")
{
double.TryParse(dr["DeliveryQty"].ToString(), out qtyvalue);
}
Amount = DeliveryQty * UnitCost;
total += DeliveryQty;
totalSale += Amount;
}
}
newrow["Total Sale"] = total;
newrow["Sale Value"] = totalSale;
Report.Rows.Add(newrow);
i++;
}
DataRow newvartical = Report.NewRow();
newvartical["Agent Name"] = "Total";
double val = 0.0;
foreach (DataColumn dc in Report.Columns)
{
if (dc.DataType == typeof(Double))
{
val = 0.0;
double.TryParse(Report.Compute("sum([" + dc.ToString() + "])", "[" + dc.ToString() + "]<>'0'").ToString(), out val);
newvartical[dc.ToString()] = val;
}
}
Report.Rows.Add(newvartical);
foreach (DataColumn col in Report.Columns)
{
string Pname = col.ToString();
string ProductName = col.ToString();
ProductName = GetSpace(ProductName);
Report.Columns[Pname].ColumnName = ProductName;
}
grdReports.DataSource = Report;
grdReports.DataBind();
Session["xportdata"] = Report;
}
else
{
pnlHide.Visible = false;
lblmsg.Text = "No Indent Found";
grdReports.DataSource = Report;
grdReports.DataBind();
}
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
grdReports.DataSource = Report;
grdReports.DataBind();
}
}
protected void btnSave_Click(object sender, EventArgs e)
{
try
{
lbl_nofifier.Text = "";
string ModuleName = string.Empty;
string Mid = string.Empty;
string VehicleName = string.Empty;
string VehicleId = string.Empty;
string VehicleCapacity = string.Empty;
int flag = 0;
int flag1 = 0;
moduledata = (DataTable)Session["moduledata"];
foreach (ListItem chkitem in chblZones.Items)
{
if (chkitem.Selected == true)
{
ModuleName = chkitem.Text;
foreach (DataRow dr in moduledata.Rows)
{
if (ModuleName == dr["ModuleName"].ToString())
{
Mid = dr["ID"].ToString();
Sno = Mid;
flag = 1;
break;
}
}
}
}
if (flag == 1)
{
vehicledata = (DataTable)Session["filteredtable"];
if (btnSave.Text == "Save")
{
foreach (ListItem chkitem in ckbVehicles.Items)
{
if (chkitem.Selected == true)
{
VehicleName = chkitem.Text;
foreach (DataRow dr in vehicledata.Rows)
{
if (VehicleName == dr["registration_no"].ToString())
{
VehicleId = dr["vm_sno"].ToString();
VehicleCapacity = dr["Capacity"].ToString();
flag1 = 1;
cmd = new MySqlCommand("Insert into vmsc (MID,VID,VehicleRegistrationNo,Capacity,Status,CreateDate,CreateBy) values(@Mid,@VehicleID,@VehicleRegistrationNo,@Capacity,@Status,@CreateDate,@CreateBy)");
cmd.Parameters.Add("@Mid", Mid);
cmd.Parameters.Add("@VehicleID", VehicleId);
cmd.Parameters.Add("@VehicleRegistrationNo", VehicleName);
cmd.Parameters.Add("@Capacity", VehicleCapacity);
cmd.Parameters.Add("@Status", 1);
cmd.Parameters.Add("@CreateDate", System.DateTime.Now);
cmd.Parameters.Add("@CreateBy", Session["UserName"].ToString());
vdm.insert(cmd);
lbl_nofifier.Text = string.Empty;
lbl_nofifier.Text = "Data Saved Successfully...";
break;
}
}
}
}
}
else
{
DataTable prev_data = (DataTable)Session["dtallVehicles"];
foreach (ListItem obj in ckbVehicles.Items)
{
if (prev_data.Select("VehicleID='" + obj.Text + "'").Length > 0)
{
if (!obj.Selected)
{
//AFTER CONFIRMATION USE THIS (DELETE WORKING)
cmd = new MySqlCommand("Delete from vmsc where MID=@Refno and VehicleRegistrationNo=@vid");
cmd.Parameters.Add("@Refno", Mid);
cmd.Parameters.Add("@vid", obj.Text);
// vdm.Delete(cmd);
}
}
else
{
if (obj.Selected)
{
VehicleName = obj.Text;
foreach (DataRow dr in vehicledata.Rows)
{
if (VehicleName == dr["registration_no"].ToString())
{
VehicleId = dr["vm_sno"].ToString();
VehicleCapacity = dr["Capacity"].ToString();
flag1 = 1;
cmd = new MySqlCommand("Insert into vmsc (MID,VID,VehicleRegistrationNo,Capacity,Status,CreateDate,CreateBy) values(@Mid,@VehicleID,@VehicleRegistrationNo,@Capacity,@Status,@CreateDate,@CreateBy)");
cmd.Parameters.Add("@Mid", Mid);
cmd.Parameters.Add("@VehicleID", VehicleId);
cmd.Parameters.Add("@VehicleRegistrationNo", VehicleName);
cmd.Parameters.Add("@Capacity", VehicleCapacity);
cmd.Parameters.Add("@Status", 1);
cmd.Parameters.Add("@CreateDate", System.DateTime.Now);
cmd.Parameters.Add("@CreateBy", Session["UserName"].ToString());
vdm.insert(cmd);
lbl_nofifier.Text = string.Empty;
lbl_nofifier.Text = "Data Saved Successfully...";
break;
}
}
}
}
}
}
Refresh();
UpdateGrid();
}
else
{
lbl_nofifier.Text = "Please, Select Atleast One ModuleName...";
}
}
catch (Exception ex)
{
lbl_nofifier.Text = ex.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();
}
}
protected void BtnSave_Click(object sender, EventArgs e)
{
try
{
vdm = new VehicleDBMgr();
if (Session["DtStockInv"] == null)
{
lblmsg.Text = "Session Expired ";
}
else
{
string SalesOfficeId = ddlSalesOffice.SelectedValue;
if (SalesOfficeId == "472")
{
SalesOfficeId = "158";
}
DataTable dtInvReport = (DataTable)Session["DtStockInv"];
//DataTable dtProReport = (DataTable)Session["DtStockPro"];
//DataTable dtStockInvProduct = (DataTable)Session["dtStockInvProduct"];
foreach (DataRow dr in dtInvReport.Rows)
{
try
{
cmd = new MySqlCommand("Insert into clotrans(BranchID,Amount,IndDate,SalesType,BranchType,BranchRouteID,EmpID) Values(@BranchID,@Amount,@IndDate,@SalesType,@BranchType,@BranchRouteID,@EmpID)");
cmd.Parameters.AddWithValue("@BranchID", dr["AgentCode"].ToString());
cmd.Parameters.AddWithValue("@Amount", dr["Amount"].ToString());
cmd.Parameters.AddWithValue("@IndDate", DateTime.Now.AddDays(-1));
cmd.Parameters.AddWithValue("@SalesType", 20);
cmd.Parameters.AddWithValue("@EmpID", Session["UserSno"].ToString());
cmd.Parameters.AddWithValue("@BranchType", dr["CollectionType"].ToString());
cmd.Parameters.AddWithValue("@BranchRouteID", dr["RouteID"].ToString());
long Sno = vdm.insertScalar(cmd);
//long Sno = 0;
DataRow[] drInvData = dtInvReport.Select("AgentCode=" + dr["AgentCode"].ToString());
if (drInvData.Count() > 0)
{
DataTable dtInv = drInvData.CopyToDataTable();
foreach (DataRow drv in dtInv.Rows)
{
cmd = new MySqlCommand("Insert into closubtraninventory (RefNo,InvSno,StockQty) values(@RefNo,@InvSno,@StockQty)");
cmd.Parameters.AddWithValue("@RefNo", Sno);
cmd.Parameters.AddWithValue("@InvSno", drv["Inv_Sno"].ToString());
cmd.Parameters.AddWithValue("@StockQty", drv["Qty"].ToString());
vdm.insert(cmd);
}
}
}
catch
{
}
}
//cmd = new MySqlCommand("Insert into clotrans(BranchID,IndDate,SalesType,EmpID) Values(@BranchID,@IndDate,@SalesType,@EmpID)");
//cmd.Parameters.AddWithValue("@BranchID", SalesOfficeId);
//cmd.Parameters.AddWithValue("@IndDate", DateTime.Now);
//cmd.Parameters.AddWithValue("@SalesType", 21);
//cmd.Parameters.AddWithValue("@EmpID", Session["UserSno"].ToString());
//long RefSno = vdm.insertScalar(cmd);
//foreach (DataRow drv in dtProReport.Rows)
//{
// cmd = new MySqlCommand("Insert into closubtranprodcts (RefNo,ProductID,StockQty,LeakQty) values(@RefNo,@ProductID,@StockQty,@LeakQty)");
// cmd.Parameters.AddWithValue("@RefNo", RefSno);
// cmd.Parameters.AddWithValue("@ProductID", drv["Product_sno"].ToString());
// float BranchQty = 0;
// float LeakQty = 0;
// float.TryParse(drv["BranchQty"].ToString(), out BranchQty);
// float.TryParse(drv["LeakQty"].ToString(), out LeakQty);
// cmd.Parameters.AddWithValue("@StockQty", BranchQty);
// cmd.Parameters.AddWithValue("@LeakQty", LeakQty);
// if (BranchQty != 0 || LeakQty != 0)
// {
// vdm.insert(cmd);
// }
//}
//foreach (DataRow drv in dtStockInvProduct.Rows)
//{
// cmd = new MySqlCommand("Insert into closubtraninventory (RefNo,InvSno,StockQty) values(@RefNo,@InvSno,@StockQty)");
// cmd.Parameters.AddWithValue("@RefNo", RefSno);
// int Qty = 0;
// int.TryParse(drv["Qty"].ToString(), out Qty);
// cmd.Parameters.AddWithValue("@InvSno", drv["sno"].ToString());
// cmd.Parameters.AddWithValue("@StockQty", Qty);
// if (Qty != 0)
// {
// vdm.insert(cmd);
// }
//}
lblmsg.Text = "Saved successfully";
}
}
catch (Exception ex)
{
lblmsg.Text = ex.ToString();
}
}
private void Loginfo()
{
try
{
String UserName = txtUserName.Text, PassWord = txtPassword.Text;
cmd = new MySqlCommand("SELECT empmanage.Sno, IFNULL(empmanage.otpstatus,0) otpstatus, empmanage.UserName,empmanage.grouplogin, empmanage.Password, empmanage.LevelType, empmanage.flag, empmanage.Userdata_sno, empmanage.Owner, empmanage.EmpName, empmanage.Address, empmanage.Mobno, empmanage.Email, empmanage.LWC, empmanage.RefName, empmanage.Dept_Sno, branchdata.BranchName,branchdata.Radius, empmanage.Branch, salestypemanagement.salestype FROM empmanage INNER JOIN branchdata ON empmanage.Branch = branchdata.sno INNER JOIN salestypemanagement ON branchdata.SalesType = salestypemanagement.sno WHERE (empmanage.UserName = @UN) AND (empmanage.Password = @Pwd) AND (empmanage.flag ='1')");
cmd.Parameters.AddWithValue("@UN", UserName);
cmd.Parameters.AddWithValue("@Pwd", PassWord);
dtemp = vdm.SelectQuery(cmd).Tables[0];
string LevelType = "";
if (dtemp.Rows.Count > 0)
{
//cmd = new MySqlCommand("SELECT branchmappingtable.SuperBranch, branchdata.BranchName, branchdata.stateid, branchdata.gstno, branchdata.statename, statemastar.statename AS BranchState, statemastar.gststatecode FROM branchmappingtable INNER JOIN empmanage ON branchmappingtable.SubBranch = empmanage.Branch INNER JOIN branchdata ON empmanage.Branch = branchdata.sno INNER JOIN statemastar ON branchdata.stateid = statemastar.sno WHERE (empmanage.UserName = @UN)");
//cmd.Parameters.AddWithValue("@UN", UserName);
//DataTable dtBranch = vdm.SelectQuery(cmd).Tables[0];
//string PlantID = "";
//string Branch = dtemp.Rows[0]["Branch"].ToString();
//if (dtBranch.Rows.Count > 0)
//{
// PlantID = dtBranch.Rows[0]["SuperBranch"].ToString();
// Session["SuperBranch"] = dtBranch.Rows[0]["SuperBranch"].ToString();
// if (PlantID == "172" || Branch == "172" || PlantID == "1801" || Branch == "1801" || PlantID == "3625" || Branch == "3625" || Branch == "3919")
// {
// Session["TitleName"] = "SRI VYSHNAVI DAIRY SPECIALITIES (P) LTD";
// if (Branch == "3625")
// {
// Session["Address"] = "No : 170/1B, M.G.R Nagar,Manapakkam,Chennai,TamilNadu (State),PinCode: 600116,TIN No:33140842508,</n>Toll Free No:1800 270 8800 ";
// Session["TinNo"] = "33140842508";
// }
// else
// {
// 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";
// }
// }
// else
// {
// Session["TitleName"] = "SRI VYSHNAVI FOODS (P) LTD";
// Session["Address"] = " Near Ayyappa Swamy Temple, Shasta Nagar, WYRA-507165,KHAMMAM (District), TELANGANA (State).Phone: 08749 – 251326, Fax: 08749 – 252198.";
// Session["TinNo"] = "36550160129";
// }
// Session["BranchName"] = dtBranch.Rows[0]["BranchName"].ToString();
//}
//Session["EmpSno"] = dtemp.Rows[0]["sno"].ToString();
DateTime Currentdate = VehicleDBMgr.GetTime(vdm.conn);
string hostName = Dns.GetHostName(); // Retrive the Name of HOST
//get ip address and device type
ipaddress = Request.ServerVariables["HTTP_X_FORWARDED_FOR"];
if (ipaddress == "" || ipaddress == null)
{
ipaddress = Request.ServerVariables["REMOTE_ADDR"];
}
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 MySqlCommand("INSERT INTO logininfo(UserId, UserName, Logintime, IpAddress,devicetype,status) values (@userid, @UserName, @logintime, @ipaddress,@devicetype,@status)");
cmd.Parameters.AddWithValue("@userid", dtemp.Rows[0]["sno"].ToString());
cmd.Parameters.AddWithValue("@UserName", dtemp.Rows[0]["EmpName"].ToString());
cmd.Parameters.AddWithValue("@logintime", Currentdate);
cmd.Parameters.AddWithValue("@ipaddress", ipaddress);
cmd.Parameters.AddWithValue("@devicetype", devicetype);
cmd.Parameters.AddWithValue("@status", "1");
vdm.insert(cmd);
//End
//Session["gstin"] = dtBranch.Rows[0]["gstno"].ToString();
//Session["statename"] = dtBranch.Rows[0]["BranchState"].ToString();
//Session["statecode"] = dtBranch.Rows[0]["gststatecode"].ToString();
//Session["stateid"] = dtBranch.Rows[0]["stateid"].ToString();
Session["UserSno"] = dtemp.Rows[0]["Sno"].ToString();
//Session["userdata_sno"] = dtemp.Rows[0]["UserData_sno"].ToString();
//Session["UserName"] = dtemp.Rows[0]["UserName"].ToString();
//Session["EmpName"] = dtemp.Rows[0]["EmpName"].ToString();
//if ("232" == dtemp.Rows[0]["Sno"].ToString())
//{
// Session["branch"] = "172";
// Session["salestype"] = "Plant";
//}
//else
//{
// Session["branch"] = dtemp.Rows[0]["Branch"].ToString();
// Session["salestype"] = dtemp.Rows[0]["salestype"].ToString();
//}
//Session["empid"] = dtemp.Rows[0]["Sno"].ToString();
//Session["LevelType"] = dtemp.Rows[0]["LevelType"].ToString();
//Session["GroupLogin"] = dtemp.Rows[0]["grouplogin"].ToString();
//Session["branchname"] = dtemp.Rows[0]["BranchName"].ToString();
//string ModuleId = "1";
string empid = dtemp.Rows[0]["Sno"].ToString();
//Session["moduleid"] = ModuleId;
LevelType = dtemp.Rows[0]["LevelType"].ToString();
string otpstatus = dtemp.Rows[0]["otpstatus"].ToString();
if (otpstatus == "1")
{
string Id = string.Empty;
string no = "";
no = dtemp.Rows[0]["Mobno"].ToString();
string numbers = "1234567890";
string characters = numbers;
int length = 6;
string otp = string.Empty;
for (int i = 0; i < length; i++)
{
string character = string.Empty;
do
{
int index = new Random().Next(0, characters.Length);
character = characters.ToCharArray()[index].ToString();
} while (otp.IndexOf(character) != -1);
otp += character;
}
DateTime sdt = VehicleDBMgr.GetTime(vdm.conn);//DBManager.GetTime(vdm.conn);
int h = Convert.ToInt32(sdt.ToString("HH"));
int m = 0;
string otpexptime = string.Empty;
string sss = string.Empty;
string mm = string.Empty;
m = Convert.ToInt32(sdt.ToString("mm")) + 3;
int ss = Convert.ToInt32(sdt.ToString("ss"));
if (ss > 60)
{
ss = ss - 60;
}
if (ss < 10)
{
sss = "0" + m.ToString();
}
if (m > 60)
{
m = m - 60;
}
if (m < 10)
{
if (ss < 10)
{
mm = "0" + m.ToString();
otpexptime = h.ToString() + ":" + mm.ToString() + ":" + sss.ToString();
}
else
{
mm = m.ToString();
otpexptime = h.ToString() + ":" + mm.ToString() + ":" + ss.ToString();
}
}
else
{
if (ss < 10)
{
otpexptime = h.ToString() + ":" + m.ToString() + ":" + sss.ToString();
}
else
{
otpexptime = h.ToString() + ":" + m.ToString() + ":" + ss.ToString();
}
}
Otpupdate(no, otp, otpexptime, empid);
Id = Encrypt(no.Trim());
string hyperlink = " http://182.18.162.51/fp/OTP.aspx?Id=" + Id.Trim();
string message1 = "OTP for " + empid + " Billproceed transaction is : " + otp + ". Valid till " + otpexptime + " Do not share OTP for security reasons.";
string strUrl = "http://103.16.101.52:8080/sendsms/bulksms?username=kapd-vyshnavi&password=vysavi&type=0&dlr=1&destination=" + no + "&source=VYSHRM&message=" + message1 + "";
WebRequest request = HttpWebRequest.Create(strUrl);
HttpWebResponse response = (HttpWebResponse)request.GetResponse();
Stream s = (Stream)response.GetResponseStream();
StreamReader readStream = new StreamReader(s);
string dataString = readStream.ReadToEnd();
response.Close();
s.Close();
readStream.Close();
string msg = hyperlink;
//string hyperlink = " http://182.18.162.51/fp/OTP.aspx?Id=" + Id.Trim();
Response.Redirect("OTP.aspx?Id=" + Id.Trim());
}
else
{
Session["userdata_sno"] = dtemp.Rows[0]["UserData_sno"].ToString();
Session["empid"] = dtemp.Rows[0]["Sno"].ToString();
cmd = new MySqlCommand("SELECT sno, branchid, empid FROM branchmonitering WHERE (empid = @empid)");
cmd.Parameters.AddWithValue("@empid", dtemp.Rows[0]["Sno"].ToString());
DataTable empmonitor = vdm.SelectQuery(cmd).Tables[0];
if (empmonitor.Rows.Count > 1)
{
Response.Redirect("SwitchAccount.aspx", false);
}
else
{
cmd = new MySqlCommand("SELECT branchmappingtable.SuperBranch, branchdata.BranchName, branchdata.stateid, branchdata.gstno, branchdata.statename, statemastar.statename AS BranchState, statemastar.gststatecode FROM branchmappingtable INNER JOIN empmanage ON branchmappingtable.SubBranch = empmanage.Branch INNER JOIN branchdata ON empmanage.Branch = branchdata.sno INNER JOIN statemastar ON branchdata.stateid = statemastar.sno WHERE (empmanage.UserName = @UN)");
cmd.Parameters.AddWithValue("@UN", UserName);
DataTable dtBranch = vdm.SelectQuery(cmd).Tables[0];
string PlantID = "";
string Branch = dtemp.Rows[0]["Branch"].ToString();
if (dtBranch.Rows.Count > 0)
{
PlantID = dtBranch.Rows[0]["SuperBranch"].ToString();
Session["SuperBranch"] = dtBranch.Rows[0]["SuperBranch"].ToString();
if (PlantID == "172" || Branch == "172" || PlantID == "1801" || Branch == "1801" || PlantID == "3625" || Branch == "3625" || Branch == "3919")
{
Session["TitleName"] = "SRI VYSHNAVI DAIRY SPECIALITIES (P) LTD";
if (Branch == "3625")
{
Session["Address"] = "No : 170/1B, M.G.R Nagar,Manapakkam,Chennai,TamilNadu (State),PinCode: 600116,TIN No:33140842508,</n>Toll Free No:1800 270 8800 ";
Session["TinNo"] = "33140842508";
}
else
{
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";
}
}
else
{
Session["TitleName"] = "SRI VYSHNAVI FOODS (P) LTD";
Session["Address"] = " Near Ayyappa Swamy Temple, Shasta Nagar, WYRA-507165,KHAMMAM (District), TELANGANA (State).Phone: 08749 – 251326, Fax: 08749 – 252198.";
Session["TinNo"] = "36550160129";
}
Session["BranchName"] = dtBranch.Rows[0]["BranchName"].ToString();
}
Session["EmpSno"] = dtemp.Rows[0]["sno"].ToString();
Session["gstin"] = dtBranch.Rows[0]["gstno"].ToString();
Session["statename"] = dtBranch.Rows[0]["BranchState"].ToString();
Session["statecode"] = dtBranch.Rows[0]["gststatecode"].ToString();
Session["stateid"] = dtBranch.Rows[0]["stateid"].ToString();
Session["UserSno"] = dtemp.Rows[0]["Sno"].ToString();
Session["userdata_sno"] = dtemp.Rows[0]["UserData_sno"].ToString();
Session["UserName"] = dtemp.Rows[0]["UserName"].ToString();
Session["EmpName"] = dtemp.Rows[0]["EmpName"].ToString();
if ("232" == dtemp.Rows[0]["Sno"].ToString())
{
Session["branch"] = "172";
Session["salestype"] = "Plant";
}
else
{
Session["branch"] = dtemp.Rows[0]["Branch"].ToString();
Session["salestype"] = dtemp.Rows[0]["salestype"].ToString();
}
Session["empid"] = dtemp.Rows[0]["Sno"].ToString();
Session["LevelType"] = dtemp.Rows[0]["LevelType"].ToString();
Session["GroupLogin"] = dtemp.Rows[0]["grouplogin"].ToString();
Session["branchname"] = dtemp.Rows[0]["BranchName"].ToString();
string ModuleId = "1";
Session["moduleid"] = ModuleId;
if (LevelType == "Opperations" || LevelType == "SODispatcher" || LevelType == "Users" || LevelType == "Dispatcher")
{
Response.Redirect("Order_Report.aspx", false);
}
if (LevelType == "PlantDispatcher")
{
Response.Redirect("PlanDespatch.aspx", false);
}
if (LevelType == "Accountant")
{
Response.Redirect("TripEnd.aspx", false);
}
if (LevelType == "Manager" || LevelType == "MAdmin" || LevelType == "Director")
{
cmd = new MySqlCommand("SELECT formname FROM formdetails ORDER BY sno DESC LIMIT 1");
DataTable dtform = vdm.SelectQuery(cmd).Tables[0];
string formname = dtform.Rows[0]["formname"].ToString();
//Response.Redirect("liveboard.aspx", false);
Response.Redirect(formname, false);
}
if (LevelType == "Admin")
{
Response.Redirect("Delivery_Collection_Report.aspx", false);
}
if (LevelType == "AccountsOfficer")
{
Response.Redirect("liveboard.aspx", false);
}
if (LevelType == "SalesManager")
{
Response.Redirect("Delivery_Collection_Report.aspx", false);
}
if (LevelType == "Casheir")
{
Response.Redirect("cashform.aspx", false);
}
if (LevelType == "Security")
{
Response.Redirect("Gatepass.aspx", false);
}
}
}
}
else
{
MessageBox.Show("Please Enter Correct UserID and Password", this);
}
}
catch (Exception ex)
{
lbl_validation.Text = ex.Message;
}
}
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());
}
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;
}
}
private void LoginInfo()
{
try
{
string username = Usernme_txt.Text, password = Pass_pas.Text;
if (username == "")
{
Response.Write("<script>alert('Please Enter Username');</script>");
return;
}
if (password == "")
{
Response.Write("<script>alert('Please Enter Password');</script>");
return;
}
lbl_username.Text = username;
lbl_passwords.Text = password;
cmd = new MySqlCommand("SELECT employdata.emp_sno, employdata.employid,employdata.loginstatus, employdata.employname, employdata.branch_id, employdata.emp_pwd, branch_info.branchname, employdata.emp_status, employdata.emp_login_id, employdata.operatedby, employdata.dept_id, employdata.emp_type, employdata.emp_type,branch_info.brnch_address,branch_info.branchtype, branch_info.brnch_sno FROM employdata INNER JOIN branch_info ON employdata.branch_id = branch_info.brnch_sno WHERE (employdata.emp_login_id = @UN) AND (employdata.emp_pwd = @Pwd)");
cmd.Parameters.Add("@UN", username);
cmd.Parameters.Add("@Pwd", password);
DataTable dt = vdm.SelectQuery(cmd).Tables[0];
if (dt.Rows.Count > 0)
{
string loginflag = dt.Rows[0]["loginstatus"].ToString();
////if (loginflag == "1")
////{
//// this.AlertBoxMessage.InnerText = "Already Some one Login With This User Name";
//// this.AlertBox.Visible = true;
////}
////else
////{
cmd = new MySqlCommand("SELECT branchmappingtable.subbranch, branchmappingtable.superbranch, branch_info.shortname FROM branchmappingtable INNER JOIN employdata ON branchmappingtable.subbranch = employdata.branch_id INNER JOIN branch_info ON branchmappingtable.subbranch = branch_info.brnch_sno WHERE (employdata.emp_login_id = @Username)");
cmd.Parameters.Add("@Username", username);
DataTable dtBranch = vdm.SelectQuery(cmd).Tables[0];
string PlantID = "";
string Branch = dt.Rows[0]["branch_id"].ToString();
if (dtBranch.Rows.Count > 0)
{
PlantID = dtBranch.Rows[0]["superbranch"].ToString();
if (PlantID == "1" || Branch == "1")
{
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["Address1"] = "R.S.No:381/2,Punabaka village Post,";
Session["Address2"] = "Pellakuru Mandal,Nellore District -524129.";
Session["Address3"] = "ANDRAPRADESH (State).Phone: 9440622077, Fax: 044 – 26177799.";
Session["TinNo"] = "37921042267";
}
else
{
Session["TitleName"] = "SRI VYSHNAVI FOODS (P) LTD";
Session["Address"] = " Near Ayyappa Swamy Temple, Shasta Nagar, WYRA-507165,KHAMMAM (District), TELANGANA (State).Phone: 08749 – 251326, Fax: 08749 – 252198.";
Session["TinNo"] = "36550160129";
}
}
Session["shortname"] = dtBranch.Rows[0]["shortname"].ToString();
Session["Employ_Sno"] = dt.Rows[0]["emp_sno"].ToString();
Session["Branch_ID"] = dt.Rows[0]["brnch_sno"].ToString();
Session["Employ_Type"] = dt.Rows[0]["emp_type"].ToString();
Session["Address"] = dt.Rows[0]["brnch_address"].ToString();
Session["Emp_Type"] = dt.Rows[0]["emp_type"].ToString();
Session["BranchType"] = dt.Rows[0]["branchtype"].ToString();
Session["employname"] = dt.Rows[0]["employname"].ToString();
Session["UserName"] = username;
Session["MID"] = "3";
ipaddress = Request.ServerVariables["HTTP_X_FORWARDED_FOR"];
if (ipaddress == "" || ipaddress == null)
{
ipaddress = Request.ServerVariables["REMOTE_ADDR"];
}
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";
}
//End
cmd = new MySqlCommand("update employdata set loginstatus=@log where emp_login_id=@username and emp_pwd=@passward");
cmd.Parameters.Add("@log", "1");
cmd.Parameters.Add("@username", username);
cmd.Parameters.Add("@passward", password);
vdm.Update(cmd);
DateTime ServerDateCurrentdate = VehicleDBMgr.GetTime(vdm.conn);
cmd = new MySqlCommand("INSERT INTO login_info(empid, logintime, devicetype, ipaddress) values (@userid, @logintime,@devicetype, @ipaddress)");
cmd.Parameters.Add("@userid", dt.Rows[0]["emp_sno"].ToString());
cmd.Parameters.Add("@UserName", Session["UserName"]);
cmd.Parameters.Add("@logintime", ServerDateCurrentdate);
cmd.Parameters.Add("@ipaddress", ipaddress);
cmd.Parameters.Add("@devicetype", devicetype);
vdm.insert(cmd);
string leval = dt.Rows[0]["emp_type"].ToString();
if (leval == "weigher")
{
Response.Redirect("Trip_assign.aspx", false);
}
else
{
Response.Redirect("DashBoard_alerts.aspx", false);
}
////}
}
else
{
lbl_validation.Text = "Username and password Not found";
}
}
catch (Exception ex)
{
lbl_validation.Text = ex.Message;
}
}