protected void finalize_stockclosing(object sender, EventArgs e)
{
vdm = new SalesDBManager();
DataTable dt = (DataTable)Session["xportdata"];
DateTime doe = SalesDBManager.GetTime(vdm.conn);
DateTime closingdate = doe.AddDays(1);
string entryby = Session["Employ_Sno"].ToString();
string branchid = Session["Po_BranchID"].ToString();
foreach (DataRow dr in dt.Rows)
{
try
{
string productname = dr["Product Name"].ToString();
if (productname != "Total")
{
string qty = dr["ClosingQty"].ToString();
if (qty != "0")
{
double price = Convert.ToDouble(dr["OpPrice"].ToString());
if (price > 1)
{
}
else
{
price = Convert.ToDouble(dr["ReceiptPrice"].ToString());
}
cmd = new SqlCommand("select productid from productmaster where productname = @productname");
cmd.Parameters.Add("@productname", productname);
DataTable dt_productid = vdm.SelectQuery(cmd).Tables[0];
string productid = dt_productid.Rows[0]["productid"].ToString();
cmd = new SqlCommand("insert into stockclosingdetails (productid,qty,price,doe,entryby,branchid) values (@productid,@qty,@price,@doe,@entryby,@branchid)");
cmd.Parameters.Add("@productid", productid);
cmd.Parameters.Add("@qty", qty);
cmd.Parameters.Add("@price", price);
cmd.Parameters.Add("@doe", closingdate);
cmd.Parameters.Add("@entryby", entryby);
cmd.Parameters.Add("@branchid", branchid);
vdm.insert(cmd);
}
else
{
}
}
}
catch (Exception ex)
{
string productname = dr["Product Name"].ToString();
}
}
lblmsg.Text = "Saved successfully";
DataTable dtempty = new DataTable();
grdReports.DataSource = dtempty;
grdReports.DataBind();
}
protected void btn_finalizeclick(object sender, EventArgs e)
{
DateTime ServerDateCurrentdate = SalesDBManager.GetTime(vdm.conn);
DataTable dtdetails = (DataTable)Session["finalize"];
if (dtdetails.Rows.Count > 0)
{
DateTime fromdate = DateTime.Now;
string[] datestrig = dtp_FromDate.Text.Split(' ');
if (datestrig.Length > 1)
{
if (datestrig[0].Split('-').Length > 0)
{
string[] dates = datestrig[0].Split('-');
string[] times = datestrig[1].Split(':');
fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
string date = fromdate.ToString("dd/MM/yyyy");
string[] data = date.Split('/');
string month = data[1].ToString();
string year = data[2].ToString();
string mainbranch = Session["Branch_ID"].ToString();
foreach (DataRow dr in dtdetails.Rows)
{
string productid = dr["ProductId"].ToString();
string packingcharge = dr["RATE PER LTR"].ToString();
cmd = new SqlCommand("Update packingcharges set rateperltr=@rateperltr where productid=@pid and month=@month and year=@yr and branchid=@bid");
cmd.Parameters.Add("@month", month);
cmd.Parameters.Add("@yr", year);
cmd.Parameters.Add("@pid", productid);
cmd.Parameters.Add("@rateperltr", packingcharge);
cmd.Parameters.Add("@bid", mainbranch);
if (vdm.Update(cmd) == 0)
{
cmd = new SqlCommand("INSERT INTO packingcharges(productid, rateperltr, month, year, branchid, doe) values (@productid, @rate, @mnth, @year, @branchid, @doe)");
cmd.Parameters.Add("@mnth", month);
cmd.Parameters.Add("@year", year);
cmd.Parameters.Add("@productid", productid);
cmd.Parameters.Add("@rate", packingcharge);
cmd.Parameters.Add("@branchid", mainbranch);
cmd.Parameters.Add("@doe", ServerDateCurrentdate);
vdm.insert(cmd);
}
}
}
}
protected void btnUpdate_Click(object sender, EventArgs e)
{
try
{
SalesDBManager nvdm = new SalesDBManager();
DataTable dtconsumption = (DataTable)Session["xportdata"];
DateTime CreateDate = SalesDBManager.GetTime(nvdm.conn);
string createdby = Session["Employ_Sno"].ToString();
if (dtconsumption.Rows.Count > 0)
{
foreach (DataRow dr in dtconsumption.Rows)
{
string date = dr["Invoice Date"].ToString();
DateTime dt = Convert.ToDateTime(date);
string qty = dr["Qty"].ToString();
string vcehicleno = dr["Customer Name"].ToString();
string Rate = dr["Rate"].ToString();
string pid = "2285";
if (Convert.ToDouble(qty) > 0)
{
sqlcmd = new SqlCommand("Insert into diesel_consumptiondetails (productid, qty, doe, vehicleno, dieselcost, createdby, createddate, branchid, categoryid, subcategoryid) values (@productid, @qty, @doe, @vehicleno, @dieselcost, @createdby, @createddate,@branchid, @catid, @subcatid)");
sqlcmd.Parameters.Add("@productid", pid);
sqlcmd.Parameters.Add("@qty", qty);
sqlcmd.Parameters.Add("@doe", dt);
sqlcmd.Parameters.Add("@vehicleno", vcehicleno);
sqlcmd.Parameters.Add("@createdby", createdby);
sqlcmd.Parameters.Add("@createddate", CreateDate);
sqlcmd.Parameters.Add("@dieselcost", Rate);
sqlcmd.Parameters.Add("@branchid", "2");
sqlcmd.Parameters.Add("@catid", "8");
sqlcmd.Parameters.Add("@subcatid", "37");
nvdm.insert(sqlcmd);
sqlcmd = new SqlCommand("update productmoniter set qty=qty-@qty where productid='2285' AND branchid=@branchid");
sqlcmd.Parameters.Add("@branchid", "2");
sqlcmd.Parameters.Add("@qty", qty);
nvdm.Update(sqlcmd);
}
}
lblmsg.Text = "Successfuly Saved";
}
}
catch (Exception ex)
{
throw ex;
}
}
protected void sessionsclick_click(object sender, EventArgs e)
{
try
{
this.AlertBox.Visible = false;
string username = lbl_username.Text.ToString();
string password = lbl_passwords.Text.ToString();
DateTime ServerDateCurrentdate = SalesDBManager.GetTime(vdm.conn);
cmd = new SqlCommand("update employee_erp set loginstatus=@log where username=@username and passward=@passward");
cmd.Parameters.Add("@log", "0");
cmd.Parameters.Add("@username", username);
cmd.Parameters.Add("@passward", password);
vdm.Update(cmd);
cmd = new SqlCommand("SELECT sno FROM employee_erp where username=@username and passward=@passward");
cmd.Parameters.Add("@username", username);
cmd.Parameters.Add("@passward", password);
DataTable dtEMP = vdm.SelectQuery(cmd).Tables[0];
if (dtEMP.Rows.Count > 0)
{
string empid = dtEMP.Rows[0]["sno"].ToString();
cmd = new SqlCommand("Select max(sno) as transno from logininfo where UserId=@userid");
cmd.Parameters.Add("@userid", empid);
DataTable dttime = vdm.SelectQuery(cmd).Tables[0];
if (dttime.Rows.Count > 0)
{
string transno = dttime.Rows[0]["transno"].ToString();
cmd = new SqlCommand("UPDATE logininfo set logouttime=@logouttime,status=@status where sno=@sno");
cmd.Parameters.Add("@logouttime", ServerDateCurrentdate);
cmd.Parameters.Add("@status", "0");
cmd.Parameters.Add("@sno", transno);
vdm.Update(cmd);
}
}
this.AlertBox.Visible = false;
fill_login_details();
}
catch
{
}
}
protected void btnSave_Click(object sender, EventArgs e)
{
try
{
string BranchID = Session["Branch_ID"].ToString();
DataTable dt = (DataTable)Session["dtImport"];
FleetDBManager fdm = new FleetDBManager();
vdm = new SalesDBManager();
DateTime ServerDateCurrentdate = SalesDBManager.GetTime(vdm.conn);
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
try
{
string sno = dr["sno"].ToString();
string tripsheetno = dr["tripsheetno"].ToString();
string dcost = dr["DIESEL COST"].ToString();
mycmd = new MySqlCommand("UPDATE tripdata set DieselCost=@DieselCost where sno=@sno and tripsheetno=@tripsheetno");
mycmd.Parameters.Add("@DieselCost", dcost);
mycmd.Parameters.Add("@sno", sno);
mycmd.Parameters.Add("@tripsheetno", tripsheetno);
fdm.Update(mycmd);
}
catch (Exception ex)
{
throw ex;
}
//
}
lblmsg.Text = "uploded success";
}
}
catch (Exception ex)
{
lblMessage.Text = ex.Message;
lblmsg.Text = ex.Message;
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["Employ_Sno"] != null)
{
string sno = Session["Employ_Sno"].ToString();
cmd = new SqlCommand("update employe_details set loginflag=@log where sno=@sno");
cmd.Parameters.Add("@log", "0");
cmd.Parameters.Add("@sno", sno);
vdm.Update(cmd);
DateTime ServerDateCurrentdate = SalesDBManager.GetTime(vdm.conn);
cmd = new SqlCommand("Select max(sno) as transno from logininfo where UserId=@userid AND UserName=@UserName");
cmd.Parameters.Add("@userid", Session["Employ_Sno"]);
cmd.Parameters.Add("@UserName", Session["UserName"]);
DataTable dttime = vdm.SelectQuery(cmd).Tables[0];
if (dttime.Rows.Count > 0)
{
string transno = dttime.Rows[0]["transno"].ToString();
cmd = new SqlCommand("UPDATE logininfo set logouttime=@logouttime where sno=@sno");
cmd.Parameters.Add("@logouttime", ServerDateCurrentdate);
cmd.Parameters.Add("@sno", transno);
vdm.Update(cmd);
}
}
else
{
cmd = new SqlCommand("update employe_details set loginflag=@log");
cmd.Parameters.Add("@log", "0");
vdm.Update(cmd);
}
Session.Clear();
Session.RemoveAll();
Session.Abandon();
Response.Cookies["UserName"].Expires = DateTime.Now.AddDays(-1);
Response.Cookies["Employ_Sno"].Expires = DateTime.Now.AddDays(-1);
Response.Redirect("Default.aspx");
}
protected void Page_Load(object sender, EventArgs e)
{
DateTime ServerDateCurrentdate = SalesDBManager.GetTime(vdm.conn);
//cmd = new SqlCommand("Select max(sno) as transno from logininfo where UserId=@userid");
//cmd.Parameters.Add("@userid", Session["Employ_Sno"]);
//cmd.Parameters.Add("@UserName", Session["UserName"]);
if (Session["Employ_Sno"] != "" || Session["Employ_Sno"] != null || Session["Employ_Sno"] != "undefined")
{
string sno = Session["Employ_Sno"].ToString();
cmd = new SqlCommand("update employee_erp set loginstatus=@log where sno=@sno");
cmd.Parameters.Add("@log", "0");
cmd.Parameters.Add("@sno", sno);
vdm.Update(cmd);
cmd = new SqlCommand("Select max(sno) as transno from logininfo where UserId=@userid");
cmd.Parameters.Add("@userid", sno);
DataTable dttime = vdm.SelectQuery(cmd).Tables[0];
if (dttime.Rows.Count > 0)
{
string transno = dttime.Rows[0]["transno"].ToString();
cmd = new SqlCommand("UPDATE logininfo set logouttime=@logouttime,status=@status where sno=@sno");
cmd.Parameters.Add("@logouttime", ServerDateCurrentdate);
cmd.Parameters.Add("@status", "0");
cmd.Parameters.Add("@sno", transno);
vdm.Update(cmd);
}
}
ExpireAllCookies();
Session.Clear();
Session.RemoveAll();
Session.Abandon();
//window.localStorage.clear();
//ClearCache();
//clearchachelocalall();
Response.Redirect("login.aspx");
}
protected void BtnSave_Click(object sender, EventArgs e)
{
try
{
vdm = new SalesDBManager();
DateTime CreateDate = SalesDBManager.GetTime(vdm.conn);
SAPdbmanger SAPvdm = new SAPdbmanger();
DateTime fromdate = DateTime.Now;
DataTable dt = (DataTable)Session["xportdata"];
string[] dateFromstrig = dtp_FromDate.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);
}
}
foreach (DataRow dr in dt.Rows)
{
if (ddltype.SelectedItem.Value == "2")
{
string AcctCode = dr["Ledger Code"].ToString();
string whCode = dr["WH Code"].ToString();
if (AcctCode == "" && whCode == "")
{
}
else
{
sqlcmd = new SqlCommand("SELECT CreateDate, RefDate, DocDate, Ref1, Ref2, Ref3, TransNo, AcctCode FROM EMROJDT WHERE (RefDate BETWEEN @d1 AND @d2) AND (TransNo = @TransNo) AND (Ref1=@Ref1) AND (OcrCode = @OcrCode) AND (AcctCode=@AcCode)");
sqlcmd.Parameters.Add("@d1", GetLowDate(fromdate));
sqlcmd.Parameters.Add("@d2", GetHighDate(fromdate));
sqlcmd.Parameters.Add("@TransNo", dr["JV No"].ToString());
sqlcmd.Parameters.Add("@OcrCode", dr["WH Code"].ToString());
sqlcmd.Parameters.Add("@Ref1", dr["Item Code"].ToString());
sqlcmd.Parameters.Add("@AcCode", dr["Ledger Code"].ToString());
DataTable dtJournelPay = SAPvdm.SelectQuery(sqlcmd).Tables[0];
if (dtJournelPay.Rows.Count > 0)
{
}
else
{
sqlcmd = new SqlCommand("Insert into EMROJDT (CreateDate, RefDate, DocDate, TransNo, AcctCode, AcctName, Debit, Credit, B1Upload, Processed,Ref1,OcrCode,OcrCode2,series) values (@CreateDate, @RefDate, @DocDate,@TransNo, @AcctCode, @AcctName, @Debit, @Credit, @B1Upload, @Processed,@Ref1,@OcrCode,@OcrCode2,@series)");
sqlcmd.Parameters.Add("@CreateDate", GetLowDate(fromdate));
sqlcmd.Parameters.Add("@RefDate", GetLowDate(fromdate));
sqlcmd.Parameters.Add("@docdate", GetLowDate(fromdate));
sqlcmd.Parameters.Add("@Ref1", dr["Item Code"].ToString());
sqlcmd.Parameters.Add("@TransNo", dr["JV No"].ToString());
sqlcmd.Parameters.Add("@AcctCode", dr["Ledger Code"].ToString());
sqlcmd.Parameters.Add("@AcctName", dr["Ledger Name"].ToString());
double amount = 0;
double.TryParse(dr["Total Amount"].ToString(), out amount);
double Debit = 0;
sqlcmd.Parameters.Add("@Debit", Debit);
sqlcmd.Parameters.Add("@Credit", amount);
string B1Upload = "N";
string Processed = "N";
sqlcmd.Parameters.Add("@B1Upload", B1Upload);
sqlcmd.Parameters.Add("@Processed", Processed);
sqlcmd.Parameters.Add("@OcrCode", dr["WH Code"].ToString());
sqlcmd.Parameters.Add("@OcrCode2", dr["Category Code"].ToString());
string series = "230";
sqlcmd.Parameters.Add("@series", series);
SAPvdm.insert(sqlcmd);
sqlcmd = new SqlCommand("Insert into EMROJDT (CreateDate, RefDate, DocDate, TransNo, AcctCode, AcctName, Debit, Credit, B1Upload, Processed,Ref1,OcrCode,OcrCode2,series) values (@CreateDate, @RefDate, @DocDate,@TransNo, @AcctCode, @AcctName, @Debit, @Credit, @B1Upload, @Processed,@Ref1,@OcrCode,@OcrCode2,@series)");
sqlcmd.Parameters.Add("@CreateDate", GetLowDate(fromdate));
sqlcmd.Parameters.Add("@RefDate", GetLowDate(fromdate));
sqlcmd.Parameters.Add("@docdate", GetLowDate(fromdate));
sqlcmd.Parameters.Add("@Ref1", dr["Item Code"].ToString());
sqlcmd.Parameters.Add("@TransNo", dr["JV No"].ToString());
sqlcmd.Parameters.Add("@AcctCode", dr["Ledger Code"].ToString());
sqlcmd.Parameters.Add("@AcctName", dr["Ledger Name"].ToString());
double tamount = 0;
double.TryParse(dr["Total Amount"].ToString(), out tamount);
double Credit = 0;
sqlcmd.Parameters.Add("@Debit", tamount);
sqlcmd.Parameters.Add("@Credit", Credit);
sqlcmd.Parameters.Add("@B1Upload", B1Upload);
sqlcmd.Parameters.Add("@Processed", Processed);
sqlcmd.Parameters.Add("@OcrCode", dr["WH Code"].ToString());
sqlcmd.Parameters.Add("@OcrCode2", dr["Category Code"].ToString());
sqlcmd.Parameters.Add("@series", series);
SAPvdm.insert(sqlcmd);
}
}
}
else
{
double amount = 0;
double.TryParse(dr["Total Amount"].ToString(), out amount);
string B1Upload = "N";
string Processed = "N";
sqlcmd = new SqlCommand("SELECT CreateDate, PostingDate, DocDate, ReferenceNo, ItemCode, ItemName FROM EMROIGE WHERE (PostingDate BETWEEN @d1 AND @d2) AND (ItemCode=@ItemCode) AND (ReferenceNo = @ReferenceNo) AND (WhsCode = @WhsCode)");
sqlcmd.Parameters.Add("@d1", GetLowDate(fromdate));
sqlcmd.Parameters.Add("@d2", GetHighDate(fromdate));
sqlcmd.Parameters.Add("@ReferenceNo", dr["JV No"].ToString());
sqlcmd.Parameters.Add("@WhsCode", dr["WH Code"].ToString());
sqlcmd.Parameters.Add("@ItemCode", dr["Item Code"].ToString());
DataTable dtGoodsIssue = SAPvdm.SelectQuery(sqlcmd).Tables[0];
if (dtGoodsIssue.Rows.Count > 0)
{
}
else
{
sqlcmd = new SqlCommand("Insert into EMROIGE (CreateDate,PostingDate,DocDate,ReferenceNo,ItemCode,ItemName,Price,Quantity,WhsCode,OcrCode,OcrCode2,Remarks,B1Upload,Processed,series) values (@CreateDate,@PostingDate,@DocDate,@ReferenceNo,@ItemCode,@ItemName,@Price,@Quantity,@WhsCode,@OcrCode,@OcrCode2,@Remarks,@B1Upload,@Processed,@series)");
sqlcmd.Parameters.Add("@CreateDate", CreateDate);
sqlcmd.Parameters.Add("@PostingDate", GetLowDate(fromdate));
sqlcmd.Parameters.Add("@DocDate", GetLowDate(fromdate));
sqlcmd.Parameters.Add("@ReferenceNo", dr["JV No"].ToString());
sqlcmd.Parameters.Add("@ItemCode", dr["Item Code"].ToString());
sqlcmd.Parameters.Add("@ItemName", dr["Item Name"].ToString());
sqlcmd.Parameters.Add("@Price", amount);
sqlcmd.Parameters.Add("@Quantity", dr["Qty"].ToString());
sqlcmd.Parameters.Add("@WhsCode", dr["WH Code"].ToString());
sqlcmd.Parameters.Add("@OcrCode", dr["WH Code"].ToString());
sqlcmd.Parameters.Add("@OcrCode2", dr["Category Code"].ToString());
sqlcmd.Parameters.Add("@Remarks", dr["Narration"].ToString());
sqlcmd.Parameters.Add("@B1Upload", B1Upload);
sqlcmd.Parameters.Add("@Processed", Processed);
string series = "240";
sqlcmd.Parameters.Add("@series", series);
SAPvdm.insert(sqlcmd);
}
string AcctCode = dr["Ledger Code"].ToString();
string whCode = dr["WH Code"].ToString();
if (AcctCode == "" && whCode == "")
{
}
else
{
sqlcmd = new SqlCommand("SELECT CreateDate, RefDate, DocDate, Ref1, Ref2, Ref3, TransNo, AcctCode FROM EMROJDT WHERE (RefDate BETWEEN @d1 AND @d2) AND (TransNo = @TransNo) AND (Ref1=@Ref1) AND (OcrCode = @OcrCode)");
sqlcmd.Parameters.Add("@d1", GetLowDate(fromdate));
sqlcmd.Parameters.Add("@d2", GetHighDate(fromdate));
sqlcmd.Parameters.Add("@TransNo", dr["JV No"].ToString());
sqlcmd.Parameters.Add("@OcrCode", dr["WH Code"].ToString());
sqlcmd.Parameters.Add("@Ref1", dr["Item Code"].ToString());
DataTable dtJournelPay = SAPvdm.SelectQuery(sqlcmd).Tables[0];
if (dtJournelPay.Rows.Count > 0)
{
}
else
{
sqlcmd = new SqlCommand("Insert into EMROJDT (CreateDate, RefDate, DocDate, TransNo, AcctCode, AcctName, Debit, Credit, B1Upload, Processed,Ref1,OcrCode,OcrCode2,series) values (@CreateDate, @RefDate, @DocDate,@TransNo, @AcctCode, @AcctName, @Debit, @Credit, @B1Upload, @Processed,@Ref1,@OcrCode,@OcrCode2,@series)");
sqlcmd.Parameters.Add("@CreateDate", GetLowDate(fromdate));
sqlcmd.Parameters.Add("@RefDate", GetLowDate(fromdate));
sqlcmd.Parameters.Add("@docdate", GetLowDate(fromdate));
sqlcmd.Parameters.Add("@Ref1", dr["Item Code"].ToString());
sqlcmd.Parameters.Add("@TransNo", dr["JV No"].ToString());
sqlcmd.Parameters.Add("@AcctCode", dr["Ledger Code"].ToString());
sqlcmd.Parameters.Add("@AcctName", dr["Ledger Name"].ToString());
double camount = 0;
double.TryParse(dr["Total Amount"].ToString(), out camount);
double Debit = 0;
sqlcmd.Parameters.Add("@Debit", Debit);
sqlcmd.Parameters.Add("@Credit", camount);
string cB1Upload = "N";
string cProcessed = "N";
sqlcmd.Parameters.Add("@B1Upload", cB1Upload);
sqlcmd.Parameters.Add("@Processed", cProcessed);
sqlcmd.Parameters.Add("@OcrCode", dr["WH Code"].ToString());
sqlcmd.Parameters.Add("@OcrCode2", dr["Category Code"].ToString());
string series = "230";
sqlcmd.Parameters.Add("@series", series);
SAPvdm.insert(sqlcmd);
sqlcmd = new SqlCommand("Insert into EMROJDT (CreateDate, RefDate, DocDate, TransNo, AcctCode, AcctName, Debit, Credit, B1Upload, Processed,Ref1,OcrCode,OcrCode2,series) values (@CreateDate, @RefDate, @DocDate,@TransNo, @AcctCode, @AcctName, @Debit, @Credit, @B1Upload, @Processed,@Ref1,@OcrCode,@OcrCode2,@series)");
sqlcmd.Parameters.Add("@CreateDate", GetLowDate(fromdate));
sqlcmd.Parameters.Add("@RefDate", GetLowDate(fromdate));
sqlcmd.Parameters.Add("@docdate", GetLowDate(fromdate));
sqlcmd.Parameters.Add("@Ref1", dr["Item Code"].ToString());
sqlcmd.Parameters.Add("@TransNo", dr["JV No"].ToString());
sqlcmd.Parameters.Add("@AcctCode", dr["Ledger Code"].ToString());
sqlcmd.Parameters.Add("@AcctName", dr["Ledger Name"].ToString());
double damount = 0;
double.TryParse(dr["Total Amount"].ToString(), out damount);
double Credit = 0;
sqlcmd.Parameters.Add("@Debit", damount);
sqlcmd.Parameters.Add("@Credit", Credit);
sqlcmd.Parameters.Add("@B1Upload", B1Upload);
sqlcmd.Parameters.Add("@Processed", Processed);
sqlcmd.Parameters.Add("@OcrCode", dr["WH Code"].ToString());
sqlcmd.Parameters.Add("@OcrCode2", dr["Category Code"].ToString());
sqlcmd.Parameters.Add("@series", series);
SAPvdm.insert(sqlcmd);
}
}
}
}
DataTable dtempty = new DataTable();
grdReports.DataSource = dtempty;
grdReports.DataBind();
lblmsg.Text = "Successfully Saved";
}
catch (Exception ex)
{
lblmsg.Text = ex.ToString();
}
}
protected void btn_Generate_Click(object sender, EventArgs e)
{
try
{
Report.Columns.Add("CREATE DATE");
Report.Columns.Add("CARD CODE");
Report.Columns.Add("CARD NAME");
Report.Columns.Add("DISCOUNT %");
Report.Columns.Add("REFERENCE NO");
Report.Columns.Add("ITEMCODE");
Report.Columns.Add("DESCRIPTION");
Report.Columns.Add("WARE HOUSE CODE");
Report.Columns.Add("QUANTITY");
Report.Columns.Add("PRICE");
Report.Columns.Add("TAX CODE");
Report.Columns.Add("TAX%");
Report.Columns.Add("TRANSPORT");
Report.Columns.Add("LINE TOTAL");
Report.Columns.Add("Narration");
lblmsg.Text = "";
SalesDBManager SalesDB = new SalesDBManager();
DateTime fromdate = DateTime.Now;
string[] datestrig = dtp_FromDate.Text.Split(' ');
if (datestrig.Length > 1)
{
if (datestrig[0].Split('-').Length > 0)
{
string[] dates = datestrig[0].Split('-');
string[] times = datestrig[1].Split(':');
fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
lblFromDate.Text = fromdate.ToString("dd/MM/yyyy");
string branchid = Session["Po_BranchID"].ToString();
DateTime ReportDate = SalesDBManager.GetTime(SalesDB.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);
}
cmd = new SqlCommand("SELECT suppliersdetails.name, suppliersdetails.suppliercode, inwarddetails.sno, inwarddetails.inwarddate, inwarddetails.invoiceno, inwarddetails.invoicedate,inwarddetails.dcno, inwarddetails.lrno, inwarddetails.supplierid, inwarddetails.podate, inwarddetails.doorno, inwarddetails.remarks, inwarddetails.pono, inwarddetails.inwardno, inwarddetails.transportname, inwarddetails.vehicleno, inwarddetails.modeofinward, inwarddetails.securityno, inwarddetails.uimid,inwarddetails.status, inwarddetails.mrnno, inwarddetails.indentno, inwarddetails.freigtamt, inwarddetails.transportcharge, inwarddetails.inwardamount, subinwarddetails.sno AS Expr1, subinwarddetails.productid, subinwarddetails.quantity, subinwarddetails.perunit, subinwarddetails.totalcost, subinwarddetails.in_refno, subinwarddetails.status AS Expr2, subinwarddetails.taxtype, subinwarddetails.ed, subinwarddetails.dis, subinwarddetails.disamt, subinwarddetails.tax, subinwarddetails.edtax, productmaster.itemcode, productmaster.productname, taxmaster.type AS tax_type, taxmaster_1.type AS ed_type, inwarddetails.pfid, pandf.pandf, subinwarddetails.igst, subinwarddetails.cgst, subinwarddetails.sgst FROM inwarddetails INNER JOIN subinwarddetails ON inwarddetails.sno = subinwarddetails.in_refno INNER JOIN productmaster ON subinwarddetails.productid = productmaster.productid INNER JOIN suppliersdetails ON inwarddetails.supplierid = suppliersdetails.supplierid left outer JOIN taxmaster ON subinwarddetails.taxtype = taxmaster.sno left outer JOIN taxmaster AS taxmaster_1 ON subinwarddetails.ed = taxmaster_1.sno LEFT OUTER JOIN pandf ON inwarddetails.pfid = pandf.sno WHERE (inwarddetails.inwarddate BETWEEN @fromdate AND @todate) AND (inwarddetails.branchid = @branchid) AND (inwarddetails.mrnno IS NOT NULL) AND (inwarddetails.mrnno <> 0)");
cmd.Parameters.Add("@fromdate", GetLowDate(fromdate));
cmd.Parameters.Add("@todate", GetHighDate(fromdate));
cmd.Parameters.Add("@branchid", branchid);
DataTable dttotalinward = vdm.SelectQuery(cmd).Tables[0];
if (dttotalinward.Rows.Count > 0)
{
string taxcode = "", taxtype = "", transport = "";
double ttotalamount = 0;
DateTime dt = DateTime.Now;
string prevdate = string.Empty;
int i = 0;
string PONo = "";
string newpoNo = "";
foreach (DataRow dr in dttotalinward.Rows)
{
DataRow newrow = Report.NewRow();
DateTime dt_inward = Convert.ToDateTime(dr["inwarddate"].ToString());
string dt_inward_date = dt_inward.ToString("dd/MM/yyyy");
DateTime invoicedate = Convert.ToDateTime(dr["invoicedate"].ToString());
string indate = invoicedate.ToString("dd/MM/yyyy");
newrow["CREATE DATE"] = dt_inward_date; //dr["inwarddate"].ToString();
newrow["CARD CODE"] = dr["suppliercode"].ToString();
newrow["CARD NAME"] = dr["name"].ToString();
string cardname = dr["name"].ToString();
newrow["DISCOUNT %"] = dr["dis"].ToString();
string newmrn = "0";
int countdc = 0;
int.TryParse(dr["mrnno"].ToString(), out countdc);
if (countdc < 10)
{
newmrn = "0000" + countdc;
}
if (countdc >= 10 && countdc <= 99)
{
newmrn = "000" + countdc;
}
if (countdc >= 99 && countdc <= 999)
{
newmrn = "00" + countdc;
}
if (countdc >= 999 && countdc <= 9999)
{
newmrn = "0" + countdc;
}
if (countdc > 9999)
{
newmrn = "" + countdc;
}
string BranchCode = Session["BranchCode"].ToString();
string invno = dr["invoiceno"].ToString();
newrow["REFERENCE NO"] = BranchCode + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "/" + newmrn;
newrow["ITEMCODE"] = dr["itemcode"].ToString();
newrow["DESCRIPTION"] = dr["productname"].ToString();
string product = dr["productname"].ToString();
newrow["WARE HOUSE CODE"] = "SVDSPP02";
newrow["QUANTITY"] = dr["quantity"].ToString();
double rate = 0;
double.TryParse(dr["perunit"].ToString(), out rate);
rate = Math.Round(rate, 2);
newrow["PRICE"] = rate;
taxtype = dr["tax_type"].ToString();
string edtype = dr["edtax"].ToString();
string IGST = dr["igst"].ToString();
string CGST = dr["cgst"].ToString();
string SGST = dr["sgst"].ToString();
if (IGST != "0")
{
double igsttax = Convert.ToDouble(IGST);
taxcode = "IGST" + IGST + "";
newrow["TAX%"] = IGST;
}
if (CGST != "0")
{
double cgsttax = Convert.ToDouble(CGST);
cgsttax = cgsttax + cgsttax;
taxcode = "CGST" + cgsttax + "";
newrow["TAX%"] = CGST;
}
if (IGST == "0" && CGST == "0")
{
taxcode = "EXEMPT";
newrow["TAX%"] = "0";
}
//if (edtype == "0")
//{
// if (taxtype == "CST 2% against Form C1" || taxtype == "Input CST @2%")
// {
// taxcode = "CST@2";
// }
// else if (taxtype == "Input CST @5%")
// {
// taxcode = "CST@5";
// }
// else if (taxtype == "Input VAT @14.5%")
// {
// taxcode = "[email protected]";
// }
// else if (taxtype == "Input VAT @5%" || taxtype == "vat 5%")
// {
// taxcode = "VAT@5";
// }
// else if (taxtype == "Input VAT @0%")
// {
// taxcode = "EXEMPT";
// }
// else if (taxtype == "CST @14.5%")
// {
// taxcode = "[email protected]";
// }
//}
//else
//{
// if (taxtype == "Input VAT @14.5%" && edtype == "12.5")
// {
// taxcode = "E125V145";
// }
// if (taxtype == "Input VAT @5%" && edtype == "12.5")
// {
// taxcode = "E12.5V5";
// }
// if ((taxtype == "CST 2% against Form C1" || taxtype == "Input CST @2%") && (edtype == "12.5"))
// {
// taxcode = "ED12.5C2";
// }
// if (taxtype == "Input VAT @0%" && edtype == "12.5")
// {
// taxcode = "[email protected]";
// }
//}
newrow["TAX CODE"] = taxcode;
double price = 0;
double.TryParse(dr["perunit"].ToString(), out price);
double qty = 0;
double.TryParse(dr["quantity"].ToString(), out qty);
double total = 0;
double total_amt = 0, edamount = 0;
total = qty * price;
double dis = 0;
double.TryParse(dr["disamt"].ToString(), out dis);
total_amt = total - dis;
double pfamt = 0; double pfamount = 0;
if (dr["pandf"].ToString() != "")
{
double.TryParse(dr["pandf"].ToString(), out pfamt);
pfamount = (total_amt * pfamt) / 100;
newrow["TRANSPORT"] = pfamount.ToString();
}
string edcode = "";
//string edtype = dr["edtax"].ToString();
//if (edtype == "12.5")
//{
// edcode = "[email protected]";
//}
//else
//{
// edcode = "EXEMPT";
//}
//newrow["ED CODE"] = edcode;
double edamt = 0;
double.TryParse(dr["edtax"].ToString(), out edamt);
edamount = (total_amt * edamt) / 100;
double totedamount = 0;
totedamount = total_amt + edamount + pfamount;
double vat_per = 0; double vat_amt = 0; double amt_tax = 0;
//double edamt = 0;
//double.TryParse(dr["edtax"].ToString(), out edamt);
//edamount = (total_amt * edamt) / 100;
//double totedamount = 0;
//totedamount = total_amt + edamount;//stock
double csttax = 0;
string trans_chrgs = dr["transportcharge"].ToString();
int trans = 0;
int.TryParse(trans_chrgs, out trans);
string freight_chrgs = dr["freigtamt"].ToString();
int frigh = 0;
int.TryParse(freight_chrgs, out frigh);
if (trans != 0)
{
transport = trans_chrgs;
}
else if (frigh != 0)
{
transport = freight_chrgs;
}
else
{
transport = "0";
}
string val = newmrn;
if (val == tempval)
{
double tamount = Convert.ToDouble(transport);
transport = "0";
tempval = val;
}
else
{
if (transport == "0")
{
transport = "0";
}
else
{
newrow["TRANSPORT"] = transport;
}
tempval = val;
}
amt_tax = totedamount + vat_amt + Convert.ToDouble(transport);
newrow["LINE TOTAL"] = amt_tax.ToString("F2");
ttotalamount += amt_tax;
string narration = "Being the purchase of " + product + ", Qty:" + qty + ", Amount:" + amt_tax + "/-. From Invoice Name :" + cardname + ". Invoice No:" + invno + ", Invoice Date:" + indate + ",MRN No:" + newmrn + ", MRN Date:" + dt_inward_date + ", taxtype:" + taxtype + "";
newrow["Narration"] = narration;
Report.Rows.Add(newrow);
i++;
}
Session["xportdata"] = Report;
grdReports.DataSource = Report;
grdReports.DataBind();
hidepanel.Visible = true;
}
else
{
lblmsg.Text = "No data were found";
hidepanel.Visible = false;
}
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
hidepanel.Visible = false;
}
}
protected void login_click(object sender, EventArgs e)
{
try
{
vdm = new SalesDBManager();
string userid = txtusername.Text, password = txtpassword.Text;
cmd = new SqlCommand("SELECT sno, employename, userid, password, emailid, phone, branchtype, leveltype, departmentid, branchid FROM employe_details where userid=@username AND password=@pwd");
cmd.Parameters.Add("@pwd", password);
cmd.Parameters.Add("@username", userid);
DataTable dt = vdm.SelectQuery(cmd).Tables[0];
if (dt.Rows.Count > 0)
{
string sno = dt.Rows[0]["sno"].ToString();
Session["Employ_Sno"] = dt.Rows[0]["sno"].ToString();
Session["BranchID"] = dt.Rows[0]["branchid"].ToString();
Session["UserName"] = dt.Rows[0]["employename"].ToString();
Session["posleveltype"] = dt.Rows[0]["leveltype"].ToString();
string leveltype = dt.Rows[0]["leveltype"].ToString();
Response.Cookies["UserName"].Value = HttpUtility.UrlEncode("true");
Response.Cookies["UserName"].Path = "/";
Response.Cookies["UserName"].Expires = DateTime.Now.AddDays(1);
Response.Cookies["Employ_Sno"].Value = HttpUtility.UrlEncode("true");
Response.Cookies["Employ_Sno"].Path = "/";
Response.Cookies["Employ_Sno"].Expires = DateTime.Now.AddDays(1);
string ipaddress;
ipaddress = Request.ServerVariables["HTTP_X_FORWARDED_FOR"];
if (ipaddress == "" || ipaddress == null)
{
ipaddress = Request.ServerVariables["REMOTE_ADDR"];
}
DateTime ServerDateCurrentdate = SalesDBManager.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("Select * from empbranchmapping Where empid=@sno");
cmd.Parameters.Add("@sno", sno);
DataTable dtemp = vdm.SelectQuery(cmd).Tables[0];
if (dtemp.Rows.Count > 1)
{
Response.Redirect("Switchaccounts.aspx", false);
}
else
{
if (leveltype == "Admin ")
{
Response.Redirect("Default.aspx", false);
}
if (leveltype == "SuperAdmin")
{
Response.Redirect("Default.aspx", false);
}
if (leveltype == "User ")
{
Response.Redirect("vpos.aspx", false);
}
if (leveltype == "Manager ")
{
Response.Redirect("Default.aspx", false);
}
if (leveltype == "Accounts ")
{
Response.Redirect("Default.aspx", false);
}
if (leveltype == "Distibutor")
{
Response.Redirect("Distibuterindent.aspx", false);
}
if (leveltype == "Indent ")
{
ValidateLogin();
Response.Redirect("Distibuterindent.aspx", false);
}
}
}
else
{
lblMsg.Visible = true;
lblMsg.Text = "Invalid userId and Password";
}
}
catch (Exception ex)
{
lblMsg.Text = ex.Message;
}
}
protected void btn_save_click(object sender, EventArgs e)
{
try
{
vdm = new SalesDBManager();
DateTime CreateDate = SalesDBManager.GetTime(vdm.conn);
SAPdbmanger SAPvdm = new SAPdbmanger();
DateTime fromdate = DateTime.Now;
DataTable dt = (DataTable)Session["xportdata"];
string[] dateFromstrig = dtp_FromDate.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);
}
}
string ocrcode2 = "", remarks = "";
foreach (DataRow dr in dt.Rows)
{
string itemcode = dr["ITEMCODE"].ToString();
if (itemcode == "")
{
}
else
{
cmd = new SqlCommand("insert into EMROPDN (CreateDate, CardCode, CardName, TaxDate, DocDate, DocDueDate, ReferenceNo, ItemCode, Dscription, WhsCode, Quantity, Price, VAT_Percent, LineTotal, OcrCode, OcrCode2, REMARKS, B1Upload, Processed) values (@CreateDate, @CardCode, @CardName, @TaxDate, @DocDate, @DocDueDate, @ReferenceNo, @ItemCode, @Dscription, @WhsCode, @Quantity, @Price, @VAT_Percent, @LineTotal, @OcrCode, @OcrCode2, @REMARKS, @B1Upload, @Processed)");
cmd.Parameters.Add("@CreateDate", fromdate);
cmd.Parameters.Add("@CardCode", dr["CARD CODE"].ToString());
cmd.Parameters.Add("@CardName", dr["CARD NAME"].ToString());
cmd.Parameters.Add("@TaxDate", fromdate);
cmd.Parameters.Add("@DocDate", fromdate);
cmd.Parameters.Add("@DocDueDate", fromdate);
cmd.Parameters.Add("@ReferenceNo", dr["REFERENCE NO"].ToString());
cmd.Parameters.Add("@ItemCode", dr["ITEMCODE"].ToString());
cmd.Parameters.Add("@Dscription", dr["DESCRIPTION"].ToString());
cmd.Parameters.Add("@WhsCode", dr["WARE HOUSE CODE"].ToString());
double qty = 0;
double.TryParse(dr["QUANTITY"].ToString(), out qty);
cmd.Parameters.Add("@Quantity", qty);
double price = 0;
double.TryParse(dr["price"].ToString(), out price);
cmd.Parameters.Add("@Price", price);
double vatpercent = 0;
double.TryParse(dr["VAT PERCENT"].ToString(), out vatpercent);
cmd.Parameters.Add("@VAT_Percent", vatpercent);
double linetotal = 0;
double.TryParse(dr["LINE TOTAL"].ToString(), out linetotal);
cmd.Parameters.Add("@LineTotal", linetotal);
cmd.Parameters.Add("@OcrCode", dr["WARE HOUSE CODE"].ToString());
cmd.Parameters.Add("@OcrCode2", ocrcode2);
cmd.Parameters.Add("@REMARKS", remarks);
string B1Upload = "N";
string Processed = "N";
cmd.Parameters.Add("@B1Upload", B1Upload);
cmd.Parameters.Add("@Processed", Processed);
SAPvdm.insert(cmd);
}
}
lblmsg.Text = "Saved successfully";
DataTable dtempty = new DataTable();
grdReports.DataSource = dtempty;
grdReports.DataBind();
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
hidepanel.Visible = false;
}
}
void GetReport()
{
try
{
lblmsg.Text = "";
pnlHide.Visible = true;
Session["RouteName"] = ddlSalesOffice.SelectedItem.Text;
Session["IDate"] = DateTime.Now.AddDays(1).ToString("dd/MM/yyyy");
vdm = new SalesDBManager();
DateTime fromdate = DateTime.Now;
DateTime todate = 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);
}
}
dateFromstrig = dtp_Todate.Text.Split(' ');
if (dateFromstrig.Length > 1)
{
if (dateFromstrig[0].Split('-').Length > 0)
{
string[] dates = dateFromstrig[0].Split('-');
string[] times = dateFromstrig[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);
}
}
DateTime ReportDate = SalesDBManager.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);
}
DataTable dtReport = new DataTable();
dtReport.Columns.Add("Voucher Date");
dtReport.Columns.Add("Voucher No");
dtReport.Columns.Add("whcode");
dtReport.Columns.Add("Voucher Type");
dtReport.Columns.Add("Ledger Code");
dtReport.Columns.Add("Ledger (Dr)");
dtReport.Columns.Add("PaidDate");
dtReport.Columns.Add("Customer Code");
dtReport.Columns.Add("Ledger (Cr)");
dtReport.Columns.Add("Amount");
dtReport.Columns.Add("Narration");
lbl_selfromdate.Text = fromdate.ToString("dd/MM/yyyy");
lblRoutName.Text = ddlSalesOffice.SelectedItem.Text;
Session["xporttype"] = "TallyReceipts";
Session["filename"] = ddlSalesOffice.SelectedItem.Text + " Tally Receipts" + fromdate.ToString("dd/MM/yyyy");
cmd = new SqlCommand("SELECT cashreceipts.ReceivedFrom,cashreceipts.BranchId,Plant_Master.ladger_dr_code, cashreceipts.AgentID, cashreceipts.Empid, cashreceipts.Amountpayable, cashreceipts.AmountPaid, cashreceipts.DOE, cashreceipts.Remarks, cashreceipts.Receipt, cashreceipts.PaymentStatus, cashreceipts.TransactionType, cashreceipts.Sno, Plant_Master.Plant_Name,Plant_Master.WHcode, Plant_Master.ladger_dr FROM cashreceipts INNER JOIN Plant_Master ON cashreceipts.BranchId = Plant_Master.Plant_Code WHERE (cashreceipts.DOE BETWEEN @d1 AND @d2) AND (cashreceipts.BranchId = @Branchid)");
cmd.Parameters.Add("@BranchID", ddlSalesOffice.SelectedValue);
cmd.Parameters.Add("@d1", GetLowDate(fromdate));
cmd.Parameters.Add("@d2", GetHighDate(todate));
DataTable dtothers = vdm.SelectQuery(cmd).Tables[0];
cmd = new SqlCommand("SELECT cashcollections.LedgerCode,cashcollections.Branchid, Plant_Master.Plant_Name, cashcollections.Name, cashcollections.Amount, cashcollections.Remarks, cashcollections.DOE, cashcollections.Receiptno, cashcollections.PaymentType, cashcollections.CollectionType FROM cashcollections INNER JOIN Plant_Master ON cashcollections.Branchid = Plant_Master.Plant_Code WHERE (cashcollections.DOE BETWEEN @d1 AND @d2) AND (cashcollections.Branchid = @Branchid)");
cmd.Parameters.Add("@BranchID", ddlSalesOffice.SelectedValue);
cmd.Parameters.Add("@d1", GetLowDate(fromdate));
cmd.Parameters.Add("@d2", GetHighDate(todate));
DataTable dtreceipts = vdm.SelectQuery(cmd).Tables[0];
if (dtothers.Rows.Count > 0)
{
foreach (DataRow dr in dtothers.Rows)
{
DataRow newrow = dtReport.NewRow();
string VoucherNo = dr["Receipt"].ToString();
string NewVoucherNo = "0";
int countdc = 0;
int.TryParse(VoucherNo, out countdc);
if (countdc <= 10)
{
NewVoucherNo = "000" + countdc;
}
if (countdc >= 10 && countdc <= 99)
{
NewVoucherNo = "00" + countdc;
}
if (countdc >= 99 && countdc <= 999)
{
NewVoucherNo = "0" + countdc;
}
string Date = dr["DOE"].ToString();
DateTime from_date = Convert.ToDateTime(Date);
newrow["Voucher Date"] = from_date.ToString("dd-MMM-yyyy");
newrow["Voucher No"] = ddlSalesOffice.SelectedValue + dtapril.ToString("yy") + dtmarch.ToString("yy") + NewVoucherNo;
int i = 1;
newrow["PaidDate"] = from_date.ToString("dd-MMM-yyyy");
newrow["whcode"] = dr["WHcode"].ToString();
double AmountPaid = 0;
double.TryParse(dr["AmountPaid"].ToString(), out AmountPaid);
newrow["Amount"] = AmountPaid;
newrow["Voucher Date"] = from_date.ToString("dd-MMM-yyyy");
string Name = "";
foreach (DataRow drcash in dtreceipts.Select("Receiptno='" + dr["Receipt"].ToString() + "' and Branchid='" + dr["BranchId"].ToString() + "'"))
{
newrow["Voucher Type"] = drcash["CollectionType"].ToString();
newrow["Ledger (Cr)"] = drcash["Name"].ToString();
Name = drcash["Name"].ToString();
//newrow["Customer Code"] = drcash["customercode"].ToString();
//getledger = newrow["Customer Code"].ToString();
newrow["Customer Code"] = drcash["LedgerCode"].ToString();
}
newrow["whcode"] = dr["whcode"].ToString();
newrow["Ledger Code"] = dr["ladger_dr_code"].ToString();
newrow["Ledger (Dr)"] = dr["ladger_dr"].ToString();
double invval = 0;
newrow["Narration"] = "Being the cash receipt to " + Name + " vide Receipt No " + dr["Receipt"].ToString() + ",Receipt Date " + from_date.ToString("dd/MM/yyyy");
dtReport.Rows.Add(newrow);
i++;
}
grdReports.DataSource = dtReport;
grdReports.DataBind();
Session["xportdata"] = dtReport;
}
else
{
pnlHide.Visible = false;
lblmsg.Text = "No Data Found";
grdReports.DataSource = dtReport;
grdReports.DataBind();
}
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
grdReports.DataSource = dtReport;
grdReports.DataBind();
}
}
void getdet()
{
try
{
vdm = new SalesDBManager();
lblmsg.Text = "";
DateTime ServerDateCurrentdate = SalesDBManager.GetTime(vdm.conn);
DateTime dtapril = new DateTime();
DateTime dtmarch = new DateTime();
int currentyear = ServerDateCurrentdate.Year;
int nextyear = ServerDateCurrentdate.Year + 1;
int currntyearnum = 0;
int nextyearnum = 0;
if (ServerDateCurrentdate.Month > 3)
{
string apr = "4/1/" + currentyear;
dtapril = DateTime.Parse(apr);
string march = "3/31/" + nextyear;
dtmarch = DateTime.Parse(march);
currntyearnum = currentyear;
nextyearnum = nextyear;
}
if (ServerDateCurrentdate.Month <= 3)
{
string apr = "4/1/" + (currentyear - 1);
dtapril = DateTime.Parse(apr);
string march = "3/31/" + (nextyear - 1);
dtmarch = DateTime.Parse(march);
currntyearnum = currentyear - 1;
nextyearnum = nextyear - 1;
}
//cmd = new SqlCommand("SELECT sno, BranchName, BranchCode FROM branchdata WHERE (sno = @BranchID)");
//cmd.Parameters.Add("@BranchID", Session["branch"]);
//DataTable dtCode = vdm.SelectQuery(cmd).Tables[0];
string Receiptid = "";
cmd = new SqlCommand("SELECT CONVERT(VARCHAR(10), DOE, 103) AS DOE , Receiptno, Name, Amount,remarks,CollectionType,PaymentType FROM cashcollections WHERE (Sno = @Receipt)");
cmd.Parameters.Add("@BranchID", Session["branch"]);
cmd.Parameters.Add("@Receipt", txtReceiptNo.Text);
DataTable dtReceiptBook = vdm.SelectQuery(cmd).Tables[0];
if (dtReceiptBook.Rows.Count > 0)
{
Receiptid = Session["branch"].ToString() + "/OTH_RCPT/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "/" + dtReceiptBook.Rows[0]["Receiptno"].ToString();
lblreceiptno.Text = Receiptid;
lblDate.Text = dtReceiptBook.Rows[0]["DOE"].ToString();
lblPayCash.Text = dtReceiptBook.Rows[0]["Name"].ToString();
lbltowards.Text = dtReceiptBook.Rows[0]["PaymentType"].ToString();
lblAmount.Text = dtReceiptBook.Rows[0]["Amount"].ToString();
lblCheque.Text = dtReceiptBook.Rows[0]["CollectionType"].ToString();
lblChequeDate.Text = dtReceiptBook.Rows[0]["DOE"].ToString();
lblRemarks.Text = dtReceiptBook.Rows[0]["remarks"].ToString();
}
string Amont = lblAmount.Text;
string[] Ones = { "", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", "Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Ninteen" };
string[] Tens = { "Ten", "Twenty", "Thirty", "Fourty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninty" };
int Num = int.Parse(Amont);
lblRupess.Text = NumToWordBD(Num) + " Rupees Only";
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
}
}
protected void btn_Generate_Click(object sender, EventArgs e)
{
try
{
DataReport.Columns.Add("CREATE DATE");
DataReport.Columns.Add("CARD CODE");
DataReport.Columns.Add("CARD NAME");
DataReport.Columns.Add("DISCOUNT %");
DataReport.Columns.Add("REFERENCE NO");
DataReport.Columns.Add("ITEMCODE");
DataReport.Columns.Add("DESCRIPTION");
DataReport.Columns.Add("WARE HOUSE CODE");
DataReport.Columns.Add("QUANTITY");
DataReport.Columns.Add("PRICE");
DataReport.Columns.Add("TAXCODE");
DataReport.Columns.Add("TAX%");
DataReport.Columns.Add("TRANSPORT");
DataReport.Columns.Add("Narration");
lblmsg.Text = "";
SalesDBManager SalesDB = new SalesDBManager();
DateTime fromdate = DateTime.Now;
string[] datestrig = dtp_FromDate.Text.Split(' ');
if (datestrig.Length > 1)
{
if (datestrig[0].Split('-').Length > 0)
{
string[] dates = datestrig[0].Split('-');
string[] times = datestrig[1].Split(':');
fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
lblFromDate.Text = fromdate.ToString("dd/MM/yyyy");
string branchid = Session["Po_BranchID"].ToString();
cmd = new SqlCommand("SELECT po_entrydetailes.remarks, po_entrydetailes.sno, po_entrydetailes.shortname, po_entrydetailes.podate, po_entrydetailes.poamount, po_entrydetailes.name, po_entrydetailes.delivarydate, po_entrydetailes.freigtamt, po_entrydetailes.status, po_entrydetailes.quotationdate, po_entrydetailes.supplierid, po_entrydetailes.others, po_entrydetailes.paymentid, po_entrydetailes.deliverytermsid, po_entrydetailes.insurance, po_entrydetailes.insuranceamount, po_entrydetailes.remarks, po_entrydetailes.otp, po_entrydetailes.ponumber, po_entrydetailes.branchid, po_entrydetailes.pricebasis, po_entrydetailes.addressid, po_entrydetailes.billaddressid, po_entrydetailes.transportcharge, po_entrydetailes.indentno, po_sub_detailes.code AS Expr1, po_sub_detailes.sno AS Expr2, po_sub_detailes.description, po_sub_detailes.qty, po_sub_detailes.cost, po_sub_detailes.taxtype, po_sub_detailes.ed, po_sub_detailes.dis, po_sub_detailes.disamt, po_sub_detailes.tax, po_sub_detailes.po_refno, po_sub_detailes.productsno, po_sub_detailes.edtax, po_sub_detailes.productamount, productmaster.productname, productmaster.itemcode, suppliersdetails.name AS sup_name, suppliersdetails.suppliercode, taxmaster.type, taxmaster_1.type AS edtype, po_entrydetailes.pfid, po_sub_detailes.igst, po_sub_detailes.cgst, po_sub_detailes.sgst FROM po_entrydetailes INNER JOIN po_sub_detailes ON po_entrydetailes.sno = po_sub_detailes.po_refno INNER JOIN productmaster ON po_sub_detailes.productsno = productmaster.productid LEFT OUTER JOIN taxmaster ON po_sub_detailes.taxtype = taxmaster.sno INNER JOIN suppliersdetails ON po_entrydetailes.supplierid = suppliersdetails.supplierid LEFT OUTER JOIN taxmaster AS taxmaster_1 ON po_sub_detailes.ed = taxmaster_1.sno WHERE (po_entrydetailes.podate BETWEEN @fromdate AND @todate) AND (po_entrydetailes.branchid = @branchid)");
cmd.Parameters.Add("@fromdate", GetLowDate(fromdate));
cmd.Parameters.Add("@todate", GetHighDate(fromdate));
cmd.Parameters.Add("@branchid", branchid);
DataTable dttotalinward = vdm.SelectQuery(cmd).Tables[0];
DateTime ServerDateCurrentdate = SalesDBManager.GetTime(vdm.conn);
if (dttotalinward.Rows.Count > 0)
{
string taxcode = "", taxtype = "", transport = "";
double ttotalamount = 0;
DateTime dt = DateTime.Now;
string prevdate = string.Empty;
DateTime dtapril = new DateTime();
DateTime dtmarch = new DateTime();
foreach (DataRow dr in dttotalinward.Rows)
{
DataRow newrow = DataReport.NewRow();
DateTime dt_po = Convert.ToDateTime(dr["podate"].ToString());
int currentyear = dt_po.Year;
int nextyear = dt_po.Year + 1;
int currntyearnum = 0;
int nextyearnum = 0;
if (dt_po.Month > 3)
{
string apr = "4/1/" + currentyear;
dtapril = DateTime.Parse(apr);
string march = "3/31/" + nextyear;
dtmarch = DateTime.Parse(march);
currntyearnum = currentyear;
nextyearnum = nextyear;
}
if (dt_po.Month <= 3)
{
string apr = "4/1/" + (currentyear - 1);
dtapril = DateTime.Parse(apr);
string march = "3/31/" + (nextyear - 1);
dtmarch = DateTime.Parse(march);
currntyearnum = currentyear - 1;
nextyearnum = nextyear - 1;
}
string dt_po_date = dt_po.ToString("dd/MM/yyyy");
newrow["CREATE DATE"] = dt_po_date;
newrow["CARD CODE"] = dr["suppliercode"].ToString();
newrow["CARD NAME"] = dr["sup_name"].ToString();
newrow["DISCOUNT %"] = dr["dis"].ToString();
string po = "";
po = "SVDS/PBK/PO/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "/" + dr["ponumber"].ToString();
newrow["REFERENCE NO"] = po.ToString();
newrow["ITEMCODE"] = dr["itemcode"].ToString();
newrow["DESCRIPTION"] = dr["productname"].ToString();
newrow["WARE HOUSE CODE"] = "SVDSPP02";
newrow["QUANTITY"] = dr["qty"].ToString();
newrow["PRICE"] = dr["cost"].ToString();
taxtype = dr["type"].ToString();
string igst = dr["igst"].ToString();
string cgst = dr["cgst"].ToString();
string sgst = dr["sgst"].ToString();
if (igst != "0")
{
taxcode = "IGST" + igst + "";
newrow["TAX%"] = igst;
}
if (cgst != "0")
{
double ctax = Convert.ToDouble(cgst);
cgst = (ctax + ctax).ToString();
taxcode = "CGST" + cgst + "";
newrow["TAX%"] = ctax;
}
if (igst == "0" && cgst == "0")
{
taxcode = "EXEMPT";
newrow["TAX%"] = "0";
}
newrow["TAXCODE"] = taxcode;
double total = 0; double vat_amt = 0; double amt_tax = 0;
string trans_chrgs = dr["transportcharge"].ToString();
string freight_chrgs = dr["freigtamt"].ToString();
if (trans_chrgs != "" || trans_chrgs != "0")
{
transport = trans_chrgs;
}
else if (freight_chrgs != "" || freight_chrgs != "0")
{
transport = freight_chrgs;
}
else
{
transport = "0";
}
amt_tax = total + vat_amt + Convert.ToDouble(transport);
newrow["TRANSPORT"] = transport;
newrow["Narration"] = dr["remarks"].ToString();
ttotalamount += amt_tax;
DataReport.Rows.Add(newrow);
}
Session["xportdata"] = DataReport;
Session["filename"] = "SAP PO Report";
grdReports.DataSource = DataReport;
grdReports.DataBind();
hidepanel.Visible = true;
}
else
{
lblmsg.Text = "No data were found";
hidepanel.Visible = false;
}
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
hidepanel.Visible = false;
}
}
void GetReport()
{
try
{
lblmsg.Text = "";
pnlHide.Visible = true;
Session["RouteName"] = ddlSalesOffice.SelectedItem.Text;
Session["IDate"] = DateTime.Now.AddDays(1).ToString("dd/MM/yyyy");
vdm = new SalesDBManager();
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);
}
}
DateTime ReportDate = SalesDBManager.GetTime(vdm.conn);
DateTime dtapril = new DateTime();
DateTime dtmarch = new DateTime();
int currentyear = ReportDate.Year;
int nextyear = ReportDate.Year + 1;
string pdate = "03/31/" + currentyear + "";
DateTime dtpdate = Convert.ToDateTime(pdate);
string cdate = GetLowDate(fromdate).ToString("MM/dd/yyyy");
DateTime dtcdate = Convert.ToDateTime(cdate);
if (dtcdate > dtpdate)
{
}
else
{
currentyear = currentyear - 1;
nextyear = currentyear + 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);
dtapril = DateTime.Parse(apr);
string march = "3/31/" + (nextyear);
dtmarch = DateTime.Parse(march);
}
DataTable Report = new DataTable();
Report.Columns.Add("DOE");
Report.Columns.Add("Ref Receipt");
Report.Columns.Add("Receipt");
Report.Columns.Add("Type");
Report.Columns.Add("Name");
Report.Columns.Add("Remarks");
Report.Columns.Add("Amount").DataType = typeof(Double);
lbl_selfromdate.Text = fromdate.ToString("dd/MM/yyyy");
lblRoutName.Text = ddlSalesOffice.SelectedItem.Text;
Session["xporttype"] = "TallyReceipts";
string ledger = "";
cmd = new SqlCommand("SELECT ladger_dr FROM Plant_Master WHERE (Plant_Code = @Plant_Code)");
cmd.Parameters.Add("@Plant_Code", ddlSalesOffice.SelectedValue);
DataTable dtledger = vdm.SelectQuery(cmd).Tables[0];
if (dtledger.Rows.Count > 0)
{
ledger = dtledger.Rows[0]["ladger_dr"].ToString();
}
Session["filename"] = ddlSalesOffice.SelectedItem.Text + " Tally Receipts" + fromdate.ToString("dd/MM/yyyy");
DataTable dtAgent = vdm.SelectQuery(cmd).Tables[0];
cmd = new SqlCommand("SELECT Branchid, Amount, Remarks, CONVERT(VARCHAR(10), DOE, 103) AS DOE, Receiptno, Name,PaymentType as Type FROM cashcollections WHERE (Branchid = @BranchID) AND (DOE BETWEEN @d1 AND @d2) AND (CollectionType = 'Cash') ORDER BY DOE");
cmd.Parameters.Add("@BranchID", ddlSalesOffice.SelectedValue);
cmd.Parameters.Add("@d1", GetLowDate(fromdate));
cmd.Parameters.Add("@d2", GetHighDate(fromdate));
cmd.Parameters.Add("@Type", "Others");
DataTable dtOthers = vdm.SelectQuery(cmd).Tables[0];
foreach (DataRow dr in dtOthers.Rows)
{
DataRow newrow = Report.NewRow();
newrow["DOE"] = dr["DOE"].ToString();
newrow["Receipt"] = dr["Receiptno"].ToString();
newrow["Type"] = dr["Type"].ToString();
newrow["Name"] = dr["Name"].ToString();
double AmountPaid = 0;
double.TryParse(dr["Amount"].ToString(), out AmountPaid);
newrow["Amount"] = AmountPaid;
newrow["Remarks"] = dr["Remarks"].ToString();
Report.Rows.Add(newrow);
}
string Receiptno = "";
Receiptno = dtapril.ToString("yy") + dtmarch.ToString("yy");
if (Report.Rows.Count > 0)
{
DataView view = new DataView(Report);
dtReport = new DataTable();
dtReport.Columns.Add("Voucher Date");
dtReport.Columns.Add("Voucher No");
dtReport.Columns.Add("Voucher Type");
dtReport.Columns.Add("Ledger (Dr)");
dtReport.Columns.Add("Ledger (Cr)");
dtReport.Columns.Add("Amount");
dtReport.Columns.Add("Narration");
//DataTable distincttable = view.ToTable(true, "BranchName", "BSno");
int i = 1;
foreach (DataRow branch in Report.Rows)
{
DataRow newrow = dtReport.NewRow();
newrow["Voucher Date"] = fromdate.ToString("dd-MMM-yyyy");
string newreceipt = "0";
int countdc = 0;
int.TryParse(branch["Receipt"].ToString(), out countdc);
if (countdc < 10)
{
newreceipt = "000" + countdc;
}
if (countdc >= 10 && countdc <= 99)
{
newreceipt = "00" + countdc;
}
if (countdc >= 99 && countdc <= 999)
{
newreceipt = "0" + countdc;
}
if (countdc > 999)
{
newreceipt = "" + countdc;
}
newrow["Voucher No"] = Receiptno + newreceipt;
newrow["Voucher Type"] = "Cash Receipt Import";
newrow["Ledger (Dr)"] = ledger;
if (branch["Name"].ToString() == "")
{
}
else
{
newrow["Ledger (Cr)"] = branch["Name"].ToString();
newrow["Amount"] = branch["Amount"].ToString();
double invval = 0;
//newrow["Narration"] = branch["Remarks"].ToString();
newrow["Narration"] = "" + branch["Remarks"].ToString() + " vide Receipt No " + branch["Receipt"].ToString() + ",Receipt Date " + fromdate.ToString("dd/MM/yyyy") + ",Emp Name " + Session["EmpName"].ToString();
dtReport.Rows.Add(newrow);
i++;
}
}
grdReports.DataSource = dtReport;
grdReports.DataBind();
Session["xportdata"] = dtReport;
}
else
{
pnlHide.Visible = false;
lblmsg.Text = "No Indent Found";
grdReports.DataSource = dtReport;
grdReports.DataBind();
}
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
grdReports.DataSource = dtReport;
grdReports.DataBind();
}
}
protected void login_click(object sender, EventArgs e)
{
try
{
username = Usernme_txt.Text;
password = Pass_pas.Text;
lbl_username.Text = username;
lbl_passwords.Text = password;
DateTime ServerDateCurrentdate = SalesDBManager.GetTime(vdm.conn);
//cmd = new SqlCommand("SELECT employee_erp.sno, employee_erp.leveltype,employee_erp.loginstatus, branch_info.venorid, employee_erp.empname, employee_erp.deptid, employee_erp.username, employee_erp.passward, employee_erp.emptype, employee_erp.branchid, branch_info.branchtype,branch_info.address,branch_info.branchname,branch_info.tinno FROM employee_erp INNER JOIN branch_info ON employee_erp.branchid = branch_info.sno WHERE (employee_erp.username = @UN) AND (employee_erp.passward = @Pwd)");
cmd = new SqlCommand("SELECT employee_erp.sno, employee_erp.leveltype,employee_erp.loginstatus, branch_info.venorid, branch_info.branchcode, employee_erp.empname, employee_erp.deptid, employee_erp.username, employee_erp.passward, employee_erp.emptype, employee_erp.branchid, branch_info.branchtype,branch_info.address,branch_info.branchname,branch_info.tinno, employee_erp.phoneno, employee_erp.otpstatus,employee_erp.empid FROM employee_erp INNER JOIN branch_info ON employee_erp.branchid = branch_info.sno WHERE (employee_erp.username = @UN) AND (employee_erp.passward = @Pwd)");
cmd.Parameters.Add("@Pwd", password);
cmd.Parameters.Add("@UN", username);
DataTable dt = vdm.SelectQuery(cmd).Tables[0];
if (dt.Rows.Count > 0)
{
string otpstatus = dt.Rows[0]["otpstatus"].ToString();
if (otpstatus == "1")
{
//session
string sno = dt.Rows[0]["sno"].ToString();
cmd = new SqlCommand("update employee_erp set loginstatus=@log where sno=@sno");
cmd.Parameters.Add("@log", "1");
cmd.Parameters.Add("@sno", sno);
vdm.Update(cmd);
Session["TitleName"] = dt.Rows[0]["branchname"].ToString(); // "SRI VYSHNAVI DAIRY SPECIALITIES (P) LTD";
Session["TinNo"] = dt.Rows[0]["tinno"].ToString();
Session["DeptID"] = dt.Rows[0]["deptid"].ToString();
Session["Employ_Sno"] = dt.Rows[0]["sno"].ToString();
Session["Branch_ID"] = dt.Rows[0]["branchid"].ToString();
Session["Emp_Type"] = dt.Rows[0]["emptype"].ToString();
Session["Address"] = dt.Rows[0]["address"].ToString(); //"R.S.No:381/2,Punabaka village Post,Pellakuru Mandal,Nellore District -524129., ANDRAPRADESH (State).Phone: 9440622077, Fax: 044 – 26177799. ";// dt.Rows[0]["brnch_address"].ToString();
Session["BranchType"] = dt.Rows[0]["branchtype"].ToString();
Session["leveltype"] = dt.Rows[0]["leveltype"].ToString();
Session["UserName"] = dt.Rows[0]["empname"].ToString();
Session["VendorID"] = dt.Rows[0]["venorid"].ToString();
Session["loginflag"] = dt.Rows[0]["loginstatus"].ToString();
Session["branchcode"] = dt.Rows[0]["branchcode"].ToString();
Session["HRMS_EmpId"] = dt.Rows[0]["empid"].ToString();
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 SqlCommand("INSERT INTO logininfo(UserId, UserName, Logintime, IpAddress,devicetype,status) values (@userid, @UserName, @logintime, @ipaddress,@devicetype,@status)");
cmd.Parameters.Add("@userid", dt.Rows[0]["sno"].ToString());
cmd.Parameters.Add("@UserName", Session["UserName"]);
cmd.Parameters.Add("@logintime", ServerDateCurrentdate);
cmd.Parameters.Add("@ipaddress", ipaddress);
cmd.Parameters.Add("@devicetype", devicetype);
cmd.Parameters.Add("@status", "1");
vdm.insert(cmd);
//End
//otp
string Id = string.Empty;
string no = dt.Rows[0]["phoneno"].ToString();
string empid = dt.Rows[0]["sno"].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 = SalesDBManager.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 = "otp.aspx?Id=" + Id.Trim();
string message1 = "OTP for " + empid + " Login : "******". Valid till " + otpexptime + " Do not share OTP for security reasons.";
string strUrl = "http://123.63.33.43/blank/sms/user/urlsms.php?username=vyshnavidairy&pass=vyshnavi@123&senderid=VYSAKG&dest_mobileno=" + no + "&message=" + message1 + "&response=Y";
//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;
Response.Redirect("otp.aspx?Id=" + Id.Trim());
}
else
{
fill_login_details();
}
}
else
{
lbl_validation.Text = "Check Your Username and password";
}
}
catch (Exception ex)
{
lbl_validation.Text = ex.Message;
}
}
void fill_login_details()
{
string sendmsgs = lbl_messsge.Text.ToString();
string username = lbl_username.Text.ToString();
string password = lbl_passwords.Text.ToString();
DateTime ServerDateCurrentdate = SalesDBManager.GetTime(vdm.conn);
cmd = new SqlCommand("SELECT employee_erp.sno, employee_erp.leveltype,employee_erp.loginstatus, branch_info.venorid, branch_info.branchcode, employee_erp.empname, employee_erp.deptid, employee_erp.username, employee_erp.passward, employee_erp.emptype, employee_erp.branchid, branch_info.branchtype,branch_info.address,branch_info.branchname,branch_info.tinno,employee_erp.empid FROM employee_erp INNER JOIN branch_info ON employee_erp.branchid = branch_info.sno WHERE (employee_erp.username = @UN) AND (employee_erp.passward = @Pwd)");
cmd.Parameters.Add("@Pwd", password);
cmd.Parameters.Add("@UN", username);
DataTable dt = vdm.SelectQuery(cmd).Tables[0];
if (dt.Rows.Count > 0)
{
string sno = dt.Rows[0]["sno"].ToString();
cmd = new SqlCommand("update employee_erp set loginstatus=@log where sno=@sno");
cmd.Parameters.Add("@log", "1");
cmd.Parameters.Add("@sno", sno);
vdm.Update(cmd);
Session["TitleName"] = dt.Rows[0]["branchname"].ToString(); // "SRI VYSHNAVI DAIRY SPECIALITIES (P) LTD";
Session["TinNo"] = dt.Rows[0]["tinno"].ToString();
Session["DeptID"] = dt.Rows[0]["deptid"].ToString();
Session["Employ_Sno"] = dt.Rows[0]["sno"].ToString();
Session["Branch_ID"] = dt.Rows[0]["branchid"].ToString();
Session["Emp_Type"] = dt.Rows[0]["emptype"].ToString();
Session["Address"] = dt.Rows[0]["address"].ToString(); //"R.S.No:381/2,Punabaka village Post,Pellakuru Mandal,Nellore District -524129., ANDRAPRADESH (State).Phone: 9440622077, Fax: 044 – 26177799. ";// dt.Rows[0]["brnch_address"].ToString();
Session["BranchType"] = dt.Rows[0]["branchtype"].ToString();
Session["leveltype"] = dt.Rows[0]["leveltype"].ToString();
Session["UserName"] = dt.Rows[0]["empname"].ToString();
Session["VendorID"] = dt.Rows[0]["venorid"].ToString();
Session["loginflag"] = dt.Rows[0]["loginstatus"].ToString();
Session["branchcode"] = dt.Rows[0]["branchcode"].ToString();
Session["HRMS_EmpId"] = dt.Rows[0]["empid"].ToString();
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";
}
//End
cmd = new SqlCommand("INSERT INTO logininfo(UserId, UserName, Logintime, IpAddress,devicetype,status) values (@userid, @UserName, @logintime, @ipaddress,@devicetype,@status)");
cmd.Parameters.Add("@userid", dt.Rows[0]["sno"].ToString());
cmd.Parameters.Add("@UserName", Session["UserName"]);
cmd.Parameters.Add("@logintime", ServerDateCurrentdate);
cmd.Parameters.Add("@ipaddress", ipaddress);
cmd.Parameters.Add("@devicetype", devicetype);
cmd.Parameters.Add("@status", "1");
vdm.insert(cmd);
cmd = new SqlCommand("SELECT TOP (1) sno, UserId, status FROM logininfo WHERE (UserId = @UserId) ORDER BY sno DESC");
cmd.Parameters.Add("@UserId", dt.Rows[0]["sno"].ToString());
DataTable dttatus = vdm.SelectQuery(cmd).Tables[0];
Session["lgininfosno"] = dttatus.Rows[0]["sno"].ToString();
Session["lgininfostatus"] = dttatus.Rows[0]["status"].ToString();
// Cookies
Response.Cookies["userInfo"]["Employ_Sno"] = Session["Employ_Sno"].ToString();
Response.Cookies["userInfo"]["UserName"] = Session["UserName"].ToString();
Response.Cookies["userInfo"]["Branch_ID"] = Session["Branch_ID"].ToString();
Response.Cookies["userInfo"]["TitleName"] = Session["TitleName"].ToString();
Response.Cookies["userInfo"]["TinNo"] = Session["TinNo"].ToString();
Response.Cookies["userInfo"]["DeptID"] = Session["DeptID"].ToString();
Response.Cookies["userInfo"]["Emp_Type"] = Session["Emp_Type"].ToString();
Response.Cookies["userInfo"]["Address"] = Session["Address"].ToString();
Response.Cookies["userInfo"]["BranchType"] = Session["BranchType"].ToString();
Response.Cookies["userInfo"]["leveltype"] = Session["leveltype"].ToString();
Response.Cookies["userInfo"]["VendorID"] = Session["VendorID"].ToString();
Response.Cookies["userInfo"]["loginflag"] = Session["loginflag"].ToString();
Response.Cookies["userInfo"]["lgininfosno"] = Session["lgininfosno"].ToString();
Response.Cookies["userInfo"]["lgininfostatus"] = Session["lgininfostatus"].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["Employ_Sno"] = Session["Employ_Sno"].ToString();
aCookie.Values["Branch_ID"] = Session["Branch_ID"].ToString();
aCookie.Values["TitleName"] = Session["TitleName"].ToString();
aCookie.Values["TinNo"] = Session["TinNo"].ToString();
aCookie.Values["DeptID"] = Session["DeptID"].ToString();
aCookie.Values["Emp_Type"] = Session["Emp_Type"].ToString();
aCookie.Values["Address"] = Session["Address"].ToString();
aCookie.Values["BranchType"] = Session["BranchType"].ToString();
aCookie.Values["leveltype"] = Session["leveltype"].ToString();
aCookie.Values["VendorID"] = Session["VendorID"].ToString();
aCookie.Values["loginflag"] = Session["loginflag"].ToString();
aCookie.Values["lgininfosno"] = Session["lgininfosno"].ToString();
aCookie.Values["lgininfostatus"] = Session["lgininfostatus"].ToString();
aCookie.Values["lastVisit"] = DateTime.Now.ToString();
aCookie.Expires = DateTime.Now.AddDays(1);
Response.Cookies.Add(aCookie);
string branchtype = dt.Rows[0]["branchtype"].ToString();
string leveltype = dt.Rows[0]["leveltype"].ToString();
if (branchtype == "CC")
{
Response.Redirect("Despatch_Entry.aspx", false);
}
else
{
if (leveltype == "lab")
{
Response.Redirect("DashBoard.aspx", false);
}
if (leveltype == "Security")
{
Response.Redirect("Inward_dc_varify.aspx", false);
}
if (leveltype == "Processing")
{
Response.Redirect("InwardSilo.aspx", false);
}
if (leveltype == "Operations")
{
Response.Redirect("chartdashboard.aspx", false);
}
if (leveltype == "packing")
{
Response.Redirect("Packingsection.aspx", false);
}
if (leveltype == "weighing")
{
Response.Redirect("WeighingDayReport.aspx", false);
}
if (leveltype == "Admin")
{
Response.Redirect("chartdashboard.aspx", false);
}
if (leveltype == "MAdmin")
{
Response.Redirect("chartdashboard.aspx", false);
}
if (leveltype == "curdblock")
{
Response.Redirect("chemicalconsumption.aspx", false);
}
if (leveltype == "Data")
{
Response.Redirect("TankersInwardReport.aspx", false);
}
if (leveltype == "Reports")
{
Response.Redirect("TankersInwardReport.aspx", false);
}
if (leveltype == "dailyreport")
{
Response.Redirect("plantdailyreport.aspx", false);
}
if (leveltype == "ghee")
{
Response.Redirect("GheeSection.aspx", false);
}
if (leveltype == "Butter")
{
Response.Redirect("butterproductiondetails.aspx", false);
}
if (leveltype == "suboperations")
{
Response.Redirect("SiloMonitor.aspx", false);
}
if (leveltype == "curdandlab")
{
Response.Redirect("chemicalconsumption.aspx", false);
}
}
}
}
protected void btn_save_click(object sender, EventArgs e)
{
try
{
vdm = new SalesDBManager();
DateTime CreateDate = SalesDBManager.GetTime(vdm.conn);
SAPdbmanger SAPvdm = new SAPdbmanger();
DateTime fromdate = DateTime.Now;
DataTable dt = (DataTable)Session["xportdata"];
string[] dateFromstrig = dtp_FromDate.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);
}
}
foreach (DataRow dr in dt.Rows)
{
string itemcode = dr["ITEMCODE"].ToString();
string cardcode = dr["CARD CODE"].ToString();
if (itemcode == "")
{
}
else if (cardcode == "")
{
}
else
{
cmd = new SqlCommand("SELECT * FROM EMROPOR WHERE (TaxDate BETWEEN @d1 AND @d2) AND (ReferenceNo = @ReferenceNo) AND (ItemCode = @ItemCode) AND (WhsCode = @WhsCode)");
cmd.Parameters.Add("@d1", GetLowDate(fromdate));
cmd.Parameters.Add("@d2", GetHighDate(fromdate));
cmd.Parameters.Add("@ReferenceNo", dr["REFERENCE NO"].ToString());
cmd.Parameters.Add("@WhsCode", dr["WARE HOUSE CODE"].ToString());
cmd.Parameters.Add("@ItemCode", dr["ITEMCODE"].ToString());
DataTable dtPCH = SAPvdm.SelectQuery(cmd).Tables[0];
if (dtPCH.Rows.Count > 0)
{
}
else
{
cmd = new SqlCommand("insert into EMROPOR (CreateDate, CardCode, CardName, TaxDate, DocDate, DocDueDate, DiscPercent, ReferenceNo, ItemCode, Dscription, WhsCode, Quantity, Price, OcrCode, TaxCode, PURCHASETYPE, B1Upload, Processed,remarks,VAT_Percent) values (@CreateDate, @CardCode, @CardName, @TaxDate, @DocDate, @DocDueDate, @DiscPercent, @ReferenceNo, @ItemCode, @Dscription, @WhsCode, @Quantity, @Price, @OcrCode, @TaxCode, @purchasetype, @B1Upload, @Processed,@remarks,@TAXPER)");
cmd.Parameters.Add("@CreateDate", CreateDate);
cmd.Parameters.Add("@CardCode", dr["CARD CODE"].ToString());
cmd.Parameters.Add("@CardName", dr["CARD NAME"].ToString());
cmd.Parameters.Add("@TaxDate", fromdate);
cmd.Parameters.Add("@DocDate", fromdate);
cmd.Parameters.Add("@DocDueDate", fromdate);
cmd.Parameters.Add("@DiscPercent", dr["DISCOUNT %"].ToString());
cmd.Parameters.Add("@ReferenceNo", dr["REFERENCE NO"].ToString());
cmd.Parameters.Add("@ItemCode", dr["ITEMCODE"].ToString());
cmd.Parameters.Add("@Dscription", dr["DESCRIPTION"].ToString());
cmd.Parameters.Add("@WhsCode", dr["WARE HOUSE CODE"].ToString());
double qty = 0;
double.TryParse(dr["QUANTITY"].ToString(), out qty);
cmd.Parameters.Add("@Quantity", qty);
double price = 0;
double.TryParse(dr["PRICE"].ToString(), out price);
cmd.Parameters.Add("@Price", price);
cmd.Parameters.Add("@OcrCode", dr["WARE HOUSE CODE"].ToString());
cmd.Parameters.Add("@TaxCode", dr["TAXCODE"].ToString());
cmd.Parameters.Add("@TAXPER", dr["TAX%"].ToString());
string B1Upload = "N";
string Processed = "N";
string purchasetype = "93";
cmd.Parameters.Add("@purchasetype", purchasetype);
cmd.Parameters.Add("@B1Upload", B1Upload);
cmd.Parameters.Add("@Processed", Processed);
cmd.Parameters.Add("@remarks", dr["Narration"].ToString());
if (qty == 0.0)
{
}
else
{
SAPvdm.insert(cmd);
}
}
}
}
hidepanel.Visible = false;
lblmsg.Text = "Saved successfully";
DataTable dtempty = new DataTable();
grdReports.DataSource = dtempty;
grdReports.DataBind();
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
hidepanel.Visible = false;
}
}
void getdet()
{
try
{
vdm = new SalesDBManager();
string BrachSOID = "";
BrachSOID = Session["branch"].ToString();
//cmd = new SqlCommand("SELECT cashpayables.onNameof, cashpayables.DOE,cashpayables.VocherID, cashpayables.Sno, cashpayables.Amount, cashpayables.ApprovedAmount, cashpayables.Remarks, cashpayables.ApprovalRemarks,cashpayables.VoucherType, empmanage.EmpName FROM cashpayables LEFT OUTER JOIN empmanage ON cashpayables.Approvedby = empmanage.Sno WHERE (cashpayables.Sno = @VocherID) AND (cashpayables.BranchID = @BranchID)");
cmd = new SqlCommand("SELECT Sno, BranchID, CashTo, DOE, VocherID, Remarks, Amount, Approvedby, ApprovedAmount, ApprovalRemarks, Status, Created_by, Modify_by, Empid, onNameof, CloBal, VoucherType, CashierRemarks, ForceApproval, ReceivedAmount, DueAmount, AccountType, AgentID, DOR, Denominations FROM cashpayables WHERE (Sno = @VocherID) AND (BranchID = @BranchID)");
cmd.Parameters.Add("@VocherID", txtVoucherNo.Text);
cmd.Parameters.Add("@BranchID", BrachSOID);
DataTable dtCash = vdm.SelectQuery(cmd).Tables[0];
//cmd = new SqlCommand("SELECT sno, BranchName, BranchCode FROM branchdata WHERE (sno = @BranchID)");
//cmd.Parameters.Add("@BranchID", BrachSOID);
//DataTable dtCode = vdm.SelectQuery(cmd).Tables[0];
string voucherid = "";
DateTime ServerDateCurrentdate = SalesDBManager.GetTime(vdm.conn);
DateTime dtapril = new DateTime();
DateTime dtmarch = new DateTime();
int currentyear = ServerDateCurrentdate.Year;
int nextyear = ServerDateCurrentdate.Year + 1;
int currntyearnum = 0;
int nextyearnum = 0;
if (ServerDateCurrentdate.Month > 3)
{
string apr = "4/1/" + currentyear;
dtapril = DateTime.Parse(apr);
string march = "3/31/" + nextyear;
dtmarch = DateTime.Parse(march);
currntyearnum = currentyear;
nextyearnum = nextyear;
}
if (ServerDateCurrentdate.Month <= 3)
{
string apr = "4/1/" + (currentyear - 1);
dtapril = DateTime.Parse(apr);
string march = "3/31/" + (nextyear - 1);
dtmarch = DateTime.Parse(march);
currntyearnum = currentyear - 1;
nextyearnum = nextyear - 1;
}
if (dtCash.Rows.Count > 0)
{
//lblVoucherno.Text = txtVoucherNo.Text;
voucherid = Session["plant_code"].ToString() + "/VOC/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "/" + dtCash.Rows[0]["VocherID"].ToString();
lblVoucherno.Text = voucherid;
string DOE = dtCash.Rows[0]["DOE"].ToString();
DateTime dtDOE = Convert.ToDateTime(DOE);
lblApprove.Text = "G.M/P.M";
string ChangedTime = dtDOE.ToString("dd/MMM/yyyy");
lblDate.Text = ChangedTime;
lblPayCash.Text = dtCash.Rows[0]["onNameof"].ToString();
string AppRemarks = dtCash.Rows[0]["ApprovalRemarks"].ToString();
if (AppRemarks == "")
{
lblTowards.Text = dtCash.Rows[0]["Remarks"].ToString();
}
else
{
lblTowards.Text = dtCash.Rows[0]["ApprovalRemarks"].ToString();
}
lblVoucherType.Text = dtCash.Rows[0]["VoucherType"].ToString().ToUpper() + " VOUCHER";
string Amont = dtCash.Rows[0]["Amount"].ToString();
string[] Ones = { "", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", "Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Ninteen" };
string[] Tens = { "Ten", "Twenty", "Thirty", "Fourty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninty" };
int Num = int.Parse(Amont);
lblReceived.Text = NumToWordBD(Num) + " Rupees Only";
}
}
catch (Exception ex)
{
lblReceived.Text = ex.Message;
}
}
protected void login_click(object sender, EventArgs e)
{
try
{
vdm = new SalesDBManager();
string userid = Usernme_txt.Text, password = Pass_pas.Text;
cmd = new SqlCommand("SELECT branchmaster.gstin, branchmaster.branchledgername, branchmapping.mainbranch, employe_details.sno, employe_details.loginflag, employe_details.employename, employe_details.userid, employe_details.password, employe_details.emailid, employe_details.phone, employe_details.branchtype, employe_details.leveltype, employe_details.departmentid, employe_details.branchid, branchmaster.branchid AS Expr1, branchmaster.branchname, branchmaster.address, branchmaster.branchcode, branchmaster.phone AS Expr2, branchmaster.tino, branchmaster.stno, branchmaster.cstno, branchmaster.emailid AS Expr3, branchmaster.statename FROM employe_details INNER JOIN branchmaster ON employe_details.branchid = branchmaster.branchid INNER JOIN departmentmaster ON departmentmaster.sno=employe_details.departmentid INNER JOIN branchmapping ON branchmaster.branchid=branchmapping.subbranch WHERE (employe_details.userid = @userid) AND (employe_details.password = @pwd)");
cmd.Parameters.Add("@pwd", password);
cmd.Parameters.Add("@userid", userid);
DataTable dt = vdm.SelectQuery(cmd).Tables[0];
if (dt.Rows.Count > 0)
{
string loginflag = dt.Rows[0]["loginflag"].ToString();
//if (loginflag == "False")
//{
string sno = dt.Rows[0]["sno"].ToString();
cmd = new SqlCommand("update employe_details set loginflag=@log where sno=@sno");
cmd.Parameters.Add("@log", "1");
cmd.Parameters.Add("@sno", sno);
vdm.Update(cmd);
Session["TinNo"] = "37921042267";
Session["mainbranch"] = dt.Rows[0]["mainbranch"].ToString();
Session["Employ_Sno"] = dt.Rows[0]["sno"].ToString();
Session["Po_BranchID"] = dt.Rows[0]["branchid"].ToString();
Session["stateid"] = dt.Rows[0]["statename"].ToString();
Session["TitleName"] = "SRI VYSHNAVI DAIRY SPECIALITIES (P) LTD";
string julydt = "07/01/2017 12:00:00 AM";
DateTime gst_dt = Convert.ToDateTime(julydt);
DateTime today = DateTime.Today;
//if (today > gst_dt)
//{
Session["Address"] = dt.Rows[0]["address"].ToString();
Session["gstin"] = dt.Rows[0]["gstin"].ToString();
//}
//else
//{
// Session["Address"] = "Survey No. 381-2, Punabaka Village, Pellakuru mandal SPSR Nellore (Dt) Pin - 524129, Andhra Pradesh,11. Email : [email protected] Phone: 7729995606; GSTIN NO: 37921042267.";
// // Session["Address"] = "Survey No. 381-2, Punabaka Village, Pellakuru mandal SPSR Nellore (Dt) Pin - 524129.Couriering address : No.45, Madhu apartments,Panagal-517640,Srikalahasthi,Chittoor(dt),AndhraPradesh. Email : [email protected];[email protected] Phone: 7729995606,7729995603,9382525913; GSTIN: 37921042267."; //dt.Rows[0]["address"].ToString();
//}
Session["BranchCode"] = dt.Rows[0]["branchcode"].ToString();
Session["TinNo"] = "37921042267";
Session["stno"] = dt.Rows[0]["stno"].ToString();
Session["cstno"] = dt.Rows[0]["cstno"].ToString();
Session["phone"] = dt.Rows[0]["phone"].ToString();
Session["emailid"] = dt.Rows[0]["emailid"].ToString();
Session["UserName"] = dt.Rows[0]["employename"].ToString();
Session["password"] = dt.Rows[0]["password"].ToString();
Session["BranchType"] = dt.Rows[0]["branchtype"].ToString();
Session["Department"] = dt.Rows[0]["departmentid"].ToString();
Session["leveltype"] = dt.Rows[0]["leveltype"].ToString();
Session["branchledgername"] = dt.Rows[0]["branchledgername"].ToString();
string branchtype = dt.Rows[0]["BranchType"].ToString();
string leveltype = dt.Rows[0]["leveltype"].ToString();
Response.Cookies["UserName"].Value = HttpUtility.UrlEncode("true");
Response.Cookies["UserName"].Path = "/";
Response.Cookies["UserName"].Expires = DateTime.Now.AddDays(1);
Response.Cookies["Employ_Sno"].Value = HttpUtility.UrlEncode("true");
Response.Cookies["Employ_Sno"].Path = "/";
Response.Cookies["Employ_Sno"].Expires = DateTime.Now.AddDays(1);
//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 = SalesDBManager.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";
}
//string alphabets = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
//string small_alphabets = "abcdefghijklmnopqrstuvwxyz";
//string numbers = "1234567890";
//string characters = numbers;
//characters += alphabets + small_alphabets + numbers;
//int length = 8;
//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;
//}
cmd = new SqlCommand("INSERT INTO logininfo(userid, username, logintime, ipaddress, devicetype) values (@userid, @UserName, @logintime, @ipaddress, @device)");
cmd.Parameters.Add("@userid", dt.Rows[0]["sno"].ToString());
cmd.Parameters.Add("@UserName", Session["UserName"]);
cmd.Parameters.Add("@logintime", ServerDateCurrentdate);
cmd.Parameters.Add("@ipaddress", ipaddress);
cmd.Parameters.Add("@device", devicetype);
//cmd.Parameters.Add("@otp", otp);
vdm.insert(cmd);
// Session["leveltype"] = "Admin";
if (leveltype == "Admin ")
{
Response.Redirect("chartdashboard.aspx", false);
}
if (leveltype == "SuperAdmin")
{
Response.Redirect("chartdashboard.aspx", false);
}
if (leveltype == "User ")
{
Response.Redirect("InwardReport.aspx", false);
}
if (leveltype == "Operations")
{
Response.Redirect("PoDashBoard.aspx", false);
}
if (leveltype == "Issue ")
{
Response.Redirect("IssueDashBoard.aspx", false);
}
if (leveltype == "Receipt ")
{
Response.Redirect("InwardDashboard.aspx", false);
}
if (leveltype == "Section ")
{
Response.Redirect("IndentEntry.aspx", false);
}
//}
//else
//{
// lblMsg.Text = "Already Some one Login With This User Name";
//}
}
else
{
lblMsg.Text = "Invalid userId and Password";
}
}
catch (Exception ex)
{
lblMsg.Text = ex.Message;
}
}
protected void BtnSave_Click(object sender, EventArgs e)
{
try
{
vdm = new SalesDBManager();
string totIOU = lblIou.Text;
//string DenCash = Session["Cash"].ToString();
double IOU = 0;
double.TryParse(totIOU, out IOU);
//double Cash = 0;
//double.TryParse(DenCash, out Cash);
// double TotalAmount = 0;
double Totalclosing = 0;
//TotalAmount = Cash + IOU;
//double diffamount = 0;
//double.TryParse(lblDiffernce.Text, out diffamount);
double.TryParse(lblhidden.Text, out Totalclosing);
DataTable dt = (DataTable)Session["IOUReport"];
lblmsg.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;
DateTime ServerDateCurrentdate = SalesDBManager.GetTime(vdm.conn);
string DenominationString = Session["DenominationString"].ToString();
cmd = new SqlCommand("SELECT BranchID FROM Collections WHERE (BranchId = @BranchId) AND (PaidDate BETWEEN @d1 AND @d2)");
cmd.Parameters.Add("@BranchID", ddlSalesOffice.SelectedValue);
cmd.Parameters.Add("@d1", GetLowDate(fromdate));
cmd.Parameters.Add("@d2", GetHighDate(fromdate));
DataTable dtCol = vdm.SelectQuery(cmd).Tables[0];
if (dtCol.Rows.Count > 0)
{
lblmsg.Text = "Cash Book already closed";
}
else
{
/////////////////....................Ravindra.....................////////////////
//cmd = new SqlCommand("SELECT cashpayables.Sno, cashpayables.BranchID, cashpayables.CashTo,subpayable.HeadSno, cashpayables.DOE, cashpayables.VocherID, cashpayables.Remarks, SUM(cashpayables.ApprovedAmount) AS Amount, accountheads.HeadName FROM cashpayables INNER JOIN subpayable ON cashpayables.Sno = subpayable.RefNo INNER JOIN accountheads ON subpayable.HeadSno = accountheads.Sno WHERE (cashpayables.BranchID = @BranchID) AND (cashpayables.Status='P') AND (cashpayables.VoucherType = 'Credit') GROUP BY accountheads.Sno ORDER BY accountheads.HeadName");
cmd = new SqlCommand("SELECT cashpayables.Sno, cashpayables.BranchID, cashpayables.CashTo, subpayable.HeadSno, cashpayables.DOE, cashpayables.VocherID, cashpayables.Remarks, SUM(cashpayables.ApprovedAmount) AS Amount, accountheads.HeadName FROM cashpayables INNER JOIN subpayable ON cashpayables.Sno = subpayable.RefNo INNER JOIN accountheads ON subpayable.HeadSno = accountheads.Sno WHERE (cashpayables.BranchID = @BranchID) AND (cashpayables.Status = 'P') AND (cashpayables.VoucherType = 'Credit') GROUP BY cashpayables.Sno, cashpayables.BranchID, cashpayables.CashTo, subpayable.HeadSno, cashpayables.DOE, cashpayables.VocherID, cashpayables.Remarks, cashpayables.Amount, accountheads.HeadName ORDER BY accountheads.HeadName");
cmd.Parameters.Add("@BranchID", ddlSalesOffice.SelectedValue);
DataTable dtCredit = vdm.SelectQuery(cmd).Tables[0];
/////////////////....................Ravindra.....................////////////////
//cmd = new SqlCommand("SELECT cashpayables.onNameof,cashpayables.CashTo, SUM(cashpayables.ApprovedAmount) AS Amount, subpayable.HeadSno, accountheads.HeadName FROM cashpayables INNER JOIN subpayable ON cashpayables.Sno = subpayable.RefNo INNER JOIN accountheads ON subpayable.HeadSno = accountheads.Sno WHERE (cashpayables.BranchID = @BranchID) AND (cashpayables.VoucherType = 'Due') AND (cashpayables.Status<> 'C') AND (cashpayables.Status='P') GROUP BY accountheads.HeadName ORDER BY accountheads.HeadName");
cmd = new SqlCommand("SELECT cashpayables.onNameof, cashpayables.CashTo, SUM(cashpayables.ApprovedAmount) AS Amount, subpayable.HeadSno, accountheads.HeadName FROM cashpayables INNER JOIN subpayable ON cashpayables.Sno = subpayable.RefNo INNER JOIN accountheads ON subpayable.HeadSno = accountheads.Sno WHERE (cashpayables.BranchID = @BranchID) AND (cashpayables.VoucherType = 'Due') AND (cashpayables.Status <> 'C') AND (cashpayables.Status = 'P') GROUP BY cashpayables.onNameof, cashpayables.CashTo, cashpayables.Amount, subpayable.HeadSno, accountheads.HeadName ORDER BY accountheads.HeadName");
cmd.Parameters.Add("@BranchID", ddlSalesOffice.SelectedValue);
DataTable dtDebit = vdm.SelectQuery(cmd).Tables[0];
foreach (DataRow dr in dtDebit.Rows)
{
string IouName = dr["HeadName"].ToString();
double iouamtdebit = 0;
double iouamtcredit = 0;
double TotIouBal = 0;
double.TryParse(dr["Amount"].ToString(), out iouamtdebit);
foreach (DataRow drcredit in dtCredit.Select("HeadSno='" + dr["HeadSno"].ToString() + "'"))
{
double.TryParse(drcredit["Amount"].ToString(), out iouamtcredit);
}
TotIouBal = iouamtdebit - iouamtcredit;
if (TotIouBal == 0)
{
}
else
{
cmd = new SqlCommand("Insert into ioutable (BranchID,IOU,Amount,DOE) values(@BranchID,@IOU,@Amount,@DOE)");
cmd.Parameters.Add("@BranchID", ddlSalesOffice.SelectedValue);
cmd.Parameters.Add("@Amount", TotIouBal);
cmd.Parameters.Add("@IOU", IouName);
cmd.Parameters.Add("@DOE", fromdate);
vdm.insert(cmd);
}
}
cmd = new SqlCommand("Insert into Collections (BranchID,AmountPaid,UserData_sno,PaidDate,PaymentType,Denominations,EmpID,VarifyDate) values(@BranchID,@AmountPaid,@UserData_sno,@PaidDate,@PaymentType,@Denominations,@EmpID,@VarifyDate)");
cmd.Parameters.Add("@BranchID", ddlSalesOffice.SelectedValue);
//cmd.Parameters.Add("@AmountPaid", TotalAmount);
cmd.Parameters.Add("@AmountPaid", Math.Round(Totalclosing, 2));
cmd.Parameters.Add("@Denominations", DenominationString);
cmd.Parameters.Add("@UserData_sno", "1");
cmd.Parameters.Add("@PaidDate", fromdate);
cmd.Parameters.Add("@VarifyDate", ServerDateCurrentdate);
cmd.Parameters.Add("@PaymentType", "Cash");
cmd.Parameters.Add("@EmpID", Session["UserSno"].ToString());
vdm.insert(cmd);
lblmsg.Text = "Cash Book saved successfully";
GetReport();
}
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
}
}
private void report()
{
try
{
lblmsg.Text = "";
string milkopeningbal = string.Empty;
string milkclosingbal = string.Empty;
SalesDBManager SalesDB = new SalesDBManager();
DateTime fromdate = DateTime.Now;
DateTime todate = DateTime.Now;
string idcno = string.Empty;
string inworddate = string.Empty;
double totalpaidamount = 0;
string[] datestrig = dtp_FromDate.Text.Split(' ');
if (datestrig.Length > 1)
{
if (datestrig[0].Split('-').Length > 0)
{
string[] dates = datestrig[0].Split('-');
string[] times = datestrig[1].Split(':');
fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
datestrig = dtp_Todate.Text.Split(' ');
if (datestrig.Length > 1)
{
if (datestrig[0].Split('-').Length > 0)
{
string[] dates = datestrig[0].Split('-');
string[] times = datestrig[1].Split(':');
todate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
lblFromDate.Text = fromdate.ToString("dd/MMM/yyyy");
lbltodate.Text = todate.ToString("dd/MMM/yyyy");
int vendorid = Convert.ToInt32(ddlbranches.SelectedItem.Value);
Report.Columns.Add("Sno");
Report.Columns.Add("Date");
Report.Columns.Add("Dc No");
Report.Columns.Add("Dc Amount");
Report.Columns.Add("Opp Bal");
Report.Columns.Add("Total Amount");
if (ddlcollectiontype.SelectedValue == "Collection")
{
Report.Columns.Add("Received Amount");
}
else
{
Report.Columns.Add("Paid Amount");
}
Report.Columns.Add("Balance");
hidepanel.Visible = true;
DateTime ServerDateCurrentdate = SalesDBManager.GetTime(vdm.conn);
cmd = new SqlCommand("SELECT vendors.vendorname, collections.opp_bal, collections.amount, collections.transtype,CONVERT(VARCHAR(20),collections.doe, 101) AS doe, collections.remarks, collections.clo_bal FROM collections INNER JOIN vendors ON collections.vendorid = vendors.sno WHERE (collections.vendorid=@vid) AND (collections.doe between @d1 and @d2) AND (collections.transtype='Payment') ");
cmd.Parameters.Add("@vid", vendorid);
cmd.Parameters.Add("@d1", GetLowDate(fromdate));
cmd.Parameters.Add("@d2", ServerDateCurrentdate);
DataTable dtcollection = vdm.SelectQuery(cmd).Tables[0];
double debitprice = 0;
foreach (DataRow dr in dtcollection.Rows)
{
double amountDebited = 0;
double.TryParse(dr["amount"].ToString(), out amountDebited);
debitprice += amountDebited;
}
cmd = new SqlCommand("SELECT vendor_accounts.sno, vendor_accounts.amount, vendor_subtable.milktype FROM vendor_accounts INNER JOIN vendors ON vendor_accounts.vendorid = vendors.sno INNER JOIN vendor_subtable ON vendors.sno = vendor_subtable.vendor_refno WHERE (vendor_accounts.vendorid = @VendorID)");
cmd.Parameters.Add("@VendorID", vendorid);
DataTable dtvendor_presentopp = vdm.SelectQuery(cmd).Tables[0];
double vendorpresentopp = 0;
double.TryParse(dtvendor_presentopp.Rows[0]["Amount"].ToString(), out vendorpresentopp);
string milktype = dtvendor_presentopp.Rows[0]["milktype"].ToString();
if (ddlcollectiontype.SelectedValue == "Collection")
{
cmd = new SqlCommand(" SELECT milktransactions.dcno, milktransactions.inwardno AS InwardNo, milktransactions.vehicleno,CONVERT(VARCHAR(20),milktransactions.doe, 101) AS doe , milktransactions.transtype, milktransactions.qty_ltr, milktransactions.qty_kgs, milktransactions.percentageon, milktransactions.snf, milktransactions.fat, milktransactions.clr, milktransaction_logs.milktype, milktransaction_logs.rate_on, milktransaction_logs.cost, milktransaction_logs.calc_on, milktransaction_logs.overheadon, milktransaction_logs.overheadcost, milktransaction_logs.m_std_snf, milktransaction_logs.p_std_snf, milktransaction_logs.snfplus_on, milktransaction_logs.m_snfpluscost, milktransaction_logs.p_snfpluscost, milktransaction_logs.transport_on, milktransaction_logs.transportcost, milktransaction_logs.transport, vendors.vendorname FROM milktransactions INNER JOIN milktransaction_logs ON milktransactions.sno = milktransaction_logs.milktransaction_sno INNER JOIN vendors ON milktransactions.sectionid = vendors.sno WHERE (milktransactions.doe BETWEEN @d1 AND @d2) AND (milktransactions.transtype = @transtype) AND (milktransactions.sectionid=@sectionid)");
cmd.Parameters.Add("@d1", GetLowDate(fromdate));
cmd.Parameters.Add("@d2", ServerDateCurrentdate);
Session["filename"] = "Collection";
Session["title"] = "Collection Details";
cmd.Parameters.Add("@transtype", "Out");
}
if (ddlcollectiontype.SelectedValue == "Payment")
{
cmd = new SqlCommand(" SELECT milktransactions.dcno, milktransactions.inwardno AS InwardNo,milktransaction_logs.m_std_fat,milktransaction_logs.fatplus_on,milktransaction_logs.p_std_fat,milktransaction_logs.m_fatpluscost,milktransaction_logs.p_fatpluscost, milktransaction_logs.p_std_snf, milktransactions.vehicleno,CONVERT(VARCHAR(20),milktransactions.doe, 101) AS doe , milktransactions.transtype, milktransactions.qty_ltr, milktransactions.qty_kgs, milktransactions.percentageon, milktransactions.snf, milktransactions.fat, milktransactions.clr, milktransaction_logs.milktype, milktransaction_logs.rate_on, milktransaction_logs.cost, milktransaction_logs.calc_on, milktransaction_logs.overheadon, milktransaction_logs.overheadcost, milktransaction_logs.m_std_snf, milktransaction_logs.p_std_snf, milktransaction_logs.snfplus_on, milktransaction_logs.m_snfpluscost, milktransaction_logs.p_snfpluscost, milktransaction_logs.transport_on, milktransaction_logs.transportcost, milktransaction_logs.transport, vendors.vendorname FROM milktransactions INNER JOIN milktransaction_logs ON milktransactions.sno = milktransaction_logs.milktransaction_sno INNER JOIN vendors ON milktransactions.sectionid = vendors.sno WHERE (milktransactions.doe BETWEEN @d1 AND @d2) AND (milktransactions.transtype = @transtype) AND (milktransactions.sectionid=@sectionid)");
cmd.Parameters.Add("@d1", GetLowDate(fromdate));
cmd.Parameters.Add("@d2", ServerDateCurrentdate);
Session["filename"] = "Payment";
Session["title"] = "Payment Details";
cmd.Parameters.Add("@transtype", "In");
}
cmd.Parameters.Add("@sectionid", vendorid);
DataTable dtcow = new DataTable();
DataTable dttankersales = SalesDB.SelectQuery(cmd).Tables[0];
if (ddlcollectiontype.SelectedValue == "Collection")
{
cmd = new SqlCommand(" SELECT directsales_purchase.dcno, directsales_purchase.partydcno, directsales_purchase.inwardno AS InwardNo, directsales_purchase.vehicleno, CONVERT(VARCHAR(20),directsales_purchase.doe, 101) AS doe, directsales_purchase.transtype, directsales_purchase.qty_ltr, directsales_purchase.qty_kgs, directsales_purchase.percentageon, directsales_purchase.snf, directsales_purchase.fat, directsales_purchase.clr, directsales_purchaselogs.milktype, directsales_purchaselogs.rate_on, directsales_purchaselogs.cost, directsales_purchaselogs.calc_on, directsales_purchaselogs.overheadon, directsales_purchaselogs.overheadcost, directsales_purchaselogs.m_std_snf, directsales_purchaselogs.p_std_snf, directsales_purchaselogs.snfplus_on, directsales_purchaselogs.m_snfpluscost, directsales_purchaselogs.p_snfpluscost, directsales_purchaselogs.transport_on, directsales_purchaselogs.transportcost, directsales_purchaselogs.transport, vendors.vendorname FROM directsales_purchase INNER JOIN directsales_purchaselogs ON directsales_purchase.sno = directsales_purchaselogs.purchaserefno INNER JOIN vendors ON directsales_purchase.sectionid = vendors.sno WHERE (directsales_purchase.doe BETWEEN @d1 AND @d2) AND (directsales_purchase.transtype = @transtype) AND (directsales_purchaselogs.milktype='Cow') AND (directsales_purchase.sectionid=@sectionid)");
cmd.Parameters.Add("@d1", GetLowDate(fromdate));
cmd.Parameters.Add("@d2", GetHighDate(todate));
cmd.Parameters.Add("@transtype", "Out");
cmd.Parameters.Add("@sectionid", ddlbranches.SelectedValue);
dtcow = SalesDB.SelectQuery(cmd).Tables[0];
}
if (ddlcollectiontype.SelectedValue == "Payment")
{
cmd = new SqlCommand(" SELECT directsales_purchase.dcno, directsales_purchase.partydcno, directsales_purchase.inwardno AS InwardNo, directsales_purchase.vehicleno, CONVERT(VARCHAR(20),directsales_purchase.doe, 101) AS doe, directsales_purchase.transtype, directsales_purchase.qty_ltr, directsales_purchase.qty_kgs, directsales_purchase.percentageon, directsales_purchase.snf, directsales_purchase.fat, directsales_purchase.clr, directsales_purchaselogs.milktype, directsales_purchaselogs.rate_on, directsales_purchaselogs.cost, directsales_purchaselogs.calc_on, directsales_purchaselogs.overheadon, directsales_purchaselogs.overheadcost, directsales_purchaselogs.m_std_snf, directsales_purchaselogs.p_std_snf, directsales_purchaselogs.snfplus_on, directsales_purchaselogs.m_snfpluscost, directsales_purchaselogs.p_snfpluscost, directsales_purchaselogs.transport_on, directsales_purchaselogs.transportcost, directsales_purchaselogs.transport, vendors.vendorname FROM directsales_purchase INNER JOIN directsales_purchaselogs ON directsales_purchase.sno = directsales_purchaselogs.purchaserefno INNER JOIN vendors ON directsales_purchase.sectionid = vendors.sno WHERE (directsales_purchase.doe BETWEEN @d1 AND @d2) AND (directsales_purchase.transtype = @transtype) AND (directsales_purchaselogs.milktype='Cow') AND (directsales_purchase.sectionid=@sectionid)");
cmd.Parameters.Add("@d1", GetLowDate(fromdate));
cmd.Parameters.Add("@d2", GetHighDate(todate));
cmd.Parameters.Add("@transtype", "in");
cmd.Parameters.Add("@sectionid", ddlbranches.SelectedValue);
dtcow = SalesDB.SelectQuery(cmd).Tables[0];
}
//cmd = new SqlCommand(" SELECT directsales_purchase.dcno,directsales_purchase.partydcno, directsales_purchase.inwardno AS InwardNo, directsales_purchase.vehicleno,CONVERT(VARCHAR(20),directsales_purchase.doe, 101) AS doe , directsales_purchase.transtype, directsales_purchase.qty_ltr, directsales_purchase.qty_kgs, directsales_purchase.percentageon, directsales_purchase.snf, directsales_purchase.fat, directsales_purchase.clr, directsales_purchaselogs.milktype, directsales_purchaselogs.rate_on, directsales_purchaselogs.cost, directsales_purchaselogs.calc_on, directsales_purchaselogs.overheadon, directsales_purchaselogs.overheadcost, directsales_purchaselogs.m_std_snf, directsales_purchaselogs.p_std_snf, directsales_purchaselogs.snfplus_on, directsales_purchaselogs.m_snfpluscost, directsales_purchaselogs.p_snfpluscost, directsales_purchaselogs.transport_on, directsales_purchaselogs.transportcost, directsales_purchaselogs.transport, vendors.vendorname FROM directsales_purchase INNER JOIN directsales_purchaselogs ON directsales_purchase.sno = directsales_purchaselogs.purchaserefno INNER JOIN vendors ON directsales_purchase.sectionid = vendors.sno WHERE (directsales_purchase.doe BETWEEN @d1 AND @d2) AND (directsales_purchase.transtype = @transtype) AND (directsales_purchaselogs.milktype='Buffalo') AND (directsales_purchase.sectionid=@sectionid)");
//cmd.Parameters.Add("@d1", GetLowDate(fromdate));
//cmd.Parameters.Add("@d2", GetHighDate(todate));
//cmd.Parameters.Add("@transtype", "in");
//cmd.Parameters.Add("@sectionid", ddlbranches.SelectedValue);
//DataTable dtBufello = SalesDB.SelectQuery(cmd).Tables[0];
DataTable dtAll = dttankersales.Copy();
dtAll.Merge(dtcow);
int i = 1;
string closingbal = "";
TimeSpan dateSpan = todate.Subtract(fromdate);
int NoOfdays = dateSpan.Days;
NoOfdays = NoOfdays + 1;
double totdebitedamount = 0;
double totsalesamount = 0;
double oppcarry = 0;
foreach (DataRow dr in dtAll.Rows)
{
string milktypes = dr["milktype"].ToString();
{
if (milktypes == "Buffalo")
{
double qty_ltr = 0;
double.TryParse(dr["qty_ltr"].ToString(), out qty_ltr);
double FAT = 0;
double.TryParse(dr["fat"].ToString(), out FAT);
FAT = Math.Round(FAT, 2);
double SNF = 0;
double.TryParse(dr["snf"].ToString(), out SNF);
string Rateon = dr["rate_on"].ToString();
double weight = 0;
double KGFAT = 0;
double KGSNF = 0;
double ltrs = 0;
double.TryParse(dr["qty_ltr"].ToString(), out ltrs);
double Kgs = 0;
double.TryParse(dr["qty_kgs"].ToString(), out Kgs);
double tstotal = 0;
tstotal = FAT + SNF;
if (Rateon == "TS")
{
double TS = 0;
TS = FAT + SNF;
weight = (TS * Kgs) / 100;
KGFAT = (FAT * Kgs) / 100;
KGSNF = (SNF * Kgs) / 100;
}
else if (Rateon == "KGFAT")
{
weight = (FAT * Kgs) / 100;
KGFAT = (FAT * Kgs) / 100;
KGSNF = (SNF * Kgs) / 100;
}
else if (Rateon == "PerLtr" || Rateon == "PerKg")
{
string CalOn = dr["calc_on"].ToString();
if (CalOn == "Ltrs")
{
weight = ltrs;
KGFAT = (FAT * ltrs) / 100;
KGSNF = (SNF * ltrs) / 100;
}
else
{
weight = Kgs;
KGFAT = (FAT * Kgs) / 100;
KGSNF = (SNF * Kgs) / 100;
}
}
double cost = 0;
double.TryParse(dr["cost"].ToString(), out cost);
KGFAT = Math.Round(KGFAT, 2);
KGSNF = Math.Round(KGSNF, 2);
double MValue = 0;
MValue = KGFAT * cost;
//MValue = MValue / 100;
MValue = Math.Round(MValue, 2);
string OverheadOn = dr["overheadon"].ToString();
double OHcost = 0;
double overheadcost = 0;
double.TryParse(dr["overheadcost"].ToString(), out overheadcost);
if (OverheadOn == "Ltrs")
{
OHcost = overheadcost * ltrs;
}
else
{
OHcost = overheadcost * Kgs;
}
double MSnf = 0;
double.TryParse(dr["m_std_snf"].ToString(), out MSnf);
double m_snfpluscost = 0;
double.TryParse(dr["m_snfpluscost"].ToString(), out m_snfpluscost);
double DiffSNFCost = 0;
if (SNF < MSnf)
{
string SNFOn = dr["snfplus_on"].ToString();
double diffSNF = 0;
diffSNF = SNF - MSnf;
diffSNF = Math.Round(diffSNF, 2);
if (SNFOn == "Ltrs")
{
DiffSNFCost = diffSNF * ltrs * m_snfpluscost * 10;
}
else
{
DiffSNFCost = diffSNF * Kgs * m_snfpluscost * 10;
}
}
double p_snfpluscost = 0;
double.TryParse(dr["p_snfpluscost"].ToString(), out p_snfpluscost);
double PSnf = 0;
double.TryParse(dr["p_std_snf"].ToString(), out PSnf);
if (SNF > PSnf)
{
string SNFOn = dr["snfplus_on"].ToString();
double diffSNF = 0;
diffSNF = SNF - MSnf;
if (SNFOn == "Ltrs")
{
DiffSNFCost = diffSNF * ltrs * p_snfpluscost * 10;
}
else
{
DiffSNFCost = diffSNF * Kgs * p_snfpluscost * 10;
}
}
double OHandMvalue = 0;
OHandMvalue = MValue + OHcost + DiffSNFCost;
OHandMvalue = Math.Round(OHandMvalue, 2);
totsalesamount += OHandMvalue;
}
else
{
DateTime dtdoe = Convert.ToDateTime(dr["doe"].ToString());
string date = dtdoe.ToString("dd/MM/yyyy");
double FAT = 0;
double.TryParse(dr["fat"].ToString(), out FAT);
FAT = Math.Round(FAT, 2);
double SNF = 0;
double.TryParse(dr["snf"].ToString(), out SNF);
string Rateon = dr["rate_on"].ToString();
double weight = 0;
double KGFAT = 0;
double KGSNF = 0;
double ltrs = 0;
double.TryParse(dr["qty_ltr"].ToString(), out ltrs);
double Kgs = 0;
double.TryParse(dr["qty_kgs"].ToString(), out Kgs);
double tstotal = 0;
tstotal = FAT + SNF;
tstotal = Math.Round(tstotal, 2);
if (Rateon == "TS")
{
double TS = 0;
TS = FAT + SNF;
weight = (TS * Kgs) / 100;
KGFAT = (FAT * Kgs) / 100;
KGSNF = (SNF * Kgs) / 100;
}
else if (Rateon == "KGFAT")
{
weight = (FAT * Kgs) / 100;
KGFAT = (FAT * Kgs) / 100;
KGSNF = (SNF * Kgs) / 100;
}
else if (Rateon == "PerLtr" || Rateon == "PerKg")
{
string CalOn = dr["calc_on"].ToString();
if (CalOn == "Ltrs")
{
weight = ltrs;
KGFAT = (FAT * ltrs) / 100;
KGSNF = (SNF * ltrs) / 100;
}
else
{
weight = Kgs;
KGFAT = (FAT * Kgs) / 100;
KGSNF = (SNF * Kgs) / 100;
}
}
double cost = 0;
double.TryParse(dr["cost"].ToString(), out cost);
KGFAT = Math.Round(KGFAT, 2);
KGSNF = Math.Round(KGSNF, 2);
double MValue = 0;
if (Rateon == "PerLtr")
{
MValue = cost * ltrs;
}
else if (Rateon == "PerKg")
{
MValue = cost * Kgs;
}
else
{
string CalOn = dr["calc_on"].ToString();
if (CalOn == "Ltrs")
{
MValue = tstotal * cost * ltrs;
MValue = MValue / 100;
}
else
{
MValue = tstotal * cost * Kgs;
MValue = MValue / 100;
}
}
MValue = Math.Round(MValue, 2);
string OverheadOn = dr["overheadon"].ToString();
double OHcost = 0;
double overheadcost = 0;
double.TryParse(dr["overheadcost"].ToString(), out overheadcost);
if (OverheadOn == "Ltrs")
{
OHcost = overheadcost * ltrs;
}
else
{
OHcost = overheadcost * Kgs;
}
double MFat = 0;
double.TryParse(dr["m_std_fat"].ToString(), out MFat);
double m_fatpluscost = 0;
double.TryParse(dr["m_fatpluscost"].ToString(), out m_fatpluscost);
double DiffFATCost = 0;
if (FAT < MFat)
{
string FATOn = dr["fatplus_on"].ToString();
double diffFAT = 0;
diffFAT = FAT - MFat;
diffFAT = Math.Round(diffFAT, 2);
if (FATOn == "Ltrs")
{
DiffFATCost = diffFAT * ltrs * m_fatpluscost * 10;
}
else
{
DiffFATCost = diffFAT * Kgs * m_fatpluscost * 10;
}
}
double p_fatpluscost = 0;
double.TryParse(dr["p_fatpluscost"].ToString(), out p_fatpluscost);
double PFat = 0;
double.TryParse(dr["p_std_fat"].ToString(), out PFat);
if (FAT > PFat)
{
string FATOn = dr["fatplus_on"].ToString();
double diffFAT = 0;
diffFAT = FAT - MFat;
if (FATOn == "Ltrs")
{
DiffFATCost = diffFAT * ltrs * p_fatpluscost * 10;
}
else
{
DiffFATCost = diffFAT * Kgs * p_fatpluscost * 10;
}
}
DiffFATCost = Math.Round(DiffFATCost, 2);
double MSnf = 0;
double.TryParse(dr["m_std_snf"].ToString(), out MSnf);
double m_snfpluscost = 0;
double.TryParse(dr["m_snfpluscost"].ToString(), out m_snfpluscost);
double DiffSNFCost = 0;
if (SNF < MSnf)
{
string SNFOn = dr["snfplus_on"].ToString();
double diffSNF = 0;
diffSNF = SNF - MSnf;
diffSNF = Math.Round(diffSNF, 2);
if (SNFOn == "Ltrs")
{
DiffSNFCost = diffSNF * ltrs * m_snfpluscost * 10;
}
else
{
DiffSNFCost = diffSNF * Kgs * m_snfpluscost * 10;
}
}
double p_snfpluscost = 0;
double.TryParse(dr["p_snfpluscost"].ToString(), out p_snfpluscost);
double PSnf = 0;
double.TryParse(dr["p_std_snf"].ToString(), out PSnf);
if (SNF > PSnf)
{
string SNFOn = dr["snfplus_on"].ToString();
double diffSNF = 0;
diffSNF = SNF - MSnf;
if (SNFOn == "Ltrs")
{
DiffSNFCost = diffSNF * ltrs * p_snfpluscost * 10;
}
else
{
DiffSNFCost = diffSNF * Kgs * p_snfpluscost * 10;
}
}
double OHandMvalue = 0;
OHandMvalue = MValue + OHcost + DiffSNFCost + DiffFATCost;
DiffSNFCost = Math.Round(DiffSNFCost, 2);
OHandMvalue = Math.Round(OHandMvalue, 2);
totsalesamount += OHandMvalue;
totsalesamount = Math.Ceiling(totsalesamount);
}
}
}
//formula as per ERP
vendorpresentopp = (Math.Round(debitprice - totsalesamount)) + vendorpresentopp;
lblopeningbalance.Text = vendorpresentopp.ToString();
for (int j = 0; j < NoOfdays; j++)
{
DataRow newrow = Report.NewRow();
newrow["Sno"] = i;
string dtcount = fromdate.AddDays(j).ToString();
DateTime dtDOE = Convert.ToDateTime(dtcount);
//string dtdate1 = branch["IndentDate"].ToString();
string dtdate1 = dtDOE.AddDays(-1).ToString();
DateTime dtDOE1 = Convert.ToDateTime(dtdate1).AddDays(1);
string ChangedTime1 = dtDOE1.ToString("MM/dd/yyyy", CultureInfo.InvariantCulture);
string ChangedTime2 = dtDOE.AddDays(-1).ToString("dd MMM yy");
newrow["Date"] = ChangedTime1;
double totsale = 0;
DataTable dtin = new DataTable();
DataRow[] drr = dtAll.Select("doe='" + ChangedTime1 + "'");
if (drr.Length > 0)
{
dtin = drr.CopyToDataTable();
}
foreach (DataRow dr in dtin.Rows)
{
string milktypes = dr["milktype"].ToString();
{
if (milktypes == "Buffalo")
{
double qty_ltr = 0;
double.TryParse(dr["qty_ltr"].ToString(), out qty_ltr);
double FAT = 0;
double.TryParse(dr["fat"].ToString(), out FAT);
FAT = Math.Round(FAT, 2);
double SNF = 0;
double.TryParse(dr["snf"].ToString(), out SNF);
string Rateon = dr["rate_on"].ToString();
double weight = 0;
double KGFAT = 0;
double KGSNF = 0;
double ltrs = 0;
double.TryParse(dr["qty_ltr"].ToString(), out ltrs);
double Kgs = 0;
double.TryParse(dr["qty_kgs"].ToString(), out Kgs);
double tstotal = 0;
tstotal = FAT + SNF;
if (Rateon == "TS")
{
double TS = 0;
TS = FAT + SNF;
weight = (TS * Kgs) / 100;
KGFAT = (FAT * Kgs) / 100;
KGSNF = (SNF * Kgs) / 100;
}
else if (Rateon == "KGFAT")
{
weight = (FAT * Kgs) / 100;
KGFAT = (FAT * Kgs) / 100;
KGSNF = (SNF * Kgs) / 100;
}
else if (Rateon == "PerLtr" || Rateon == "PerKg")
{
string CalOn = dr["calc_on"].ToString();
if (CalOn == "Ltrs")
{
weight = ltrs;
KGFAT = (FAT * ltrs) / 100;
KGSNF = (SNF * ltrs) / 100;
}
else
{
weight = Kgs;
KGFAT = (FAT * Kgs) / 100;
KGSNF = (SNF * Kgs) / 100;
}
}
double cost = 0;
double.TryParse(dr["cost"].ToString(), out cost);
KGFAT = Math.Round(KGFAT, 2);
KGSNF = Math.Round(KGSNF, 2);
double MValue = 0;
MValue = KGFAT * cost;
//MValue = MValue / 100;
MValue = Math.Round(MValue, 2);
string OverheadOn = dr["overheadon"].ToString();
double OHcost = 0;
double overheadcost = 0;
double.TryParse(dr["overheadcost"].ToString(), out overheadcost);
if (OverheadOn == "Ltrs")
{
OHcost = overheadcost * ltrs;
}
else
{
OHcost = overheadcost * Kgs;
}
double MSnf = 0;
double.TryParse(dr["m_std_snf"].ToString(), out MSnf);
double m_snfpluscost = 0;
double.TryParse(dr["m_snfpluscost"].ToString(), out m_snfpluscost);
double DiffSNFCost = 0;
if (SNF < MSnf)
{
string SNFOn = dr["snfplus_on"].ToString();
double diffSNF = 0;
diffSNF = SNF - MSnf;
diffSNF = Math.Round(diffSNF, 2);
if (SNFOn == "Ltrs")
{
DiffSNFCost = diffSNF * ltrs * m_snfpluscost * 10;
}
else
{
DiffSNFCost = diffSNF * Kgs * m_snfpluscost * 10;
}
}
double p_snfpluscost = 0;
double.TryParse(dr["p_snfpluscost"].ToString(), out p_snfpluscost);
double PSnf = 0;
double.TryParse(dr["p_std_snf"].ToString(), out PSnf);
if (SNF > PSnf)
{
string SNFOn = dr["snfplus_on"].ToString();
double diffSNF = 0;
diffSNF = SNF - MSnf;
if (SNFOn == "Ltrs")
{
DiffSNFCost = diffSNF * ltrs * p_snfpluscost * 10;
}
else
{
DiffSNFCost = diffSNF * Kgs * p_snfpluscost * 10;
}
}
double OHandMvalue = 0;
OHandMvalue = MValue + OHcost + DiffSNFCost;
OHandMvalue = Math.Round(OHandMvalue, 2);
totsale += OHandMvalue;
totsale = Math.Ceiling(totsale);
newrow["Dc No"] = dr["dcno"].ToString();
newrow["Dc Amount"] = totsale;
}
else
{
DateTime dtdoe = Convert.ToDateTime(dr["doe"].ToString());
string date = dtdoe.ToString("dd/MM/yyyy");
double FAT = 0;
double.TryParse(dr["fat"].ToString(), out FAT);
FAT = Math.Round(FAT, 2);
double SNF = 0;
double.TryParse(dr["snf"].ToString(), out SNF);
string Rateon = dr["rate_on"].ToString();
double weight = 0;
double KGFAT = 0;
double KGSNF = 0;
double ltrs = 0;
double.TryParse(dr["qty_ltr"].ToString(), out ltrs);
double Kgs = 0;
double.TryParse(dr["qty_kgs"].ToString(), out Kgs);
double tstotal = 0;
tstotal = FAT + SNF;
tstotal = Math.Round(tstotal, 2);
if (Rateon == "TS")
{
double TS = 0;
TS = FAT + SNF;
weight = (TS * Kgs) / 100;
KGFAT = (FAT * Kgs) / 100;
KGSNF = (SNF * Kgs) / 100;
}
else if (Rateon == "KGFAT")
{
weight = (FAT * Kgs) / 100;
KGFAT = (FAT * Kgs) / 100;
KGSNF = (SNF * Kgs) / 100;
}
else if (Rateon == "PerLtr" || Rateon == "PerKg")
{
string CalOn = dr["calc_on"].ToString();
if (CalOn == "Ltrs")
{
weight = ltrs;
KGFAT = (FAT * ltrs) / 100;
KGSNF = (SNF * ltrs) / 100;
}
else
{
weight = Kgs;
KGFAT = (FAT * Kgs) / 100;
KGSNF = (SNF * Kgs) / 100;
}
}
double cost = 0;
double.TryParse(dr["cost"].ToString(), out cost);
KGFAT = Math.Round(KGFAT, 2);
KGSNF = Math.Round(KGSNF, 2);
double MValue = 0;
if (Rateon == "PerLtr")
{
MValue = cost * ltrs;
}
else if (Rateon == "PerKg")
{
MValue = cost * Kgs;
}
else
{
string CalOn = dr["calc_on"].ToString();
if (CalOn == "Ltrs")
{
MValue = tstotal * cost * ltrs;
MValue = MValue / 100;
}
else
{
MValue = tstotal * cost * Kgs;
MValue = MValue / 100;
}
}
MValue = Math.Round(MValue, 2);
string OverheadOn = dr["overheadon"].ToString();
double OHcost = 0;
double overheadcost = 0;
double.TryParse(dr["overheadcost"].ToString(), out overheadcost);
if (OverheadOn == "Ltrs")
{
OHcost = overheadcost * ltrs;
}
else
{
OHcost = overheadcost * Kgs;
}
double MFat = 0;
double.TryParse(dr["m_std_fat"].ToString(), out MFat);
double m_fatpluscost = 0;
double.TryParse(dr["m_fatpluscost"].ToString(), out m_fatpluscost);
double DiffFATCost = 0;
if (FAT < MFat)
{
string FATOn = dr["fatplus_on"].ToString();
double diffFAT = 0;
diffFAT = FAT - MFat;
diffFAT = Math.Round(diffFAT, 2);
if (FATOn == "Ltrs")
{
DiffFATCost = diffFAT * ltrs * m_fatpluscost * 10;
}
else
{
DiffFATCost = diffFAT * Kgs * m_fatpluscost * 10;
}
}
double p_fatpluscost = 0;
double.TryParse(dr["p_fatpluscost"].ToString(), out p_fatpluscost);
double PFat = 0;
double.TryParse(dr["p_std_fat"].ToString(), out PFat);
if (FAT > PFat)
{
string FATOn = dr["fatplus_on"].ToString();
double diffFAT = 0;
diffFAT = FAT - MFat;
if (FATOn == "Ltrs")
{
DiffFATCost = diffFAT * ltrs * p_fatpluscost * 10;
}
else
{
DiffFATCost = diffFAT * Kgs * p_fatpluscost * 10;
}
}
DiffFATCost = Math.Round(DiffFATCost, 2);
double MSnf = 0;
double.TryParse(dr["m_std_snf"].ToString(), out MSnf);
double m_snfpluscost = 0;
double.TryParse(dr["m_snfpluscost"].ToString(), out m_snfpluscost);
double DiffSNFCost = 0;
if (SNF < MSnf)
{
string SNFOn = dr["snfplus_on"].ToString();
double diffSNF = 0;
diffSNF = SNF - MSnf;
diffSNF = Math.Round(diffSNF, 2);
if (SNFOn == "Ltrs")
{
DiffSNFCost = diffSNF * ltrs * m_snfpluscost * 10;
}
else
{
DiffSNFCost = diffSNF * Kgs * m_snfpluscost * 10;
}
}
double p_snfpluscost = 0;
double.TryParse(dr["p_snfpluscost"].ToString(), out p_snfpluscost);
double PSnf = 0;
double.TryParse(dr["p_std_snf"].ToString(), out PSnf);
if (SNF > PSnf)
{
string SNFOn = dr["snfplus_on"].ToString();
double diffSNF = 0;
diffSNF = SNF - MSnf;
if (SNFOn == "Ltrs")
{
DiffSNFCost = diffSNF * ltrs * p_snfpluscost * 10;
}
else
{
DiffSNFCost = diffSNF * Kgs * p_snfpluscost * 10;
}
}
double OHandMvalue = 0;
OHandMvalue = MValue + OHcost + DiffSNFCost + DiffFATCost;
DiffSNFCost = Math.Round(DiffSNFCost, 2);
OHandMvalue = Math.Round(OHandMvalue, 2);
totsale += OHandMvalue;
totsale = Math.Ceiling(totsale);
newrow["Dc No"] = dr["dcno"].ToString();
newrow["Dc Amount"] = totsale;
}
}
}
double amtpaid = 0;
DataTable dtamountcol = new DataTable();
DataRow[] drramountcol = dtcollection.Select("doe='" + ChangedTime1 + "'");
if (drramountcol.Length > 0)
{
dtamountcol = drramountcol.CopyToDataTable();
}
double amount = 0;
foreach (DataRow dr in dtamountcol.Rows)
{
double.TryParse(dr["amount"].ToString(), out amount);
amtpaid += amount;
}
///Get Opp Bal according to Erp Formula ..By Srinu
double aopp = 0;
if (i == 1)
{
aopp = vendorpresentopp;
}
else
{
aopp = vendorpresentopp;
if (totsale == 0)
{
aopp = oppcarry;
}
}
newrow["Opp Bal"] = Math.Ceiling(aopp);
double totalamt = aopp + totsale;
totalamt = Math.Ceiling(totalamt);
newrow["Total Amount"] = totalamt;
amtpaid = Math.Ceiling(amtpaid);
if (ddlcollectiontype.SelectedValue == "Collection")
{
newrow["Received Amount"] = amtpaid;
}
else
{
newrow["Paid Amount"] = amtpaid;
}
totalpaidamount += amtpaid;
double totalbalance = totalamt - amtpaid;
totalbalance = Math.Ceiling(totalbalance);
newrow["Balance"] = totalbalance;
oppcarry = totalbalance;
vendorpresentopp = totalbalance;
Report.Rows.Add(newrow);
i++;
lblclosingbalance.Text = totalbalance.ToString();
}
DataRow newrow2 = Report.NewRow();
newrow2["Date"] = "Total";
newrow2["Dc Amount"] = totsalesamount;
if (ddlcollectiontype.SelectedValue == "Collection")
{
newrow2["Received Amount"] = totalpaidamount;
}
else
{
newrow2["Paid Amount"] = totalpaidamount;
}
Report.Rows.Add(newrow2);
grdReports.DataSource = Report;
grdReports.DataBind();
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
}
}
protected void BtnSave_Click(object sender, EventArgs e)
{
try
{
vdm = new SalesDBManager();
DateTime CreateDate = SalesDBManager.GetTime(vdm.conn);
SAPdbmanger SAPvdm = new SAPdbmanger();
DateTime fromdate = DateTime.Now;
DataTable dt = (DataTable)Session["xportdata"];
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);
}
}
foreach (DataRow dr in dt.Rows)
{
string PaymentMode = "CASH";
double amount = 0;
double.TryParse(dr["Amount"].ToString(), out amount);
string B1Upload = "N";
string Processed = "N";
string date = dr["Voucher Date"].ToString();
DateTime from_date = Convert.ToDateTime(date);
sqlcmd = new SqlCommand("Insert into EMROJDTP (CreateDate, RefDate, DocDate, TransNo,TransCode, AcctCode, AcctName, Debit, Credit, B1Upload, Processed,Ref1,OcrCode,Remarks) values (@CreateDate, @RefDate, @DocDate,@TransNo,@TransCode, @AcctCode, @AcctName, @Debit, @Credit, @B1Upload, @Processed,@Ref1,@OcrCode,@Remarks)");
sqlcmd.Parameters.Add("@CreateDate", CreateDate);
sqlcmd.Parameters.Add("@RefDate", GetLowDate(from_date));
sqlcmd.Parameters.Add("@docdate", GetLowDate(from_date));
sqlcmd.Parameters.Add("@Ref1", dr["Voucher No"].ToString());
string TransCode = "T1";
sqlcmd.Parameters.Add("@TransNo", dr["Voucher No"].ToString());
sqlcmd.Parameters.Add("@TransCode", TransCode);
sqlcmd.Parameters.Add("@AcctCode", dr["Debit Code"].ToString());
sqlcmd.Parameters.Add("@AcctName", dr["Ledger (Dr)"].ToString());
double.TryParse(dr["Amount"].ToString(), out amount);
sqlcmd.Parameters.Add("@Debit", amount);
string Creditamount = "0";
sqlcmd.Parameters.Add("@Credit", Creditamount);
sqlcmd.Parameters.Add("@B1Upload", B1Upload);
sqlcmd.Parameters.Add("@Processed", Processed);
sqlcmd.Parameters.Add("@OcrCode", dr["whcode"].ToString());
sqlcmd.Parameters.Add("@Remarks", dr["Narration"].ToString());
SAPvdm.insert(sqlcmd);
sqlcmd = new SqlCommand("Insert into EMROJDTP (CreateDate, RefDate, DocDate, TransNo,TransCode, AcctCode, AcctName, Debit, Credit, B1Upload, Processed,Ref1,OcrCode,Remarks) values (@CreateDate, @RefDate, @DocDate,@TransNo,@TransCode, @AcctCode, @AcctName, @Debit, @Credit, @B1Upload, @Processed,@Ref1,@OcrCode,@Remarks)");
sqlcmd.Parameters.Add("@CreateDate", CreateDate);
sqlcmd.Parameters.Add("@RefDate", GetLowDate(from_date));
sqlcmd.Parameters.Add("@docdate", GetLowDate(from_date));
sqlcmd.Parameters.Add("@Ref1", dr["Voucher No"].ToString());
sqlcmd.Parameters.Add("@TransNo", dr["Voucher No"].ToString());
sqlcmd.Parameters.Add("@TransCode", TransCode);
sqlcmd.Parameters.Add("@AcctCode", dr["Credit Code"].ToString());
sqlcmd.Parameters.Add("@AcctName", dr["Ledger (Cr)"].ToString());
string Debitamount = "0";
sqlcmd.Parameters.Add("@Debit", Debitamount);
sqlcmd.Parameters.Add("@Credit", amount);
sqlcmd.Parameters.Add("@B1Upload", B1Upload);
sqlcmd.Parameters.Add("@Processed", Processed);
sqlcmd.Parameters.Add("@OcrCode", dr["whcode"].ToString());
sqlcmd.Parameters.Add("@Remarks", dr["Narration"].ToString());
SAPvdm.insert(sqlcmd);
}
pnlHide.Visible = false;
DataTable dtempty = new DataTable();
grdReports.DataSource = dtempty;
grdReports.DataBind();
lblmsg.Text = "Successfully Saved";
}
catch (Exception ex)
{
lblmsg.Text = ex.ToString();
}
}
protected void btn_Generate_Click(object sender, EventArgs e)
{
try
{
Report.Columns.Add("Ledger Type");
Report.Columns.Add("Customer Name");
Report.Columns.Add("Invoce No.");
Report.Columns.Add("Invoice Date");
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");
lblmsg.Text = "";
SalesDBManager SalesDB = new SalesDBManager();
DateTime fromdate = DateTime.Now;
DateTime todate = DateTime.Now;
string[] datestrig = dtp_FromDate.Text.Split(' ');
if (datestrig.Length > 1)
{
if (datestrig[0].Split('-').Length > 0)
{
string[] dates = datestrig[0].Split('-');
string[] times = datestrig[1].Split(':');
fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
datestrig = dtp_Todate.Text.Split(' ');
if (datestrig.Length > 1)
{
if (datestrig[0].Split('-').Length > 0)
{
string[] dates = datestrig[0].Split('-');
string[] times = datestrig[1].Split(':');
todate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
lblFromDate.Text = fromdate.ToString("dd/MM/yyyy");
lbltodate.Text = todate.ToString("dd/MM/yyyy");
string branch_id = Session["Po_BranchID"].ToString();
string bid = ddlbranch.SelectedItem.Value;
cmd = new SqlCommand("SELECT stocktransferdetails.salesinvoiceno, stocktransfersubdetails.igst, stocktransfersubdetails.cgst, stocktransfersubdetails.sgst, stocktransferdetails.salesstno, stocktransferdetails.transportname, stocktransferdetails.remarks, stocktransferdetails.invoicetype,stocktransferdetails.invoiceno, stocktransferdetails.invoicedate AS invoicedate, stocktransferdetails.vehicleno,stocktransfersubdetails.price,stocktransfersubdetails.taxvalue,stocktransfersubdetails.taxtype,stocktransfersubdetails.freigtamt,stocktransferdetails.sno as refno,stocktransfersubdetails.sno,branchmaster.branchname, branchmaster_1.branchname AS BranchName, productmaster.productname, stocktransferdetails.frombranch, stocktransferdetails.tobranch, stocktransfersubdetails.quantity, stocktransfersubdetails.stock_refno, stocktransfersubdetails.productid, stocktransferdetails.doe FROM stocktransferdetails INNER JOIN stocktransfersubdetails ON stocktransferdetails.sno = stocktransfersubdetails.stock_refno LEFT OUTER JOIN branchmaster ON stocktransferdetails.frombranch = branchmaster.branchid LEFT OUTER JOIN branchmaster AS branchmaster_1 ON stocktransferdetails.tobranch = branchmaster_1.branchid INNER JOIN productmaster ON stocktransfersubdetails.productid = productmaster.productid where (stocktransferdetails.invoicedate between @fromdate and @todate) AND (stocktransferdetails.tobranch=@branch_id) AND (stocktransferdetails.branchid=@MAINBRANCH) and (stocktransfersubdetails.quantity>0)");
cmd.Parameters.Add("@fromdate", GetLowDate(fromdate));
cmd.Parameters.Add("@todate", GetHighDate(todate));
cmd.Parameters.Add("@branch_id", bid);
cmd.Parameters.Add("@MAINBRANCH", branch_id);
DataTable dtstocktransfer = SalesDB.SelectQuery(cmd).Tables[0];
if (dtstocktransfer.Rows.Count > 0)
{
double total_amount = 0, totamt = 0, totalprice = 0, totalqty = 0, totaltaxamt = 0, gtotaltaxamt = 0, taxamount = 0, ttaxamount = 0, ttotalamount = 0;
int count = 1;
int rowcount = 1;
double tfreight = 0, gtotalqty = 0, gtotalamount = 0, gfrightamt = 0, gtaxamount = 0;
string previnvoiceno = "";
DateTime ServerDateCurrentdate = SalesDBManager.GetTime(vdm.conn);
DateTime dtapril = new DateTime();
DateTime dtmarch = new DateTime();
int currentyear = ServerDateCurrentdate.Year;
int nextyear = ServerDateCurrentdate.Year + 1;
int currntyearnum = 0;
int nextyearnum = 0;
if (ServerDateCurrentdate.Month > 3)
{
string apr = "4/1/" + currentyear;
dtapril = DateTime.Parse(apr);
string march = "3/31/" + nextyear;
dtmarch = DateTime.Parse(march);
currntyearnum = currentyear;
nextyearnum = nextyear;
}
if (ServerDateCurrentdate.Month <= 3)
{
string apr = "4/1/" + (currentyear - 1);
dtapril = DateTime.Parse(apr);
string march = "3/31/" + (nextyear - 1);
dtmarch = DateTime.Parse(march);
currntyearnum = currentyear - 1;
nextyearnum = nextyear - 1;
}
cmd = new SqlCommand("select statemaster.sno,statemaster.code AS statecode,statemaster.gststatecode,statemaster.statename,branchmaster.GSTIN,branchmaster.address,branchmaster.branchname from statemaster INNER JOIN branchmaster ON statemaster.sno = branchmaster.statename WHERE branchmaster.branchid = @branch_id");
cmd.Parameters.Add("@branch_id", branch_id);
DataTable dt_branch = vdm.SelectQuery(cmd).Tables[0];
string fromstate = dt_branch.Rows[0]["statename"].ToString();
string frombranch_gstin = dt_branch.Rows[0]["GSTIN"].ToString();
string frombranch_address = dt_branch.Rows[0]["address"].ToString();
string frombranch_stateid = dt_branch.Rows[0]["gststatecode"].ToString();
string frombranch_statesno = dt_branch.Rows[0]["sno"].ToString();
string frombranch_name = dt_branch.Rows[0]["branchname"].ToString();
string frombranch_statecode = dt_branch.Rows[0]["statecode"].ToString();
foreach (DataRow dr in dtstocktransfer.Rows)
{
string date = dr["invoicedate"].ToString();
if (date != "")
{
DataRow newrow = Report.NewRow();
string ledgertype = "Sales Accounts";
string bname = dr["BranchName1"].ToString();
newrow["Ledger Type"] = ledgertype;
newrow["Customer Name"] = bname;
string invoiceno = dr["invoiceno"].ToString();
string salesinvoiceno = dr["salesinvoiceno"].ToString();
string stnumber = "";
double igst = 0;
double.TryParse(dr["igst"].ToString(), out igst);
double cgst = 0;
double.TryParse(dr["cgst"].ToString(), out cgst);
double sgst = 0;
double.TryParse(dr["sgst"].ToString(), out sgst);
if (salesinvoiceno == "0")
{
salesinvoiceno = dr["salesstno"].ToString();
string newreceipt = "0";
int countdc = 0;
int.TryParse(salesinvoiceno, out countdc);
if (countdc < 10)
{
newreceipt = "000" + countdc;
}
if (countdc >= 10 && countdc <= 99)
{
newreceipt = "00" + countdc;
}
if (countdc >= 99 && countdc <= 999)
{
newreceipt = "0" + countdc;
}
if (countdc > 999)
{
newreceipt = "" + countdc;
}
stnumber = frombranch_statecode + "/ST" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "/" + newreceipt;
}
else
{
string newreceipt = "0";
int countdc = 0;
int.TryParse(salesinvoiceno, out countdc);
if (countdc < 10)
{
newreceipt = "000" + countdc;
}
if (countdc >= 10 && countdc <= 99)
{
newreceipt = "00" + countdc;
}
if (countdc >= 99 && countdc <= 999)
{
newreceipt = "0" + countdc;
}
if (countdc > 999)
{
newreceipt = "" + countdc;
}
double tax = igst + sgst + cgst;
if (tax > 0)
{
stnumber = frombranch_statecode + "/T" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "/" + newreceipt;
}
else
{
stnumber = frombranch_statecode + "/N" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "/" + newreceipt;
}
}
DateTime dtdate = Convert.ToDateTime(date);
string invoicedate = dtdate.ToString("dd-MMM-yyyy");
string invoicedate1 = dtdate.ToString("dd/MM/yyyy");
newrow["Invoce No."] = stnumber;
newrow["Invoice Date"] = invoicedate;
newrow["Item Name"] = dr["productname"].ToString();
double price = 0;
double.TryParse(dr["price"].ToString(), out price);
totalprice += price;
newrow["Rate"] = Math.Round(price, 2).ToString("f2"); //dr["price"].ToString();
double qty = 0;
double.TryParse(dr["quantity"].ToString(), out qty);
totalqty += qty;
newrow["Qty"] = dr["quantity"].ToString();
string taxper = dr["taxvalue"].ToString();
double total = 0; double totamount = 0;
total = qty * price;
newrow["Taxable Value"] = Math.Round(total, 2).ToString("f2");
newrow["SGST%"] = sgst;
newrow["CGST%"] = cgst;
newrow["IGST%"] = igst;
double igstamount = (total * igst) / 100;
double cgstamount = (total * cgst) / 100;
double sgstamount = (total * sgst) / 100;
newrow["SGST Amount"] = Math.Round(sgstamount, 2).ToString("f2");
newrow["CGST Amount"] = Math.Round(cgstamount, 2).ToString("f2");
newrow["IGST Amount"] = Math.Round(igstamount, 2).ToString("f2");
double netvalue = total + sgstamount + cgstamount + igstamount;
totamount += total;
double freight = 0;
double.TryParse(dr["freigtamt"].ToString(), out freight);
tfreight += freight;
totamount = netvalue + freight;
double totalamount = 0;
totalamount = totamount;
newrow["Net Value"] = Math.Round(totalamount, 2).ToString("f2");
ttotalamount += totalamount;
newrow["Narration"] = "Being " + dr["remarks"].ToString() + " From SVDS Punabaka,Invoice No: " + stnumber + ",Inv Dt" + invoicedate1 + ",Amount" + totalamount + "";//dr["invoicedate"].ToString()
Report.Rows.Add(newrow);
}
}
Session["xportdata"] = Report;
Session["filename"] = "Stores Branch Transfer report";
grdReports.DataSource = Report;
grdReports.DataBind();
hidepanel.Visible = true;
}
else
{
grdReports.DataSource = null;
grdReports.DataBind();
}
}
catch (Exception ex)
{
}
}
void GetReport()
{
try
{
lblmsg.Text = "";
pnlHide.Visible = true;
Session["RouteName"] = ddlSalesOffice.SelectedItem.Text;
Session["IDate"] = DateTime.Now.AddDays(1).ToString("dd/MM/yyyy");
vdm = new SalesDBManager();
DateTime fromdate = DateTime.Now;
DateTime todate = 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);
}
}
dateFromstrig = dtp_Todate.Text.Split(' ');
if (dateFromstrig.Length > 1)
{
if (dateFromstrig[0].Split('-').Length > 0)
{
string[] dates = dateFromstrig[0].Split('-');
string[] times = dateFromstrig[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);
}
}
DataTable Report = new DataTable();
Report.Columns.Add("DOE");
Report.Columns.Add("Ref Receipt");
Report.Columns.Add("Receipt");
Report.Columns.Add("Type");
Report.Columns.Add("Name");
Report.Columns.Add("Amount").DataType = typeof(Double);
lbl_selfromdate.Text = fromdate.ToString("dd/MM/yyyy");
lblRoutName.Text = ddlSalesOffice.SelectedItem.Text;
DateTime ReportDate = SalesDBManager.GetTime(vdm.conn);
DateTime dtapril = new DateTime();
DateTime dtmarch = new DateTime();
int currentyear = ReportDate.Year;
int nextyear = ReportDate.Year + 1;
if (ReportDate.Month > 3)
{
string apr = "4/1/" + currentyear;
dtapril = DateTime.Parse(apr);
string march = "3/31/" + nextyear;
dtmarch = DateTime.Parse(march);
}
if (ReportDate.Month <= 3)
{
string apr = "4/1/" + (currentyear - 1);
dtapril = DateTime.Parse(apr);
string march = "3/31/" + (nextyear - 1);
dtmarch = DateTime.Parse(march);
}
Session["xporttype"] = "TallyPayments";
string ledger = "";
Session["filename"] = ddlSalesOffice.SelectedItem.Text + " Tally Payments" + fromdate.ToString("dd/MM/yyyy");
cmd = new SqlCommand("SELECT cashpayables.DOE, cashpayables.VocherID, subpayable.vouchercode, cashpayables.Sno, subpayable.HeadSno, cashpayables.Remarks,cashpayables.Remarks AS Expr1, subpayable.sno AS Expr2, subpayable.Amount, accountheads.HeadName,accountheads.ledger_code, cashpayables.BranchID, Plant_Master.ladger_dr,Plant_Master.WHcode, Plant_Master.ladger_dr_code FROM cashpayables INNER JOIN subpayable ON cashpayables.Sno = subpayable.RefNo INNER JOIN accountheads ON subpayable.HeadSno = accountheads.Sno INNER JOIN Plant_Master ON cashpayables.BranchID = Plant_Master.Plant_Code WHERE (cashpayables.BranchID = @BranchID) AND (cashpayables.DOE BETWEEN @d1 AND @d2) AND (cashpayables.VoucherType = 'Debit') AND (cashpayables.Status = 'P')");
cmd.Parameters.Add("@BranchID", ddlSalesOffice.SelectedValue);
cmd.Parameters.Add("@d1", GetLowDate(fromdate));
cmd.Parameters.Add("@d2", GetHighDate(todate));
DataTable dtAgent = vdm.SelectQuery(cmd).Tables[0];
if (dtAgent.Rows.Count > 0)
{
DataView view = new DataView(dtAgent);
dtReport = new DataTable();
dtReport.Columns.Add("Voucher Date");
dtReport.Columns.Add("Voucher No");
dtReport.Columns.Add("Voucher Type");
dtReport.Columns.Add("whcode");
//dtReport.Columns.Add("ApprovedBy");
dtReport.Columns.Add("Credit Code");
dtReport.Columns.Add("Ledger (Cr)");
dtReport.Columns.Add("Debit Code");
dtReport.Columns.Add("Ledger (Dr)");
dtReport.Columns.Add("Amount");
dtReport.Columns.Add("Narration");
int i = 1;
foreach (DataRow branch in dtAgent.Rows)
{
string VoucherNo = "";
cmd = new SqlCommand("SELECT vouchercode,RefNo, HeadDesc, Amount, HeadSno, sno, branchid, paiddate FROM subpayable WHERE (branchid = @BranchID) AND (paiddate BETWEEN @d1 AND @d2)");
cmd.Parameters.Add("@BranchID", ddlSalesOffice.SelectedValue);
cmd.Parameters.Add("@d1", GetLowDate(fromdate));
cmd.Parameters.Add("@d2", GetLowDate(fromdate));
DataTable dtVoucher = vdm.SelectQuery(cmd).Tables[0];
if (dtVoucher.Rows.Count > 0)
{
DataRow[] drvoucher = dtVoucher.Select("branchid='" + ddlSalesOffice.SelectedValue + "' and RefNo='" + branch["sno"].ToString() + "' and HeadSno='" + branch["HeadSno"].ToString() + "'");
if (drvoucher.Length > 0)
{
foreach (DataRow drv in drvoucher)
{
VoucherNo = drv.ItemArray[0].ToString();
}
}
else
{
cmd = new SqlCommand("SELECT ISNULL(MAX(vouchercode), 0) + 1 AS Sno FROM subpayable WHERE (branchid = @branchid) AND (paiddate BETWEEN @d1 AND @d2)");
cmd.Parameters.Add("@branchid", ddlSalesOffice.SelectedValue);
cmd.Parameters.Add("@HeadSno", branch["HeadSno"].ToString());
cmd.Parameters.Add("@d1", GetLowDate(dtapril.AddDays(-1)));
cmd.Parameters.Add("@d2", GetHighDate(dtmarch.AddDays(-1)));
DataTable dtvoucherno = vdm.SelectQuery(cmd).Tables[0];
VoucherNo = dtvoucherno.Rows[0]["Sno"].ToString();
cmd = new SqlCommand("update subpayable set vouchercode=@vouchercode, paiddate=@paiddate,branchid=@branchid where (RefNo=@RefNo) AND (HeadSno = @HeadSno)");;
cmd.Parameters.Add("@vouchercode", VoucherNo);
cmd.Parameters.Add("@paiddate", fromdate);
cmd.Parameters.Add("@branchid", ddlSalesOffice.SelectedValue);
cmd.Parameters.Add("@RefNo", branch["sno"].ToString());
cmd.Parameters.Add("@HeadSno", branch["HeadSno"].ToString());
vdm.Update(cmd);
}
}
else
{
cmd = new SqlCommand("SELECT ISNULL(MAX(vouchercode), 0) + 1 AS Sno FROM subpayable WHERE (branchid = @branchid) AND (paiddate BETWEEN @d1 AND @d2)");
cmd.Parameters.Add("@branchid", ddlSalesOffice.SelectedValue);
cmd.Parameters.Add("@HeadSno", branch["HeadSno"].ToString());
cmd.Parameters.Add("@d1", GetLowDate(dtapril.AddDays(-1)));
cmd.Parameters.Add("@d2", GetHighDate(dtmarch.AddDays(-1)));
DataTable dtvoucherno = vdm.SelectQuery(cmd).Tables[0];
VoucherNo = dtvoucherno.Rows[0]["Sno"].ToString();
cmd = new SqlCommand("update subpayable set vouchercode=@vouchercode, paiddate=@paiddate,branchid=@branchid where (RefNo=@RefNo) AND (HeadSno = @HeadSno)");;
cmd.Parameters.Add("@vouchercode", VoucherNo);
cmd.Parameters.Add("@paiddate", fromdate);
cmd.Parameters.Add("@branchid", ddlSalesOffice.SelectedValue);
cmd.Parameters.Add("@RefNo", branch["sno"].ToString());
cmd.Parameters.Add("@HeadSno", branch["HeadSno"].ToString());
vdm.Update(cmd);
}
if (VoucherNo == "0")
{
cmd = new SqlCommand("SELECT IFNULL(MAX(vouchercode), 0) + 1 AS Sno FROM subpayable WHERE (branchid = @branchid) AND (paiddate BETWEEN @d1 AND @d2)");
cmd.Parameters.Add("@branchid", ddlSalesOffice.SelectedValue);
cmd.Parameters.Add("@HeadSno", branch["HeadSno"].ToString());
cmd.Parameters.Add("@d1", GetLowDate(dtapril.AddDays(-1)));
cmd.Parameters.Add("@d2", GetHighDate(dtmarch.AddDays(-1)));
DataTable dtvoucherno = vdm.SelectQuery(cmd).Tables[0];
VoucherNo = dtvoucherno.Rows[0]["Sno"].ToString();
cmd = new SqlCommand("update subpayable set vouchercode=@vouchercode, paiddate=@paiddate,branchid=@branchid where (RefNo=@RefNo) AND (HeadSno = @HeadSno)");;
cmd.Parameters.Add("@vouchercode", VoucherNo);
cmd.Parameters.Add("@paiddate", fromdate);
cmd.Parameters.Add("@branchid", ddlSalesOffice.SelectedValue);
cmd.Parameters.Add("@RefNo", branch["sno"].ToString());
cmd.Parameters.Add("@HeadSno", branch["HeadSno"].ToString());
vdm.Update(cmd);
}
string NewVoucherNo = "0";
int countdc = 0;
int.TryParse(VoucherNo, out countdc);
if (countdc <= 10)
{
NewVoucherNo = "000" + countdc;
}
if (countdc >= 10 && countdc <= 99)
{
NewVoucherNo = "00" + countdc;
}
if (countdc >= 99 && countdc <= 999)
{
NewVoucherNo = "0" + countdc;
}
DataRow newrow = dtReport.NewRow();
string Date = branch["DOE"].ToString();
DateTime from_date = Convert.ToDateTime(Date);
newrow["Voucher Date"] = from_date.ToString("dd-MMM-yyyy");
newrow["Voucher No"] = ddlSalesOffice.SelectedValue + dtapril.ToString("yy") + dtmarch.ToString("yy") + NewVoucherNo;
newrow["Voucher Type"] = "Cash Payment Import";
newrow["Ledger (Cr)"] = branch["ladger_dr"].ToString();
newrow["Debit Code"] = branch["ledger_code"].ToString();
newrow["Credit Code"] = branch["ladger_dr_code"].ToString();
newrow["Ledger (Dr)"] = branch["HeadName"].ToString();
newrow["Amount"] = branch["Amount"].ToString();
newrow["whcode"] = branch["WHcode"].ToString();
double invval = 0;
newrow["Narration"] = branch["Remarks"].ToString() + ",VoucherID " + VoucherNo + ",Emp Name " + Session["EmpName"].ToString();
dtReport.Rows.Add(newrow);
i++;
}
grdReports.DataSource = dtReport;
grdReports.DataBind();
Session["xportdata"] = dtReport;
}
else
{
pnlHide.Visible = false;
lblmsg.Text = "No Indent Found";
grdReports.DataSource = dtReport;
grdReports.DataBind();
}
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
grdReports.DataSource = dtReport;
grdReports.DataBind();
}
}
private void report()
{
try
{
lblmsg.Text = "";
string milkopeningbal = string.Empty;
string milkclosingbal = string.Empty;
SalesDBManager SalesDB = new SalesDBManager();
DateTime fromdate = DateTime.Now;
DateTime todate = DateTime.Now;
string idcno = string.Empty;
string inworddate = string.Empty;
double totalinwardqty = 0;
double totalissueqty = 0;
double totaltransferquantity = 0;
double tranqtydateqty = 0;
double totalReturnqty = 0;
double returndateqty = 0;
double totinwardvalue = 0;
double totoutwardvalue = 0;
double tottransfervalue = 0;
double totreturnvalue = 0;
string[] datestrig = dtp_FromDate.Text.Split(' ');
if (datestrig.Length > 1)
{
if (datestrig[0].Split('-').Length > 0)
{
string[] dates = datestrig[0].Split('-');
string[] times = datestrig[1].Split(':');
fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
datestrig = dtp_Todate.Text.Split(' ');
if (datestrig.Length > 1)
{
if (datestrig[0].Split('-').Length > 0)
{
string[] dates = datestrig[0].Split('-');
string[] times = datestrig[1].Split(':');
todate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
lblFromDate.Text = fromdate.ToString("dd/MMM/yyyy");
lbltodate.Text = todate.ToString("dd/MMM/yyyy");
string productid = TextBox1.Text;
Report.Columns.Add("Sno");
Report.Columns.Add("Date");
Report.Columns.Add("Opp Qty");
Report.Columns.Add("Opp Price");
Report.Columns.Add("Opp Value");
Report.Columns.Add("ReceiptsQty").DataType = typeof(double);
Report.Columns.Add("ReceiptsPrice");
Report.Columns.Add("ReceiptsValue").DataType = typeof(double);
Report.Columns.Add("ReturnQty").DataType = typeof(double);
Report.Columns.Add("ReturnPrice");
Report.Columns.Add("ReturnValue").DataType = typeof(double);
Report.Columns.Add("IssuesQty").DataType = typeof(double);
Report.Columns.Add("IssuesPrice");
Report.Columns.Add("IssuesValue").DataType = typeof(double);
Report.Columns.Add("IssuesQty1").DataType = typeof(double);
Report.Columns.Add("IssuesPrice1");
Report.Columns.Add("IssuesValue1").DataType = typeof(double);
Report.Columns.Add("TransferQty").DataType = typeof(double);
Report.Columns.Add("TransferPrice");
Report.Columns.Add("TransferValue").DataType = typeof(double);
Report.Columns.Add("TransferQty1").DataType = typeof(double);
Report.Columns.Add("Transferprice1");
Report.Columns.Add("Transfervale1").DataType = typeof(double);
Report.Columns.Add("Clo Qty");
Report.Columns.Add("Clo Price");
Report.Columns.Add("Clo Value");
hidepanel.Visible = true;
Session["filename"] = "Ledger";
Session["title"] = "Ledger Details";
TimeSpan dateSpan = todate.Subtract(fromdate);
int NoOfdays = dateSpan.Days;
NoOfdays = NoOfdays + 1;
DateTime ServerDateCurrentdate = SalesDBManager.GetTime(vdm.conn);
string branchid = Session["Po_BranchID"].ToString();
// inward
cmd = new SqlCommand("SELECT SUM(subinwarddetails.quantity) AS inwardqty,subinwarddetails.perunit,SUM(subinwarddetails.quantity*subinwarddetails.perunit) AS inwardvalue, productmaster.productname, subinwarddetails.tax, subinwarddetails.edtax, inwarddetails.inwarddate AS inwarddate FROM inwarddetails INNER JOIN subinwarddetails ON inwarddetails.sno = subinwarddetails.in_refno INNER JOIN productmaster ON subinwarddetails.productid = productmaster.productid WHERE (productmaster.productname = @ProductID) AND (inwarddetails.inwarddate BETWEEN @d1 AND @d2) AND (inwarddetails.branchid=@branchid) AND (subinwarddetails.quantity>0) AND (inwarddetails.status='A') GROUP BY productmaster.productname,subinwarddetails.perunit, inwarddetails.inwarddate, subinwarddetails.tax, subinwarddetails.edtax");
cmd.Parameters.Add("@d1", GetLowDate(fromdate));
cmd.Parameters.Add("@d2", ServerDateCurrentdate);
cmd.Parameters.Add("@ProductID", productid);
cmd.Parameters.Add("@branchid", branchid);
DataTable dtinward = SalesDB.SelectQuery(cmd).Tables[0];
double inquantity = 0; double suminvalue = 0;
foreach (DataRow drin in dtinward.Rows)
{
double.TryParse(drin["inwardqty"].ToString(), out inquantity);
double.TryParse(drin["inwardvalue"].ToString(), out suminvalue);
totalinwardqty += inquantity;
totinwardvalue += suminvalue;
}
// outward (or) issue
cmd = new SqlCommand("SELECT SUM(suboutwarddetails.quantity) AS issueqty,suboutwarddetails.perunit,SUM(suboutwarddetails.quantity*suboutwarddetails.perunit) AS outwardvalue,productmaster.productname, outwarddetails.inwarddate AS outwarddate FROM outwarddetails INNER JOIN suboutwarddetails ON outwarddetails.sno = suboutwarddetails.in_refno INNER JOIN productmaster ON suboutwarddetails.productid = productmaster.productid WHERE (productmaster.productname = @ProductID) AND (outwarddetails.inwarddate BETWEEN @d1 AND @d2) AND (outwarddetails.branchid=@branchid) AND (suboutwarddetails.quantity>0) AND (outwarddetails.status='A') GROUP BY productmaster.productname,suboutwarddetails.perunit, outwarddetails.inwarddate");
cmd.Parameters.Add("@d1", GetLowDate(fromdate));
cmd.Parameters.Add("@d2", GetLowDate(todate));
cmd.Parameters.Add("@ProductID", productid);
cmd.Parameters.Add("@branchid", branchid);
DataTable dtoutward = SalesDB.SelectQuery(cmd).Tables[0];
double outquantity = 0; string outwarddate; double sumoutvalue = 0;
foreach (DataRow drout in dtoutward.Rows)
{
double.TryParse(drout["issueqty"].ToString(), out outquantity);
double.TryParse(drout["outwardvalue"].ToString(), out sumoutvalue);
totalissueqty += outquantity;
totoutwardvalue += sumoutvalue;
}
// stocktransfer
cmd = new SqlCommand("SELECT SUM(stocktransfersubdetails.quantity) AS transferqty,stocktransfersubdetails.price, productmaster.productname,SUM(stocktransfersubdetails.quantity*stocktransfersubdetails.price) AS transfervalue, stocktransferdetails.invoicedate FROM stocktransferdetails INNER JOIN stocktransfersubdetails ON stocktransferdetails.sno = stocktransfersubdetails.stock_refno INNER JOIN productmaster ON stocktransfersubdetails.productid = productmaster.productid WHERE (productmaster.productname = @ProductID) AND (stocktransferdetails.invoicedate BETWEEN @d1 AND @d2) AND (stocktransferdetails.branchid=@branchid) and (stocktransfersubdetails.quantity>0) and (stocktransferdetails.status='A') GROUP BY productmaster.productname,stocktransfersubdetails.price,stocktransfersubdetails.price, stocktransferdetails.invoicedate");
cmd.Parameters.Add("@d1", GetLowDate(fromdate));
cmd.Parameters.Add("@d2", ServerDateCurrentdate);
cmd.Parameters.Add("@ProductID", productid);
cmd.Parameters.Add("@branchid", branchid);
DataTable dttransfer = SalesDB.SelectQuery(cmd).Tables[0];
double transferquantity = 0; string invoicedate; double sumtransfervalue = 0;
foreach (DataRow drtran in dttransfer.Rows)
{
double.TryParse(drtran["transferqty"].ToString(), out transferquantity);
double.TryParse(drtran["transfervalue"].ToString(), out sumtransfervalue);
totaltransferquantity += transferquantity;
tottransfervalue += sumtransfervalue;
// outwarddate = drout["transferqty"].ToString();
}
// returns
cmd = new SqlCommand("SELECT SUM(sub_stores_return.quantity) AS returnqty,sub_stores_return.perunit,SUM(sub_stores_return.quantity*sub_stores_return.perunit) AS returnvalue, productmaster.productname, stores_return.doe FROM stores_return INNER JOIN sub_stores_return ON stores_return.sno = sub_stores_return.storesreturn_sno INNER JOIN productmaster ON sub_stores_return.productid = productmaster.productid WHERE (productmaster.productname = @ProductID) AND (stores_return.doe BETWEEN @d1 AND @d2) AND (stores_return.branchid=@branchid) AND (sub_stores_return.quantity>0) GROUP BY productmaster.productname, stores_return.doe,sub_stores_return.perunit");
cmd.Parameters.Add("@d1", GetLowDate(fromdate));
cmd.Parameters.Add("@d2", ServerDateCurrentdate);
cmd.Parameters.Add("@ProductID", productid);
cmd.Parameters.Add("@branchid", branchid);
DataTable dtreturn = SalesDB.SelectQuery(cmd).Tables[0];
double Returnqty = 0; double sumreturnvalue = 0;
foreach (DataRow drreturn in dtreturn.Rows)
{
double.TryParse(drreturn["returnqty"].ToString(), out Returnqty);
double.TryParse(drreturn["returnvalue"].ToString(), out sumreturnvalue);
totalReturnqty += Returnqty;
totreturnvalue += sumreturnvalue;
}
cmd = new SqlCommand("SELECT Distinct stockclosingdetails.productid,stockclosingdetails.qty, stockclosingdetails.price, (stockclosingdetails.qty * stockclosingdetails.price) opvalue, stockclosingdetails.branchid, productmaster.productname FROM stockclosingdetails INNER JOIN productmaster ON stockclosingdetails.productid = productmaster.productid WHERE (productmaster.productname = @productid) AND (stockclosingdetails.branchid = @branchid) AND (stockclosingdetails.doe BETWEEN @d11 and @d12) GROUP BY stockclosingdetails.productid,stockclosingdetails.qty, stockclosingdetails.branchid, stockclosingdetails.price, productmaster.productname");
cmd.Parameters.Add("@productid", productid);
cmd.Parameters.Add("@branchid", branchid);
cmd.Parameters.Add("@d11", GetLowDate(fromdate));
cmd.Parameters.Add("@d12", GetHighDate(fromdate));
DataTable dtProduct_presentopp = vdm.SelectQuery(cmd).Tables[0];
double productpresentopp = 0; double productpresenvalue = 0; double openingprice = 0;
if (dtProduct_presentopp.Rows.Count > 0)
{
double.TryParse(dtProduct_presentopp.Rows[0]["qty"].ToString(), out productpresentopp);
double.TryParse(dtProduct_presentopp.Rows[0]["opvalue"].ToString(), out productpresenvalue);
double.TryParse(dtProduct_presentopp.Rows[0]["price"].ToString(), out openingprice);
}
int i = 1;
double openbal = productpresentopp;
double opevalue = productpresenvalue;
double openprice = openingprice;
double opbal = productpresentopp;
//double openbal = (productpresentopp + totalissueqty + totaltransferquantity) - (totalinwardqty + totalReturnqty);
// double opevalue = (productpresenvalue + totoutwardvalue + tottransfervalue) - (totinwardvalue + totreturnvalue);
double oprate = 0; double rate = 0;
double Prevqty = 0; double Prevalue = 0; double recipttotal = 0; double prevprice = 0; double outreciptprice = 0; double reciptprice = 0; double diffoutvalue = 0; double diffob = 0; double transdiffoutvalue = 0; double transdiffob = 0;
for (int j = 0; j < NoOfdays; j++)
{
double inwardquantity = 0;
double outwarquantity = 0;
double inwardvalue = 0;
double outwardvalue = 0;
double transfervalue = 0;
double transoutwardvalue = 0;
double returnvalue = 0;
DataRow newrow = Report.NewRow();
newrow["Sno"] = i;
string dtcount = fromdate.AddDays(j).ToString();
DateTime dtDOE = Convert.ToDateTime(dtcount);
string dtdate1 = dtDOE.AddDays(-1).ToString();
DateTime dtDOE1 = Convert.ToDateTime(dtdate1).AddDays(1);
string ChangedTime1 = dtDOE1.ToString("MM/dd/yyyy", CultureInfo.InvariantCulture);
DateTime dateinward = DateTime.ParseExact(ChangedTime1, "MM/dd/yyyy", CultureInfo.InvariantCulture);
dateinward = dateinward.Date;
newrow["Date"] = dtDOE1.ToString("dd-MM-yyyy"); //ChangedTime1;
DataTable dtin = new DataTable();
DataRow[] drrin = dtinward.Select("inwarddate = '" + dateinward + "'");
if (drrin.Length > 0)
{
dtin = drrin.CopyToDataTable();
}
double indateqty = 0; double invalue = 0; double inprice = 0; double tax = 0; double edtax = 0; double recprice = 0;
foreach (DataRow drinward in dtin.Rows)
{
double.TryParse(drinward["inwardqty"].ToString(), out inwardquantity);
double.TryParse(drinward["inwardvalue"].ToString(), out inwardvalue);
double.TryParse(drinward["perunit"].ToString(), out inprice);
indateqty += inwardquantity;
invalue += inwardvalue;
if (inprice > 0)
{
double.TryParse(drinward["tax"].ToString(), out tax);
double.TryParse(drinward["edtax"].ToString(), out edtax);
double edvalue = (inprice * edtax) / 100;
double taxvalue = (inprice * tax) / 100;
reciptprice = inprice + edvalue + taxvalue;
reciptprice = Math.Round(reciptprice, 2);
recprice = reciptprice;
inwardvalue = inwardquantity * reciptprice;
inwardvalue = Math.Round(inwardvalue, 2);
}
}
DataTable dtout = new DataTable();
DataRow[] drrout = dtoutward.Select("outwarddate ='" + dateinward + "'");
if (drrout.Length > 0)
{
dtout = drrout.CopyToDataTable();
}
double outdateqty = 0; double outvalue = 0; double outprice = 0; double issueqty1 = 0; double issueprice = 0; double issuevalue1 = 0; double diffobvalue = 0;
foreach (DataRow droutward in dtout.Rows)
{
double.TryParse(droutward["issueqty"].ToString(), out outwarquantity);
double.TryParse(droutward["perunit"].ToString(), out outprice);
outdateqty += outwarquantity;
recipttotal += outwarquantity;
if (recipttotal > opbal)
{
if (opbal > 0)
{
diffob = opbal - outwarquantity;
}
else
{
diffob = outwarquantity;
}
diffoutvalue = outwarquantity - diffob;
issueprice = reciptprice;
issueqty1 = diffoutvalue;
outreciptprice = issueprice;
if (issueqty1 < 0)
{
diffob = outwarquantity;
issueprice = 0;
issueqty1 = 0;
outprice = reciptprice;
}
recipttotal = 0;
if (opbal > 0)
{
diffobvalue = diffob * outprice;
}
else
{
diffobvalue = diffob * reciptprice;
outprice = reciptprice;
}
outwardvalue = diffob * reciptprice;
opbal = Prevqty;
}
else
{
if (outreciptprice > 0)
{
outprice = reciptprice;
outwardvalue = outwarquantity * outprice;
}
else
{
outwardvalue = outwarquantity * outprice;
}
}
outvalue += outwardvalue;
}
DataTable drtran = new DataTable();
DataRow[] drrtrans = dttransfer.Select("invoicedate ='" + dateinward + "'");
if (drrtrans.Length > 0)
{
drtran = drrtrans.CopyToDataTable();
}
double tranqty = 0; double negative = 0; double outtransvalue = 0; double transoutreciptprice = 0; double transdiffobvalue = 0; double transvalue = 0; double transferprice = 0; double transoutprice = 0; double transissueprice = 0; double transissueqty1 = 0;
foreach (DataRow drr1tran in drtran.Rows)
{
double.TryParse(drr1tran["transferqty"].ToString(), out tranqty);
double.TryParse(drr1tran["transfervalue"].ToString(), out transfervalue);
double.TryParse(drr1tran["price"].ToString(), out transferprice);
tranqtydateqty += tranqty;
transvalue += transfervalue;
if (tranqtydateqty > opbal)
{
if (opbal > 0)
{
transdiffob = opbal - tranqty;
negative = transdiffob;
if (transdiffob < 0)
{
transdiffob = tranqty - opbal;
}
}
else
{
transdiffob = tranqty;
}
transdiffoutvalue = tranqty - transdiffob;
transissueprice = reciptprice;
transissueqty1 = transdiffoutvalue;
transoutreciptprice = transissueprice;
if (transissueqty1 < 0)
{
transdiffob = tranqty;
issueprice = 0;
issueqty1 = 0;
transferprice = reciptprice;
}
recipttotal = 0;
if (opbal > 0)
{
transdiffobvalue = transdiffob * transferprice;
}
else
{
transdiffobvalue = transdiffob * reciptprice;
transferprice = reciptprice;
}
transoutwardvalue = transdiffob * reciptprice;
opbal = Prevqty;
}
else
{
if (transoutreciptprice > 0)
{
transferprice = reciptprice;
transoutwardvalue = tranqty * transferprice;
}
else
{
transoutwardvalue = tranqty * transferprice;
}
}
outtransvalue += transoutwardvalue;
}
DataTable dttreturn = new DataTable();
DataRow[] drrreturn = dtreturn.Select("doe ='" + dateinward + "'");
if (drrreturn.Length > 0)
{
dttreturn = drrreturn.CopyToDataTable();
}
double returnqty = 0; double retvalue = 0; double returnprice = 0;
foreach (DataRow drrreturn1 in dttreturn.Rows)
{
double.TryParse(drrreturn1["returnqty"].ToString(), out returnqty);
double.TryParse(drrreturn1["returnvalue"].ToString(), out returnvalue);
double.TryParse(drrreturn1["perunit"].ToString(), out returnprice);
returndateqty += returnqty;
retvalue += returnvalue;
}
if (i == 1)
{
openbal = openbal;
opevalue = opevalue;
openingprice = openingprice;
}
else
{
openbal = Prevqty;
opevalue = Prevalue;
openingprice = prevprice;
}
double openamt = 0;
double openval = 0;
double closingqty = 0;
double closingvalue = 0;
double opprice = 0;
productpresentopp = 0;
issuevalue1 = issueqty1 * issueprice;
if (diffobvalue > 0)
{
outwardvalue = diffobvalue;
closingvalue = (opevalue - outwardvalue - transfervalue - issuevalue1) + (inwardvalue + returnvalue);
}
else
{
closingvalue = (opevalue - outwardvalue - transfervalue - issuevalue1) + (inwardvalue + returnvalue);
}
closingqty = (openbal - outdateqty - tranqty) + (indateqty + returnqty);
oprate = closingvalue / closingqty;
newrow["Opp Qty"] = Math.Round(openbal, 2);
openbal = openamt;
newrow["Opp Price"] = Math.Round(openingprice, 2);
openingprice = opprice;
newrow["Opp Value"] = Math.Round(opevalue, 2);
opevalue = openval;
newrow["ReceiptsQty"] = Math.Round(indateqty, 2);
newrow["ReceiptsPrice"] = Math.Round(recprice, 2);
newrow["ReceiptsValue"] = Math.Round(inwardvalue, 2);
newrow["ReturnQty"] = Math.Round(returnqty, 2);
newrow["ReturnPrice"] = returnprice;
newrow["ReturnValue"] = Math.Round(retvalue, 2);
if (diffob > 0)
{
newrow["IssuesQty"] = Math.Round(diffob, 2);
newrow["IssuesValue"] = Math.Round(diffobvalue, 2);
}
else
{
newrow["IssuesQty"] = Math.Round(outdateqty, 2);
newrow["IssuesValue"] = Math.Round(outvalue, 2);
}
newrow["IssuesPrice"] = outprice;
newrow["IssuesQty1"] = issueqty1;
newrow["IssuesPrice1"] = issueprice;
double sum = issueqty1 * issueprice;
newrow["IssuesValue1"] = Math.Round(sum, 2);
if (transdiffob > 0)
{
newrow["TransferQty"] = Math.Round(transdiffob, 2);
double transsum = transdiffob * transferprice;
newrow["TransferValue"] = Math.Round(transsum, 2);
}
else
{
newrow["TransferQty"] = Math.Round(tranqty, 2);
double transsumm = tranqty * transferprice;
newrow["TransferValue"] = Math.Round(transsumm, 2);
}
newrow["TransferPrice"] = Math.Round(transferprice, 2);
newrow["TransferQty1"] = transissueqty1;
newrow["Transferprice1"] = transissueprice;
double transssum = transissueqty1 * transissueprice;
newrow["Transfervale1"] = Math.Round(transssum, 2);
//if (negative > 0)
//{
// if (transdiffob > 0)
// {
// newrow["TransferQty"] = Math.Round(transdiffob, 2);
// double transsum = transdiffob * transferprice;
// newrow["TransferValue"] = Math.Round(transsum, 2);
// }
// else
// {
// newrow["TransferQty"] = Math.Round(tranqty, 2);
// double transsumm = tranqty * transferprice;
// newrow["TransferValue"] = Math.Round(transsumm, 2);
// }
// newrow["TransferPrice"] = Math.Round(transferprice, 2);
// newrow["TransferQty1"] = transissueqty1;
// newrow["Transferprice1"] = transissueprice;
// double transssum = transissueqty1 * transissueprice;
// newrow["Transfervale1"] = Math.Round(transssum, 2);
//}
//else
//{
//}
if (closingvalue < 0)
{
closingqty = 0;
oprate = 0;
closingvalue = 0;
}
newrow["Clo Qty"] = Math.Round(closingqty, 2);
string cval = oprate.ToString();
if (cval == "NaN")
{
oprate = 0;
}
newrow["Clo Price"] = Math.Round(oprate, 2);
newrow["Clo Value"] = Math.Round(closingvalue, 2);
Prevqty = closingqty;
Prevalue = closingvalue;
prevprice = oprate;
issueqty1 = 0;
transissueqty1 = 0;
transissueprice = 0;
issueprice = 0;
diffob = 0;
diffobvalue = 0;
transdiffob = 0;
inwardvalue = 0;
recprice = 0;
Report.Rows.Add(newrow);
i++;
}
DataRow newTotal = Report.NewRow();
newTotal["Date"] = "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);
newTotal[dc.ToString()] = val;
}
}
Report.Rows.Add(newTotal);
grdReports.DataSource = Report;
grdReports.DataBind();
Session["xportdata"] = Report;
Session["filename"] = "Item Ledger";
Session["Address"] = "Punabaka";
hidepanel.Visible = true;
ScriptManager.RegisterStartupScript(Page, GetType(), "JsStatus", "get_product_details();", true);
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
hidepanel.Visible = false;
}
}
protected void btn_save_click(object sender, EventArgs e)
{
try
{
vdm = new SalesDBManager();
DateTime CreateDate = SalesDBManager.GetTime(vdm.conn);
SAPdbmanger SAPvdm = new SAPdbmanger();
DateTime fromdate = DateTime.Now;
DataTable dt = (DataTable)Session["xportdata"];
string[] dateFromstrig = dtp_FromDate.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);
}
}
fromdate = fromdate;
foreach (DataRow dr in dt.Rows)
{
double amount = 10;
string B1Upload = "N";
string Processed = "N";
string ledgercode = dr["Item Code"].ToString();
if (ledgercode == "")
{
}
else
{
sqlcmd = new SqlCommand("SELECT CreateDate, PostingDate, DocDate, ReferenceNo AS Processed, Series FROM EMROWTR WHERE (PostingDate BETWEEN @d1 AND @d2) AND (ItemCode=@ItemCode) AND (ReferenceNo = @ReferenceNo)");
sqlcmd.Parameters.Add("@d1", GetLowDate(fromdate));
sqlcmd.Parameters.Add("@d2", GetHighDate(fromdate));
sqlcmd.Parameters.Add("@ReferenceNo", dr["ReferenceNo"].ToString());
sqlcmd.Parameters.Add("@ItemCode", dr["Item Code"].ToString());
DataTable dtST = SAPvdm.SelectQuery(sqlcmd).Tables[0];
if (dtST.Rows.Count > 0)
{
}
else
{
sqlcmd = new SqlCommand("Insert into EMROWTR (CreateDate,PostingDate,DocDate,ReferenceNo,FromWhsCode,ToWhsCode,ItemCode,ItemName,Quantity,Price,OcrCode,OcrCode2,Remarks,B1Upload,Processed,series) values (@CreateDate,@PostingDate,@DocDate,@ReferenceNo,@FromWhsCode,@ToWhsCode,@ItemCode,@ItemName,@Quantity,@Price,@OcrCode,@OcrCode2,@Remarks,@B1Upload,@Processed,@series)");
sqlcmd.Parameters.Add("@CreateDate", GetLowDate(fromdate));
sqlcmd.Parameters.Add("@PostingDate", GetLowDate(fromdate));
sqlcmd.Parameters.Add("@DocDate", GetLowDate(fromdate));
sqlcmd.Parameters.Add("@ReferenceNo", dr["ReferenceNo"].ToString());
string fromwhcode = "SVDSPP02";
sqlcmd.Parameters.Add("@FromWhsCode", fromwhcode);
sqlcmd.Parameters.Add("@ToWhsCode", dr["Wh Code"].ToString());
sqlcmd.Parameters.Add("@ItemCode", dr["Item Code"].ToString());
sqlcmd.Parameters.Add("@ItemName", dr["Item Name"].ToString());
sqlcmd.Parameters.Add("@Quantity", dr["Qty"].ToString());
sqlcmd.Parameters.Add("@Price", dr["Price"].ToString());
sqlcmd.Parameters.Add("@OcrCode", fromwhcode);
sqlcmd.Parameters.Add("@OcrCode2", "");
sqlcmd.Parameters.Add("@Remarks", dr["Narration"].ToString());
sqlcmd.Parameters.Add("@B1Upload", B1Upload);
sqlcmd.Parameters.Add("@Processed", Processed);
string series = "241";
sqlcmd.Parameters.Add("@series", series);
SAPvdm.insert(sqlcmd);
}
}
}
DataTable dtempty = new DataTable();
grdReports.DataSource = dtempty;
grdReports.DataBind();
lblmsg.Text = "Successfully Saved";
}
catch (Exception ex)
{
lblmsg.Text = ex.ToString();
}
}