void getdet()
{
try
{
vdm = new VehicleDBMgr();
lblmsg.Text = "";
DateTime ServerDateCurrentdate = VehicleDBMgr.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 MySqlCommand("SELECT sno, BranchName, BranchCode FROM branchdata WHERE (sno = @BranchID)");
cmd.Parameters.AddWithValue("@BranchID", Session["branch"]);
DataTable dtCode = vdm.SelectQuery(cmd).Tables[0];
string Receiptid = "";
//cmd = new MySqlCommand("SELECT Sno, BranchId, ReceivedFrom, AgentID, Empid, Amountpayable, AmountPaid, DOE, Create_by, Modified_by, Remarks, OppBal, dispatchid, Receipt FROM cashreceipts WHERE (BranchId = @BranchID) AND (Receipt = @Receipt)");
//cmd.Parameters.AddWithValue("@BranchID", Session["branch"]);
//cmd.Parameters.AddWithValue("@Receipt", txtReceiptNo.Text);
//DataTable dtReceipt = vdm.SelectQuery(cmd).Tables[0];
//if (dtReceipt.Rows.Count > 0)
//{
//string Status = dtReceipt.Rows[0]["ReceivedFrom"].ToString();
string Status = txthiddentype.Value;
if (Status == "SalesMen")
{
cmd = new MySqlCommand("SELECT cashreceipts.Remarks,DATE_FORMAT(cashreceipts.DOE, '%d %b %y') AS DOE, cashreceipts.Sno AS RefNo, cashreceipts.Receipt, dispatch.DispName, empmanage.EmpName, cashreceipts.AmountPaid,cashreceipts.GroupRecieptNo FROM cashreceipts INNER JOIN dispatch ON cashreceipts.dispatchid = dispatch.sno INNER JOIN empmanage ON cashreceipts.Empid = empmanage.Sno WHERE (cashreceipts.Sno = @Receipt)");
cmd.Parameters.AddWithValue("@BranchID", Session["branch"]);
cmd.Parameters.AddWithValue("@Receipt", txtReceiptNo.Text);
DataTable dtReceiptBook = vdm.SelectQuery(cmd).Tables[0];
if (dtReceiptBook.Rows.Count > 0)
{
//lblreceiptno.Text = txtReceiptNo.Text;
//lblreceiptno.Text = dtReceiptBook.Rows[0]["GroupRecieptNo"].ToString();
Receiptid = dtCode.Rows[0]["BranchCode"].ToString() + "/ROUTE_RCPT/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "/" + dtReceiptBook.Rows[0]["GroupRecieptNo"].ToString();
lblreceiptno.Text = Receiptid;
lblDate.Text = dtReceiptBook.Rows[0]["DOE"].ToString();
lblPayCash.Text = dtReceiptBook.Rows[0]["DispName"].ToString();
lbltowards.Text = "MILK";
lblAmount.Text = dtReceiptBook.Rows[0]["AmountPaid"].ToString();
lblCheque.Text = "Cash";
lblChequeDate.Text = dtReceiptBook.Rows[0]["DOE"].ToString();
lblRemarks.Text = dtReceiptBook.Rows[0]["Remarks"].ToString();
}
}
if (Status == "Agent" || Status == "Cash")
{
//cmd = new MySqlCommand("SELECT cashreceipts.Remarks,cashreceipts.Sno, DATE_FORMAT(cashreceipts.DOE, '%d %b %y') AS DOE, cashreceipts.Sno AS RefNo,cashreceipts.Receipt,cashreceipts.PaymentStatus,cashreceipts.ChequeNo, branchdata.BranchName, cashreceipts.AmountPaid FROM cashreceipts INNER JOIN branchdata ON cashreceipts.AgentID = branchdata.sno WHERE (cashreceipts.BranchId = @BranchID) AND (cashreceipts.Receipt = @Receipt)");
cmd = new MySqlCommand("SELECT cashreceipts.Remarks, cashreceipts.Sno, DATE_FORMAT(cashreceipts.DOE, '%d %b %y') AS DOE, cashreceipts.Sno AS RefNo, cashreceipts.Receipt,cashreceipts.PaymentStatus, cashreceipts.ChequeNo, branchdata.BranchName, cashreceipts.AmountPaid, branchroutes.RouteName FROM cashreceipts INNER JOIN branchdata ON cashreceipts.AgentID = branchdata.sno INNER JOIN branchroutesubtable ON branchdata.sno = branchroutesubtable.BranchID INNER JOIN branchroutes ON branchroutesubtable.RefNo = branchroutes.Sno WHERE (cashreceipts.Sno = @Receipt)");
cmd.Parameters.AddWithValue("@BranchID", Session["branch"]);
cmd.Parameters.AddWithValue("@Receipt", txtReceiptNo.Text);
DataTable dtReceiptBook = vdm.SelectQuery(cmd).Tables[0];
if (dtReceiptBook.Rows.Count > 0)
{
//lblreceiptno.Text = txtReceiptNo.Text;
//lblreceiptno.Text = dtReceiptBook.Rows[0]["Receipt"].ToString();
Receiptid = dtCode.Rows[0]["BranchCode"].ToString() + "/AGENT_RCPT/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "/" + dtReceiptBook.Rows[0]["Receipt"].ToString();
lblreceiptno.Text = Receiptid;
lblDate.Text = dtReceiptBook.Rows[0]["DOE"].ToString();
string Branch = Session["branch"].ToString();
if (Branch == "174")
{
lblPayCash.Text = dtReceiptBook.Rows[0]["BranchName"].ToString() + "(" + dtReceiptBook.Rows[0]["RouteName"].ToString() + ")";
}
else
{
lblPayCash.Text = dtReceiptBook.Rows[0]["BranchName"].ToString();
}
string PaymentStatus = dtReceiptBook.Rows[0]["PaymentStatus"].ToString();
if (PaymentStatus == "Cheque")
{
lblCheque.Text = dtReceiptBook.Rows[0]["ChequeNo"].ToString();
}
else
{
lblCheque.Text = "Cash";
}
lbltowards.Text = "MILK";
lblAmount.Text = dtReceiptBook.Rows[0]["AmountPaid"].ToString();
lblChequeDate.Text = dtReceiptBook.Rows[0]["DOE"].ToString();
lblRemarks.Text = dtReceiptBook.Rows[0]["Remarks"].ToString();
}
}
if (Status == "Others" || Status == "freezer deposit" || Status == "Journal Voucher" || Status == "Cheque" || Status == "Bank Transfer")
{
cmd = new MySqlCommand("SELECT DATE_FORMAT(DOE, '%d %b %y') AS DOE, Receiptno, Name, Amount,remarks,CollectionType,PaymentType FROM cashcollections WHERE (Sno = @Receipt)");
cmd.Parameters.AddWithValue("@BranchID", Session["branch"]);
cmd.Parameters.AddWithValue("@Receipt", txtReceiptNo.Text);
DataTable dtReceiptBook = vdm.SelectQuery(cmd).Tables[0];
if (dtReceiptBook.Rows.Count > 0)
{
//lblreceiptno.Text = txtReceiptNo.Text;
//lblreceiptno.Text = dtReceiptBook.Rows[0]["Receiptno"].ToString();
Receiptid = dtCode.Rows[0]["BranchCode"].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 = "Others";
lbltowards.Text = dtReceiptBook.Rows[0]["PaymentType"].ToString();
lblAmount.Text = dtReceiptBook.Rows[0]["Amount"].ToString();
//lblCheque.Text = "Cash";
lblCheque.Text = dtReceiptBook.Rows[0]["CollectionType"].ToString();
lblChequeDate.Text = dtReceiptBook.Rows[0]["DOE"].ToString();
lblRemarks.Text = dtReceiptBook.Rows[0]["remarks"].ToString();
}
else
{
cmd = new MySqlCommand("SELECT branchdata.BranchName,collections.Remarks, DATE_FORMAT(collections.PaidDate, '%d %b %y') AS DOE, collections.PaymentType, collections.AmountPaid, collections.Sno, collections.ReceiptNo FROM collections INNER JOIN branchmappingtable ON collections.Branchid = branchmappingtable.SubBranch INNER JOIN branchdata ON collections.Branchid = branchdata.sno WHERE (collections.Sno=@ReceiptNo) ORDER BY branchdata.BranchName");
cmd.Parameters.AddWithValue("@ReceiptNo", txtReceiptNo.Text);
DataTable dtReceip = vdm.SelectQuery(cmd).Tables[0];
if (dtReceip.Rows.Count > 0)
{
//lblreceiptno.Text = txtReceiptNo.Text;
//lblreceiptno.Text = dtReceiptBook.Rows[0]["Receiptno"].ToString();
Receiptid = dtCode.Rows[0]["BranchCode"].ToString() + "/OTH_RCPT/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "/" + dtReceip.Rows[0]["Sno"].ToString();
lblreceiptno.Text = Receiptid;
lblDate.Text = dtReceip.Rows[0]["DOE"].ToString();
lblPayCash.Text = dtReceip.Rows[0]["BranchName"].ToString();
//lbltowards.Text = "Others";
lbltowards.Text = dtReceip.Rows[0]["PaymentType"].ToString();
lblAmount.Text = dtReceip.Rows[0]["AmountPaid"].ToString();
//lblCheque.Text = "Cash";
lblCheque.Text = dtReceip.Rows[0]["PaymentType"].ToString();
lblChequeDate.Text = dtReceip.Rows[0]["DOE"].ToString();
lblRemarks.Text = dtReceip.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);
//string InWords = "";
//if (Num >= 1 && Num < 20)
// InWords += Below20[Num];
//if (Num >= 20 && Num <= 99)
// InWords += Below100[Num / 10] + Below20[Num % 10];
//if (Num >= 100 && Num <= 999)
// InWords += NumToWordBD(Num / 100) + " Hundred " + NumToWordBD(Num % 100);
//if (Num >= 1000 && Num <= 99999)
// InWords += NumToWordBD(Num / 1000) + " Thousand " + NumToWordBD(Num % 1000);
//if (Num >= 100000 && Num <= 9999999)
// InWords += NumToWordBD(Num / 100000) + " Lac " + NumToWordBD(Num % 100000);
//if (Num >= 10000000)
// InWords += NumToWordBD(Num / 10000000) + " Crore " + NumToWordBD(Num % 10000000);
////return InWords;
// NumToWordBD(Num);
lblRupess.Text = NumToWordBD(Num) + " Rupees Only";
//}
//else
//{
// lblmsg.Text = "No Receipt were found";
//}
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
}
}
void GetReport()
{
try
{
lblmsg.Text = "";
pnlHide.Visible = true;
Report = new DataTable();
Session["RouteName"] = ddlSalesOffice.SelectedItem.Text;
Session["xporttype"] = "TallySales";
Session["IDate"] = DateTime.Now.AddDays(1).ToString("dd/MM/yyyy");
vdm = new VehicleDBMgr();
DateTime fromdate = DateTime.Now;
string[] dateFromstrig = txtFromdate.Text.Split(' ');
if (dateFromstrig.Length > 1)
{
if (dateFromstrig[0].Split('-').Length > 0)
{
string[] dates = dateFromstrig[0].Split('-');
string[] times = dateFromstrig[1].Split(':');
fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
lbl_selfromdate.Text = fromdate.ToString("dd/MM/yyyy");
lblRoutName.Text = ddlSalesOffice.SelectedItem.Text;
Session["filename"] = ddlSalesOffice.SelectedItem.Text + " Tally Sales " + fromdate.ToString("dd/MM/yyyy");
if (ddltype.SelectedValue == "Non Tax")
{
cmd = new MySqlCommand("SELECT tripdata.Sno AS TripId, DATE_FORMAT(tripdata.AssignDate, '%d %b %y') AS AssignDate, tripdata.Permissions, tripdata.VehicleNo,dispatch.DispName AS DispatchName, empmanage.EmpName AS Employee FROM tripdata INNER JOIN empmanage ON tripdata.EmpId = empmanage.Sno INNER JOIN triproutes ON tripdata.Sno = triproutes.Tripdata_sno INNER JOIN dispatch ON triproutes.RouteID = dispatch.sno INNER JOIN branchdata ON empmanage.Branch = branchdata.sno WHERE (tripdata.DC_Type = 1) AND (tripdata.Status <> 'c') AND (tripdata.AssignDate BETWEEN @d1 AND @d2) AND (tripdata.Permissions LIKE '%D%') AND (empmanage.Branch = @BranchID)");
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@d2", GetHighDate(fromdate));
DataTable dttripid = vdm.SelectQuery(cmd).Tables[0];
foreach (DataRow drdc in dttripid.Rows)
{
cmd = new MySqlCommand("SELECT products_category.categorycode, products_category.sno AS categoryid, productsdata.Itemcode, products_category.tcategory, branchdata.BranchName,branchdata.whcode, branchdata.sno AS BSno, indent.IndentType, indents_subtable.DeliveryQty, indents_subtable.UnitCost, productsdata.tproduct,productsdata.ProductName, productsdata.Units, productsdata.sno AS productsno, products_category.tcategory AS Expr1, tripdata.to_adr_Id,addresstable.companyname AS tBranchName, IFNULL(branchproducts.VatPercent, 0) AS VatPercent FROM (SELECT IndentNo, Branch_id, I_date, Status, IndentType FROM indents WHERE (I_date BETWEEN @d1 AND @d2) AND (Status <> 'D')) indent INNER JOIN branchdata ON indent.Branch_id = branchdata.sno INNER JOIN tripdata ON tripdata.BranchID = branchdata.sno INNER JOIN addresstable ON addresstable.sno = tripdata.to_adr_Id INNER JOIN indents_subtable ON indent.IndentNo = indents_subtable.IndentNo INNER JOIN productsdata ON indents_subtable.Product_sno = productsdata.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno INNER JOIN branchproducts ON branchdata.sno = branchproducts.branch_sno AND productsdata.sno = branchproducts.product_sno WHERE (indents_subtable.DeliveryQty <> 0) AND (tripdata.Sno = @BranchID) GROUP BY productsdata.sno, BSno, branchproducts.VatPercent ORDER BY branchdata.BranchName");
cmd.Parameters.AddWithValue("@BranchID", drdc["TripId"].ToString());
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@d2", GetHighDate(fromdate));
DataTable dtothntax = vdm.SelectQuery(cmd).Tables[0];
DateTime ReportDate = VehicleDBMgr.GetTime(vdm.conn);
DateTime dtapril = new DateTime();
DateTime dtmarch = new DateTime();
int currentyear = ReportDate.Year;
int nextyear = ReportDate.Year + 1;
if (ReportDate.Month > 3)
{
string apr = "4/1/" + currentyear;
dtapril = DateTime.Parse(apr);
string march = "3/31/" + nextyear;
dtmarch = DateTime.Parse(march);
}
if (ReportDate.Month <= 3)
{
string apr = "4/1/" + (currentyear - 1);
dtapril = DateTime.Parse(apr);
string march = "3/31/" + (nextyear - 1);
dtmarch = DateTime.Parse(march);
}
if (dtothntax.Rows.Count > 0)
{
Report = new DataTable();
Report.Columns.Add("Ledger Type");
Report.Columns.Add("Customer Code");
Report.Columns.Add("Customer Name");
Report.Columns.Add("Invoice Date");
Report.Columns.Add("Invoce No");
Report.Columns.Add("Item Code");
Report.Columns.Add("Item Name");
Report.Columns.Add("Qty");
Report.Columns.Add("Rate");
Report.Columns.Add("Tax Code");
Report.Columns.Add("Sales Type");
Report.Columns.Add("Category Code");
Report.Columns.Add("vat_percent");
Report.Columns.Add("TaxAmount");
Report.Columns.Add("Rounding Off");
Report.Columns.Add("WH Code");
Report.Columns.Add("Inv Value");
Report.Columns.Add("Net Value");
Report.Columns.Add("Narration");
int i = 1;
cmd = new MySqlCommand("SELECT sno, BranchName, statename,tax,ntax FROM branchdata WHERE (sno = @BranchID)");
if (Session["salestype"].ToString() == "Plant")
{
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
}
else
{
cmd.Parameters.AddWithValue("@BranchID", Session["branch"]);
}
DataTable dtstatename = vdm.SelectQuery(cmd).Tables[0];
string statename = "";
string tax = "";
string ntax = "";
if (dtstatename.Rows.Count > 0)
{
statename = dtstatename.Rows[0]["statename"].ToString();
tax = dtstatename.Rows[0]["tax"].ToString();
ntax = dtstatename.Rows[0]["ntax"].ToString();
}
foreach (DataRow branch in dtothntax.Rows)
{
DataRow newrow = Report.NewRow();
cmd = new MySqlCommand("SELECT agentdcno FROM agentdc WHERE (BranchID = @BranchID) AND (IndDate BETWEEN @d1 AND @d2)");
cmd.Parameters.AddWithValue("@BranchID", branch["BSno"].ToString());
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate.AddDays(-1)));
cmd.Parameters.AddWithValue("@d2", GetHighDate(fromdate.AddDays(-1)));
DataTable dtsubDc = vdm.SelectQuery(cmd).Tables[0];
string DCNO = "";
if (dtsubDc.Rows.Count > 0)
{
DCNO = dtsubDc.Rows[0]["agentdcno"].ToString();
}
if (branch["vatpercent"].ToString() == "0")
{
newrow["WH Code"] = branch["whcode"].ToString();
}
cmd = new MySqlCommand("SELECT sno, companyname, buildingaddress, street, mandal, district, state, pin, tin, cst, email, panno, customercode FROM addresstable WHERE (sno = @to_adr_Id)");
cmd.Parameters.AddWithValue("@to_adr_Id", branch["to_adr_Id"].ToString());
DataTable dtcustomer = vdm.SelectQuery(cmd).Tables[0];
newrow["Customer Name"] = dtcustomer.Rows[0]["companyname"].ToString();
newrow["Customer Code"] = dtcustomer.Rows[0]["customercode"].ToString();
double Roundingoff = 0;
int countdc = 0;
int.TryParse(DCNO, out countdc);
if (countdc < 10)
{
DCNO = "00" + DCNO;
}
if (countdc > 10 && countdc < 99)
{
DCNO = "0" + DCNO;
}
if (ddlSalesOffice.SelectedValue == "174")
{
DCNO = "CHN/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "NT/" + DCNO;
}
if (ddlSalesOffice.SelectedValue == "4607")
{
DCNO = "MPK/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "NT/" + DCNO;
}
if (ddlSalesOffice.SelectedValue == "271")
{
DCNO = "NLR/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "NT/" + DCNO;
}
if (ddlSalesOffice.SelectedValue == "285")
{
if (branch["BSno"].ToString() == "2624")
{
DCNO = "PLR/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "NT/" + DCNO;
}
else
{
DCNO = "TPT/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "NT/" + DCNO;
}
}
if (ddlSalesOffice.SelectedValue == "306")
{
DCNO = "KANCHI/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "NT/" + DCNO;
}
if (ddlSalesOffice.SelectedValue == "2749")
{
if (branch["sno"].ToString() == "3781")
{
DCNO = "TDP/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "NT/" + DCNO;
}
else
{
DCNO = "MDPL/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "NT/" + DCNO;
}
}
if (ddlSalesOffice.SelectedValue == "3928")
{
DCNO = "AB/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "NT/" + DCNO;
}
if (ddlSalesOffice.SelectedValue == "2909")
{
DCNO = "VLR/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "NT/" + DCNO;
}
if (ddlSalesOffice.SelectedValue == "538")
{
DCNO = "BANG/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "NT/" + DCNO;
}
if (ddlSalesOffice.SelectedValue == "159")
{
DCNO = "HYD/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "NT/" + DCNO;
}
if (ddlSalesOffice.SelectedValue == "570")
{
DCNO = "VJD/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "NT/" + DCNO;
}
if (ddlSalesOffice.SelectedValue == "457")
{
DCNO = "WNGL/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "NT/" + DCNO;
}
if (ddlSalesOffice.SelectedValue == "158" || ddlSalesOffice.SelectedValue == "572")
{
DCNO = "WYRA/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "NT/" + DCNO;
}
if (ddlSalesOffice.SelectedValue == "282")
{
DCNO = "KLH/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "NT/" + DCNO;
}
if (ddlSalesOffice.SelectedValue == "458")
{
DCNO = "KMM/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "NT/" + DCNO;
}
if (ddlSalesOffice.SelectedValue == "3559")
{
DCNO = "CTR/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "NT/" + DCNO;
}
if (branch["vatpercent"].ToString() == "0")
{
newrow["Invoce No"] = DCNO;
newrow["Invoice Date"] = fromdate.ToString("dd-MMM-yyyy");
newrow["Item Name"] = branch["tProduct"].ToString();
newrow["Item Code"] = branch["Itemcode"].ToString();
double vatpercent = 0;
double.TryParse(branch["vatpercent"].ToString(), out vatpercent);
string tcategory = "";
string TaxCode = "EXEMPT";
if (vatpercent == null || vatpercent == 0.0)
{
tcategory = branch["tcategory"].ToString();
string category = branch["tcategory"].ToString();
if (category == "G.Sale Of Milk" || category == "G.Sale Of Curd " || category == "Sale Of Buttermilk" || category == "Sale Of Lassi" || category == "Sale Of Butter")
{
tcategory = branch["tcategory"].ToString();
TaxCode = "EXEMPT";
newrow["Sales Type"] = ntax;
}
else
{
tcategory = branch["tcategory"].ToString();
}
}
newrow["Category Code"] = branch["categorycode"].ToString();
newrow["Tax Code"] = TaxCode.ToString();
newrow["Ledger Type"] = tcategory.ToString();
if (branch["tBranchName"].ToString() == "" || branch["DeliveryQty"].ToString() == "" || branch["DeliveryQty"].ToString() == "0")
{
}
else
{
double percent = 0;
newrow["Qty"] = branch["DeliveryQty"].ToString();
double UnitCost = 0;
double Unitprice = 0;
double.TryParse(branch["UnitCost"].ToString(), out UnitCost);
Unitprice = UnitCost;
double.TryParse(branch["vatpercent"].ToString(), out vatpercent);
if (vatpercent == 0.0)
{
newrow["Rate"] = UnitCost.ToString();
}
else
{
percent = vatpercent / 100;
percent = percent + 1;
UnitCost = UnitCost / percent;
UnitCost = Math.Round(UnitCost, 2);
newrow["Rate"] = UnitCost.ToString();
}
newrow["vat_percent"] = vatpercent.ToString();
newrow["Rounding Off"] = Roundingoff;
double invval = 0;
double qty = 0;
double.TryParse(branch["DeliveryQty"].ToString(), out qty);
double taxval = 0;
if (vatpercent == 0.0)
{
invval = qty * UnitCost;
}
else
{
double diffcost = 0;
diffcost = Unitprice - UnitCost;
taxval = qty * diffcost;
invval = qty * UnitCost;
}
invval = Math.Round(invval, 2);
taxval = Math.Round(taxval, 2);
newrow["TaxAmount"] = taxval;
double netvalue = 0;
netvalue = invval + taxval;
netvalue = Math.Round(netvalue, 2);
newrow["Inv Value"] = invval;
newrow["Net Value"] = netvalue;
newrow["Narration"] = "Being the sale of milk to " + branch["tBranchName"].ToString() + " vide DC No " + DCNO + ",DC Date " + fromdate.ToString("dd/MM/yyyy") + ",Emp Name " + Session["EmpName"].ToString();
Report.Rows.Add(newrow);
i++;
}
}
}
grdReports.DataSource = Report;
grdReports.DataBind();
Session["xportdata"] = Report;
}
else
{
pnlHide.Visible = false;
lblmsg.Text = "No Data Found";
grdReports.DataSource = Report;
grdReports.DataBind();
}
}
}
else
{
DateTime ReportDate = VehicleDBMgr.GetTime(vdm.conn);
DateTime dtapril = new DateTime();
DateTime dtmarch = new DateTime();
int currentyear = ReportDate.Year;
int nextyear = ReportDate.Year + 1;
if (ReportDate.Month > 3)
{
string apr = "4/1/" + currentyear;
dtapril = DateTime.Parse(apr);
string march = "3/31/" + nextyear;
dtmarch = DateTime.Parse(march);
}
if (ReportDate.Month <= 3)
{
string apr = "4/1/" + (currentyear - 1);
dtapril = DateTime.Parse(apr);
string march = "3/31/" + (nextyear - 1);
dtmarch = DateTime.Parse(march);
}
Report = new DataTable();
Report.Columns.Add("Ledger Type");
Report.Columns.Add("Customer Code");
Report.Columns.Add("Customer Name");
Report.Columns.Add("Invoice Date");
Report.Columns.Add("Invoce No");
Report.Columns.Add("Item Code");
Report.Columns.Add("Item Name");
Report.Columns.Add("Qty");
Report.Columns.Add("Rate");
Report.Columns.Add("Tax Code");
Report.Columns.Add("Sales Type");
Report.Columns.Add("Category Code");
Report.Columns.Add("vat_percent");
Report.Columns.Add("TaxAmount");
Report.Columns.Add("Rounding Off");
Report.Columns.Add("WH Code");
Report.Columns.Add("Inv Value");
Report.Columns.Add("Net Value");
Report.Columns.Add("Narration");
int i = 1;
cmd = new MySqlCommand("SELECT tripdata.Sno AS TripId, DATE_FORMAT(tripdata.AssignDate, '%d %b %y') AS AssignDate, tripdata.Permissions, tripdata.VehicleNo,dispatch.DispName AS DispatchName, empmanage.EmpName AS Employee FROM tripdata INNER JOIN empmanage ON tripdata.EmpId = empmanage.Sno INNER JOIN triproutes ON tripdata.Sno = triproutes.Tripdata_sno INNER JOIN dispatch ON triproutes.RouteID = dispatch.sno INNER JOIN branchdata ON empmanage.Branch = branchdata.sno WHERE (tripdata.DC_Type = 1) AND (tripdata.Status <> 'c') AND (tripdata.AssignDate BETWEEN @d1 AND @d2) AND (tripdata.Permissions LIKE '%D%') AND (empmanage.Branch = @BranchID)");
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@d2", GetHighDate(fromdate));
DataTable dttripid = vdm.SelectQuery(cmd).Tables[0];
foreach (DataRow drdc in dttripid.Rows)
{
cmd = new MySqlCommand("SELECT addresstable.customercode,products_category.categorycode, productsdata.Itemcode, branchdata.whcode, products_category.sno AS categoryid, branchdata.tbranchname,branchdata.BranchName, branchdata.sno AS BSno, indent.IndentType, indents_subtable.DeliveryQty, indents_subtable.UnitCost, productsdata.tproduct,productsdata.ProductName, productsdata.Units, productsdata.sno AS productsno, products_category.tcategory, branchproducts.VatPercent, tripdata.to_adr_Id,addresstable.companyname AS tBranchName FROM (SELECT IndentNo, Branch_id, I_date, Status, IndentType FROM indents WHERE (I_date BETWEEN @d1 AND @d2) AND (Status <> 'D')) indent INNER JOIN branchdata ON indent.Branch_id = branchdata.sno INNER JOIN tripdata ON tripdata.BranchID = branchdata.sno INNER JOIN addresstable ON addresstable.sno = tripdata.to_adr_Id INNER JOIN indents_subtable ON indent.IndentNo = indents_subtable.IndentNo INNER JOIN productsdata ON indents_subtable.Product_sno = productsdata.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno INNER JOIN branchproducts ON branchdata.sno = branchproducts.branch_sno AND productsdata.sno = branchproducts.product_sno WHERE (indents_subtable.DeliveryQty <> 0) AND (branchproducts.VatPercent > 0) AND (tripdata.Sno = @BranchID) GROUP BY productsdata.sno, BSno, branchproducts.VatPercent ORDER BY branchdata.BranchName");
cmd.Parameters.AddWithValue("@BranchID", drdc["TripId"].ToString());
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@d2", GetHighDate(fromdate));
DataTable dtothers = vdm.SelectQuery(cmd).Tables[0];
if (dtothers.Rows.Count > 0)
{
int j = 1;
cmd = new MySqlCommand("SELECT sno, BranchName, statename,tax,ntax FROM branchdata WHERE (sno = @BranchID)");
if (Session["salestype"].ToString() == "Plant")
{
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
}
else
{
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
}
DataTable dtstate = vdm.SelectQuery(cmd).Tables[0];
string statename = "";
string tax = "";
string ntax = "";
if (dtstate.Rows.Count > 0)
{
statename = dtstate.Rows[0]["statename"].ToString();
tax = dtstate.Rows[0]["tax"].ToString();
ntax = dtstate.Rows[0]["ntax"].ToString();
}
foreach (DataRow branch in dtothers.Rows)
{
DataRow newrow = Report.NewRow();
string DCNO = "0";
long DcNo = 0;
cmd = new MySqlCommand("SELECT sno, taxdcno, invoiceno, agentid, branchid, productid, doe, indentdate FROM taxdc_table WHERE (branchid = @BranchID) AND (indentdate BETWEEN @d1 AND @d2)");
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate.AddDays(-1)));
cmd.Parameters.AddWithValue("@d2", GetHighDate(fromdate.AddDays(-1)));
DataTable dtotherDc = vdm.SelectQuery(cmd).Tables[0];
if (dtotherDc.Rows.Count > 0)
{
DataRow[] drotherdc = dtotherDc.Select("productid='" + branch["categoryid"].ToString() + "' and branchid='" + ddlSalesOffice.SelectedValue + "' and agentid='" + branch["BSno"].ToString() + "'");
if (drotherdc.Length > 0)
{
foreach (DataRow drotherc in drotherdc)
{
DCNO = drotherc.ItemArray[1].ToString();
}
}
}
int countdc = 0;
int.TryParse(DCNO, out countdc);
if (countdc <= 10)
{
DCNO = "00" + DCNO;
}
if (countdc >= 10 && countdc <= 99)
{
DCNO = "0" + DCNO;
}
if (ddlSalesOffice.SelectedValue == "174")
{
DCNO = "CHN/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "T/" + DCNO;
}
if (ddlSalesOffice.SelectedValue == "4607")
{
DCNO = "MPK/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "T/" + DCNO;
}
if (ddlSalesOffice.SelectedValue == "271")
{
DCNO = "NLR/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "T/" + DCNO;
}
if (ddlSalesOffice.SelectedValue == "285")
{
if (branch["BSno"].ToString() == "2624")
{
DCNO = "PLR/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "T/" + DCNO;
}
else
{
DCNO = "TPT/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "T/" + DCNO;
}
}
if (ddlSalesOffice.SelectedValue == "306")
{
DCNO = "KANCHI/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "NT/" + DCNO;
}
if (ddlSalesOffice.SelectedValue == "2749")
{
if (branch["sno"].ToString() == "3781")
{
DCNO = "TDP/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "T/" + DCNO;
}
else
{
DCNO = "MDPL/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "T/" + DCNO;
}
}
if (ddlSalesOffice.SelectedValue == "3928")
{
DCNO = "AB/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "T/" + DCNO;
}
if (ddlSalesOffice.SelectedValue == "2909")
{
DCNO = "VLR/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "NT/" + DCNO;
}
if (ddlSalesOffice.SelectedValue == "538")
{
DCNO = "BANG/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "T/" + DCNO;
}
if (ddlSalesOffice.SelectedValue == "159")
{
DCNO = "HYD/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "T/" + DCNO;
}
if (ddlSalesOffice.SelectedValue == "570")
{
DCNO = "VJD/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "T/" + DCNO;
}
if (ddlSalesOffice.SelectedValue == "457")
{
DCNO = "WNGL/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "T/" + DCNO;
}
if (ddlSalesOffice.SelectedValue == "158" || ddlSalesOffice.SelectedValue == "572")
{
DCNO = "WYRA/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "T/" + DCNO;
}
if (ddlSalesOffice.SelectedValue == "282")
{
DCNO = "KLH/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "T/" + DCNO;
}
if (ddlSalesOffice.SelectedValue == "458")
{
DCNO = "KMM/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "T/" + DCNO;
}
if (ddlSalesOffice.SelectedValue == "3559")
{
DCNO = "CTR/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "T/" + DCNO;
}
cmd = new MySqlCommand("SELECT sno, companyname, buildingaddress, street, mandal, district, state, pin, tin, cst, email, panno, customercode FROM addresstable WHERE (sno = @to_adr_Id)");
cmd.Parameters.AddWithValue("@to_adr_Id", branch["to_adr_Id"].ToString());
DataTable dtcustomer = vdm.SelectQuery(cmd).Tables[0];
newrow["Customer Code"] = dtcustomer.Rows[0]["customercode"].ToString(); // branch["tbranchname1"].ToString();
newrow["WH Code"] = branch["whcode"].ToString();
newrow["Customer Name"] = dtcustomer.Rows[0]["companyname"].ToString(); // branch["tbranchname1"].ToString();
double Roundingoff = 0;
newrow["Invoce No"] = DCNO;
newrow["Invoice Date"] = fromdate.ToString("dd-MMM-yyyy");
newrow["Item Name"] = branch["tProduct"].ToString();
newrow["Item Code"] = branch["Itemcode"].ToString();
double vatpercent = 0;
double.TryParse(branch["vatpercent"].ToString(), out vatpercent);
double delqty = 0;
double.TryParse(branch["DeliveryQty"].ToString(), out delqty);
string tcategory = "";
string TaxCode = "EXEMPT";
if (vatpercent == null || vatpercent == 0.0)
{
tcategory = branch["tcategory"].ToString();
newrow["Sales Type"] = ntax;
}
else
{
tcategory = branch["tcategory"].ToString() + " " + "@" + " " + branch["vatpercent"].ToString() + "%-" + statename;
TaxCode = "VAT@" + branch["vatpercent"].ToString();
newrow["Sales Type"] = tax;
}
newrow["Ledger Type"] = tcategory.ToString();
newrow["Tax Code"] = TaxCode.ToString();
newrow["Category Code"] = branch["categorycode"].ToString();
double percent = 0;
newrow["Qty"] = branch["DeliveryQty"].ToString();
double UnitCost = 0;
double Unitprice = 0;
double.TryParse(branch["UnitCost"].ToString(), out UnitCost);
Unitprice = UnitCost;
double.TryParse(branch["vatpercent"].ToString(), out vatpercent);
if (vatpercent == 0.0)
{
newrow["Rate"] = UnitCost.ToString();
}
else
{
percent = vatpercent / 100;
percent = percent + 1;
UnitCost = UnitCost / percent;
UnitCost = Math.Round(UnitCost, 2);
newrow["Rate"] = UnitCost.ToString();
}
newrow["vat_percent"] = vatpercent.ToString();
newrow["Rounding Off"] = Roundingoff;
double invval = 0;
double qty = 0;
double.TryParse(branch["DeliveryQty"].ToString(), out qty);
double taxval = 0;
if (vatpercent == 0.0)
{
invval = qty * UnitCost;
}
else
{
double diffcost = 0;
diffcost = Unitprice - UnitCost;
taxval = qty * diffcost;
invval = qty * UnitCost;
}
invval = Math.Round(invval, 2);
taxval = Math.Round(taxval, 2);
newrow["TaxAmount"] = taxval;
double netvalue = 0;
netvalue = invval + taxval;
netvalue = Math.Round(netvalue, 2);
newrow["Inv Value"] = invval;
newrow["Net Value"] = netvalue;
newrow["Narration"] = "Being the sale of milk to " + branch["tbranchname"].ToString() + " vide DC No " + DCNO + ",DC Date " + fromdate.ToString("dd/MM/yyyy") + ",Emp Name " + Session["EmpName"].ToString();
Report.Rows.Add(newrow);
j++;
//}
}
grdReports.DataSource = Report;
grdReports.DataBind();
Session["xportdata"] = Report;
}
else
{
pnlHide.Visible = false;
lblmsg.Text = "No Data Found";
grdReports.DataSource = Report;
grdReports.DataBind();
}
}
}
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
grdReports.DataSource = Report;
grdReports.DataBind();
}
}
protected void BtnSave_Click(object sender, EventArgs e)
{
try
{
vdm = new VehicleDBMgr();
DateTime CreateDate = VehicleDBMgr.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 Customercode = dr["Customer Code"].ToString();
string whccode = dr["WH Code"].ToString();
if (Customercode == "")
{
}
else
{
string Itemcode = dr["Item Code"].ToString();
if (Itemcode == "")
{
}
else
{
sqlcmd = new SqlCommand("Insert into EMROINV (cardcode,cardname,TaxDate, DocDate, DocDueDate,dscription,itemcode,quantity,price,whscode,vat_percent,taxamount,ReferenceNo,TaxCode,B1Upload,Processed,CreateDate,REMARKS,SALETYPE,OcrCode,ocrCode2) values(@cardcode,@cardname,@TaxDate,@DocDate,@DocDueDate,@dscription,@itemcode,@quantity,@price,@whscode,@vat_percent,@taxamount,@ReferenceNo,@TaxCode,@B1Upload,@Processed,@CreateDate,@REMARKS,@SALETYPE,@OcrCode,@ocrCode2)");
sqlcmd.Parameters.Add("@cardcode", dr["Customer Code"].ToString());
sqlcmd.Parameters.Add("@cardname", dr["Customer Name"].ToString());
sqlcmd.Parameters.Add("@TaxDate", GetLowDate(fromdate));
sqlcmd.Parameters.Add("@docdate", GetLowDate(fromdate));
sqlcmd.Parameters.Add("@DocDueDate", GetLowDate(fromdate));
sqlcmd.Parameters.Add("@dscription", dr["Item Name"].ToString());
sqlcmd.Parameters.Add("@itemcode", dr["Item Code"].ToString());
sqlcmd.Parameters.Add("@quantity", dr["Qty"].ToString());
sqlcmd.Parameters.Add("@price", dr["Rate"].ToString());
sqlcmd.Parameters.Add("@whscode", whccode);
sqlcmd.Parameters.Add("@OcrCode", whccode);
sqlcmd.Parameters.Add("@ocrCode2", dr["Category Code"].ToString());
sqlcmd.Parameters.Add("@vat_percent", dr["vat_percent"].ToString());
sqlcmd.Parameters.Add("@taxamount", dr["TaxAmount"].ToString());
sqlcmd.Parameters.Add("@ReferenceNo", dr["Invoce No"].ToString());
string TaxCode = dr["Tax Code"].ToString();
string B1Upload = "N";
string Processed = "N";
sqlcmd.Parameters.Add("@TaxCode", TaxCode);
sqlcmd.Parameters.Add("@B1Upload", B1Upload);
sqlcmd.Parameters.Add("@Processed", Processed);
sqlcmd.Parameters.Add("@CreateDate", CreateDate);
sqlcmd.Parameters.Add("@REMARKS", dr["Narration"].ToString());
string salestype = dr["Sales Type"].ToString();
sqlcmd.Parameters.Add("@SALETYPE", salestype);//Cash-sale
//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();
}
}
void GetReport()
{
try
{
vdm = new VehicleDBMgr();
DataTable Report = new DataTable();
BranchID = ddlSalesOffice.SelectedValue;
string branchname = Session["branchname"].ToString();
Session["filename"] = branchname + " RateSheet " + DateTime.Now.ToString("dd/MM/yyyy");
cmd = new MySqlCommand("SELECT branchdata.BranchName, branchproducts.product_sno, productsdata.ProductName, branchproducts.unitprice, branchdata.sno FROM branchdata INNER JOIN branchproducts ON branchdata.sno = branchproducts.branch_sno INNER JOIN productsdata ON branchproducts.product_sno = productsdata.sno INNER JOIN branchmappingtable ON branchdata.sno = branchmappingtable.SubBranch INNER JOIN branchdata branchdata_1 ON branchmappingtable.SuperBranch = branchdata_1.sno WHERE ((branchmappingtable.SuperBranch = @BranchID)) OR ((branchdata_1.SalesOfficeID = @SOID)) ORDER BY branchproducts.Rank");
//cmd.Parameters.AddWithValue("@Flag", "1");
cmd.Parameters.AddWithValue("@BranchID", BranchID);
cmd.Parameters.AddWithValue("@SOID", BranchID);
DataTable dtAgents = vdm.SelectQuery(cmd).Tables[0];
cmd = new MySqlCommand("SELECT productsdata.ProductName, branchproducts.product_sno, branchproducts.unitprice, branchdata.BranchName, branchdata.sno FROM branchproducts INNER JOIN productsdata ON branchproducts.product_sno = productsdata.sno INNER JOIN branchdata ON branchproducts.branch_sno = branchdata.sno INNER JOIN branchdata branchdata_1 ON branchdata.sno = branchdata_1.sno WHERE ((branchdata.sno = @BranchID)) OR ((branchdata_1.SalesOfficeID = @SOID)) ORDER BY branchproducts.Rank");
//cmd.Parameters.AddWithValue("@Flag", "1");
cmd.Parameters.AddWithValue("@SOID", BranchID);
cmd.Parameters.AddWithValue("@BranchID", BranchID);
DataTable dtBranch = vdm.SelectQuery(cmd).Tables[0];
if (dtBranch.Rows.Count > 0)
{
foreach (DataRow dr in dtBranch.Rows)
{
DataRow drnew = dtAgents.NewRow();
drnew["BranchName"] = dr["BranchName"].ToString();
drnew["product_sno"] = dr["product_sno"].ToString();
drnew["ProductName"] = dr["ProductName"].ToString();
drnew["unitprice"] = dr["unitprice"].ToString();
drnew["sno"] = dr["sno"].ToString();
dtAgents.Rows.Add(drnew);
}
}
cmd = new MySqlCommand("SELECT products_category.Categoryname, productsdata.sno, productsdata.ProductName, branchproducts.product_sno FROM productsdata INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno INNER JOIN branchproducts ON productsdata.sno = branchproducts.product_sno WHERE (branchproducts.branch_sno = @BranchID) ORDER BY branchproducts.Rank");
//cmd.Parameters.AddWithValue("@Flag", "1");
cmd.Parameters.AddWithValue("@BranchID", BranchID);
DataTable produtstbl = vdm.SelectQuery(cmd).Tables[0];
if (produtstbl.Rows.Count > 0)
{
DataView view = new DataView(dtAgents);
DataTable distincttable = view.ToTable(true, "BranchName", "sno");
Report = new DataTable();
Report.Columns.Add("SNo");
Report.Columns.Add("Agent Code");
Report.Columns.Add("Agent Name");
foreach (DataRow dr in produtstbl.Rows)
{
Report.Columns.Add(dr["ProductName"].ToString()).DataType = typeof(Double);
}
int i = 1;
foreach (DataRow branch in distincttable.Rows)
{
DataRow newrow = Report.NewRow();
newrow["SNo"] = i;
newrow["Agent Code"] = branch["sno"].ToString();
newrow["Agent Name"] = branch["BranchName"].ToString();
foreach (DataRow dr in dtAgents.Rows)
{
if (branch["BranchName"].ToString() == dr["BranchName"].ToString())
{
double unitprice = 0;
double.TryParse(dr["unitprice"].ToString(), out unitprice);
newrow[dr["ProductName"].ToString()] = unitprice;
}
}
Report.Rows.Add(newrow);
i++;
}
}
grdReports.DataSource = Report;
grdReports.DataBind();
Session["xportdata"] = Report;
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
}
}
void GetReport()
{
try
{
lblmsg.Text = "";
pnlHide.Visible = true;
Session["IDate"] = DateTime.Now.AddDays(1).ToString("dd/MM/yyyy");
vdm = new VehicleDBMgr();
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);
}
}
string strmonth = fromdate.ToString("MMM/dd/yyyy");
lbl_selfromdate.Text = fromdate.ToString("dd/MM/yyyy");
Session["xporttype"] = "TallyTermloans";
string DCNO = "";
Report.Columns.Add("JV No");
Report.Columns.Add("JV Date");
Report.Columns.Add("WH Code");
Report.Columns.Add("Ledger Code");
Report.Columns.Add("Ledger Name");
Report.Columns.Add("Amount");
Report.Columns.Add("Narration");
Session["filename"] = " Tally Termloans" + fromdate.ToString("dd/MM/yyyy");
cmd = new MySqlCommand("SELECT sno, vehsno, doe, paymenttype, amount, remarks, branchid, ledgername, vehicleno, whcode,ledgercode FROM termloantransactions WHERE (whcode = @whcode) AND (doe BETWEEN @d1 AND @d2)");
// cmd = new MySqlCommand("SELECT termloanentry.ledger_code, termloanentry.bankname, vehicel_master.registration_no, termloanentry.type, termloanentry.termloandate, vehicel_master.vm_owner, vehicel_master.vm_model,termloanentry.ledgername, termloanentry.interest_per, termloanentry.instalamount, termloanentry.loanamount, termloanentry.totalinstall, termloanentry.com_install, termloanentry.instaldate,vehicel_master.whcode, termloantransactions.amount FROM termloanentry INNER JOIN vehicel_master ON termloanentry.vehsno = vehicel_master.vm_sno INNER JOIN termloantransactions ON termloanentry.vehsno = termloantransactions.vehsno WHERE (vehicel_master.whcode = @whcode) AND (termloantransactions.doe BETWEEN @d1 AND @d2) GROUP BY termloanentry.type, vehicel_master.registration_no");
cmd.Parameters.Add("@whcode", ddlbranch.SelectedValue);
cmd.Parameters.Add("@d1", GetLowMonthRetrive(fromdate));
cmd.Parameters.Add("@d2", GetHighMonth(fromdate));
DataTable dtble = vdm.SelectQuery(cmd).Tables[0];
double totamount = 0;
fromdate = fromdate.AddDays(-1);
string frmdate = fromdate.ToString("dd-MM-yyyy");
string[] strjv = frmdate.Split('-');
//int branchid=0;
//branchid= Convert.ToInt32(ddlbranch.SelectedValue);
DCNO = "TL";
foreach (DataRow branch in dtble.Rows)
{
DataRow newrow = Report.NewRow();
newrow["JV No"] = DCNO + strjv[1];
newrow["JV Date"] = fromdate.AddMonths(-1).ToString("dd-MMM-yyyy");
newrow["WH Code"] = branch["whcode"].ToString();
string ledgercode = branch["ledgername"].ToString();
newrow["Ledger Code"] = branch["ledgercode"].ToString();
newrow["Ledger Name"] = branch["ledgername"].ToString();
double amount = 0;
double.TryParse(branch["amount"].ToString(), out amount);
totamount += amount;
newrow["Amount"] = "-" + amount;
newrow["Narration"] = "Being the vehicle interest for the month of " + fromdate.AddMonths(-1).ToString("MMM-yyyy") + " Amount " + amount + " Vehicle Number " + branch["vehicleno"].ToString() + ",Emp Name " + Session["employname"].ToString();
Report.Rows.Add(newrow);
}
DataRow new_row = Report.NewRow();
new_row["JV No"] = DCNO + strjv[1];
new_row["JV Date"] = fromdate.AddMonths(-1).ToString("dd-MMM-yyyy");
new_row["Ledger Code"] = "5115064";
new_row["Ledger Name"] = "Interest on Vehicle Loans";
new_row["WH Code"] = ddlbranch.SelectedValue;
new_row["Amount"] = totamount;
new_row["Narration"] = "Being the vehicle interest for the month of " + fromdate.AddMonths(-1).ToString("MMM-yyyy") + " Amount " + totamount + ",Emp Name " + Session["employname"].ToString();
Report.Rows.Add(new_row);
grdReports.DataSource = Report;
grdReports.DataBind();
Session["xportdata"] = Report;
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
grdReports.DataSource = Report;
grdReports.DataBind();
}
}
void GetReport()
{
try
{
vdm = new VehicleDBMgr();
Report = new DataTable();
lblmsg.Text = "";
pnlHide.Visible = true;
DateTime fromdate = DateTime.Now;
string[] fromdatestrig = txtFromdate.Text.Split(' ');
if (fromdatestrig.Length > 1)
{
if (fromdatestrig[0].Split('-').Length > 0)
{
string[] dates = fromdatestrig[0].Split('-');
string[] times = fromdatestrig[1].Split(':');
fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
DateTime todate = DateTime.Now;
string[] todatestrig = txtTodate.Text.Split(' ');
if (todatestrig.Length > 1)
{
if (todatestrig[0].Split('-').Length > 0)
{
string[] dates = todatestrig[0].Split('-');
string[] times = todatestrig[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);
}
}
lblDate.Text = fromdate.ToString("dd/MM/yyyy");
lbl_selttodate.Text = todate.ToString("dd/MM/yyyy");
Session["filename"] = "AGENT WISE INVENTORY TRANSACTION";
lblAgent.Text = ddlDispName.SelectedItem.Text;
cmd = new MySqlCommand("SELECT tripinvdata.Qty, tripinvdata.Remaining, invmaster.InvName, invmaster.sno, tripdata.I_Date FROM dispatch INNER JOIN triproutes ON dispatch.sno = triproutes.RouteID INNER JOIN tripdata ON triproutes.Tripdata_sno = tripdata.Sno INNER JOIN tripinvdata ON tripdata.Sno = tripinvdata.Tripdata_sno INNER JOIN invmaster ON tripinvdata.invid = invmaster.sno WHERE (dispatch.sno = @dispatchSno) AND (tripdata.I_Date BETWEEN @d1 AND @d2) AND invmaster.sno <> 6 order by invmaster.sno,tripdata.I_Date");
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate.AddDays(-1)));
cmd.Parameters.AddWithValue("@d2", GetHighDate(todate.AddDays(-1)));
cmd.Parameters.AddWithValue("@dispatchSno", ddlDispName.SelectedValue);
DataTable dtInventory = vdm.SelectQuery(cmd).Tables[0];
if (dtInventory.Rows.Count > 0)
{
DataView view = new DataView(dtInventory);
Report.Columns.Add("Date");
Report.Columns.Add("Issued crates").DataType = typeof(Int32);
Report.Columns.Add("Received crates").DataType = typeof(Int32);
Report.Columns.Add("Issued can 20ltr").DataType = typeof(Int32);
Report.Columns.Add("Received can 20ltr").DataType = typeof(Int32);
Report.Columns.Add("Issued can 40ltr").DataType = typeof(Int32);
Report.Columns.Add("Received can 40ltr").DataType = typeof(Int32);
int i = 1;
int k = 0;
DataTable distincttable = view.ToTable(true, "I_Date");
foreach (DataRow drinv in distincttable.Rows)
{
DataRow drnew = Report.NewRow();
string dtdate1 = drinv["I_Date"].ToString();
DateTime dtDOE1 = Convert.ToDateTime(dtdate1).AddDays(1);
string ChangedTime1 = dtDOE1.ToString("dd/MMM/yyyy");
drnew["Date"] = ChangedTime1;
foreach (DataRow drinvc in dtInventory.Rows)
{
string dtdate2 = drinvc["I_Date"].ToString();
DateTime dtDOE2 = Convert.ToDateTime(dtdate2).AddDays(1);
string ChangedTime2 = dtDOE2.ToString("dd/MMM/yyyy");
string InvName = drinvc["InvName"].ToString();
if (ChangedTime1 == ChangedTime2)
{
if (drinvc["sno"].ToString() == "1")
{
drnew["Issued crates"] = drinvc["Qty"].ToString();
drnew["Received crates"] = drinvc["Remaining"].ToString();
}
if (drinvc["sno"].ToString() == "3")
{
drnew["Issued can 20ltr"] = drinvc["Qty"].ToString();
drnew["Received can 20ltr"] = drinvc["Remaining"].ToString();
}
if (drinvc["sno"].ToString() == "4")
{
drnew["Issued can 40ltr"] = drinvc["Qty"].ToString();
drnew["Received can 40ltr"] = drinvc["Remaining"].ToString();
}
//else
//{
// int Qty = 0;
// int.TryParse(drinvc["Qty"].ToString(), out Qty);
// int Remaining = 0;
// int.TryParse(drinvc["Remaining"].ToString(), out Remaining);
// Report.Rows[k][3] = Qty;
// Report.Rows[k][4] = Remaining;
//}
}
}
Report.Rows.Add(drnew);
k++;
}
DataRow newvartical = Report.NewRow();
newvartical["Date"] = "Total";
int val = 0;
foreach (DataColumn dc in Report.Columns)
{
if (dc.DataType == typeof(Int32))
{
val = 0;
Int32.TryParse(Report.Compute("sum([" + dc.ToString() + "])", "[" + dc.ToString() + "]<>'0'").ToString(), out val);
newvartical[dc.ToString()] = val;
}
}
Report.Rows.Add(newvartical);
grdReports.DataSource = Report;
grdReports.DataBind();
Session["xportdata"] = Report;
}
else
{
pnlHide.Visible = false;
lblmsg.Text = "No Data Found";
grdReports.DataSource = Report;
grdReports.DataBind();
}
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
grdReports.DataSource = Report;
grdReports.DataBind();
}
}
void GetReport()
{
try
{
vdm = new VehicleDBMgr();
DataTable Report = new DataTable();
DateTime fromdate = DateTime.Now;
string[] fromdatestrig = txtFromdate.Text.Split(' ');
lblRoute.Text = ddlRouteName.SelectedItem.Text;
if (fromdatestrig.Length > 1)
{
if (fromdatestrig[0].Split('-').Length > 0)
{
string[] dates = fromdatestrig[0].Split('-');
string[] times = fromdatestrig[1].Split(':');
fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
DateTime todate = DateTime.Now;
string[] todatestrig = txtTodate.Text.Split(' ');
if (todatestrig.Length > 1)
{
if (todatestrig[0].Split('-').Length > 0)
{
string[] dates = todatestrig[0].Split('-');
string[] times = todatestrig[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);
}
}
lblDate.Text = DateTime.Now.ToString("dd/MMM/yyyy");
cmd = new MySqlCommand("SELECT invmaster.InvName,sum(tripinvdata.Qty) as Qty,sum(tripinvdata.Remaining) as rr FROM dispatch INNER JOIN triproutes ON dispatch.sno = triproutes.RouteID INNER JOIN tripdata ON triproutes.Tripdata_sno = tripdata.Sno INNER JOIN tripinvdata ON tripdata.Sno = tripinvdata.Tripdata_sno INNER JOIN invmaster ON tripinvdata.invid = invmaster.sno WHERE (dispatch.sno = @dispatchsno) AND (tripdata.I_Date > @starttime) AND (tripdata.I_Date < @endtime) Group by invmaster.InvName order by invmaster.sno");
cmd.Parameters.AddWithValue("@dispatchsno", ddlRouteName.SelectedValue);
cmd.Parameters.AddWithValue("@starttime", GetLowDate(fromdate.AddDays(-2)));
cmd.Parameters.AddWithValue("@endtime", GetHighDate(todate));
DataTable dtInventory = vdm.SelectQuery(cmd).Tables[0];
cmd = new MySqlCommand("SELECT sno, InvName FROM invmaster order by sno");
DataTable produtstbl = vdm.SelectQuery(cmd).Tables[0];
Report = new DataTable();
Report.Columns.Add("Type");
foreach (DataRow dr in produtstbl.Rows)
{
Report.Columns.Add(dr["InvName"].ToString()).DataType = typeof(Double);
}
DataRow newrowISS = Report.NewRow();
foreach (DataRow dr in dtInventory.Rows)
{
newrowISS["Type"] = "Issued";
foreach (DataRow drr in produtstbl.Rows)
{
if (dr["InvName"].ToString() == drr["InvName"].ToString())
{
newrowISS[drr["InvName"].ToString()] = dr["Qty"].ToString();
}
}
}
Report.Rows.Add(newrowISS);
DataRow newrowRE = Report.NewRow();
foreach (DataRow dr in dtInventory.Rows)
{
newrowRE["Type"] = "Return";
foreach (DataRow drr in produtstbl.Rows)
{
if (dr["InvName"].ToString() == drr["InvName"].ToString())
{
newrowRE[drr["InvName"].ToString()] = dr["rr"].ToString();
}
}
}
Report.Rows.Add(newrowRE);
grdReports.DataSource = Report;
grdReports.DataBind();
}
catch
{
}
}
protected void Page_Load(object sender, EventArgs e)
{
vdm = new VehicleDBMgr();
ddwnldr = new DataDownloader();
vdm.InitializeDB();
}
void GetReport()
{
try
{
vdm = new VehicleDBMgr();
pnlHide.Visible = true;
int number = 11000;
int roundednumber = RoundOff(number, 10);
DateTime fromdate = DateTime.Now;
string[] datestrig = txtdate.Text.Split(' ');
if (datestrig.Length > 1)
{
if (datestrig[0].Split('-').Length > 0)
{
string[] dates = datestrig[0].Split('-');
string[] times = datestrig[1].Split(':');
fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
Session["xporttype"] = "TallySales";
DateTime todate = DateTime.Now;
string[] todatestrig = txtTodate.Text.Split(' ');
if (todatestrig.Length > 1)
{
if (todatestrig[0].Split('-').Length > 0)
{
string[] dates = todatestrig[0].Split('-');
string[] times = todatestrig[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);
}
}
lbl_selfromdate.Text = fromdate.ToString();
lbl_selttodate.Text = todate.ToString();
if (Session["salestype"].ToString() == "Plant")
{
cmd = new MySqlCommand("SELECT dispatch.sno, dispatch.DispName, dispatch.DispType, dispatch.DispMode FROM dispatch INNER JOIN branchdata ON dispatch.Branch_Id = branchdata.sno INNER JOIN branchdata branchdata_1 ON dispatch.Branch_Id = branchdata_1.sno WHERE (dispatch.DispType ='SM') AND (branchdata.sno = @BranchID) OR (dispatch.DispType ='SM') AND (branchdata_1.SalesOfficeID = @SOID)");
}
else
{
cmd = new MySqlCommand("SELECT dispatch.sno, dispatch.DispName, dispatch.DispType, dispatch.DispMode FROM dispatch INNER JOIN branchdata ON dispatch.Branch_Id = branchdata.sno INNER JOIN branchdata branchdata_1 ON dispatch.Branch_Id = branchdata_1.sno WHERE (dispatch.DispType IS NULL) AND (branchdata.sno = @BranchID) OR (dispatch.DispType IS NULL) AND (branchdata_1.SalesOfficeID = @SOID)");
}
cmd.Parameters.AddWithValue("@BranchID", Session["branch"].ToString());
cmd.Parameters.AddWithValue("@SOID", Session["branch"].ToString());
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate.AddDays(-1)));
cmd.Parameters.AddWithValue("@d2", GetHighDate(todate.AddDays(-1)));
DataTable dtRoutes = vdm.SelectQuery(cmd).Tables[0];
if (Session["salestype"].ToString() == "Plant")
{
cmd = new MySqlCommand("SELECT dispatch.sno, dispatch.DispName, SUM(tripsubdata.Qty) AS dispatchqty, tripdat.AssignDate, tripdat.I_Date FROM dispatch INNER JOIN triproutes ON dispatch.sno = triproutes.RouteID INNER JOIN (SELECT Sno, AssignDate, I_Date FROM tripdata WHERE (I_Date BETWEEN @d1 AND @d2)) tripdat ON triproutes.Tripdata_sno = tripdat.Sno INNER JOIN tripsubdata ON tripdat.Sno = tripsubdata.Tripdata_sno INNER JOIN branchdata ON dispatch.Branch_Id = branchdata.sno INNER JOIN branchdata branchdata_1 ON dispatch.Branch_Id = branchdata_1.sno WHERE (dispatch.DispType ='SM') AND (branchdata.sno = @BranchID) OR (dispatch.DispType ='SM') AND (branchdata_1.SalesOfficeID = @SOID) GROUP BY dispatch.sno, tripdat.I_Date ORDER BY dispatch.sno");
}
else
{
cmd = new MySqlCommand("SELECT dispatch.sno, dispatch.DispName, SUM(tripsubdata.Qty) AS dispatchqty, tripdat.AssignDate, tripdat.I_Date FROM dispatch INNER JOIN triproutes ON dispatch.sno = triproutes.RouteID INNER JOIN (SELECT Sno, AssignDate, I_Date FROM tripdata WHERE (I_Date BETWEEN @d1 AND @d2)) tripdat ON triproutes.Tripdata_sno = tripdat.Sno INNER JOIN tripsubdata ON tripdat.Sno = tripsubdata.Tripdata_sno INNER JOIN branchdata ON dispatch.Branch_Id = branchdata.sno INNER JOIN branchdata branchdata_1 ON dispatch.Branch_Id = branchdata_1.sno WHERE (dispatch.DispType IS NULL) AND (branchdata.sno = @BranchID) OR (dispatch.DispType IS NULL) AND (branchdata_1.SalesOfficeID = @SOID) GROUP BY dispatch.sno, tripdat.I_Date ORDER BY dispatch.sno");
}
cmd.Parameters.AddWithValue("@BranchID", Session["branch"].ToString());
cmd.Parameters.AddWithValue("@SOID", Session["branch"].ToString());
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate.AddDays(-1)));
cmd.Parameters.AddWithValue("@d2", GetHighDate(todate.AddDays(-1)));
DataTable dtRoutesData = vdm.SelectQuery(cmd).Tables[0];
cmd = new MySqlCommand("SELECT ROUND(SUM(t2.Qty), 2) AS dispatchqty, t1.AssignDate AS I_Date, t1.BranchID AS sno, t1.BranchName AS DispName FROM (SELECT triproutes.Tripdata_sno, tripdata.AssignDate, branchdata.BranchName, branchdata.sno AS Expr1, dispatch.BranchID FROM dispatch INNER JOIN triproutes ON dispatch.sno = triproutes.RouteID INNER JOIN tripdata ON triproutes.Tripdata_sno = tripdata.Sno INNER JOIN branchdata ON branchdata.sno = dispatch.BranchID INNER JOIN branchdata branchdata_1 ON dispatch.BranchID = branchdata_1.sno WHERE (tripdata.AssignDate BETWEEN @d1 AND @d2) AND (dispatch.BranchID = @BranchID) OR (branchdata_1.SalesOfficeID = @SOID)) t1 INNER JOIN (SELECT SUM(tripsubdata.Qty) AS Qty, tripdata_1.Sno FROM tripdata tripdata_1 INNER JOIN tripsubdata ON tripdata_1.Sno = tripsubdata.Tripdata_sno WHERE (tripdata_1.AssignDate BETWEEN @d1 AND @d2) AND (tripdata_1.Status <> 'C') GROUP BY tripdata_1.Sno) t2 ON t1.Tripdata_sno = t2.Sno GROUP BY DATE_FORMAT(t1.AssignDate, '%d/%b/%y'), t1.BranchID");
//cmd = new MySqlCommand("SELECT dispatch.sno, dispatch.DispName, SUM(tripsubdata.Qty) AS dispatchqty, tripdat.AssignDate, tripdat.I_Date, dispatch.BranchID, branchdata.SalesOfficeID, branchdata.BranchName FROM dispatch INNER JOIN triproutes ON dispatch.sno = triproutes.RouteID INNER JOIN (SELECT Sno, AssignDate, I_Date FROM tripdata WHERE (I_Date BETWEEN @d1 AND @d2)) tripdat ON triproutes.Tripdata_sno = tripdat.Sno INNER JOIN tripsubdata ON tripdat.Sno = tripsubdata.Tripdata_sno INNER JOIN branchdata ON dispatch.BranchID = branchdata.sno WHERE (dispatch.BranchID = @BranchID) OR (branchdata.SalesOfficeID = @SOID) GROUP BY dispatch.BranchID, tripdat.I_Date, branchdata.BranchName ORDER BY tripdat.I_Date");
//cmd = new MySqlCommand("SELECT dispatch.sno, dispatch.DispName, SUM(tripsubdata.Qty) AS dispatchqty, tripdat.AssignDate, tripdat.I_Date, dispatch.BranchID, branchdata.SalesOfficeID FROM dispatch INNER JOIN triproutes ON dispatch.sno = triproutes.RouteID INNER JOIN (SELECT Sno, AssignDate, I_Date FROM tripdata WHERE (I_Date BETWEEN @d1 AND @d2)) tripdat ON triproutes.Tripdata_sno = tripdat.Sno INNER JOIN tripsubdata ON tripdat.Sno = tripsubdata.Tripdata_sno INNER JOIN branchdata ON dispatch.BranchID = branchdata.sno WHERE (dispatch.BranchID = @BranchID) OR (branchdata.SalesOfficeID = @SOID)GROUP BY dispatch.sno, tripdat.I_Date ORDER BY dispatch.sno");
cmd.Parameters.AddWithValue("@BranchID", Session["branch"].ToString());
cmd.Parameters.AddWithValue("@SOID", Session["branch"].ToString());
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate.AddDays(-1)));
cmd.Parameters.AddWithValue("@d2", GetHighDate(todate.AddDays(-1)));
DataTable dtPlantData = vdm.SelectQuery(cmd).Tables[0];
if (dtRoutesData.Rows.Count > 0)
{
if (ddlreporttype.Text == "Helper Charges")
{
DataView view = new DataView(dtRoutesData);
DataTable distinctproducts = view.ToTable(true, "DispName", "sno");
DataView view1 = new DataView(dtRoutesData);
DataTable distinctdate = view1.ToTable(true, "I_Date");
Report = new DataTable();
Report.Columns.Add("SNo");
Report.Columns.Add("Date");
int count = 0;
foreach (DataRow dr in distinctproducts.Rows)
{
Report.Columns.Add(dr["DispName"].ToString()).DataType = typeof(Double);
count++;
}
DataView viewPlant = new DataView(dtPlantData);
DataTable Plantproducts = viewPlant.ToTable(true, "DispName", "sno");
foreach (DataRow dr in Plantproducts.Rows)
{
string Disp = dr["DispName"].ToString();
//string[] strName = Disp.Split('_');
Report.Columns.Add(Disp).DataType = typeof(Double);
count++;
//string Disp = dr["DispName"].ToString();
//Report.Columns.Add(Disp).DataType = typeof(Double);
}
Report.Columns.Add("TOTAL").DataType = typeof(Double);
int i = 1;
foreach (DataRow branch in distinctdate.Rows)
{
DataRow newrow = Report.NewRow();
newrow["SNo"] = i++.ToString();
string dtdate1 = branch["I_Date"].ToString();
DateTime dtDOE1 = Convert.ToDateTime(dtdate1).AddDays(1);
string ChangedTime1 = dtDOE1.ToString("dd/MMM/yy");
newrow["Date"] = ChangedTime1;
double total = 0;
foreach (DataRow dr in dtRoutesData.Rows)
{
string dtdate2 = dr["I_Date"].ToString();
DateTime dtDOE2 = Convert.ToDateTime(dtdate2).AddDays(1);
string ChangedTime2 = dtDOE2.ToString("dd/MMM/yy");
int helpers = 0;
if (ChangedTime1 == ChangedTime2)
{
double dispatchqty = 0;
double.TryParse(dr["dispatchqty"].ToString(), out dispatchqty);
cmd = new MySqlCommand("SELECT helpermaster.sno, helpermaster.despsno, helpermaster.first, helpermaster.second, helpermaster.third, helpermaster.fourth, helpermaster.amount, helpermaster.flag, helpermaster.doe, dispatch.DispName FROM helpermaster INNER JOIN dispatch ON helpermaster.despsno = dispatch.sno WHERE (dispatch.DispName = @DispName)");
cmd.Parameters.AddWithValue("@DispName", dr["DispName"].ToString());
DataTable dtHelper = vdm.SelectQuery(cmd).Tables[0];
if (dtHelper.Rows.Count > 0)
{
string fst = dtHelper.Rows[0]["first"].ToString();
double first = 0;
double.TryParse(fst, out first);
string snd = dtHelper.Rows[0]["second"].ToString();
double second = 0;
double.TryParse(snd, out second);
string thrd = dtHelper.Rows[0]["third"].ToString();
double third = 0;
double.TryParse(thrd, out third);
string foth = dtHelper.Rows[0]["fourth"].ToString();
double fourth = 0;
double.TryParse(foth, out fourth);
if (dispatchqty <= first)
{
helpers = 0;
}
else if (dispatchqty <= second)
{
helpers = 1;
}
else if (dispatchqty <= third)
{
helpers = 2;
}
else if (dispatchqty <= fourth)
{
helpers = 3;
}
else if (dispatchqty > fourth)
{
helpers = 4;
}
newrow[dr["DispName"].ToString()] = helpers;
total += helpers;
}
}
}
newrow["TOTAL"] = total;
Report.Rows.Add(newrow);
}
foreach (DataRow drR in Report.Rows)
{
foreach (DataRow drP in dtPlantData.Rows)
{
string dtdate2 = drP["I_Date"].ToString();
DateTime dtDOE2 = Convert.ToDateTime(dtdate2).AddDays(1);
string ChangedTime2 = dtDOE2.ToString("dd/MMM/yy");
int helpers = 0;
string Disp = drP["DispName"].ToString();
//string[] strName = Disp.Split('_');
if (drR["Date"].ToString() == ChangedTime2)
{
double dispatchqty = 0;
double.TryParse(drP["dispatchqty"].ToString(), out dispatchqty);
cmd = new MySqlCommand("SELECT helpermaster.sno, helpermaster.despsno, helpermaster.first, helpermaster.second, helpermaster.third, helpermaster.fourth, helpermaster.amount, helpermaster.flag, helpermaster.doe, dispatch.DispName, dispatch.Branch_Id FROM helpermaster INNER JOIN dispatch ON helpermaster.despsno = dispatch.sno WHERE (dispatch.Branchid = @Branchid)");
cmd.Parameters.AddWithValue("@Branchid", drP["sno"].ToString());
//cmd = new MySqlCommand("SELECT helpermaster.sno, helpermaster.despsno, helpermaster.first, helpermaster.second, helpermaster.third, helpermaster.fourth, helpermaster.amount, helpermaster.flag, helpermaster.doe, dispatch.DispName FROM helpermaster INNER JOIN dispatch ON helpermaster.despsno = dispatch.sno WHERE (dispatch.DispName = @DispName)");
//cmd.Parameters.AddWithValue("@DispName", drP["DispName"].ToString());
DataTable dtHelper = vdm.SelectQuery(cmd).Tables[0];
if (dtHelper.Rows.Count > 0)
{
string fst = dtHelper.Rows[0]["first"].ToString();
double first = 0;
double.TryParse(fst, out first);
string snd = dtHelper.Rows[0]["second"].ToString();
double second = 0;
double.TryParse(snd, out second);
string thrd = dtHelper.Rows[0]["third"].ToString();
double third = 0;
double.TryParse(thrd, out third);
string foth = dtHelper.Rows[0]["fourth"].ToString();
double fourth = 0;
double.TryParse(foth, out fourth);
if (dispatchqty <= first)
{
helpers = 1;
}
else if (dispatchqty <= second)
{
helpers = 2;
}
else if (dispatchqty <= third)
{
helpers = 3;
}
else if (dispatchqty <= fourth)
{
helpers = 4;
}
else if (dispatchqty >= fourth)
{
helpers = 4;
}
drR[Disp] = helpers;
double Emp = 0;
double.TryParse(drR["TOTAL"].ToString(), out Emp);
double totalemp = Emp + helpers;
drR["TOTAL"] = totalemp;/////
}
}
}
}
DataRow New = Report.NewRow();
New["Date"] = "Total";
double valnewCash = 0.0;
DataRow newAvg = Report.NewRow();
newAvg["Date"] = "Amount";
double Avgval = 0.0;
double Totamount = 0.0;
DataRow newCharges = Report.NewRow();
newCharges["Date"] = "Charges";
cmd = new MySqlCommand("SELECT helpermaster.despsno, dispatch.DispName FROM helpermaster INNER JOIN dispatch ON helpermaster.despsno = dispatch.sno");
DataTable dtdispatchsno = vdm.SelectQuery(cmd).Tables[0];
foreach (DataColumn dc in Report.Columns)
{
if (dc.DataType == typeof(Double))
{
var cell = dc.ColumnName;
if (cell == "TOTAL")
{
double.TryParse(Report.Compute("sum([" + dc.ToString() + "])", "[" + dc.ToString() + "]<>'0'").ToString(), out valnewCash);
New[dc.ToString()] = valnewCash;
newAvg[dc.ToString()] = Math.Round(Totamount, 2);
newCharges[dc.ToString()] = 0;
}
else
{
foreach (DataRow drdispsno in dtdispatchsno.Rows)
{
cmd = new MySqlCommand("SELECT helpermaster.sno, helpermaster.despsno, helpermaster.first, helpermaster.second, helpermaster.third, helpermaster.fourth, helpermaster.amount, helpermaster.flag, helpermaster.doe, dispatch.DispName FROM helpermaster INNER JOIN dispatch ON helpermaster.despsno = dispatch.sno WHERE (helpermaster.despsno = @DispName)");
cmd.Parameters.AddWithValue("@DispName", drdispsno["despsno"].ToString());
DataTable dtHelper = vdm.SelectQuery(cmd).Tables[0];
if (dtHelper.Rows.Count > 0)
{
string fstamount = dtHelper.Rows[0]["amount"].ToString();
double amount = 0;
double.TryParse(fstamount, out amount);
valnewCash = 0.0;
double.TryParse(Report.Compute("sum([" + dc.ToString() + "])", "[" + dc.ToString() + "]<>'0'").ToString(), out valnewCash);
New[dc.ToString()] = valnewCash;
Avgval = valnewCash * amount;
Totamount += Avgval;
newAvg[dc.ToString()] = Math.Round(Avgval, 2);
newCharges[dc.ToString()] = Math.Round(amount, 2);
}
}
}
}
}
Report.Rows.Add(New);
Report.Rows.Add(newAvg);
Report.Rows.Add(newCharges);
cmd = new MySqlCommand("SELECT SUM(tripinvdata.Qty) AS issued, SUM(tripinvdata.Remaining) AS returnqty, invmaster.InvName, invmaster.sno, dispatch.sno AS dispatchsno,dispatch.DispName FROM dispatch INNER JOIN triproutes ON dispatch.sno = triproutes.RouteID INNER JOIN (SELECT Sno, AssignDate FROM tripdata WHERE (I_Date BETWEEN @d1 AND @d2)) tripdat ON triproutes.Tripdata_sno = tripdat.Sno INNER JOIN tripinvdata ON tripdat.Sno = tripinvdata.Tripdata_sno INNER JOIN invmaster ON tripinvdata.invid = invmaster.sno INNER JOIN branchdata ON dispatch.Branch_Id = branchdata.sno WHERE (dispatch.Branch_Id = @brnchid) AND (dispatch.DispType IS NULL) OR (branchdata.SalesOfficeID = @brnchid) AND (dispatch.DispType IS NULL) GROUP BY dispatch.sno, invmaster.sno");
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate.AddDays(-1)));
cmd.Parameters.AddWithValue("@d2", GetHighDate(todate.AddDays(-1)));
cmd.Parameters.AddWithValue("@brnchid", Session["branch"].ToString());
DataTable dtInventory = vdm.SelectQuery(cmd).Tables[0];
DataView view2 = new DataView(dtInventory);
DataTable distincttable = view2.ToTable(true, "DispName", "dispatchsno");
DataRow newCrates = Report.NewRow();
newCrates["Date"] = "Crates";
foreach (DataRow drinv in distincttable.Rows)
{
string dtdate1 = drinv["dispatchsno"].ToString();
string dispatchname = drinv["DispName"].ToString();
foreach (DataRow drinvc in dtInventory.Rows)
{
string dtdate2 = drinvc["DispName"].ToString();
if (dispatchname == dtdate2)
{
if (drinvc["sno"].ToString() == "1")
{
int issuedcrates = 0;
int receivedcrates = 0;
int diff = 0;
int.TryParse(drinvc["issued"].ToString(), out issuedcrates);
int.TryParse(drinvc["returnqty"].ToString(), out receivedcrates);
diff = issuedcrates - receivedcrates;
newCrates[drinvc["DispName"].ToString()] = diff;
}
}
}
}
Report.Rows.Add(newCrates);
DataRow newCans = Report.NewRow();
newCans["Date"] = "Cans";
foreach (DataRow drinv in distincttable.Rows)
{
string dtdate1 = drinv["dispatchsno"].ToString();
string dispatchname = drinv["DispName"].ToString();
int totcansissued = 0;
int totcansreceived = 0;
int diffcans = 0;
foreach (DataRow drinvc in dtInventory.Rows)
{
string dtdate2 = drinvc["DispName"].ToString();
if (dispatchname == dtdate2)
{
if (drinvc["sno"].ToString() == "2")
{
int issuedcrates = 0;
int receivedcrates = 0;
int.TryParse(drinvc["issued"].ToString(), out issuedcrates);
int.TryParse(drinvc["returnqty"].ToString(), out receivedcrates);
totcansissued += issuedcrates;
totcansreceived += receivedcrates;
}
if (drinvc["sno"].ToString() == "3")
{
int issuedcrates = 0;
int receivedcrates = 0;
int.TryParse(drinvc["issued"].ToString(), out issuedcrates);
int.TryParse(drinvc["returnqty"].ToString(), out receivedcrates);
totcansissued += issuedcrates;
totcansreceived += receivedcrates;
}
if (drinvc["sno"].ToString() == "4")
{
int issuedcrates = 0;
int receivedcrates = 0;
int.TryParse(drinvc["issued"].ToString(), out issuedcrates);
int.TryParse(drinvc["returnqty"].ToString(), out receivedcrates);
totcansissued += issuedcrates;
totcansreceived += receivedcrates;
}
if (drinvc["sno"].ToString() == "5")
{
int issuedcrates = 0;
int receivedcrates = 0;
int.TryParse(drinvc["issued"].ToString(), out issuedcrates);
int.TryParse(drinvc["returnqty"].ToString(), out receivedcrates);
totcansissued += issuedcrates;
totcansreceived += receivedcrates;
}
}
}
diffcans = totcansissued - totcansreceived;
newCans[drinv["DispName"].ToString()] = diffcans;
}
Report.Rows.Add(newCans);
DataRow lastRow = Report.Rows[Report.Rows.Count - 1];
DataRow newAmount = Report.NewRow();
newAmount["Date"] = "Amount";
foreach (DataRow drinv in distincttable.Rows)
{
string dtdate1 = drinv["dispatchsno"].ToString();
string dispatchname = drinv["DispName"].ToString();
foreach (DataRow drinvc in dtInventory.Rows)
{
string dtdate2 = drinvc["DispName"].ToString();
if (dispatchname == dtdate2)
{
int crates = 0;
int cans = 0;
int diff = 0;
int.TryParse(Report.Rows[Report.Rows.Count - 2][dtdate2].ToString(), out crates);
int.TryParse(Report.Rows[Report.Rows.Count - 1][dtdate2].ToString(), out cans);
crates = Math.Abs(crates) * 250;
cans = Math.Abs(cans) * 400;
diff = crates + cans;
newAmount[drinvc["DispName"].ToString()] = diff;
}
}
}
Report.Rows.Add(newAmount);
//DataRow invCharges = Report.NewRow();
//invCharges["Date"] = "Charges";
//foreach (DataRow drinv in distincttable.Rows)
//{
// string dtdate1 = drinv["dispatchsno"].ToString();
// string dispatchname = drinv["DispName"].ToString();
// foreach (DataRow drinvc in dtInventory.Rows)
// {
// string dtdate2 = drinvc["DispName"].ToString();
// if (dispatchname == dtdate2)
// {
// if (drinvc["sno"].ToString() == "1")
// {
// invCharges[drinvc["DispName"].ToString()] = "200.400";
// }
// }
// }
//}
//Report.Rows.Add(invCharges);
DataRow finalamount = Report.NewRow();
finalamount["Date"] = "Final Amount";
Report.Rows.Add(finalamount);
grdReports.DataSource = Report;
grdReports.DataBind();
Session["xportdata"] = Report;
}
if (ddlreporttype.Text == "Dispatch Qty")
{
DataView view = new DataView(dtRoutesData);
DataTable distinctproducts = view.ToTable(true, "DispName", "sno");
DataView view1 = new DataView(dtRoutesData);
DataTable distinctdate = view1.ToTable(true, "I_Date");
Report = new DataTable();
Report.Columns.Add("SNo");
Report.Columns.Add("Date");
int count = 0;
foreach (DataRow dr in distinctproducts.Rows)
{
Report.Columns.Add(dr["DispName"].ToString()).DataType = typeof(Double);
count++;
}
DataView viewPlant = new DataView(dtPlantData);
DataTable Plantproducts = viewPlant.ToTable(true, "DispName", "sno");
foreach (DataRow dr in Plantproducts.Rows)
{
string Disp = dr["DispName"].ToString();
//string[] strName = Disp.Split('_');
Report.Columns.Add(Disp).DataType = typeof(Double);
count++;
//string Disp = dr["DispName"].ToString();
//Report.Columns.Add(Disp).DataType = typeof(Double);
}
Report.Columns.Add("TOTAL").DataType = typeof(Double);
int i = 1;
foreach (DataRow branch in distinctdate.Rows)
{
DataRow newrow = Report.NewRow();
newrow["SNo"] = i++.ToString();
string dtdate1 = branch["I_Date"].ToString();
DateTime dtDOE1 = Convert.ToDateTime(dtdate1).AddDays(1);
string ChangedTime1 = dtDOE1.ToString("dd/MMM/yy");
newrow["Date"] = ChangedTime1;
double total = 0;
foreach (DataRow dr in dtRoutesData.Rows)
{
string dtdate2 = dr["I_Date"].ToString();
DateTime dtDOE2 = Convert.ToDateTime(dtdate2).AddDays(1);
string ChangedTime2 = dtDOE2.ToString("dd/MMM/yy");
int helpers = 0;
if (ChangedTime1 == ChangedTime2)
{
double dispatchqty = 0;
double.TryParse(dr["dispatchqty"].ToString(), out dispatchqty);
newrow[dr["DispName"].ToString()] = Math.Round(dispatchqty, 2);
total += dispatchqty;
}
}
newrow["TOTAL"] = Math.Round(total, 2);
Report.Rows.Add(newrow);
}
foreach (DataRow drR in Report.Rows)
{
foreach (DataRow drP in dtPlantData.Rows)
{
string dtdate2 = drP["I_Date"].ToString();
DateTime dtDOE2 = Convert.ToDateTime(dtdate2).AddDays(1);
string ChangedTime2 = dtDOE2.ToString("dd/MMM/yy");
int helpers = 0;
string Disp = drP["DispName"].ToString();
//string[] strName = Disp.Split('_');
if (drR["Date"].ToString() == ChangedTime2)
{
double dispatchqty = 0;
double.TryParse(drP["dispatchqty"].ToString(), out dispatchqty);
cmd = new MySqlCommand("SELECT helpermaster.sno, helpermaster.despsno, helpermaster.first, helpermaster.second, helpermaster.third, helpermaster.fourth, helpermaster.amount, helpermaster.flag, helpermaster.doe, dispatch.DispName, dispatch.Branch_Id FROM helpermaster INNER JOIN dispatch ON helpermaster.despsno = dispatch.sno WHERE (dispatch.BranchID = @Branchid)");
cmd.Parameters.AddWithValue("@Branchid", drP["sno"].ToString());
//cmd = new MySqlCommand("SELECT helpermaster.sno, helpermaster.despsno, helpermaster.first, helpermaster.second, helpermaster.third, helpermaster.fourth, helpermaster.amount, helpermaster.flag, helpermaster.doe, dispatch.DispName FROM helpermaster INNER JOIN dispatch ON helpermaster.despsno = dispatch.sno WHERE (dispatch.DispName = @DispName)");
//cmd.Parameters.AddWithValue("@DispName", drP["DispName"].ToString());
DataTable dtHelper = vdm.SelectQuery(cmd).Tables[0];
if (dtHelper.Rows.Count > 0)
{
string fst = dtHelper.Rows[0]["first"].ToString();
double first = 0;
double.TryParse(fst, out first);
string snd = dtHelper.Rows[0]["second"].ToString();
double second = 0;
double.TryParse(snd, out second);
string thrd = dtHelper.Rows[0]["third"].ToString();
double third = 0;
double.TryParse(thrd, out third);
string foth = dtHelper.Rows[0]["fourth"].ToString();
double fourth = 0;
double.TryParse(foth, out fourth);
if (dispatchqty <= first)
{
helpers = 1;
}
else if (dispatchqty <= second)
{
helpers = 2;
}
else if (dispatchqty <= third)
{
helpers = 3;
}
else if (dispatchqty <= fourth)
{
helpers = 4;
}
else if (dispatchqty >= fourth)
{
helpers = 4;
}
drR[Disp] = Math.Round(dispatchqty, 2);
double Emp = 0;
double.TryParse(drR["TOTAL"].ToString(), out Emp);
double totalemp = Emp + dispatchqty;
drR["TOTAL"] = Math.Round(totalemp, 2);
}
}
}
}
DataRow New = Report.NewRow();
New["Date"] = "Total";
double valnewCash = 0.0;
foreach (DataColumn dc in Report.Columns)
{
if (dc.DataType == typeof(Double))
{
var cell = dc.ColumnName;
double.TryParse(Report.Compute("sum([" + dc.ToString() + "])", "[" + dc.ToString() + "]<>'0'").ToString(), out valnewCash);
New[dc.ToString()] = Math.Round(valnewCash, 2);
//newAvg[dc.ToString()] = Math.Round(Totamount, 2);
//newCharges[dc.ToString()] = 0;
}
}
Report.Rows.Add(New);
//Report.Rows.Add(newAvg);
//Report.Rows.Add(newCharges);
grdReports.DataSource = Report;
grdReports.DataBind();
Session["xportdata"] = Report;
}
}
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
grdReports.DataSource = Report;
grdReports.DataBind();
}
}
void GetReport()
{
try
{
lblmsg.Text = "";
pnlHide.Visible = true;
Report = new DataTable();
Session["RouteName"] = ddlSalesOffice.SelectedItem.Text;
Session["xporttype"] = "SapSales";
Session["IDate"] = DateTime.Now.AddDays(1).ToString("dd/MM/yyyy");
vdm = new VehicleDBMgr();
DateTime fromdate = DateTime.Now;
string[] dateFromstrig = txtFromdate.Text.Split(' ');
if (dateFromstrig.Length > 1)
{
if (dateFromstrig[0].Split('-').Length > 0)
{
string[] dates = dateFromstrig[0].Split('-');
string[] times = dateFromstrig[1].Split(':');
fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
lbl_selfromdate.Text = fromdate.ToString("dd/MM/yyyy");
lblRoutName.Text = ddlSalesOffice.SelectedItem.Text;
Session["filename"] = ddlSalesOffice.SelectedItem.Text + " Sap Sales " + fromdate.ToString("dd/MM/yyyy");
//Old 02/02/2017
//cmd = new MySqlCommand("SELECT branchdata_1.whcode, branchdata.tbranchname,branchdata.customercode, branchdata_1.sno, branchdata.BranchName, branchdata.sno AS BSno,indent.IndentNo, indent.IndentType, ROUND(SUM(indents_subtable.unitQty), 2) AS unitQty, indents_subtable.UnitCost, productsdata.tproduct, productsdata.ProductName,productsdata.Itemcode,productsdata.Units, productsdata.sno AS Expr1, branchdata_1.SalesOfficeID, products_category.tcategory, branchproducts.VatPercent FROM (SELECT IndentNo, Branch_id, I_date, Status, IndentType FROM indents WHERE (I_date BETWEEN @starttime AND @endtime) AND (Status <> 'D')) indent INNER JOIN branchdata ON indent.Branch_id = branchdata.sno INNER JOIN indents_subtable ON indent.IndentNo = indents_subtable.IndentNo INNER JOIN productsdata ON indents_subtable.Product_sno = productsdata.sno INNER JOIN branchmappingtable ON branchdata.sno = branchmappingtable.SubBranch INNER JOIN branchdata branchdata_1 ON branchmappingtable.SuperBranch = branchdata_1.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno INNER JOIN branchproducts ON branchmappingtable.SuperBranch = branchproducts.branch_sno AND productsdata.sno = branchproducts.product_sno WHERE (branchmappingtable.SuperBranch = @BranchID) OR (branchdata_1.SalesOfficeID = @SOID) GROUP BY productsdata.sno, BSno, branchmappingtable.SuperBranch ORDER BY branchdata.BranchName");
cmd = new MySqlCommand("SELECT branchdata_1.whcode, branchdata.tbranchname, branchdata.customercode, branchdata_1.sno, branchdata.BranchName, branchdata.sno AS BSno, indent.IndentNo,indent.IndentType, ROUND(SUM(indents_subtable.unitQty), 2) AS unitQty, indents_subtable.UnitCost, productsdata.tproduct, productsdata.ProductName, productsdata.Itemcode,productsdata.hsncode, productsdata.Units, productsdata.sno AS Expr1, branchdata_1.SalesOfficeID, products_category.tcategory, branchproducts.VatPercent,productsdata.igst, productsdata.cgst, productsdata.sgst, branchdata.BranchCode, branchdata_1.stateid FROM (SELECT IndentNo, Branch_id, I_date, Status, IndentType FROM indents WHERE (I_date BETWEEN @starttime AND @endtime) AND (Status <> 'D')) indent INNER JOIN branchdata ON indent.Branch_id = branchdata.sno INNER JOIN indents_subtable ON indent.IndentNo = indents_subtable.IndentNo INNER JOIN productsdata ON indents_subtable.Product_sno = productsdata.sno INNER JOIN branchmappingtable ON branchdata.sno = branchmappingtable.SubBranch INNER JOIN branchdata branchdata_1 ON branchmappingtable.SuperBranch = branchdata_1.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno INNER JOIN branchproducts ON branchmappingtable.SuperBranch = branchproducts.branch_sno AND productsdata.sno = branchproducts.product_sno WHERE (branchmappingtable.SuperBranch = @BranchID) AND (indents_subtable.unitQty <>0) OR (branchdata_1.SalesOfficeID = @SOID) AND (indents_subtable.unitQty <>0) GROUP BY productsdata.sno, BSno, branchmappingtable.SuperBranch ORDER BY branchdata.BranchName");
if (Session["salestype"].ToString() == "Plant")
{
string BranchID = ddlSalesOffice.SelectedValue;
if (BranchID == "572")
{
BranchID = "158";
}
cmd.Parameters.AddWithValue("@BranchID", BranchID);
cmd.Parameters.AddWithValue("@SOID", BranchID);
}
else
{
cmd.Parameters.AddWithValue("@BranchID", Session["branch"]);
cmd.Parameters.AddWithValue("@SOID", Session["branch"]);
}
cmd.Parameters.AddWithValue("@starttime", GetLowDate(fromdate.AddDays(-1)));
cmd.Parameters.AddWithValue("@endtime", GetHighDate(fromdate.AddDays(-1)));
DataTable dtble = vdm.SelectQuery(cmd).Tables[0];
DateTime ReportDate = VehicleDBMgr.GetTime(vdm.conn);
DateTime dtapril = new DateTime();
DateTime dtmarch = new DateTime();
int currentyear = ReportDate.Year;
int nextyear = ReportDate.Year + 1;
if (ReportDate.Month > 3)
{
string apr = "4/1/" + currentyear;
dtapril = DateTime.Parse(apr);
string march = "3/31/" + nextyear;
dtmarch = DateTime.Parse(march);
}
if (ReportDate.Month <= 3)
{
string apr = "4/1/" + (currentyear - 1);
dtapril = DateTime.Parse(apr);
string march = "3/31/" + (nextyear - 1);
dtmarch = DateTime.Parse(march);
}
if (dtble.Rows.Count > 0)
{
DataView view = new DataView(dtble);
Report.Columns.Add("Ledger Type");
Report.Columns.Add("Customer Code");
Report.Columns.Add("Customer Name");
Report.Columns.Add("Invoice Date");
Report.Columns.Add("Invoce No");
Report.Columns.Add("HSN CODE");
Report.Columns.Add("Item Code");
Report.Columns.Add("Item Name");
Report.Columns.Add("Qty");
Report.Columns.Add("Rate");
Report.Columns.Add("Tax Code");
Report.Columns.Add("Sales Type");
Report.Columns.Add("TAX%");
Report.Columns.Add("Taxable Value");
Report.Columns.Add("Rounding Off");
Report.Columns.Add("WH Code");
Report.Columns.Add("Inv Value");
Report.Columns.Add("Net Value");
Report.Columns.Add("Narration");
int i = 1;
cmd = new MySqlCommand("SELECT branchdata.whcode,branchdata.sno,branchdata.Branchcode,branchdata.companycode,branchdata.tax,branchdata.ntax, branchdata.BranchName,branchdata.stateid, statemastar.statename, statemastar.statecode , statemastar.gststatecode FROM branchdata INNER JOIN statemastar ON branchdata.stateid = statemastar.sno WHERE (branchdata.sno = @BranchID)");
if (Session["salestype"].ToString() == "Plant")
{
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
}
else
{
cmd.Parameters.AddWithValue("@BranchID", Session["branch"]);
}
DataTable dtstatename = vdm.SelectQuery(cmd).Tables[0];
string statename = "";
string statecode = "";
string fromstateid = "";
string Branchcode = "";
string gststatecode = "";
string companycode = "";
string whcode = "";
string ntax = "";
string tax = "";
if (dtstatename.Rows.Count > 0)
{
Branchcode = dtstatename.Rows[0]["Branchcode"].ToString();
statename = dtstatename.Rows[0]["statename"].ToString();
statecode = dtstatename.Rows[0]["statecode"].ToString();
fromstateid = dtstatename.Rows[0]["stateid"].ToString();
gststatecode = dtstatename.Rows[0]["gststatecode"].ToString();
companycode = dtstatename.Rows[0]["companycode"].ToString();
whcode = dtstatename.Rows[0]["whcode"].ToString();
ntax = dtstatename.Rows[0]["ntax"].ToString();
tax = dtstatename.Rows[0]["tax"].ToString();
}
foreach (DataRow branch in dtble.Rows)
{
if (branch["igst"].ToString() != "0")
{
DataRow newrow = Report.NewRow();
string DCNO = branch["IndentNo"].ToString();
whcode = branch["whcode"].ToString();
DCNO = Branchcode + "/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "T/" + DCNO;
double igst = 0;
double.TryParse(branch["igst"].ToString(), out igst);
string tcategory = "";
string TaxCode = "GSTEXEMP";
double vatpercent = 0;
//NEW
newrow["Customer Name"] = branch["tBranchName"].ToString();
newrow["Customer Code"] = branch["customercode"].ToString();
// newrow["Customer Code"] = branch["customercode"].ToString();
newrow["WH Code"] = whcode;
newrow["Invoce No"] = DCNO;
if (ddlSalesOffice.SelectedValue == "306")
{
newrow["Invoice Date"] = fromdate.AddDays(1).ToString("dd-MMM-yyyy");
}
else
{
newrow["Invoice Date"] = fromdate.ToString("dd-MMM-yyyy");
}
newrow["HSN CODE"] = branch["hsncode"].ToString();
newrow["Item Name"] = branch["tProduct"].ToString();
newrow["Item Code"] = branch["Itemcode"].ToString();
//newrow["Category Code"] = branch["categorycode"].ToString();
double percent = 0;
newrow["Qty"] = branch["unitQty"].ToString();
double UnitCost = 0;
double Unitprice = 0;
double.TryParse(branch["UnitCost"].ToString(), out UnitCost);
Unitprice = UnitCost;
double.TryParse(branch["igst"].ToString(), out igst);
float rate = 0;
double invval = 0;
double qty = 0;
double.TryParse(branch["unitQty"].ToString(), out qty);
double taxval = 0;
float.TryParse(branch["UnitCost"].ToString(), out rate);
double tot_vatamount = 0;
double PAmount = 0;
string tostateid = branch["stateid"].ToString();
//NEW CLOSING
tcategory = branch["tcategory"].ToString();
if (fromstateid == tostateid)
{
double sgst = 0;
double sgstamount = 0;
double cgst = 0;
double cgstamount = 0;
double Igst = 0;
double Igstamount = 0;
double totRate = 0;
double.TryParse(branch["Igst"].ToString(), out Igst);
double Igstcon = 100 + Igst;
Igstamount = (rate / Igstcon) * Igst;
Igstamount = Math.Round(Igstamount, 2);
totRate = Igstamount;
if (igst == null || igst == 0.0)
{
tcategory = branch["tcategory"].ToString();
}
else
{
tcategory = branch["tcategory"].ToString() + "@" + branch["cgst"].ToString() + "-CGST/SGST-" + Branchcode;
}
newrow["Ledger Type"] = tcategory.ToString();
double Vatrate = rate - totRate;
Vatrate = Math.Round(Vatrate, 2);
newrow["Rate"] = Vatrate.ToString();
PAmount = qty * Vatrate;
newrow["Taxable Value"] = Math.Round(PAmount, 2);
tot_vatamount = (PAmount * Igst) / 100;
sgstamount = (tot_vatamount / 2);
sgstamount = Math.Round(sgstamount, 2);
if (branch["cgst"].ToString() != "0")
{
double cgsttax = Convert.ToDouble(branch["cgst"].ToString());
cgsttax = cgsttax + cgsttax;
TaxCode = "CGST" + cgsttax + "";
newrow["TAX%"] = Convert.ToDouble(branch["cgst"].ToString()) + Convert.ToDouble(branch["cgst"].ToString());
}
newrow["TAX CODE"] = TaxCode;
newrow["Sales Type"] = "8";
tcategory = branch["tcategory"].ToString() + " " + "@" + " " + "-CGST/SGST-" + Branchcode;;
}
else
{
double Igst = 0;
double Igstamount = 0;
double totRate = 0;
double.TryParse(branch["Igst"].ToString(), out Igst);
double Igstcon = 100 + Igst;
Igstamount = (rate / Igstcon) * Igst;
Igstamount = Math.Round(Igstamount, 2);
totRate = Igstamount;
if (igst == null || igst == 0.0)
{
tcategory = branch["tcategory"].ToString();
}
else
{
tcategory = branch["tcategory"].ToString() + "@" + branch["Igst"].ToString() + "-IGST-" + Branchcode;
}
newrow["Ledger Type"] = tcategory.ToString();
double Vatrate = rate - totRate;
Vatrate = Math.Round(Vatrate, 2);
newrow["Rate"] = Vatrate.ToString();
PAmount = qty * Vatrate;
newrow["Taxable Value"] = Math.Round(PAmount, 2);
tot_vatamount = (PAmount * Igst) / 100;
newrow["Sales Type"] = "208";
if (branch["Igst"].ToString() != "0")
{
double igsttax = Convert.ToDouble(branch["Igst"].ToString());
TaxCode = "IGST" + branch["Igst"].ToString() + "";
newrow["TAX%"] = branch["Igst"].ToString();
}
newrow["TAX CODE"] = TaxCode;
}
newrow["Tax Code"] = TaxCode.ToString();
invval = Math.Round(invval, 2);
double netvalue = 0;
netvalue = invval + taxval;
netvalue = Math.Round(netvalue, 2);
double tot_amount = PAmount + tot_vatamount;
tot_amount = Math.Round(tot_amount, 2);
newrow["Net Value"] = tot_amount;
newrow["Narration"] = "Being the sale of milk to " + branch["tBranchName"].ToString() + " vide DC No " + DCNO + ",DC Date " + fromdate.ToString("dd/MM/yyyy") + ",Emp Name " + Session["EmpName"].ToString();
Report.Rows.Add(newrow);
i++;
}
else
{
string TaxCode = "";
DataRow newrow = Report.NewRow();
string DCNO = branch["IndentNo"].ToString();
whcode = branch["whcode"].ToString();
DCNO = Branchcode + "/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "N/" + DCNO;
newrow["Customer Name"] = branch["tBranchName"].ToString();
newrow["Customer Code"] = branch["customercode"].ToString();
newrow["WH Code"] = whcode;
newrow["Invoce No"] = DCNO;
if (ddlSalesOffice.SelectedValue == "306")
{
newrow["Invoice Date"] = fromdate.AddDays(1).ToString("dd-MMM-yyyy");
}
else
{
newrow["Invoice Date"] = fromdate.ToString("dd-MMM-yyyy");
}
newrow["HSN CODE"] = branch["hsncode"].ToString();
newrow["Item Name"] = branch["tProduct"].ToString();
newrow["Item Code"] = branch["Itemcode"].ToString();
double igst = 0;
double.TryParse(branch["igst"].ToString(), out igst);
double delqty = 0;
double.TryParse(branch["unitQty"].ToString(), out delqty);
string tcategory = "";
double percent = 0;
newrow["Qty"] = branch["unitQty"].ToString();
double UnitCost = 0;
double Unitprice = 0;
double.TryParse(branch["unitQty"].ToString(), out UnitCost);
Unitprice = UnitCost;
double.TryParse(branch["igst"].ToString(), out igst);
float rate = 0;
double invval = 0;
double qty = 0;
double.TryParse(branch["unitQty"].ToString(), out qty);
double taxval = 0;
float.TryParse(branch["UnitCost"].ToString(), out rate);
double tot_vatamount = 0;
double PAmount = 0;
string tostateid = branch["stateid"].ToString();
double Igst = 0;
double totRate = 0;
double.TryParse(branch["Igst"].ToString(), out Igst);
if (igst == null || igst == 0.0)
{
tcategory = branch["tcategory"].ToString();
}
newrow["TAX%"] = "0";
newrow["Sales Type"] = 208;
newrow["Ledger Type"] = tcategory.ToString();
newrow["Rate"] = rate.ToString();
PAmount = qty * rate;
newrow["Taxable Value"] = Math.Round(PAmount, 2);
TaxCode = "GSTEXEMP";
newrow["TAX CODE"] = TaxCode;
newrow["Net Value"] = Math.Round(PAmount, 2);
newrow["Narration"] = "Being the sale of milk to " + branch["tBranchName"].ToString() + " vide DC No " + DCNO + ",DC Date " + fromdate.ToString("dd/MM/yyyy") + ",Emp Name " + Session["EmpName"].ToString();
Report.Rows.Add(newrow);
}
}
grdReports.DataSource = Report;
grdReports.DataBind();
Session["xportdata"] = Report;
}
else
{
pnlHide.Visible = false;
lblmsg.Text = "No Indent Found";
grdReports.DataSource = Report;
grdReports.DataBind();
}
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
grdReports.DataSource = Report;
grdReports.DataBind();
}
}
protected void BtnSave_Click(object sender, EventArgs e)
{
try
{
vdm = new VehicleDBMgr();
DateTime CreateDate = VehicleDBMgr.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);
}
}
DataTable CustomerCodes = new DataTable();
CustomerCodes.Columns.Add("Ledger Type");
CustomerCodes.Columns.Add("Customer Code");
CustomerCodes.Columns.Add("Customer Name");
CustomerCodes.Columns.Add("Invoice Date");
CustomerCodes.Columns.Add("Invoce No");
CustomerCodes.Columns.Add("HSN CODE");
CustomerCodes.Columns.Add("Item Code");
CustomerCodes.Columns.Add("Item Name");
CustomerCodes.Columns.Add("Qty");
CustomerCodes.Columns.Add("Rate");
CustomerCodes.Columns.Add("Tax Code");
CustomerCodes.Columns.Add("Sales Type");
CustomerCodes.Columns.Add("TAX%");
CustomerCodes.Columns.Add("Taxable Value");
CustomerCodes.Columns.Add("Rounding Off");
CustomerCodes.Columns.Add("WH Code");
CustomerCodes.Columns.Add("Inv Value");
CustomerCodes.Columns.Add("Net Value");
CustomerCodes.Columns.Add("Narration");
cmd = new MySqlCommand("SELECT sno, BranchName, whcode, ladger_dr_code, tax, ntax, ledger_jv_code FROM branchdata WHERE (sno = @BranchID)");
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
DataTable dtwhscode = vdm.SelectQuery(cmd).Tables[0];
////sqlcmd = new SqlCommand("SELECT CreateDate, CardCode, CardName, TaxDate, DocDate, DocDueDate, DiscPercent, ReferenceNo FROM EMRORDR WHERE (TaxDate BETWEEN @d1 AND @d2) AND (WhsCode = @WhsCode) AND CardCode=@CardCode");
////sqlcmd.Parameters.Add("@d1", GetLowDate(fromdate));
////sqlcmd.Parameters.Add("@d2", GetHighDate(fromdate));
////sqlcmd.Parameters.Add("@WhsCode", dtwhscode.Rows[0]["whcode"].ToString());
////sqlcmd.Parameters.Add("@CardCode", dtwhscode.Rows[0]["whcode"].ToString());
DataTable dtOrder = SAPvdm.SelectQuery(sqlcmd).Tables[0];
if (dtOrder.Rows.Count > 0)
{
lblmsg.Text = "This Transaction already saved";
}
else
{
foreach (DataRow dr in dt.Rows)
{
sqlcmd = new SqlCommand("SELECT CreateDate, CardCode, CardName, TaxDate, DocDate, DocDueDate, DiscPercent, ReferenceNo FROM EMRORDR WHERE (TaxDate BETWEEN @d1 AND @d2) AND (WhsCode = @WhsCode) AND CardCode=@CardCode");
sqlcmd.Parameters.Add("@d1", GetLowDate(fromdate));
sqlcmd.Parameters.Add("@d2", GetHighDate(fromdate));
sqlcmd.Parameters.Add("@WhsCode", dtwhscode.Rows[0]["whcode"].ToString());
sqlcmd.Parameters.Add("@CardCode", dtwhscode.Rows[0]["whcode"].ToString());
string Customercode = dr["Customer Code"].ToString();
string whccode = dr["WH Code"].ToString();
if (Customercode == "CHN01")
{
}
if (Customercode == "")
{
DataRow newrow = CustomerCodes.NewRow();
newrow["Ledger Type"] = dr["Ledger Type"].ToString();
newrow["Customer Name"] = dr["Customer Name"].ToString();
newrow["Customer Code"] = dr["Customer Code"].ToString();
newrow["Invoice Date"] = dr["Invoice Date"].ToString();
newrow["Invoce No"] = dr["Invoce No"].ToString();
newrow["HSN CODE"] = dr["HSN CODE"].ToString();
newrow["Item Code"] = dr["Item Code"].ToString();
newrow["Item Name"] = dr["Item Name"].ToString();
newrow["Qty"] = dr["Qty"].ToString();
newrow["Rate"] = dr["Rate"].ToString();
newrow["Tax Code"] = dr["Tax Code"].ToString();
newrow["Sales Type"] = dr["Sales Type"].ToString();
newrow["TAX%"] = dr["TAX%"].ToString();
newrow["Taxable Value"] = dr["Taxable Value"].ToString();
newrow["WH Code"] = dr["WH Code"].ToString();
newrow["Net Value"] = dr["Net Value"].ToString();
newrow["Narration"] = dr["Narration"].ToString();
CustomerCodes.Rows.Add(newrow);
}
else
{
if (Customercode.Length >= 8)
{
string Itemcode = dr["Item Code"].ToString();
if (Itemcode == "")
{
}
else
{
//sqlcmd = new SqlCommand("SELECT CreateDate, CardCode, CardName, TaxDate, DocDate, DocDueDate, DiscPercent, ReferenceNo FROM EMRORDR WHERE (TaxDate BETWEEN @d1 AND @d2) AND (ReferenceNo = @ReferenceNo) AND (itemcode=@itemcode) AND (WhsCode = @WhsCode)");
//sqlcmd.Parameters.Add("@d1", GetLowDate(fromdate));
//sqlcmd.Parameters.Add("@d2", GetHighDate(fromdate));
//sqlcmd.Parameters.Add("@ReferenceNo", dr["Invoce No"].ToString());
//sqlcmd.Parameters.Add("@WhsCode", whccode);
//sqlcmd.Parameters.Add("@itemcode", dr["Item Code"].ToString());
//DataTable dtSalesOrder = SAPvdm.SelectQuery(sqlcmd).Tables[0];
//if (dtSalesOrder.Rows.Count > 0)
//{
//}
//else
//{
sqlcmd = new SqlCommand("Insert into EMRORDR (cardcode,cardname,TaxDate, DocDate, DocDueDate,dscription,itemcode,quantity,price,whscode,vat_percent,taxamount,ReferenceNo,TaxCode,B1Upload,Processed,CreateDate,REMARKS,SALETYPE) values(@cardcode,@cardname,@TaxDate,@DocDate,@DocDueDate,@dscription,@itemcode,@quantity,@price,@whscode,@vat_percent,@taxamount,@ReferenceNo,@TaxCode,@B1Upload,@Processed,@CreateDate,@REMARKS,@SALETYPE)");
sqlcmd.Parameters.Add("@cardcode", dr["Customer Code"].ToString());
sqlcmd.Parameters.Add("@cardname", dr["Customer Name"].ToString());
sqlcmd.Parameters.Add("@TaxDate", GetLowDate(fromdate));
sqlcmd.Parameters.Add("@docdate", GetLowDate(fromdate));
sqlcmd.Parameters.Add("@DocDueDate", GetLowDate(fromdate));
sqlcmd.Parameters.Add("@dscription", dr["Item Name"].ToString());
sqlcmd.Parameters.Add("@itemcode", dr["Item Code"].ToString());
sqlcmd.Parameters.Add("@quantity", dr["Qty"].ToString());
sqlcmd.Parameters.Add("@price", dr["Rate"].ToString());
sqlcmd.Parameters.Add("@whscode", whccode);
sqlcmd.Parameters.Add("@vat_percent", dr["TAX%"].ToString());
sqlcmd.Parameters.Add("@taxamount", dr["Taxable Value"].ToString());
sqlcmd.Parameters.Add("@ReferenceNo", dr["Invoce No"].ToString());
string TaxCode = dr["Tax Code"].ToString();
string B1Upload = "N";
string Processed = "N";
sqlcmd.Parameters.Add("@TaxCode", TaxCode);
sqlcmd.Parameters.Add("@B1Upload", B1Upload);
sqlcmd.Parameters.Add("@Processed", Processed);
sqlcmd.Parameters.Add("@CreateDate", CreateDate);
sqlcmd.Parameters.Add("@REMARKS", dr["Narration"].ToString());
string salestype = dr["Sales Type"].ToString();
sqlcmd.Parameters.Add("@SALETYPE", salestype);
//SAPvdm.insert(sqlcmd);
}
//}
}
else
{
DataRow newrow1 = CustomerCodes.NewRow();
newrow1["Ledger Type"] = dr["Ledger Type"].ToString();
newrow1["Customer Name"] = dr["Customer Name"].ToString();
newrow1["Customer Code"] = dr["Customer Code"].ToString();
newrow1["Invoice Date"] = dr["Invoice Date"].ToString();
newrow1["Invoce No"] = dr["Invoce No"].ToString();
newrow1["HSN CODE"] = dr["HSN CODE"].ToString();
newrow1["Item Code"] = dr["Item Code"].ToString();
newrow1["Item Name"] = dr["Item Name"].ToString();
newrow1["Qty"] = dr["Qty"].ToString();
newrow1["Rate"] = dr["Rate"].ToString();
newrow1["Tax Code"] = dr["Tax Code"].ToString();
newrow1["Sales Type"] = dr["Sales Type"].ToString();
newrow1["TAX%"] = dr["TAX%"].ToString();
newrow1["Taxable Value"] = dr["Taxable Value"].ToString();
newrow1["WH Code"] = dr["WH Code"].ToString();
newrow1["Net Value"] = dr["Net Value"].ToString();
newrow1["Narration"] = dr["Narration"].ToString();
CustomerCodes.Rows.Add(newrow1);
}
}
}
//pnlHide.Visible = false;
DataTable dtempty = new DataTable();
grdReports.DataSource = dtempty;
grdReports.DataBind();
grdReports1.DataSource = CustomerCodes;
grdReports1.DataBind();
lblmsg.Text = "Successfully Saved";
}
}
catch (Exception ex)
{
lblmsg.Text = ex.ToString();
}
}
protected void BtnSave_Click(object sender, EventArgs e)
{
try
{
vdm = new VehicleDBMgr();
DateTime CreateDate = VehicleDBMgr.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);
}
}
if (ddlSalesOffice.SelectedValue == "306")
{
fromdate = fromdate.AddDays(1);
}
else
{
fromdate = fromdate;
}
//cmd = new MySqlCommand("SELECT sno, BranchName, whcode, ladger_dr_code, tax, ntax, ledger_jv_code FROM branchdata WHERE (sno = @BranchID)");
//cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
//DataTable dtwhscode = vdm.SelectQuery(cmd).Tables[0];
//sqlcmd = new SqlCommand("SELECT CreateDate, RefDate, DocDate, Ref1, Ref2, Ref3, TransNo, AcctCode FROM EMROJDT WHERE (RefDate BETWEEN @d1 AND @d2) AND (OcrCode = @WhsCode)");
//sqlcmd.Parameters.Add("@d1", GetLowDate(fromdate));
//sqlcmd.Parameters.Add("@d2", GetHighDate(fromdate));
//sqlcmd.Parameters.Add("@WhsCode", dtwhscode.Rows[0]["whcode"].ToString());
//DataTable dtGI = SAPvdm.SelectQuery(sqlcmd).Tables[0];
//if (dtGI.Rows.Count > 0)
//{
// lblmsg.Text = "This Transaction already saved";
//}
//else
//{
DataTable dtJournelPay = new DataTable();
foreach (DataRow dr in dt.Rows)
{
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());
dtJournelPay = SAPvdm.SelectQuery(sqlcmd).Tables[0];
if (dtJournelPay.Rows.Count > 0)
{
lblmsg.Text = "This Transaction already saved";
break;
}
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);
amount = Math.Round(amount, 2);
if (amount < 0)
{
amount = Math.Abs(amount);
double Debit = 0;
sqlcmd.Parameters.Add("@Debit", Debit);
sqlcmd.Parameters.Add("@Credit", amount);
}
else
{
amount = Math.Abs(amount);
double Credit = 0;
sqlcmd.Parameters.Add("@Debit", amount);
sqlcmd.Parameters.Add("@Credit", Credit);
}
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 = "17";
sqlcmd.Parameters.Add("@series", series);
if (amount == 0.0)
{
}
else
{
SAPvdm.insert(sqlcmd);
}
}
}
}
if (dtJournelPay.Rows.Count > 0)
{
pnlHide.Visible = false;
DataTable dtempty = new DataTable();
grdReports.DataSource = dtempty;
grdReports.DataBind();
lblmsg.Text = "This Transaction already saved";
}
else
{
pnlHide.Visible = false;
DataTable dtempty = new DataTable();
grdReports.DataSource = dtempty;
grdReports.DataBind();
lblmsg.Text = "Successfully Saved";
}
//}
}
catch (Exception ex)
{
lblmsg.Text = ex.ToString();
}
}
void GetReport()
{
try
{
lblmsg.Text = "";
pnlHide.Visible = true;
Session["RouteName"] = ddlSalesOffice.SelectedItem.Text;
Session["IDate"] = DateTime.Now.AddDays(1).ToString("dd/MM/yyyy");
vdm = new VehicleDBMgr();
DateTime fromdate = DateTime.Now;
string[] dateFromstrig = txtFromdate.Text.Split(' ');
if (dateFromstrig.Length > 1)
{
if (dateFromstrig[0].Split('-').Length > 0)
{
string[] dates = dateFromstrig[0].Split('-');
string[] times = dateFromstrig[1].Split(':');
fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
Report.Columns.Add("JV No");
Report.Columns.Add("JV Date");
Report.Columns.Add("WH Code");
Report.Columns.Add("Ledger Code");
Report.Columns.Add("Ledger Name");
Report.Columns.Add("Item Code");
Report.Columns.Add("Item Name");
Report.Columns.Add("Category Code");
Report.Columns.Add("Total Amount");
Report.Columns.Add("Narration");
lbl_selfromdate.Text = fromdate.ToString("dd/MM/yyyy");
lblRoutName.Text = ddlSalesOffice.SelectedItem.Text;
Session["xporttype"] = "TallyLekas";
DateTime ReportDate = fromdate;
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);
}
string DCNO = "";
if (ddlSalesOffice.SelectedValue == "174")
{
DCNO = "CHN/JV/" + DCNO;
}
if (ddlSalesOffice.SelectedValue == "271")
{
DCNO = "NLR/JV/" + DCNO;
}
if (ddlSalesOffice.SelectedValue == "285")
{
DCNO = "TPT/JV/" + DCNO;
}
if (ddlSalesOffice.SelectedValue == "306")
{
DCNO = "KANCHI/JV/" + DCNO;
}
if (ddlSalesOffice.SelectedValue == "2749")
{
DCNO = "MDPL/JV/" + DCNO;
}
if (ddlSalesOffice.SelectedValue == "2909")
{
DCNO = "VLR/JV/" + DCNO;
}
if (ddlSalesOffice.SelectedValue == "538")
{
DCNO = "BANG/JV/" + DCNO;
}
if (ddlSalesOffice.SelectedValue == "159")
{
DCNO = "HYD/JV/" + DCNO;
}
if (ddlSalesOffice.SelectedValue == "570")
{
DCNO = "VJD/JV/" + DCNO;
}
if (ddlSalesOffice.SelectedValue == "457")
{
DCNO = "WNGL/JV/" + DCNO;
}
if (ddlSalesOffice.SelectedValue == "3928")
{
DCNO = "AB/JV/" + DCNO;
}
if (ddlSalesOffice.SelectedValue == "158" || ddlSalesOffice.SelectedValue == "572")
{
DCNO = "WYRA/JV/" + DCNO;
}
if (ddlSalesOffice.SelectedValue == "282")
{
DCNO = "KLH/JV/" + DCNO;
}
if (ddlSalesOffice.SelectedValue == "458")
{
DCNO = "KMM/JV/" + DCNO;
}
if (ddlSalesOffice.SelectedValue == "3559")
{
DCNO = "CTR/JV/" + DCNO;
}
cmd = new MySqlCommand("SELECT sno, BranchName, incentivename,ledger_jv_code,whcode FROM branchdata WHERE (sno = @BranchID)");
if (Session["salestype"].ToString() == "Plant")
{
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
}
else
{
cmd.Parameters.AddWithValue("@BranchID", Session["branch"]);
}
DataTable dtincetivename = vdm.SelectQuery(cmd).Tables[0];
Session["filename"] = ddlSalesOffice.SelectedItem.Text + " Tally Lekas" + fromdate.ToString("dd/MM/yyyy");
cmd = new MySqlCommand("SELECT dispatch.DispName, dispatch.sno, dispatch.BranchID, tripdata.I_Date,tripdata.dcno, tripdata.Sno AS TripSno, dispatch.DispMode, branchmappingtable.SuperBranch, triproutes.Tripdata_sno FROM dispatch INNER JOIN triproutes ON dispatch.sno = triproutes.RouteID INNER JOIN tripdata ON triproutes.Tripdata_sno = tripdata.Sno INNER JOIN branchmappingtable ON dispatch.BranchID = branchmappingtable.SubBranch WHERE (dispatch.BranchID = @BranchID) AND (tripdata.I_Date BETWEEN @d1 AND @d2)and (dispatch.DispType='SO') and (tripdata.Status<>'C') OR (tripdata.I_Date BETWEEN @d1 AND @d2) AND (branchmappingtable.SuperBranch = @SuperBranch) and (dispatch.DispType='SO')and (tripdata.Status<>'C') GROUP BY tripdata.Sno ORDER BY dispatch.sno");
cmd.Parameters.AddWithValue("@SuperBranch", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate.AddDays(-1)));
cmd.Parameters.AddWithValue("@d2", GetHighDate(fromdate.AddDays(-1)));
DataTable dtDispnames = vdm.SelectQuery(cmd).Tables[0];
foreach (DataRow dr in dtDispnames.Rows)
{
//cmd = new MySqlCommand("SELECT leakages.TotalLeaks, leakages.TripID, leakages.VLeaks, leakages.VReturns, leakages.ReturnQty, productsdata.tproduct, branchproducts.unitprice, leakages.ProductID, leakages.FreeMilk, leakages.ShortQty, branchproducts.branch_sno FROM productsdata INNER JOIN leakages ON productsdata.sno = leakages.ProductID INNER JOIN branchproducts ON productsdata.sno = branchproducts.product_sno WHERE (leakages.TripID = @tripID) AND (branchproducts.branch_sno = @BranchID)");
cmd = new MySqlCommand("SELECT leakages.TotalLeaks, leakages.TripID, leakages.VLeaks, leakages.VReturns, leakages.ReturnQty, productsdata.tproduct,productsdata.itemcode, branchproducts.unitprice, leakages.ProductID, leakages.FreeMilk, leakages.ShortQty, branchproducts.branch_sno, products_category.categorycode FROM productsdata INNER JOIN leakages ON productsdata.sno = leakages.ProductID INNER JOIN branchproducts ON productsdata.sno = branchproducts.product_sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno WHERE (leakages.TripID = @tripID) AND (branchproducts.branch_sno = @BranchID)");
cmd.Parameters.AddWithValue("@tripID", dr["TripSno"].ToString());
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate.AddDays(-1)));
cmd.Parameters.AddWithValue("@d2", GetHighDate(fromdate.AddDays(-1)));
DataTable dtLeakble = vdm.SelectQuery(cmd).Tables[0];
string tripID = "";
if (dtLeakble.Rows.Count > 0)
{
DataView view = new DataView(dtLeakble);
int i = 1;
double totleaksamount = 0;
foreach (DataRow branch in dtLeakble.Rows)
{
DataRow newrow = Report.NewRow();
double Qty = 0;
double.TryParse(branch["TotalLeaks"].ToString(), out Qty);
if (Qty == 0.0)
{
}
else
{
double Rate = 0;
double.TryParse(branch["unitprice"].ToString(), out Rate);
double amount = 0;
amount = Qty * Rate;
totleaksamount += amount;
tripID = dr["dcno"].ToString();
newrow["JV No"] = DCNO + "L" + dr["dcno"].ToString();
if (ddlSalesOffice.SelectedValue == "306")
{
newrow["JV Date"] = fromdate.AddDays(1).ToString("dd-MMM-yyyy");
}
else
{
newrow["JV Date"] = fromdate.ToString("dd-MMM-yyyy");
}
newrow["WH Code"] = dtincetivename.Rows[0]["whcode"].ToString();
newrow["Ledger Code"] = dtincetivename.Rows[0]["ledger_jv_code"].ToString();
newrow["Ledger Name"] = "Sales-" + dtincetivename.Rows[0]["incentivename"].ToString();
newrow["Item Code"] = branch["itemcode"].ToString();
newrow["Item Name"] = branch["tproduct"].ToString();
newrow["Category Code"] = branch["categorycode"].ToString();
newrow["Total Amount"] = "-" + amount;
if (amount == 0.0)
{
}
else
{
newrow["Narration"] = "Being the Sale Of Milk Through " + ddlSalesOffice.SelectedItem.Text + ". This is Leakage Milk Vide JV No " + dr["dcno"].ToString() + ",Emp Name " + Session["EmpName"].ToString();
Report.Rows.Add(newrow);
i++;
}
}
}
//DataRow newrowleaks = Report.NewRow();
//newrowleaks["JV No"] = DCNO + "L" + tripID;
//newrowleaks["JV Date"] = fromdate.ToString("dd-MMM-yyyy");
//newrowleaks["WH Code"] = dtincetivename.Rows[0]["whcode"].ToString();
//newrowleaks["Ledger Code"] = dtincetivename.Rows[0]["ledger_jv_code"].ToString();
//newrowleaks["Ledger Name"] = "Sales-" + dtincetivename.Rows[0]["incentivename"].ToString();
//newrowleaks["Total Amount"] = "-" + totleaksamount;
//newrowleaks["Narration"] = "Being the Sale Of Milk Through " + ddlSalesOffice.SelectedItem.Text + ". This is Leakage Milk Vide JV No " + dr["dcno"].ToString() + ",Emp Name " + Session["EmpName"].ToString();
//Report.Rows.Add(newrowleaks);
DataRow newrow2 = Report.NewRow();
newrow2["JV No"] = DCNO + "L" + tripID;
if (ddlSalesOffice.SelectedValue == "306")
{
newrow2["JV Date"] = fromdate.AddDays(1).ToString("dd-MMM-yyyy");
}
else
{
newrow2["JV Date"] = fromdate.ToString("dd-MMM-yyyy");
}
newrow2["WH Code"] = dtincetivename.Rows[0]["whcode"].ToString();
newrow2["Ledger Code"] = "5134004";
newrow2["Ledger Name"] = "Sales leakages-" + dtincetivename.Rows[0]["incentivename"].ToString();
newrow2["Total Amount"] = totleaksamount;
Report.Rows.Add(newrow2);
double totreturnamount = 0;
foreach (DataRow branch in dtLeakble.Rows)
{
DataRow newrow = Report.NewRow();
double ReturnQty = 0;
double.TryParse(branch["ReturnQty"].ToString(), out ReturnQty);
if (ReturnQty == 0.0)
{
}
else
{
double Rate = 0;
double.TryParse(branch["unitprice"].ToString(), out Rate);
double amount = 0;
amount = ReturnQty * Rate;
totreturnamount += amount;
tripID = dr["dcno"].ToString();
newrow["JV No"] = DCNO + "R" + dr["dcno"].ToString();
if (ddlSalesOffice.SelectedValue == "306")
{
newrow["JV Date"] = fromdate.AddDays(1).ToString("dd-MMM-yyyy");
}
else
{
newrow["JV Date"] = fromdate.ToString("dd-MMM-yyyy");
}
newrow["WH Code"] = dtincetivename.Rows[0]["whcode"].ToString();
newrow["Ledger Code"] = dtincetivename.Rows[0]["ledger_jv_code"].ToString();
newrow["Ledger Name"] = "Sales-" + dtincetivename.Rows[0]["incentivename"].ToString();
newrow["Item Code"] = branch["itemcode"].ToString();
newrow["Item Name"] = branch["tproduct"].ToString();
newrow["Category Code"] = branch["categorycode"].ToString();
newrow["Total Amount"] = "-" + amount;
newrow["Narration"] = "Being the Sale Of Milk Through " + ddlSalesOffice.SelectedItem.Text + ". This is Return Milk Vide JV No " + dr["dcno"].ToString() + ",Emp Name " + Session["EmpName"].ToString();
Report.Rows.Add(newrow);
i++;
}
}
//DataRow newretruns = Report.NewRow();
//newretruns["JV No"] = DCNO + "R" + tripID;
//newretruns["JV Date"] = fromdate.ToString("dd-MMM-yyyy");
//newretruns["WH Code"] = dtincetivename.Rows[0]["whcode"].ToString();
//newretruns["Ledger Code"] = dtincetivename.Rows[0]["ledger_jv_code"].ToString();
//newretruns["Ledger Name"] = "Sales-" + dtincetivename.Rows[0]["incentivename"].ToString();
//if (totreturnamount == 0.0)
//{
//}
//else
//{
// newretruns["Total Amount"] = "-" + totreturnamount;
// newretruns["Narration"] = "Being the Sale Of Milk Through " + ddlSalesOffice.SelectedItem.Text + ". This is Return Milk Vide JV No " + dr["dcno"].ToString() + ",Emp Name " + Session["EmpName"].ToString();
// Report.Rows.Add(newretruns);
//}
DataRow newrow4 = Report.NewRow();
newrow4["JV No"] = DCNO + "R" + tripID;
if (ddlSalesOffice.SelectedValue == "306")
{
newrow4["JV Date"] = fromdate.AddDays(1).ToString("dd-MMM-yyyy");
}
else
{
newrow4["JV Date"] = fromdate.ToString("dd-MMM-yyyy");
}
newrow4["WH Code"] = dtincetivename.Rows[0]["whcode"].ToString();
newrow4["Ledger Code"] = "5134005";
newrow4["Ledger Name"] = "Sales Returns-" + dtincetivename.Rows[0]["incentivename"].ToString();
if (totreturnamount == 0.0)
{
}
else
{
newrow4["Total Amount"] = totreturnamount;
Report.Rows.Add(newrow4);
}
}
//cmd = new MySqlCommand("SELECT SUM(leakages.ShortQty) AS ShortQty, SUM(leakages.FreeMilk) AS FreeMilk, productsdata.tproduct, branchproducts.unitprice FROM leakages INNER JOIN tripdata ON leakages.TripID = tripdata.Sno INNER JOIN productsdata ON leakages.ProductID = productsdata.sno INNER JOIN branchproducts ON productsdata.sno = branchproducts.product_sno WHERE (tripdata.ATripid = @TripID) AND (branchproducts.branch_sno = @BranchID) GROUP BY productsdata.tproduct");
cmd = new MySqlCommand("SELECT SUM(leakages.ShortQty) AS ShortQty, SUM(leakages.FreeMilk) AS FreeMilk, productsdata.tproduct, branchproducts.unitprice, products_category.categorycode,productsdata.itemcode FROM leakages INNER JOIN tripdata ON leakages.TripID = tripdata.Sno INNER JOIN productsdata ON leakages.ProductID = productsdata.sno INNER JOIN branchproducts ON productsdata.sno = branchproducts.product_sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno WHERE (tripdata.ATripid = @TripID) AND (branchproducts.branch_sno = @BranchID) GROUP BY productsdata.tproduct, products_category.categorycode,productsdata.ProductName");
cmd.Parameters.AddWithValue("@TripID", dr["TripSno"].ToString());
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
DataTable Dtfreemilk = vdm.SelectQuery(cmd).Tables[0];
//cmd = new MySqlCommand("SELECT branchleaktrans.ShortQty AS ShortQty,branchleaktrans.FreeQty AS FreeMilk, productsdata.tproduct, branchproducts.unitprice FROM branchproducts INNER JOIN productsdata ON branchproducts.product_sno = productsdata.sno INNER JOIN branchleaktrans ON productsdata.sno = branchleaktrans.ProdId WHERE (branchproducts.branch_sno = @BranchID) AND (branchleaktrans.TripId = @TripID)GROUP BY productsdata.tproduct");
cmd = new MySqlCommand("SELECT branchleaktrans.ShortQty, branchleaktrans.FreeQty AS FreeMilk, productsdata.tproduct, branchproducts.unitprice, products_category.categorycode,productsdata.itemcode FROM branchproducts INNER JOIN productsdata ON branchproducts.product_sno = productsdata.sno INNER JOIN branchleaktrans ON productsdata.sno = branchleaktrans.ProdId INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno WHERE (branchproducts.branch_sno = @BranchID) AND (branchleaktrans.TripId = @TripID) GROUP BY productsdata.tproduct, products_category.categorycode,productsdata.ProductName");
cmd.Parameters.AddWithValue("@TripID", dr["TripSno"].ToString());
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
DataTable dtsalesofficeshortfree = vdm.SelectQuery(cmd).Tables[0];
DataTable newdt = new DataTable();
newdt = Dtfreemilk.Copy();
newdt.Merge(dtsalesofficeshortfree, true, MissingSchemaAction.Ignore);
double totshortamount = 0;
foreach (DataRow branch in newdt.Rows)
{
DataRow newrow = Report.NewRow();
double ShortQty = 0;
double.TryParse(branch["ShortQty"].ToString(), out ShortQty);
if (ShortQty == 0.0)
{
}
else
{
double Rate = 0;
double.TryParse(branch["unitprice"].ToString(), out Rate);
ShortQty = Math.Round(ShortQty, 2);
double amount = 0;
amount = ShortQty * Rate;
totshortamount += amount;
amount = Math.Round(amount, 2);
tripID = dr["dcno"].ToString();
newrow["JV No"] = DCNO + "S" + dr["dcno"].ToString();
if (ddlSalesOffice.SelectedValue == "306")
{
newrow["JV Date"] = fromdate.AddDays(1).ToString("dd-MMM-yyyy");
}
else
{
newrow["JV Date"] = fromdate.ToString("dd-MMM-yyyy");
}
newrow["WH Code"] = dtincetivename.Rows[0]["whcode"].ToString();
newrow["Ledger Code"] = dtincetivename.Rows[0]["ledger_jv_code"].ToString();
newrow["Ledger Name"] = "Sales-" + dtincetivename.Rows[0]["incentivename"].ToString();
newrow["Item Code"] = branch["itemcode"].ToString();
newrow["Item Name"] = branch["tproduct"].ToString();
newrow["Category Code"] = branch["categorycode"].ToString();
newrow["Total Amount"] = "-" + amount;
newrow["Narration"] = "Being the Sale Of Milk Through " + ddlSalesOffice.SelectedItem.Text + ". This is Short Milk Vide JV No " + dr["dcno"].ToString() + ",Emp Name " + Session["EmpName"].ToString();
Report.Rows.Add(newrow);
}
}
//DataRow newShorts = Report.NewRow();
//newShorts["JV No"] = DCNO + "S" + tripID;
//newShorts["JV Date"] = fromdate.ToString("dd-MMM-yyyy");
//newShorts["WH Code"] = dtincetivename.Rows[0]["whcode"].ToString();
//newShorts["Ledger Code"] = dtincetivename.Rows[0]["ledger_jv_code"].ToString();
//newShorts["Ledger Name"] = "Sales-" + dtincetivename.Rows[0]["incentivename"].ToString();
//if (totshortamount == 0.0)
//{
//}
//else
//{
// totshortamount = Math.Round(totshortamount, 2);
// newShorts["Total Amount"] = "-" + totshortamount;
// newShorts["Narration"] = "Being the Sale Of Milk Through " + ddlSalesOffice.SelectedItem.Text + ". This is Short Milk Vide JV No " + dr["dcno"].ToString() + ",Emp Name " + Session["EmpName"].ToString();
// Report.Rows.Add(newShorts);
//}
DataRow newrow6 = Report.NewRow();
newrow6["JV No"] = DCNO + "S" + tripID;
if (ddlSalesOffice.SelectedValue == "306")
{
newrow6["JV Date"] = fromdate.AddDays(1).ToString("dd-MMM-yyyy");
}
else
{
newrow6["JV Date"] = fromdate.ToString("dd-MMM-yyyy");
}
newrow6["WH Code"] = dtincetivename.Rows[0]["whcode"].ToString();
newrow6["Ledger Code"] = "5134009";
newrow6["Ledger Name"] = "Spolige & Shortage-" + dtincetivename.Rows[0]["incentivename"].ToString();
if (totshortamount == 0.0)
{
}
else
{
totshortamount = Math.Round(totshortamount, 2);
newrow6["Total Amount"] = totshortamount;
Report.Rows.Add(newrow6);
}
double totfreeamount = 0;
foreach (DataRow branch in Dtfreemilk.Rows)
{
DataRow newrow = Report.NewRow();
double FreeMilk = 0;
double.TryParse(branch["FreeMilk"].ToString(), out FreeMilk);
if (FreeMilk == 0.0)
{
}
else
{
double Rate = 0;
double.TryParse(branch["unitprice"].ToString(), out Rate);
FreeMilk = Math.Round(FreeMilk, 2);
double amount = 0;
amount = FreeMilk * Rate;
totfreeamount += amount;
tripID = dr["dcno"].ToString();
newrow["JV No"] = DCNO + "F" + dr["dcno"].ToString();
if (ddlSalesOffice.SelectedValue == "306")
{
newrow["JV Date"] = fromdate.AddDays(1).ToString("dd-MMM-yyyy");
}
else
{
newrow["JV Date"] = fromdate.ToString("dd-MMM-yyyy");
}
newrow["WH Code"] = dtincetivename.Rows[0]["whcode"].ToString();
newrow["Ledger Code"] = dtincetivename.Rows[0]["ledger_jv_code"].ToString();
newrow["Ledger Name"] = "Sales-" + dtincetivename.Rows[0]["incentivename"].ToString();
newrow["Item Code"] = branch["itemcode"].ToString();
newrow["Item Name"] = branch["tproduct"].ToString();
newrow["Category Code"] = branch["categorycode"].ToString();
newrow["Total Amount"] = "-" + amount;
newrow["Narration"] = "Being the Sale Of Milk Through " + ddlSalesOffice.SelectedItem.Text + ". This is Free Milk Vide JV No " + dr["dcno"].ToString() + ",Emp Name " + Session["EmpName"].ToString();
Report.Rows.Add(newrow);
}
}
//DataRow newfree = Report.NewRow();
//newfree["JV Date"] = fromdate.ToString("dd-MMM-yyyy");
//newfree["WH Code"] = dtincetivename.Rows[0]["whcode"].ToString();
//newfree["Ledger Code"] = dtincetivename.Rows[0]["ledger_jv_code"].ToString();
//newfree["Ledger Name"] = "Sales-" + dtincetivename.Rows[0]["incentivename"].ToString();
//if (totfreeamount == 0.0)
//{
//}
//else
//{
// totfreeamount = Math.Round(totfreeamount, 2);5134008
// newfree["Total Amount"] = "-" + totfreeamount;
// newfree["Narration"] = "Being the Sale Of Milk Through " + ddlSalesOffice.SelectedItem.Text + ". This is Free Milk Vide JV No " + dr["dcno"].ToString() + ",Emp Name " + Session["EmpName"].ToString();
// Report.Rows.Add(newfree);
//}
DataRow newrow7 = Report.NewRow();
newrow7["JV No"] = DCNO + "F" + tripID;
if (ddlSalesOffice.SelectedValue == "306")
{
newrow7["JV Date"] = fromdate.AddDays(1).ToString("dd-MMM-yyyy");
}
else
{
newrow7["JV Date"] = fromdate.ToString("dd-MMM-yyyy");
}
newrow7["WH Code"] = dtincetivename.Rows[0]["whcode"].ToString();
newrow7["Ledger Code"] = "5134008";
newrow7["Ledger Name"] = "Free Sales-Milk-" + dtincetivename.Rows[0]["incentivename"].ToString();
if (totfreeamount == 0.0)
{
}
else
{
totfreeamount = Math.Round(totfreeamount, 2);
newrow7["Total Amount"] = totfreeamount;
Report.Rows.Add(newrow7);
}
}
grdReports.DataSource = Report;
grdReports.DataBind();
Session["xportdata"] = Report;
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
grdReports.DataSource = Report;
grdReports.DataBind();
}
}
void GetReport()
{
try
{
lblmsg.Text = "";
pnlHide.Visible = true;
Session["IDate"] = DateTime.Now.AddDays(1).ToString("dd/MM/yyyy");
//lblDate.Text = DateTime.Now.AddDays(1).ToString("dd/MM/yyyy");
lblDispatchName.Text = ddlRouteName.SelectedItem.Text;
vdm = new VehicleDBMgr();
DataTable Report = new DataTable();
string[] datestrig = txtdate.Text.Split(' ');
if (datestrig.Length > 1)
{
if (datestrig[0].Split('-').Length > 0)
{
string[] dates = datestrig[0].Split('-');
string[] times = datestrig[1].Split(':');
fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
lblDate.Text = fromdate.AddDays(1).ToString("dd/MM/yyyy");
Session["RouteName"] = ddlRouteName.SelectedItem.Text + fromdate.AddDays(1).ToString("dd/MM/yyyy");
Session["filename"] = ddlRouteName.SelectedItem.Text + fromdate.AddDays(1).ToString("dd/MM/yyyy");
//cmd = new MySqlCommand("SELECT branchroutes.RouteName, productsdata.ProductName, ROUND(SUM(indents_subtable.unitQty), 2) AS unitQty, products_category.Categoryname FROM indents INNER JOIN branchroutesubtable ON indents.Branch_id = branchroutesubtable.BranchID INNER JOIN branchroutes ON branchroutesubtable.RefNo = branchroutes.Sno INNER JOIN indents_subtable ON indents.IndentNo = indents_subtable.IndentNo INNER JOIN productsdata ON indents_subtable.Product_sno = productsdata.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno WHERE (indents.I_date >= @starttime) AND (indents.I_date <= @endtime) AND (branchroutes.Sno BETWEEN 29 AND 33) GROUP BY branchroutes.RouteName, productsdata.ProductName, products_category.Categoryname");
cmd = new MySqlCommand("select Route_id,IndentType from dispatch_sub where dispatch_sno=@dispsno");
cmd.Parameters.AddWithValue("@dispsno", ddlRouteName.SelectedValue);
DataTable dtrouteindenttype = vdm.SelectQuery(cmd).Tables[0];
var routeitype = "";
foreach (DataRow drrouteitype in dtrouteindenttype.Rows)
{
var routeid = drrouteitype["Route_id"].ToString();
routeitype = drrouteitype["IndentType"].ToString();
}
//cmd = new MySqlCommand("SELECT branchroutes.RouteName, productsdata.ProductName, ROUND(SUM(indents_subtable.unitQty), 2) AS unitQty, products_category.Categoryname,productsdata.Units, branchproducts.Rank, invmaster.Qty FROM indents INNER JOIN branchroutesubtable ON indents.Branch_id = branchroutesubtable.BranchID INNER JOIN branchroutes ON branchroutesubtable.RefNo = branchroutes.Sno INNER JOIN indents_subtable ON indents.IndentNo = indents_subtable.IndentNo INNER JOIN productsdata ON indents_subtable.Product_sno = productsdata.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno INNER JOIN dispatch_sub ON branchroutes.Sno = dispatch_sub.Route_id INNER JOIN dispatch ON dispatch_sub.dispatch_sno = dispatch.sno INNER JOIN branchproducts ON productsdata.sno = branchproducts.product_sno INNER JOIN invmaster ON productsdata.Inventorysno = invmaster.sno WHERE (indents.I_date BETWEEN @starttime AND @endtime) AND (indents.IndentType = @itype) AND (dispatch.sno = @dispatchSno) AND (branchproducts.branch_sno=@BranchID) GROUP BY branchroutes.RouteName, productsdata.ProductName, products_category.Categoryname ORDER BY branchproducts.Rank");
cmd = new MySqlCommand("SELECT modifiedroutes.RouteName, ROUND(SUM(indents_subtable.unitQty), 2) AS unitQty, productsdata.ProductName, productsdata.Units, products_category.Categoryname, invmaster.Qty, brnchprdt.Rank FROM dispatch INNER JOIN dispatch_sub ON dispatch.sno = dispatch_sub.dispatch_sno INNER JOIN modifiedroutes ON dispatch_sub.Route_id = modifiedroutes.Sno INNER JOIN modifiedroutesubtable ON modifiedroutes.Sno = modifiedroutesubtable.RefNo INNER JOIN (SELECT IndentNo, Branch_id, I_date, IndentType FROM indents WHERE (I_date BETWEEN @starttime AND @endtime) AND (IndentType = @itype)) indent ON modifiedroutesubtable.BranchID = indent.Branch_id INNER JOIN indents_subtable ON indent.IndentNo = indents_subtable.IndentNo INNER JOIN productsdata ON indents_subtable.Product_sno = productsdata.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno INNER JOIN invmaster ON productsdata.Inventorysno = invmaster.sno INNER JOIN (SELECT branch_sno, product_sno, Rank FROM branchproducts WHERE (branch_sno = @BranchID)) brnchprdt ON productsdata.sno = brnchprdt.product_sno WHERE (dispatch.sno = @dispatchSno) AND (modifiedroutesubtable.EDate IS NULL) AND (modifiedroutesubtable.CDate <= @starttime) OR (dispatch.sno = @dispatchSno) AND (modifiedroutesubtable.EDate > @starttime) AND (modifiedroutesubtable.CDate <= @starttime) GROUP BY modifiedroutes.RouteName, products_category.Categoryname, productsdata.sno ORDER BY brnchprdt.Rank");
if (Session["salestype"].ToString() == "Plant")
{
cmd.Parameters.AddWithValue("@BranchID", Session["BranchID"]);
}
else
{
cmd.Parameters.AddWithValue("@BranchID", Session["branch"]);
}
cmd.Parameters.AddWithValue("@dispatchSno", ddlRouteName.SelectedValue);
cmd.Parameters.AddWithValue("@starttime", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@endtime", GetHighDate(fromdate));
cmd.Parameters.AddWithValue("@itype", routeitype);
DataTable dtble = vdm.SelectQuery(cmd).Tables[0];
// cmd = new MySqlCommand("SELECT products_category.Categoryname, products_subcategory.SubCatName, productsdata.ProductName, productsdata.Units, invmaster.Qty FROM branchproducts INNER JOIN dispatch ON branchproducts.branch_sno = dispatch.Branch_Id INNER JOIN productsdata ON branchproducts.product_sno = productsdata.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno INNER JOIN invmaster ON productsdata.Inventorysno = invmaster.sno WHERE (dispatch.sno = @dispatchSno) GROUP BY productsdata.ProductName ORDER BY productsdata.Rank");
//cmd = new MySqlCommand("SELECT products_category.Categoryname, products_subcategory.SubCatName, productsdata.ProductName, invmaster.Qty, productsdata.Units FROM dispatch INNER JOIN dispatch_sub ON dispatch.sno = dispatch_sub.dispatch_sno INNER JOIN branchroutes ON dispatch_sub.Route_id = branchroutes.Sno INNER JOIN branchproducts ON branchroutes.BranchID = branchproducts.branch_sno INNER JOIN productsdata ON branchproducts.product_sno = productsdata.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno INNER JOIN invmaster ON productsdata.Inventorysno = invmaster.sno WHERE (dispatch.sno = @dispatchSno) GROUP BY productsdata.ProductName ORDER BY productsdata.Rank");
//cmd.Parameters.AddWithValue("@dispatchSno", ddlRouteName.SelectedValue);
//DataTable produtstbl = vdm.SelectQuery(cmd).Tables[0];
if (dtble.Rows.Count > 0)
{
DataView view = new DataView(dtble);
DataTable produtstbl = view.ToTable(true, "ProductName", "Categoryname", "Units", "Qty");
Report = new DataTable();
Report.Columns.Add("SNo");
Report.Columns.Add("Route Name");
int count = 0;
foreach (DataRow dr in produtstbl.Rows)
{
if (dr["Categoryname"].ToString() == "MILK")
{
Report.Columns.Add(dr["ProductName"].ToString()).DataType = typeof(Double);
count++;
}
else
{
Report.Columns.Add(dr["ProductName"].ToString()).DataType = typeof(Double);
}
}
Report.Columns.Add("Total Indent", typeof(Double)).SetOrdinal(count + 2);
Report.Columns.Add("Total MILK CURD AND BM", typeof(Double));
DataTable distincttable = view.ToTable(true, "RouteName");
int i = 1;
foreach (DataRow branch in distincttable.Rows)
{
DataRow newrow = Report.NewRow();
newrow["SNo"] = i;
newrow["Route Name"] = branch["RouteName"].ToString();
double total = 0;
foreach (DataRow dr in dtble.Rows)
{
if (branch["RouteName"].ToString() == dr["RouteName"].ToString())
{
double qtyvalue = 0;
double curdqtyvalue = 0;
double BMqtyvalue = 0;
//newrow[dr["ProductName"].ToString()] = dr["unitQty"].ToString();
if (dr["Categoryname"].ToString() == "MILK")
{
double.TryParse(dr["unitQty"].ToString(), out qtyvalue);
double Qty = 0;
double cratesQty = 0;
double.TryParse(dr["Qty"].ToString(), out Qty);
cratesQty = qtyvalue / Qty;
if (Qty == 12)
{
total += cratesQty;
}
newrow[dr["ProductName"].ToString()] = Math.Round(cratesQty, 2);//cratesQty.ToString();
}
if (dr["Categoryname"].ToString() == "CURD")
{
if (dr["ProductName"].ToString() == "CURD 10 MRP")
{
double.TryParse(dr["unitQty"].ToString(), out curdqtyvalue);
double Qty = 0;
double cratesQty = 0;
double.TryParse(dr["Qty"].ToString(), out Qty);
cratesQty = curdqtyvalue / 10.5;
if (Qty == 12)
{
total += cratesQty;
}
//totalcurd += curdqtyvalue;
newrow[dr["ProductName"].ToString()] = Math.Round(cratesQty, 2);//cratesQty.ToString();
}
if (dr["ProductName"].ToString() == "CURD-450ml")
{
double.TryParse(dr["unitQty"].ToString(), out curdqtyvalue);
double Qty = 0;
double cratesQty = 0;
double.TryParse(dr["Qty"].ToString(), out Qty);
cratesQty = curdqtyvalue / 10.8;
if (Qty == 12)
{
total += cratesQty;
}
//totalcurd += curdqtyvalue;
newrow[dr["ProductName"].ToString()] = Math.Round(cratesQty, 2); // cratesQty.ToString();
}
else
{
double.TryParse(dr["unitQty"].ToString(), out curdqtyvalue);
double Qty = 0;
double cratesQty = 0;
double.TryParse(dr["Qty"].ToString(), out Qty);
cratesQty = curdqtyvalue / Qty;
if (Qty == 12)
{
total += cratesQty;
}
//totalcurd += curdqtyvalue;
newrow[dr["ProductName"].ToString()] = Math.Round(cratesQty, 2);//cratesQty.ToString();
}
}
if (dr["Categoryname"].ToString() == "ButterMilk")
{
double.TryParse(dr["unitQty"].ToString(), out BMqtyvalue);
double Qty = 0;
double cratesQty = 0;
double.TryParse(dr["Qty"].ToString(), out Qty);
cratesQty = BMqtyvalue / Qty;
if (Qty == 12)
{
total += cratesQty;
}
// totalbuttermilk += BMqtyvalue;
newrow[dr["ProductName"].ToString()] = Math.Round(cratesQty, 2);//cratesQty.ToString();
}
}
}
//newrow["Total Indent"] = total;
newrow["Total MILK CURD AND BM"] = total;
Report.Rows.Add(newrow);
i++;
}
DataRow newvartical = Report.NewRow();
newvartical["Route Name"] = "Total";
double val = 0.0;
foreach (DataColumn dc in Report.Columns)
{
if (dc.DataType == typeof(Double))
{
val = 0.0;
double.TryParse(Report.Compute("sum([" + dc.ToString() + "])", "[" + dc.ToString() + "]<>'0'").ToString(), out val);
newvartical[dc.ToString()] = val;
}
}
Report.Rows.Add(newvartical);
DataRow Break = Report.NewRow();
Break["Route Name"] = "";
Report.Rows.Add(Break);
foreach (DataColumn col in Report.Columns)
{
string Pname = col.ToString();
string ProductName = col.ToString();
ProductName = GetSpace(ProductName);
Report.Columns[Pname].ColumnName = ProductName;
}
grdReports.DataSource = Report;
grdReports.DataBind();
Session["xportdata"] = Report;
}
else
{
pnlHide.Visible = false;
lblmsg.Text = "No Indent Found";
grdReports.DataSource = Report;
grdReports.DataBind();
}
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
}
}
void GetReport()
{
try
{
lblmsg.Text = "";
Report = new DataTable();
vdm = new VehicleDBMgr();
lblDate.Text = txtdate.Text;
DateTime fromdate = DateTime.Now;
string[] datestrig = txtdate.Text.Split(' ');
if (datestrig.Length > 1)
{
if (datestrig[0].Split('-').Length > 0)
{
string[] dates = datestrig[0].Split('-');
string[] times = datestrig[1].Split(':');
fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
Session["RouteName"] = "DAY WISE DUE REPORT " + fromdate.AddDays(1).ToString("dd/MM/yyyy");
Session["filename"] = "DAY WISE DUE REPORT " + fromdate.ToString("dd/MM/yyyy");
cmd = new MySqlCommand("SELECT branchdata.sno, branchdata.BranchName, branchdata.SalesType, branchdata.flag FROM branchmappingtable INNER JOIN branchdata ON branchmappingtable.SubBranch = branchdata.sno WHERE (branchmappingtable.SuperBranch = 172)");
DataTable dtsalesoffice = vdm.SelectQuery(cmd).Tables[0];
Report = new DataTable();
Report.Columns.Add("Sno");
//Report.Columns.Add("RouteCode");
Report.Columns.Add("Route Name");
Report.Columns.Add("Sale Value").DataType = typeof(Double);
Report.Columns.Add("Received Amount").DataType = typeof(Double);
Report.Columns.Add("Balance Amount").DataType = typeof(Double);
Report.Columns.Add("Excess Amount").DataType = typeof(Double);
Report.Columns.Add("Due Amount").DataType = typeof(Double);
Report.Columns.Add("Remarks");
foreach (DataRow dr in dtsalesoffice.Rows)
{
if (dr["sno"].ToString() == "527" || dr["sno"].ToString() == "554" || dr["sno"].ToString() == "760" || dr["sno"].ToString() == "925" || dr["sno"].ToString() == "1349")
{
}
else
{
cmd = new MySqlCommand("SELECT branchdata.BranchName, branchdata.sno,branchdata.CollectionType, branchroutes.Sno AS routesno, branchroutes.RouteName FROM branchdata INNER JOIN branchdata branchdata_1 ON branchdata.sno = branchdata_1.sno INNER JOIN branchroutes ON branchdata.sno = branchroutes.BranchID WHERE (branchdata_1.SalesOfficeID = @SOID) AND (branchdata.SalesType IS NOT NULL) AND (branchroutes.flag <> 0) OR (branchdata.SalesType IS NOT NULL) AND (branchdata.sno = @BranchID) AND (branchroutes.flag <> 0) ORDER BY branchdata.sno");
cmd.Parameters.AddWithValue("@SOID", dr["sno"].ToString());
cmd.Parameters.AddWithValue("@BranchID", dr["sno"].ToString());
DataTable dtroutes = vdm.SelectQuery(cmd).Tables[0];
cmd = new MySqlCommand("SELECT dispatch.DispName, dispatch.sno, triproutes.Tripdata_sno, tripdata.SubmittedAmount, tripdata.ReceivedAmount, dispatch.Route_id FROM dispatch INNER JOIN branchdata ON dispatch.Branch_Id = branchdata.sno INNER JOIN branchdata branchdata_1 ON dispatch.Branch_Id = branchdata_1.sno INNER JOIN triproutes ON dispatch.sno = triproutes.RouteID INNER JOIN tripdata ON triproutes.Tripdata_sno = tripdata.Sno WHERE (branchdata.sno = @BranchID) AND (dispatch.DispType IS NULL) AND (tripdata.I_Date BETWEEN @d1 AND @d2) OR (dispatch.DispType IS NULL) AND (branchdata_1.SalesOfficeID = @SOID) AND (tripdata.I_Date BETWEEN @d1 AND @d2)");
cmd.Parameters.AddWithValue("@SOID", dr["sno"].ToString());
cmd.Parameters.AddWithValue("@BranchID", dr["sno"].ToString());
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate.AddDays(-1)));
cmd.Parameters.AddWithValue("@d2", GetLowDate(fromdate.AddDays(-1)));
DataTable dttripcollection = vdm.SelectQuery(cmd).Tables[0];
//cmd = new MySqlCommand("SELECT modifiedroutes.RouteName, SUM(indents_subtable.DeliveryQty) AS saleQty, SUM(indents_subtable.DeliveryQty * indents_subtable.UnitCost) AS salevalue,modifiedroutes.Sno AS routesno, modifidroutssubtab.BranchID, branchdata_2.BranchName, branchdata_2.flag,SUM(branchdata_2.DueLimit) AS Duelimit FROM branchdata branchdata_2 RIGHT OUTER JOIN branchdata INNER JOIN branchdata branchdata_1 ON branchdata.sno = branchdata_1.sno INNER JOIN modifiedroutes ON branchdata.sno = modifiedroutes.BranchID INNER JOIN (SELECT RefNo, Rank, LevelType, BranchID, CDate, EDate FROM modifiedroutesubtable WHERE (EDate IS NULL) AND (CDate <= @starttime) OR (EDate > @starttime) AND (CDate <= @starttime)) modifidroutssubtab ON modifiedroutes.Sno = modifidroutssubtab.RefNo ON branchdata_2.sno = modifidroutssubtab.BranchID LEFT OUTER JOIN indents_subtable INNER JOIN (SELECT IndentNo, I_date, Branch_id FROM indents WHERE (I_date BETWEEN @starttime AND @endtime)) indt ON indents_subtable.IndentNo = indt.IndentNo ON modifidroutssubtab.BranchID = indt.Branch_id WHERE (branchdata_1.SalesOfficeID = @SOID) AND (branchdata.SalesType IS NOT NULL) OR (branchdata.SalesType IS NOT NULL) AND (branchdata.sno = @BranchID) GROUP BY modifiedroutes.Sno ORDER BY routesno");
cmd = new MySqlCommand("SELECT modifiedroutes.RouteName, SUM(indents_subtable.DeliveryQty) AS saleQty, SUM(indents_subtable.DeliveryQty * indents_subtable.UnitCost) AS salevalue,modifiedroutes.Sno AS routesno, modifidroutssubtab.BranchID, branchdata_2.BranchName, branchdata_2.flag, SUM(branchdata_2.duelimit) AS Duelimit FROM branchdata branchdata_2 RIGHT OUTER JOIN branchdata INNER JOIN branchdata branchdata_1 ON branchdata.sno = branchdata_1.sno INNER JOIN modifiedroutes ON branchdata.sno = modifiedroutes.BranchID INNER JOIN (SELECT RefNo, Rank, LevelType, BranchID, CDate, EDate FROM modifiedroutesubtable WHERE (EDate IS NULL) AND (CDate <= @starttime) OR (EDate > @starttime) AND (CDate <= @starttime)) modifidroutssubtab ON modifiedroutes.Sno = modifidroutssubtab.RefNo ON branchdata_2.sno = modifidroutssubtab.BranchID LEFT OUTER JOIN indents_subtable INNER JOIN (SELECT IndentNo, I_date, Branch_id FROM indents WHERE (I_date BETWEEN @starttime AND @endtime)) indt ON indents_subtable.IndentNo = indt.IndentNo ON modifidroutssubtab.BranchID = indt.Branch_id WHERE (branchdata_1.SalesOfficeID = @SOID) AND (branchdata.SalesType IS NOT NULL) AND (branchdata_2.Due_Limit_Days = '0') OR (branchdata.SalesType IS NOT NULL) AND (branchdata.sno = @BranchID) AND (branchdata_2.Due_Limit_Days = '0') GROUP BY modifiedroutes.Sno ORDER BY routesno");
cmd.Parameters.AddWithValue("@SOID", dr["sno"].ToString());
cmd.Parameters.AddWithValue("@BranchID", dr["sno"].ToString());
cmd.Parameters.AddWithValue("@starttime", GetLowDate(fromdate.AddDays(-1)));
cmd.Parameters.AddWithValue("@endtime", GetHighDate(fromdate.AddDays(-1)));
DataTable dtroutecollection = vdm.SelectQuery(cmd).Tables[0];
cmd = new MySqlCommand("SELECT modifiedroutes.RouteName, SUM(indents_subtable.DeliveryQty) AS saleQty, SUM(indents_subtable.DeliveryQty * indents_subtable.UnitCost) AS salevalue,modifiedroutes.Sno AS routesno, modifidroutssubtab.BranchID, branchdata_2.BranchName, branchdata_2.flag, SUM(branchdata_2.duelimit) AS Duelimit FROM branchdata branchdata_2 RIGHT OUTER JOIN branchdata INNER JOIN branchdata branchdata_1 ON branchdata.sno = branchdata_1.sno INNER JOIN modifiedroutes ON branchdata.sno = modifiedroutes.BranchID INNER JOIN (SELECT RefNo, Rank, LevelType, BranchID, CDate, EDate FROM modifiedroutesubtable WHERE (EDate IS NULL) AND (CDate <= @starttime) OR (EDate > @starttime) AND (CDate <= @starttime)) modifidroutssubtab ON modifiedroutes.Sno = modifidroutssubtab.RefNo ON branchdata_2.sno = modifidroutssubtab.BranchID LEFT OUTER JOIN indents_subtable INNER JOIN (SELECT IndentNo, I_date, Branch_id FROM indents WHERE (I_date BETWEEN @starttime AND @endtime)) indt ON indents_subtable.IndentNo = indt.IndentNo ON modifidroutssubtab.BranchID = indt.Branch_id WHERE (branchdata_1.SalesOfficeID = @SOID) AND (branchdata.SalesType IS NOT NULL) AND (branchdata_2.Due_Limit_Days <> '0') OR (branchdata.SalesType IS NOT NULL) AND (branchdata.sno = @BranchID) AND (branchdata_2.Due_Limit_Days <> '0') GROUP BY branchdata_2.sno ORDER BY routesno");
cmd.Parameters.AddWithValue("@SOID", dr["sno"].ToString());
cmd.Parameters.AddWithValue("@BranchID", dr["sno"].ToString());
cmd.Parameters.AddWithValue("@starttime", GetLowDate(fromdate.AddDays(-1)));
cmd.Parameters.AddWithValue("@endtime", GetHighDate(fromdate.AddDays(-1)));
DataTable dtDue_routecollection = vdm.SelectQuery(cmd).Tables[0];
//cmd = new MySqlCommand("SELECT branchdata.BranchName, branchdata.sno, modifiedroutes.RouteName, modifidroutssubtab.BranchID, modifiedroutes.Sno AS routesno, SUM(colltion.AmountPaid) AS amtpaid, branchdata_1.SalesType, branchdata_2.CollectionType FROM branchdata INNER JOIN branchdata branchdata_1 ON branchdata.sno = branchdata_1.sno INNER JOIN modifiedroutes ON branchdata.sno = modifiedroutes.BranchID INNER JOIN (SELECT RefNo, Rank, LevelType, BranchID, CDate, EDate FROM modifiedroutesubtable WHERE (EDate IS NULL) AND (CDate <= @starttime) OR (EDate > @starttime) AND (CDate <= @starttime)) modifidroutssubtab ON modifiedroutes.Sno = modifidroutssubtab.RefNo INNER JOIN (SELECT Branchid, AmountPaid, PaidDate FROM collections WHERE (PaymentType <> 'Cheque') AND (PaidDate BETWEEN @d1 AND @d2)) colltion ON modifidroutssubtab.BranchID = colltion.Branchid INNER JOIN branchdata branchdata_2 ON modifidroutssubtab.BranchID = branchdata_2.sno WHERE (branchdata_1.SalesOfficeID = @SOID) AND (branchdata.SalesType IS NOT NULL) OR (branchdata.SalesType IS NOT NULL) AND (branchdata.sno = @BranchID) GROUP BY modifiedroutes.Sno ORDER BY modifiedroutes.Sno ");
cmd = new MySqlCommand("SELECT branchdata.BranchName, branchdata.sno, modifiedroutes.RouteName, modifidroutssubtab.BranchID, modifiedroutes.Sno AS routesno, SUM(colltion.AmountPaid) AS amtpaid, branchdata_1.SalesType, branchdata_2.CollectionType FROM branchdata INNER JOIN branchdata branchdata_1 ON branchdata.sno = branchdata_1.sno INNER JOIN modifiedroutes ON branchdata.sno = modifiedroutes.BranchID INNER JOIN (SELECT RefNo, Rank, LevelType, BranchID, CDate, EDate FROM modifiedroutesubtable WHERE (EDate IS NULL) AND (CDate <= @starttime) OR (EDate > @starttime) AND (CDate <= @starttime)) modifidroutssubtab ON modifiedroutes.Sno = modifidroutssubtab.RefNo INNER JOIN (SELECT Branchid, AmountPaid, PaidDate FROM collections WHERE (PaymentType <> 'Cheque') AND (PaidDate BETWEEN @d1 AND @d2)) colltion ON modifidroutssubtab.BranchID = colltion.Branchid INNER JOIN branchdata branchdata_2 ON modifidroutssubtab.BranchID = branchdata_2.sno WHERE (branchdata_1.SalesOfficeID = @SOID) AND (branchdata.SalesType IS NOT NULL) AND (branchdata_2.Due_Limit_Days = '0') OR (branchdata.SalesType IS NOT NULL) AND (branchdata.sno = @BranchID) AND (branchdata_2.Due_Limit_Days = '0') GROUP BY modifiedroutes.Sno ORDER BY routesno");
cmd.Parameters.AddWithValue("@starttime", GetLowDate(fromdate.AddDays(-1)));
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@d2", GetHighDate(fromdate));
cmd.Parameters.AddWithValue("@SOID", dr["sno"].ToString());
cmd.Parameters.AddWithValue("@BranchID", dr["sno"].ToString());
DataTable dtrouteamount = vdm.SelectQuery(cmd).Tables[0];
cmd = new MySqlCommand("SELECT branchdata.BranchName, branchdata.sno, modifiedroutes.RouteName, modifidroutssubtab.BranchID, modifiedroutes.Sno AS routesno, SUM(colltion.AmountPaid) AS amtpaid, branchdata_1.SalesType, branchdata_2.CollectionType, branchdata_2.BranchName AS agentname FROM branchdata INNER JOIN branchdata branchdata_1 ON branchdata.sno = branchdata_1.sno INNER JOIN modifiedroutes ON branchdata.sno = modifiedroutes.BranchID INNER JOIN (SELECT RefNo, Rank, LevelType, BranchID, CDate, EDate FROM modifiedroutesubtable WHERE (EDate IS NULL) AND (CDate <= @starttime) OR (EDate > @starttime) AND (CDate <= @starttime)) modifidroutssubtab ON modifiedroutes.Sno = modifidroutssubtab.RefNo INNER JOIN (SELECT Branchid, AmountPaid, PaidDate FROM collections WHERE (PaymentType <> 'Cheque') AND (PaidDate BETWEEN @d1 AND @d2)) colltion ON modifidroutssubtab.BranchID = colltion.Branchid INNER JOIN branchdata branchdata_2 ON modifidroutssubtab.BranchID = branchdata_2.sno WHERE (branchdata_1.SalesOfficeID = @SOID) AND (branchdata.SalesType IS NOT NULL) AND (branchdata_2.Due_Limit_Days <> '0') OR (branchdata.SalesType IS NOT NULL) AND (branchdata.sno = @BranchID) AND (branchdata_2.Due_Limit_Days <> '0') GROUP BY branchdata_2.sno ORDER BY routesno");
cmd.Parameters.AddWithValue("@starttime", GetLowDate(fromdate.AddDays(-1)));
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@d2", GetHighDate(fromdate));
cmd.Parameters.AddWithValue("@SOID", dr["sno"].ToString());
cmd.Parameters.AddWithValue("@BranchID", dr["sno"].ToString());
DataTable dtDue_routeamount = vdm.SelectQuery(cmd).Tables[0];
double totalsaleqty = 0;
double totalsalevalue = 0;
double totalamountpaid = 0;
double totalbalanceamt = 0;
double totalexcessamount = 0;
double totaldueamount = 0;
int sno = 1;
DataRow newrowso = Report.NewRow();
newrowso["Route Name"] = dr["BranchName"].ToString();
Report.Rows.Add(newrowso);
DataRow break1 = Report.NewRow();
break1["Route Name"] = "";
Report.Rows.Add(break1);
foreach (DataRow drroutes in dtroutes.Rows)
{
foreach (DataRow drsale in dtroutecollection.Select("routesno='" + drroutes["routesno"].ToString() + "'"))
{
DataRow newrow1 = Report.NewRow();
newrow1["Sno"] = sno++;
//newrow1["RouteCode"] = drsale["routesno"].ToString();
//newrow1["Route Code"] = drsale["routesno"].ToString();
newrow1["Route Name"] = drsale["RouteName"].ToString();
double saleqty = 0;
double salevalue = 0;
double amountpaid = 0;
double chequeamountpaid = 0;
//double.TryParse(drsale["saleQty"].ToString(), out saleqty);
double.TryParse(drsale["salevalue"].ToString(), out salevalue);
//newrow1["Sale Qty"] = Math.Round(saleqty, 2);
newrow1["Sale Value"] = Math.Round(salevalue, 2);
totalsaleqty += Math.Round(saleqty, 2);
totalsalevalue += Math.Round(salevalue, 2);
foreach (DataRow drcoll in dtrouteamount.Select("routesno='" + drsale["routesno"].ToString() + "'"))
{
double.TryParse(drcoll["amtpaid"].ToString(), out amountpaid);
totalamountpaid += Math.Round(amountpaid, 2);
}
//foreach (DataRow drChequecoll in dtrouteChequeamount.Select("routesno='" + drsale["routesno"].ToString() + "'"))
//{
// double.TryParse(drChequecoll["amtpaid"].ToString(), out chequeamountpaid);
// totalamountpaid += Math.Round(chequeamountpaid, 2);
// //collectiontype = drChequecoll["CollectionType"].ToString();
//}
double totamt = 0;
double balamount = 0;
double excessamount = 0;
double dueamount = 0;
double tripamt = 0;
totamt = amountpaid + chequeamountpaid;
newrow1["Received Amount"] = Math.Round(totamt, 2);
foreach (DataRow drtrip in dtroutecollection.Select("Route_id='" + drroutes["routesno"].ToString() + "'"))
{
double.TryParse(drtrip["ReceivedAmount"].ToString(), out tripamt);
}
balamount = salevalue - totamt;
excessamount = totamt - salevalue;
if (excessamount < 0)
{
excessamount = 0;
}
newrow1["Balance Amount"] = Math.Round(balamount, 2);
totalbalanceamt += balamount;
totalexcessamount += excessamount;
totaldueamount += dueamount;
newrow1["Excess Amount"] = Math.Round(excessamount, 2);
newrow1["Due Amount"] = dueamount;
Report.Rows.Add(newrow1);
}
}
foreach (DataRow drduesale in dtDue_routecollection.Rows)
{
DataRow duerow1 = Report.NewRow();
duerow1["Sno"] = sno++;
duerow1["Route Name"] = drduesale["BranchName"].ToString();
double saleqty = 0;
double salevalue = 0;
double amountpaid = 0;
double chequeamountpaid = 0;
double.TryParse(drduesale["salevalue"].ToString(), out salevalue);
duerow1["Sale Value"] = Math.Round(salevalue, 2);
totalsaleqty += Math.Round(saleqty, 2);
totalsalevalue += Math.Round(salevalue, 2);
foreach (DataRow drcoll in dtDue_routeamount.Select("BranchID='" + drduesale["BranchID"].ToString() + "'"))
{
double.TryParse(drcoll["amtpaid"].ToString(), out amountpaid);
totalamountpaid += Math.Round(amountpaid, 2);
}
//foreach (DataRow drChequecoll in dtrouteChequeamount.Select("routesno='" + drsale["routesno"].ToString() + "'"))
//{
// double.TryParse(drChequecoll["amtpaid"].ToString(), out chequeamountpaid);
// totalamountpaid += Math.Round(chequeamountpaid, 2);
// //collectiontype = drChequecoll["CollectionType"].ToString();
//}
double totamt = 0;
totamt = amountpaid + chequeamountpaid;
duerow1["Received Amount"] = Math.Round(totamt, 2);
double balamount = 0;
double excessamount = 0;
double dueamount = 0;
balamount = salevalue - totamt;
excessamount = totamt - salevalue;
if (excessamount < 0)
{
excessamount = 0;
}
duerow1["Balance Amount"] = Math.Round(balamount, 2);
totalbalanceamt += balamount;
totalexcessamount += excessamount;
totaldueamount += dueamount;
duerow1["Excess Amount"] = Math.Round(excessamount, 2);
duerow1["Due Amount"] = dueamount;
Report.Rows.Add(duerow1);
}
DataRow TotRow = Report.NewRow();
TotRow["Route Name"] = "Total";
TotRow["Sale Value"] = totalsalevalue;
TotRow["Received Amount"] = totalamountpaid;
TotRow["Balance Amount"] = Math.Round(totalbalanceamt, 2);
TotRow["Excess Amount"] = Math.Round(totalexcessamount, 2);
TotRow["Due Amount"] = totaldueamount;
Report.Rows.Add(TotRow);
DataRow break2 = Report.NewRow();
break2["Route Name"] = "";
Report.Rows.Add(break2);
DataRow break3 = Report.NewRow();
break3["Route Name"] = "";
Report.Rows.Add(break3);
}
}
grdReports.DataSource = Report;
grdReports.DataBind();
}
catch (Exception ex)
{
}
}
void GetReport()
{
try
{
lblmsg.Text = "";
Report = new DataTable();
pnlHide.Visible = true;
Session["IDate"] = DateTime.Now.AddDays(1).ToString("dd/MM/yyyy");
vdm = new VehicleDBMgr();
DateTime fromdate = DateTime.Now;
string[] dateFromstrig = txtfromdate.Text.Split(' ');
if (dateFromstrig.Length > 1)
{
if (dateFromstrig[0].Split('-').Length > 0)
{
string[] dates = dateFromstrig[0].Split('-');
string[] times = dateFromstrig[1].Split(':');
fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
DateTime Todate = DateTime.Now;
string[] dateTostrig = txttodate.Text.Split(' ');
if (dateTostrig.Length > 1)
{
if (dateTostrig[0].Split('-').Length > 0)
{
string[] dates = dateTostrig[0].Split('-');
string[] times = dateTostrig[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);
}
}
lblDispName.Text = ddlDispName.SelectedItem.Text;
lbl_fromDate.Text = fromdate.ToString("dd/MM/yyyy");
lbl_selttodate.Text = Todate.ToString("dd/MM/yyyy");
Session["filename"] = "TOTAL DC REPORT";
//cmd = new MySqlCommand("SELECT tripdata.Sno, tripsubdata.Qty, productsdata.ProductName, tripdata.VehicleNo, dispatch.DispName FROM tripdata INNER JOIN tripsubdata ON tripdata.Sno = tripsubdata.Tripdata_sno INNER JOIN triproutes ON tripdata.Sno = triproutes.Tripdata_sno INNER JOIN dispatch ON triproutes.RouteID = dispatch.sno INNER JOIN branchroutes ON dispatch.Route_id = branchroutes.Sno INNER JOIN productsdata ON tripsubdata.ProductId = productsdata.sno WHERE (branchroutes.BranchID = @branch) AND (tripdata.AssignDate BETWEEN @d1 AND @d2)");
cmd = new MySqlCommand("SELECT tripdata.Sno, tripsubdata.Qty, productsdata.ProductName,tripdata.I_Date, tripdata.VehicleNo,tripdata.Status, dispatch.DispName, products_category.Categoryname FROM tripdata INNER JOIN tripsubdata ON tripdata.Sno = tripsubdata.Tripdata_sno INNER JOIN triproutes ON tripdata.Sno = triproutes.Tripdata_sno INNER JOIN dispatch ON triproutes.RouteID = dispatch.sno INNER JOIN branchroutes ON dispatch.Route_id = branchroutes.Sno INNER JOIN productsdata ON tripsubdata.ProductId = productsdata.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno WHERE (dispatch.Branch_Id = @branch) AND (tripdata.AssignDate BETWEEN @d1 AND @d2) and (dispatch.sno=@DispNo)");
if (Session["salestype"].ToString() == "Plant")
{
cmd.Parameters.AddWithValue("@branch", Session["branch"]);
}
else
{
cmd.Parameters.AddWithValue("@branch", ddlSalesOffice.SelectedValue);
}
cmd.Parameters.AddWithValue("@DispNo", ddlDispName.SelectedValue);
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@d2", GetHighDate(Todate));
DataTable dtble = vdm.SelectQuery(cmd).Tables[0];
//cmd = new MySqlCommand(" SELECT products_category.Categoryname, products_subcategory.SubCatName,branchproducts.Rank, productsdata.ProductName FROM branchproducts INNER JOIN productsdata ON branchproducts.product_sno = productsdata.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno WHERE (branchproducts.branch_sno = @BranchID) and (branchproducts.flag=@Flag) GROUP BY productsdata.ProductName ORDER BY branchproducts.Rank");
cmd = new MySqlCommand("SELECT products_category.Categoryname, products_subcategory.SubCatName, branchproducts.Rank, productsdata.ProductName, branchproducts.branch_sno FROM tripdata INNER JOIN tripsubdata ON tripdata.Sno = tripsubdata.Tripdata_sno INNER JOIN productsdata ON tripsubdata.ProductId = productsdata.sno INNER JOIN branchproducts ON productsdata.sno = branchproducts.product_sno INNER JOIN empmanage ON tripdata.DEmpId = empmanage.Sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno WHERE (tripdata.AssignDate BETWEEN @d1 AND @d2) AND (empmanage.Branch = @BranchID) AND (branchproducts.branch_sno = @Branch) GROUP BY productsdata.ProductName ORDER BY branchproducts.Rank");
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@d2", GetHighDate(Todate));
cmd.Parameters.AddWithValue("@BranchID", Session["branch"].ToString());
cmd.Parameters.AddWithValue("@Branch", Session["branch"].ToString());
DataTable produtstbl = vdm.SelectQuery(cmd).Tables[0];
if (produtstbl.Rows.Count > 0)
{
DataView view = new DataView(dtble);
//DataTable distinctproducts = view.ToTable(true, "ProductName");
Report = new DataTable();
Report.Columns.Add("SNo");
Report.Columns.Add("VehicleNo");
Report.Columns.Add("DC No");
Report.Columns.Add("DC Date");
foreach (DataRow dr in produtstbl.Rows)
{
Report.Columns.Add(dr["ProductName"].ToString()).DataType = typeof(Double);
}
Report.Columns.Add("Total Milk", typeof(Double));
Report.Columns.Add("Total Curd&BM", typeof(Double));
Report.Columns.Add("Total Lts", typeof(Double));
Report.Columns.Add("Issued Crates", typeof(Double));
Report.Columns.Add("Issued Cans", typeof(Double));
Report.Columns.Add("Total Amount", typeof(Double));
DataTable distincttable = view.ToTable(true, "DispName", "VehicleNo", "Sno", "Status", "I_Date");
int i = 1;
foreach (DataRow branch in distincttable.Rows)
{
if (branch["Status"].ToString() == "C")
{
}
else
{
cmd = new MySqlCommand("SELECT invid, Qty FROM tripinvdata WHERE (Tripdata_sno = @tripid)");
cmd.Parameters.AddWithValue("@tripid", branch["Sno"].ToString());
DataTable dtissuedinv = vdm.SelectQuery(cmd).Tables[0];
DataRow newrow = Report.NewRow();
newrow["SNo"] = i;
newrow["VehicleNo"] = branch["VehicleNo"].ToString();
newrow["DC No"] = branch["Sno"].ToString();
//newrow["Route Name"] = branch["DispName"].ToString();
string AssignDate = branch["I_Date"].ToString();
DateTime dtAssignDate = Convert.ToDateTime(AssignDate);
string ChangedTime = dtAssignDate.ToString("dd/MMM/yyyy");
newrow["DC Date"] = ChangedTime;
double total = 0;
double totalcurdandBM = 0;
double totalltrs = 0;
foreach (DataRow dr in dtble.Rows)
{
if (branch["Sno"].ToString() == dr["Sno"].ToString())
{
double assqty = 0;
double curdBm = 0;
double Buttermilk = 0;
double AssignQty = 0;
double.TryParse(dr["Qty"].ToString(), out AssignQty);
newrow[dr["ProductName"].ToString()] = AssignQty;
if (dr["Categoryname"].ToString() == "MILK")
{
double.TryParse(dr["Qty"].ToString(), out assqty);
total += assqty;
}
if (dr["Categoryname"].ToString() == "CURD")
{
double.TryParse(dr["Qty"].ToString(), out curdBm);
totalcurdandBM += curdBm;
}
if (dr["Categoryname"].ToString() == "ButterMilk")
{
double.TryParse(dr["Qty"].ToString(), out Buttermilk);
totalcurdandBM += Buttermilk;
}
}
}
newrow["Total Milk"] = total;
newrow["Total Curd&BM"] = totalcurdandBM;
newrow["Total Lts"] = total + totalcurdandBM;
foreach (DataRow drinv in dtissuedinv.Rows)
{
if (drinv["invid"].ToString() == "1")
{
double issuedcrates = 0;
double.TryParse(drinv["Qty"].ToString(), out issuedcrates);
newrow["Issued Crates"] = issuedcrates;
}
if (drinv["invid"].ToString() == "4")
{
double issuedcans = 0;
double.TryParse(drinv["Qty"].ToString(), out issuedcans);
newrow["Issued Cans"] = issuedcans;
}
}
Report.Rows.Add(newrow);
i++;
}
}
foreach (var column in Report.Columns.Cast <DataColumn>().ToArray())
{
if (Report.AsEnumerable().All(dr => dr.IsNull(column)))
{
Report.Columns.Remove(column);
}
}
DataRow newvartical = Report.NewRow();
newvartical["VehicleNo"] = "Total";
double val = 0.0;
foreach (DataColumn dc in Report.Columns)
{
if (dc.DataType == typeof(Double))
{
val = 0.0;
double.TryParse(Report.Compute("sum([" + dc.ToString() + "])", "[" + dc.ToString() + "]<>'0'").ToString(), out val);
newvartical[dc.ToString()] = val;
}
}
Report.Rows.Add(newvartical);
foreach (DataColumn col in Report.Columns)
{
string Pname = col.ToString();
string ProductName = col.ToString();
ProductName = GetSpace(ProductName);
Report.Columns[Pname].ColumnName = ProductName;
}
grdtotal_dcReports.DataSource = Report;
grdtotal_dcReports.DataBind();
Session["xportdata"] = Report;
}
else
{
pnlHide.Visible = false;
lblmsg.Text = "No DC Found";
grdtotal_dcReports.DataSource = Report;
grdtotal_dcReports.DataBind();
}
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
grdtotal_dcReports.DataSource = Report;
grdtotal_dcReports.DataBind();
}
}
protected void btnGenerate_Click(object sender, EventArgs e)
{
try
{
vdm = new VehicleDBMgr();
vdm.InitializeDB();
lblmsg.Text = "";
lblmsg.Text = "";
DateTime fromdate = DateTime.Now;
DateTime todate = DateTime.Now;
string[] 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(':');
fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
DataTable trips = new DataTable();
lblDate.Text = fromdate.ToString("dd/MM/yyyy");
DataTable Report = new DataTable();
Report.Columns.Add("Sno");
Report.Columns.Add("DespTime");
Report.Columns.Add("Vehicle No");
Report.Columns.Add("Description");
Report.Columns.Add("Driver Name");
Report.Columns.Add("Phone No");
Report.Columns.Add("Route Name");
Report.Columns.Add("Power On");
Report.Columns.Add("Type");
Report.Columns.Add("Problems");
Report.Columns.Add("Solutions");
Report.Columns.Add("Work To Be Done");
cmd = new MySqlCommand("SELECT shiftchangetable.sno, shiftchangetable.desptime, shiftchangetable.vehicleno, shiftchangetable.description, shiftchangetable.drivername, shiftchangetable.phoneno, shiftchangetable.routename, shiftchangetable.poweron, shiftchangetable.pbms, shiftchangetable.slns, shiftchangetable.work, shiftchangetable.type, loginstable.loginid FROM shiftchangetable INNER JOIN loginstable ON shiftchangetable.operatedby = loginstable.refno WHERE (shiftchangetable.doe BETWEEN @d1 AND @d2) AND (shiftchangetable.operatedby = @EmpID)");
cmd.Parameters.Add("@d1", GetLowDate(fromdate));
cmd.Parameters.Add("@d2", GetHighDate(todate));
cmd.Parameters.Add("@EmpID", txtEmpID.Text);
trips = vdm.SelectQuery(cmd).Tables[0];
if (trips.Rows.Count > 0)
{
int i = 1;
foreach (DataRow dr in trips.Rows)
{
DataRow newrow = Report.NewRow();
newrow["Sno"] = i++.ToString();
newrow["DespTime"] = dr["desptime"].ToString();
newrow["Vehicle No"] = dr["vehicleno"].ToString();
newrow["Description"] = dr["description"].ToString();
newrow["Driver Name"] = dr["drivername"].ToString();
newrow["Phone No"] = dr["phoneno"].ToString();
newrow["Route Name"] = dr["routename"].ToString();
newrow["Power On"] = dr["poweron"].ToString();
newrow["Type"] = dr["type"].ToString();
newrow["Problems"] = dr["pbms"].ToString();
newrow["Solutions"] = dr["slns"].ToString();
newrow["Work To Be Done"] = dr["work"].ToString();
Report.Rows.Add(newrow);
}
string title = "ShiftChange Report From: " + fromdate.ToString() + " To: " + todate.ToString();
Session["title"] = title;
Session["filename"] = "ShiftChangeReport";
Session["xportdata"] = Report;
grdReports.DataSource = Report;
grdReports.DataBind();
}
else
{
lblmsg.Text = "No data found";
grdReports.DataSource = Report;
grdReports.DataBind();
}
}
catch
{
}
}
protected void btnSMS_Click(object sender, EventArgs e)
{
string MobNo = txtMobNo.Text;
if (MobNo.Length == 10)
{
vdm = new VehicleDBMgr();
DataTable Report = new DataTable();
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 Todate = DateTime.Now;
string[] dateTostrig = txttodate.Text.Split(' ');
if (dateTostrig.Length > 1)
{
if (dateTostrig[0].Split('-').Length > 0)
{
string[] dates = dateTostrig[0].Split('-');
string[] times = dateTostrig[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);
}
}
lbl_fromDate.Text = fromdate.ToString("dd/MM/yyyy");
lbl_selttodate.Text = Todate.ToString("dd/MM/yyyy");
Session["filename"] = "TOTAL DC REPORT";
//cmd = new MySqlCommand("SELECT tripdata.Sno, tripsubdata.Qty, productsdata.ProductName, tripdata.VehicleNo, dispatch.DispName FROM tripdata INNER JOIN tripsubdata ON tripdata.Sno = tripsubdata.Tripdata_sno INNER JOIN triproutes ON tripdata.Sno = triproutes.Tripdata_sno INNER JOIN dispatch ON triproutes.RouteID = dispatch.sno INNER JOIN branchroutes ON dispatch.Route_id = branchroutes.Sno INNER JOIN productsdata ON tripsubdata.ProductId = productsdata.sno WHERE (branchroutes.BranchID = @branch) AND (tripdata.AssignDate BETWEEN @d1 AND @d2)");
cmd = new MySqlCommand("SELECT ROUND(SUM(tripsubdata.Qty), 2) AS Qty, productsdata.ProductName FROM tripdata INNER JOIN tripsubdata ON tripdata.Sno = tripsubdata.Tripdata_sno INNER JOIN triproutes ON tripdata.Sno = triproutes.Tripdata_sno INNER JOIN dispatch ON triproutes.RouteID = dispatch.sno INNER JOIN branchroutes ON dispatch.Route_id = branchroutes.Sno INNER JOIN productsdata ON tripsubdata.ProductId = productsdata.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno WHERE (dispatch.Branch_Id = @branch) AND (tripdata.AssignDate BETWEEN @d1 AND @d2) AND (triproutes.RouteID = @DispNo) GROUP BY productsdata.ProductName");
if (Session["salestype"].ToString() == "Plant")
{
cmd.Parameters.AddWithValue("@branch", Session["branch"]);
}
else
{
cmd.Parameters.AddWithValue("@branch", ddlSalesOffice.SelectedValue);
}
cmd.Parameters.AddWithValue("@DispNo", ddlDispName.SelectedValue);
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@d2", GetHighDate(Todate));
DataTable dtTotalDespatch = vdm.SelectQuery(cmd).Tables[0];
double TotalQty = 0;
string ProductName = "";
if (dtTotalDespatch.Rows.Count > 0)
{
foreach (DataRow dr in dtTotalDespatch.Rows)
{
double unitQty = 0;
double.TryParse(dr["Qty"].ToString(), out unitQty);
ProductName += dr["ProductName"].ToString() + "->" + Math.Round(unitQty, 2) + ";";
TotalQty += Math.Round(unitQty, 2);
}
}
string Date = DateTime.Now.ToString("dd/MM/yyyy");
WebClient client = new WebClient();
string DispatchName = "";
if (Session["BranchName"] != null)
{
DispatchName = Session["BranchName"].ToString();
}
else
{
DispatchName = "SRIKALAHASTHI";
}
string baseurl = "http://103.16.101.52:8080/sendsms/bulksms?username=kapd-vyshnavi&password=vysavi&type=0&dlr=1&destination=" + MobNo + "&source=VYSNAVI&message=%20" + DispatchName + "%20,%20 + Despatch%20For%20" + ProductName + "TotalQty ->" + TotalQty + "";
Stream data = client.OpenRead(baseurl);
StreamReader reader = new StreamReader(data);
string ResponseID = reader.ReadToEnd();
data.Close();
reader.Close();
lblmsg.Text = "Message Sent Successfully";
txtMobNo.Text = "";
}
else
{
lblmsg.Text = "Please Enter 10 digit Number";
}
}
void getPlantwiseReport()
{
try
{
vdm = new VehicleDBMgr();
lblmsg.Text = "";
pnlHide.Visible = true;
DataTable Report = new DataTable();
DateTime fromdate = DateTime.Now;
string[] fromdatestrig = txtFromdate.Text.Split(' ');
if (fromdatestrig.Length > 1)
{
if (fromdatestrig[0].Split('-').Length > 0)
{
string[] dates = fromdatestrig[0].Split('-');
string[] times = fromdatestrig[1].Split(':');
fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
DateTime todate = DateTime.Now;
string[] todatestrig = txtTodate.Text.Split(' ');
if (todatestrig.Length > 1)
{
if (todatestrig[0].Split('-').Length > 0)
{
string[] dates = todatestrig[0].Split('-');
string[] times = todatestrig[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);
}
}
if (ddlType.SelectedValue == "Day wise")
{
//ravi
cmd = new MySqlCommand("SELECT InvInfo.InvName, InvInfo.sno AS Invsno, ff.I_Date, InvInfo.VehicleNo, ff.DispName, InvInfo.Qty, InvInfo.Remaining, ff.Despsno FROM (SELECT invmaster.InvName, invmaster.sno, tripdata.Sno AS Tripsno, tripdata.VehicleNo, tripinvdata.Qty, tripinvdata.Remaining FROM tripdata INNER JOIN tripinvdata ON tripdata.Sno = tripinvdata.Tripdata_sno INNER JOIN invmaster ON tripinvdata.invid = invmaster.sno WHERE (tripdata.I_Date BETWEEN @d1 AND @d2) AND (invmaster.sno <> 6)) InvInfo INNER JOIN (SELECT I_Date, Branch_Id, Sno, DispName, Despsno FROM (SELECT tripdata_1.I_Date, dispatch.Branch_Id, tripdata_1.Sno, dispatch.DispName, dispatch.sno AS Despsno FROM dispatch INNER JOIN triproutes ON dispatch.sno = triproutes.RouteID INNER JOIN tripdata tripdata_1 ON triproutes.Tripdata_sno = tripdata_1.Sno WHERE (tripdata_1.I_Date BETWEEN @d1 AND @d2) AND (dispatch.Branch_Id = @BranchID)) TripInfo) ff ON ff.Sno = InvInfo.Tripsno GROUP BY ff.DispName, InvInfo.InvName ORDER BY Invsno");
//cmd = new MySqlCommand("SELECT tripdata.Sno,tripdata.I_Date, tripdata.VehicleNo, tripdata.Status, dispatch.DispName, tripdata.Sno, tripinvdata.Qty, tripinvdata.Remaining, invmaster.InvName,invmaster.sno as invsno,tripinvdata.invid FROM tripdata INNER JOIN triproutes ON tripdata.Sno = triproutes.Tripdata_sno INNER JOIN dispatch ON triproutes.RouteID = dispatch.sno INNER JOIN tripinvdata ON tripdata.Sno = tripinvdata.Tripdata_sno INNER JOIN invmaster ON tripinvdata.invid = invmaster.sno WHERE (dispatch.branch_id = @branch) AND (tripdata.I_Date BETWEEN @d1 AND @d2) AND invmaster.sno <> 6 order by invmaster.sno ");
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@d2", GetHighDate(todate));
cmd.Parameters.AddWithValue("@BranchID", Session["branch"]);
}
else
{
cmd = new MySqlCommand("SELECT tripdata.Sno,tripdata.I_Date, tripdata.VehicleNo, tripdata.Status, dispatch.DispName, tripdata.Sno, tripinvdata.Qty, tripinvdata.Remaining, invmaster.InvName,invmaster.sno as invsno,tripinvdata.invid FROM tripdata INNER JOIN triproutes ON tripdata.Sno = triproutes.Tripdata_sno INNER JOIN dispatch ON triproutes.RouteID = dispatch.sno INNER JOIN tripinvdata ON tripdata.Sno = tripinvdata.Tripdata_sno INNER JOIN invmaster ON tripinvdata.invid = invmaster.sno WHERE (dispatch.Sno = @DispSno) AND (tripdata.I_Date BETWEEN @d1 AND @d2) AND invmaster.sno <> 6 group by dispatch.DispName order by invmaster.sno ");
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@d2", GetHighDate(todate));
cmd.Parameters.AddWithValue("@DispSno", ddlDispName.SelectedValue);
}
//cmd = new MySqlCommand("SELECT tripdata.I_Date, tripdata.VehicleNo, tripdata.Status, dispatch.DispName, tripdata.Sno, tripinvdata.invid, tripinvdata.Qty, tripinvdata.Remaining FROM tripdata INNER JOIN triproutes ON tripdata.Sno = triproutes.Tripdata_sno INNER JOIN dispatch ON triproutes.RouteID = dispatch.sno INNER JOIN tripinvdata ON tripdata.Sno = tripinvdata.Tripdata_sno WHERE (dispatch.Branch_Id = @branch) AND (tripdata.I_Date BETWEEN @d1 AND @d2)");
DataTable dtInventory = vdm.SelectQuery(cmd).Tables[0];
if (dtInventory.Rows.Count > 0)
{
DataView view = new DataView(dtInventory);
Report.Columns.Add("Date");
Report.Columns.Add("Route Name");
Report.Columns.Add("Issued crates").DataType = typeof(Int32);
Report.Columns.Add("Received crates").DataType = typeof(Int32);
Report.Columns.Add("Issued 10 ltr can").DataType = typeof(Int32);
Report.Columns.Add("Received 10 ltr can").DataType = typeof(Int32);
Report.Columns.Add("Issued 20 ltr can").DataType = typeof(Int32);
Report.Columns.Add("Received 20 ltr can").DataType = typeof(Int32);
Report.Columns.Add("Issued 40 ltr can").DataType = typeof(Int32);
Report.Columns.Add("Received 40 ltr can").DataType = typeof(Int32);
int i = 1;
int k = 0;
DataTable distincttable = view.ToTable(true, "I_Date", "DispName", "Despsno");
foreach (DataRow drinvc in distincttable.Rows)
{
DataRow drnew = Report.NewRow();
string dtdate1 = drinvc["I_Date"].ToString();
DateTime dtDOE1 = Convert.ToDateTime(dtdate1);
string ChangedTime1 = dtDOE1.ToString("dd/MMM/yyyy");
drnew["Date"] = ChangedTime1;
drnew["Route Name"] = drinvc["DispName"].ToString();
foreach (DataRow dr in dtInventory.Rows)
{
string dtdate2 = dr["I_Date"].ToString();
DateTime dtDOE2 = Convert.ToDateTime(dtdate2);
string ChangedTime2 = dtDOE2.ToString("dd/MMM/yyyy");
string InvName = dr["InvName"].ToString();
string invsno = dr["invsno"].ToString();
if (drinvc["Despsno"].ToString() == dr["Despsno"].ToString())
{
if (ChangedTime1 == ChangedTime2)
{
if (invsno == "1")
{
drnew["Issued crates"] = dr["Qty"].ToString();
drnew["Received crates"] = dr["Remaining"].ToString();
Report.Rows.Add(drnew);
}
if (invsno == "2")
{
//drnew["Issued can"] = drinvc["Qty"].ToString();
//drnew["Received can"] = drinvc["Remaining"].ToString();
int count = Report.Rows.Count - 1;
Report.Rows[count][4] = dr["Qty"].ToString();
Report.Rows[count][5] = dr["Remaining"].ToString();
}
if (invsno == "3")
{
//drnew["Issued can"] = drinvc["Qty"].ToString();
//drnew["Received can"] = drinvc["Remaining"].ToString();
int count = Report.Rows.Count - 1;
Report.Rows[count][6] = dr["Qty"].ToString();
Report.Rows[count][7] = dr["Remaining"].ToString();
}
if (invsno == "4")
{
//drnew["Issued can"] = drinvc["Qty"].ToString();
//drnew["Received can"] = drinvc["Remaining"].ToString();
int count = Report.Rows.Count - 1;
Report.Rows[count][8] = dr["Qty"].ToString();
Report.Rows[count][9] = dr["Remaining"].ToString();
}
//else
//{
// //int Qty = 0;
// //int.TryParse(drinvc["Qty"].ToString(), out Qty);
// //int Remaining = 0;
// //int.TryParse(drinvc["Remaining"].ToString(), out Remaining);
// //Report.Rows[k][3] = Qty;
// //Report.Rows[k][4] = Remaining;
// drnew["Issued can"] = drinvc["Qty"].ToString();
// drnew["Received can"] = drinvc["Remaining"].ToString();
//}
}
}
}
k++;
}
DataRow newvartical = Report.NewRow();
newvartical["Date"] = "Total";
int val = 0;
foreach (DataColumn dc in Report.Columns)
{
if (dc.DataType == typeof(Int32))
{
val = 0;
Int32.TryParse(Report.Compute("sum([" + dc.ToString() + "])", "[" + dc.ToString() + "]<>'0'").ToString(), out val);
newvartical[dc.ToString()] = val;
}
}
Report.Rows.Add(newvartical);
grdReports.DataSource = Report;
grdReports.DataBind();
Session["xportdata"] = Report;
}
else
{
grdReports.DataSource = Report;
grdReports.DataBind();
lblmsg.Text = "No Data Found";
}
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
}
}
void GetReport()
{
try
{
lblmsg.Text = "";
PanelHide.Visible = true;
Report = new DataTable();
Session["RouteName"] = ddlRouteName.SelectedItem.Text;
Session["IDate"] = DateTime.Now.AddDays(1).ToString("dd/MM/yyyy");
vdm = new VehicleDBMgr();
DateTime fromdate = DateTime.Now;
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);
}
}
string[] datetostrig = txtTodate.Text.Split(' ');
if (datetostrig.Length > 1)
{
if (datetostrig[0].Split('-').Length > 0)
{
string[] dates = datetostrig[0].Split('-');
string[] times = datetostrig[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);
}
}
lbl_selfromdate.Text = fromdate.ToString("dd/MM/yyyy");
lbl_selttodate.Text = todate.ToString("dd/MM/yyyy");
lblRoutName.Text = ddlRouteName.SelectedItem.Text;
Session["filename"] = "AGENT WISE OFFER\r" + ddlreporttype.SelectedItem.Text + "\rREPORT";
lbltype.Text = ddlreporttype.SelectedItem.Text;
cmd = new MySqlCommand("select Route_id,IndentType from dispatch_sub where dispatch_sno=@dispsno");
cmd.Parameters.AddWithValue("@dispsno", ddlRouteName.SelectedValue);
DataTable dtrouteindenttype = vdm.SelectQuery(cmd).Tables[0];
foreach (DataRow drrouteitype in dtrouteindenttype.Rows)
{
routeid = drrouteitype["Route_id"].ToString();
routeitype = drrouteitype["IndentType"].ToString();
}
if (ddlreporttype.SelectedItem.Text == "DELIVERY")
{
cmd = new MySqlCommand("SELECT modifiedroutes.RouteName, branchdata.sno AS BSno, branchdata.BranchName, ROUND(SUM(offer_indents_sub.offer_delivered_qty), 2) AS DeliveryQty, ROUND(SUM(offer_indents_sub.offer_delivered_qty * offer_indents_sub.unit_price), 2) AS SaleValue, offer_indents_sub.unit_price, productsdata.sno, productsdata.ProductName, products_category.Categoryname, brnchprdt.Rank FROM offer_indents_sub INNER JOIN (SELECT idoffer_indents, idoffers_assignment, salesoffice_id, route_id, agent_id, indent_date, indents_id, IndentType, I_modified_by FROM offer_indents WHERE (indent_date BETWEEN @starttime AND @endtime) AND (IndentType = @indenttype)) offerindents ON offer_indents_sub.idoffer_indents = offerindents.idoffer_indents INNER JOIN productsdata ON offer_indents_sub.product_id = productsdata.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno INNER JOIN (SELECT branch_sno, product_sno, unitprice, flag, Rank FROM branchproducts WHERE (branch_sno = @BranchID)) brnchprdt ON productsdata.sno = brnchprdt.product_sno RIGHT OUTER JOIN modifiedroutes INNER JOIN modifiedroutesubtable ON modifiedroutes.Sno = modifiedroutesubtable.RefNo INNER JOIN branchdata ON modifiedroutesubtable.BranchID = branchdata.sno ON offerindents.agent_id = branchdata.sno WHERE (modifiedroutes.Sno = @RouteID) AND (modifiedroutesubtable.EDate IS NULL) AND (modifiedroutesubtable.CDate <= @endtime) OR (modifiedroutes.Sno = @RouteID) AND (modifiedroutesubtable.EDate > @starttime) AND (modifiedroutesubtable.CDate <= @starttime) GROUP BY branchdata.sno, productsdata.sno ORDER BY brnchprdt.Rank");
cmd.Parameters.AddWithValue("@starttime", GetLowDate(fromdate.AddDays(-1)));
cmd.Parameters.AddWithValue("@endtime", GetHighDate(todate.AddDays(-1)));
}
if (ddlreporttype.SelectedItem.Text == "INDENT")
{
cmd = new MySqlCommand("SELECT modifiedroutes.RouteName, branchdata.sno AS BSno, branchdata.BranchName, ROUND(SUM(offer_indents_sub.offer_indent_qty), 2) AS DeliveryQty, ROUND(SUM(offer_indents_sub.offer_indent_qty * offer_indents_sub.unit_price), 2) AS SaleValue, offer_indents_sub.unit_price, productsdata.sno, productsdata.ProductName, products_category.Categoryname, brnchprdt.Rank FROM offer_indents_sub INNER JOIN (SELECT idoffer_indents, idoffers_assignment, salesoffice_id, route_id, agent_id, indent_date, indents_id, IndentType, I_modified_by FROM offer_indents WHERE (indent_date BETWEEN @starttime AND @endtime) AND (IndentType = @indenttype)) offerindents ON offer_indents_sub.idoffer_indents = offerindents.idoffer_indents INNER JOIN productsdata ON offer_indents_sub.product_id = productsdata.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno INNER JOIN (SELECT branch_sno, product_sno, unitprice, flag, Rank FROM branchproducts WHERE (branch_sno = @BranchID)) brnchprdt ON productsdata.sno = brnchprdt.product_sno RIGHT OUTER JOIN modifiedroutes INNER JOIN modifiedroutesubtable ON modifiedroutes.Sno = modifiedroutesubtable.RefNo INNER JOIN branchdata ON modifiedroutesubtable.BranchID = branchdata.sno ON offerindents.agent_id = branchdata.sno WHERE (modifiedroutes.Sno = @RouteID) AND (modifiedroutesubtable.EDate IS NULL) AND (modifiedroutesubtable.CDate <= @endtime) OR (modifiedroutes.Sno = @RouteID) AND (modifiedroutesubtable.EDate > @starttime) AND (modifiedroutesubtable.CDate <= @starttime) GROUP BY branchdata.sno, productsdata.sno ORDER BY brnchprdt.Rank");
cmd.Parameters.AddWithValue("@starttime", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@endtime", GetHighDate(todate));
}
if (Session["salestype"].ToString() == "Plant")
{
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
}
else
{
cmd.Parameters.AddWithValue("@BranchID", Session["branch"]);
}
cmd.Parameters.AddWithValue("@RouteID", routeid);
cmd.Parameters.AddWithValue("@indenttype", routeitype);
DataTable dtble = vdm.SelectQuery(cmd).Tables[0];
if (dtble.Rows.Count > 0)
{
DataView view = new DataView(dtble);
DataTable produtstbl = view.ToTable(true, "ProductName", "Categoryname");
Report = new DataTable();
Report.Columns.Add("SNo");
Report.Columns.Add("Agent Name");
foreach (DataRow dr in produtstbl.Rows)
{
if (dr["ProductName"].ToString() != "")
{
Report.Columns.Add(dr["ProductName"].ToString()).DataType = typeof(Double);
}
}
Report.Columns.Add("Total Sale").DataType = typeof(Double);
Report.Columns.Add("Sale Value").DataType = typeof(Double);
DataTable distincttable = view.ToTable(true, "BranchName", "BSno");
int i = 1;
foreach (DataRow branch in distincttable.Rows)
{
DataRow newrow = Report.NewRow();
newrow["SNo"] = i;
newrow["Agent Name"] = branch["BranchName"].ToString();
double total = 0;
double totalSale = 0;
foreach (DataRow dr in dtble.Rows)
{
if (branch["BranchName"].ToString() == dr["BranchName"].ToString())
{
double Amount = 0;
double qtyvalue = 0;
double DeliveryQty = 0;
double salevalue = 0;
double.TryParse(dr["DeliveryQty"].ToString(), out DeliveryQty);
double UnitCost = 0;
if (dr["ProductName"].ToString() == "")
{
}
else
{
newrow[dr["ProductName"].ToString()] = DeliveryQty;
}
if (dr["Categoryname"].ToString() == "MILK")
{
double.TryParse(dr["DeliveryQty"].ToString(), out qtyvalue);
double.TryParse(dr["SaleValue"].ToString(), out salevalue);
}
Amount = DeliveryQty * UnitCost;
total += DeliveryQty;
totalSale += salevalue;
}
}
newrow["Total Sale"] = total;
newrow["Sale Value"] = totalSale;
if (totalSale > 0)
{
Report.Rows.Add(newrow);
i++;
}
}
DataRow newvartical = Report.NewRow();
newvartical["Agent Name"] = "Total";
double val = 0.0;
foreach (DataColumn dc in Report.Columns)
{
if (dc.DataType == typeof(Double))
{
val = 0.0;
double.TryParse(Report.Compute("sum([" + dc.ToString() + "])", "[" + dc.ToString() + "]<>'0'").ToString(), out val);
newvartical[dc.ToString()] = val;
}
}
Report.Rows.Add(newvartical);
foreach (DataColumn col in Report.Columns)
{
string Pname = col.ToString();
string ProductName = col.ToString();
ProductName = GetSpace(ProductName);
Report.Columns[Pname].ColumnName = ProductName;
}
grdReports.DataSource = Report;
grdReports.DataBind();
Session["xportdata"] = Report;
}
else
{
PanelHide.Visible = false;
lblmsg.Text = "No Indent Found";
grdReports.DataSource = Report;
grdReports.DataBind();
}
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
grdReports.DataSource = Report;
grdReports.DataBind();
}
}
protected void btn_WIDB_Click(object sender, EventArgs e)
{
try
{
vdm = new VehicleDBMgr();
DateTime ServerDateCurrentdate = VehicleDBMgr.GetTime(vdm.conn);
DataTable dt = (DataTable)Session["dtImport"];
cmd = new MySqlCommand("SELECT branch_sno, product_sno, unitprice FROM branchproducts ");
//cmd = new MySqlCommand("SELECT branch_sno, product_sno, unitprice FROM branchproducts WHERE (branch_sno = @branchsno) UNION SELECT branchproducts_1.branch_sno, branchproducts_1.product_sno, branchproducts_1.unitprice FROM branchmappingtable INNER JOIN branchproducts branchproducts_1 ON branchmappingtable.SubBranch = branchproducts_1.branch_sno WHERE (branchmappingtable.SuperBranch = @branchsno)");
//cmd.Parameters.AddWithValue("@branchsno", Session["branch"]);
DataTable dtBrnchPrdt = vdm.SelectQuery(cmd).Tables[0];
int i = 0;
foreach (DataRow dr in dt.Rows)
{
string AgentCode = dr["Agent Code"].ToString();
DataTable dtAgentprdt = new DataTable();
dtAgentprdt.Columns.Add("branch_sno");
dtAgentprdt.Columns.Add("product_sno");
dtAgentprdt.Columns.Add("unitprice");
DataRow[] drBp = dtBrnchPrdt.Select("branch_sno='" + dr["Agent Code"].ToString() + "'");
for (int k = 0; k < drBp.Length; k++)
{
DataRow newrow = dtAgentprdt.NewRow();
newrow["branch_sno"] = drBp[k][0].ToString();
newrow["product_sno"] = drBp[k][1].ToString();
newrow["unitprice"] = drBp[k][2].ToString();
dtAgentprdt.Rows.Add(newrow);
}
int j = 3;
foreach (DataColumn dc in dt.Columns)
{
var cell = dc.ColumnName;
if (cell == "SNo" || cell == "Agent Code" || cell == "Agent Name")
{
}
else
{
string UnitPrice = dt.Rows[i][j].ToString();
if (UnitPrice == " ")
{
UnitPrice = "0";
}
cmd = new MySqlCommand("Select Sno from productsdata where ProductName=@ProductName");
cmd.Parameters.AddWithValue("@ProductName", dc.ColumnName);
DataTable dtProduct = vdm.SelectQuery(cmd).Tables[0];
string ProductID = dtProduct.Rows[0]["Sno"].ToString();
DataTable oldunitprice = new DataTable();
oldunitprice.Columns.Add("unitprice");
DataRow[] drAp = dtAgentprdt.Select("product_sno='" + ProductID + "'");
if (drAp.Length == 0)
{
if (UnitPrice == "0")
{
}
else
{
cmd = new MySqlCommand("insert into branchproducts (branch_sno,product_sno,unitprice,userdata_sno,DTarget,WTarget,MTarget) values (@branchname,@productname,@unitprice, @username,@DTarget,@WTarget,@MTarget)");
cmd.Parameters.AddWithValue("@branchname", AgentCode);
cmd.Parameters.AddWithValue("@productname", ProductID);
float UntCost = 0;
float.TryParse(UnitPrice, out UntCost);
cmd.Parameters.AddWithValue("@unitprice", UntCost);
//cmd.Parameters.AddWithValue("@unitprice", 0);
cmd.Parameters.AddWithValue("@username", Session["userdata_sno"]);
int productDaytarget = 0;
int productWeektarget = 0;
int productMonthtarget = 0;
cmd.Parameters.AddWithValue("@DTarget", productDaytarget);
cmd.Parameters.AddWithValue("@WTarget", productWeektarget);
cmd.Parameters.AddWithValue("@MTarget", productMonthtarget);
vdm.insert(cmd);
}
}
else
{
for (int ap = 0; ap < drAp.Length; ap++)
{
DataRow newaprow = oldunitprice.NewRow();
newaprow["unitprice"] = drAp[ap][2].ToString();
oldunitprice.Rows.Add(newaprow);
}
string oldprice = "0";
if (oldunitprice.Rows.Count > 0)
{
oldprice = oldunitprice.Rows[0]["unitprice"].ToString();
}
float UnitCost = 0;
float.TryParse(UnitPrice, out UnitCost);
float oldUnitCost = 0;
float.TryParse(oldprice, out oldUnitCost);
if (UnitCost == oldUnitCost)
{
}
else
{
cmd = new MySqlCommand("Update branchproducts set UnitPrice=@UnitPrice where Branch_sno=@Branch_sno and Product_sno=@Product_sno");
cmd.Parameters.AddWithValue("@UnitPrice", UnitCost);
cmd.Parameters.AddWithValue("@Branch_sno", AgentCode);
cmd.Parameters.AddWithValue("@Product_sno", ProductID);
vdm.Update(cmd);
cmd = new MySqlCommand("insert into productsrateslogs (PrdtSno,BranchId,OldPrice,EditedPrice,EditedUserid,DateOfEdit) values (@PrdtSno,@BranchId,@OldPrice,@EditedPrice,@EditedUserid,@DateOfEdit)");
cmd.Parameters.AddWithValue("@PrdtSno", ProductID);
cmd.Parameters.AddWithValue("@BranchId", AgentCode);
cmd.Parameters.AddWithValue("@OldPrice", oldUnitCost);
cmd.Parameters.AddWithValue("@EditedPrice", UnitCost);
cmd.Parameters.AddWithValue("@EditedUserid", Session["UserSno"]);
cmd.Parameters.AddWithValue("@DateOfEdit", ServerDateCurrentdate);
vdm.insert(cmd);
}
}
j++;
}
}
i++;
}
lblmsg.Text = "Updated Successfully";
}
catch (Exception ex)
{
if (ex.Message == "Object reference not set to an instance of an object.")
{
lblmsg.Text = "Session Expired";
Response.Redirect("Login.aspx");
}
else
{
lblmsg.Text = ex.Message;
}
}
}
void GetReport()
{
try
{
lblmsg.Text = "";
pnlHide.Visible = true;
vdm = new VehicleDBMgr();
DateTime fromdate = DateTime.Now;
Report = new DataTable();
string[] fromdatestrig = txtFromdate.Text.Split(' ');
if (fromdatestrig.Length > 1)
{
if (fromdatestrig[0].Split('-').Length > 0)
{
string[] dates = fromdatestrig[0].Split('-');
string[] times = fromdatestrig[1].Split(':');
fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
DateTime todate = DateTime.Now;
string[] todatestrig = txtTodate.Text.Split(' ');
if (todatestrig.Length > 1)
{
if (todatestrig[0].Split('-').Length > 0)
{
string[] dates = todatestrig[0].Split('-');
string[] times = todatestrig[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);
}
}
Session["filename"] = "Statement of Account ->" + ddlAgentName.SelectedItem.Text;
lblAgent.Text = ddlAgentName.SelectedItem.Text;
lbl_fromDate.Text = txtFromdate.Text;
lbl_selttodate.Text = txtTodate.Text;
cmd = new MySqlCommand("SELECT ROUND(SUM(indents_subtable.DeliveryQty * indents_subtable.UnitCost),2) AS Totalsalevalue,ROUND(SUM(indents_subtable.DeliveryQty),2) AS DeliveryQty,products_category.Categoryname, productsdata.ProductName, DATE_FORMAT(indents.I_date, '%d %b %y') AS IndentDate,branchdata.sno FROM productsdata INNER JOIN indents_subtable ON productsdata.sno = indents_subtable.Product_sno INNER JOIN indents ON indents_subtable.IndentNo = indents.IndentNo INNER JOIN branchdata ON indents.Branch_id = branchdata.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno WHERE (indents.I_date BETWEEN @d1 AND @d2) AND (branchdata.sno = @BranchID) GROUP BY productsdata.sno, IndentDate ORDER BY indents.I_date");
cmd.Parameters.AddWithValue("@BranchID", ddlAgentName.SelectedValue);
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate.AddDays(-1)));
cmd.Parameters.AddWithValue("@d2", GetHighDate(todate.AddDays(-1)));
DataTable dtAgent = vdm.SelectQuery(cmd).Tables[0];
if (dtAgent.Rows.Count <= 0)
{
cmd = new MySqlCommand("SELECT ROUND(SUM(indents_subtable.DeliveryQty * indents_subtable.UnitCost), 2) AS Totalsalevalue, ROUND(SUM(indents_subtable.DeliveryQty), 2) AS DeliveryQty, MAX(indents.I_date) AS indentdate,branchdata.sno FROM productsdata INNER JOIN indents_subtable ON productsdata.sno = indents_subtable.Product_sno INNER JOIN indents ON indents_subtable.IndentNo = indents.IndentNo INNER JOIN branchdata ON indents.Branch_id = branchdata.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno WHERE (branchdata.sno = @BranchID) AND (indents_subtable.DeliveryQty > 0)");
cmd.Parameters.AddWithValue("@BranchID", ddlAgentName.SelectedValue);
DataTable dtAgent_lastDelivery = vdm.SelectQuery(cmd).Tables[0];
if (dtAgent_lastDelivery.Rows.Count > 0)
{
string dtlastdel = dtAgent_lastDelivery.Rows[0]["indentdate"].ToString();
if (dtlastdel != "")
{
fromdate = Convert.ToDateTime(dtlastdel).AddDays(1);
}
cmd = new MySqlCommand("SELECT ROUND(SUM(indents_subtable.DeliveryQty * indents_subtable.UnitCost),2) AS Totalsalevalue,ROUND(SUM(indents_subtable.DeliveryQty),2) AS DeliveryQty,products_category.Categoryname, productsdata.ProductName, DATE_FORMAT(indents.I_date, '%d %b %y') AS IndentDate,branchdata.sno FROM productsdata INNER JOIN indents_subtable ON productsdata.sno = indents_subtable.Product_sno INNER JOIN indents ON indents_subtable.IndentNo = indents.IndentNo INNER JOIN branchdata ON indents.Branch_id = branchdata.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno WHERE (indents.I_date BETWEEN @d1 AND @d2) AND (branchdata.sno = @BranchID) GROUP BY productsdata.sno, IndentDate ORDER BY indents.I_date");
cmd.Parameters.AddWithValue("@BranchID", ddlAgentName.SelectedValue);
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate.AddDays(-1)));
cmd.Parameters.AddWithValue("@d2", GetHighDate(todate.AddDays(-1)));
dtAgent = vdm.SelectQuery(cmd).Tables[0];
}
}
cmd = new MySqlCommand("SELECT Branchid, AmountPaid, Remarks, DATE_FORMAT(PaidDate, '%d/%b/%y') AS PDate, PayTime, EmpID, ReceiptNo, VarifyDate, TransactionType, AmountDebited, DiffAmount, SalesOfficeID, Status FROM collections WHERE (Branchid = @BranchID) AND (TransactionType = @type) AND (Status = @status) AND (PaidDate BETWEEN @d1 AND @d2)");
cmd.Parameters.AddWithValue("@BranchID", ddlAgentName.SelectedValue);
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@d2", GetHighDate(todate));
cmd.Parameters.AddWithValue("@type", "Debit");
cmd.Parameters.AddWithValue("@status", "1");
DataTable dtAgent_Debits = vdm.SelectQuery(cmd).Tables[0];
//cmd = new MySqlCommand("SELECT SUM(AmountPaid) AS AmountPaid, DATE_FORMAT(PaidDate, '%d/%b/%y') AS PaidDate, CheckStatus FROM collections WHERE (Branchid = @BranchID) AND (PaidDate BETWEEN @d1 AND @d2) AND (CheckStatus <> 'P' OR CheckStatus IS NULL) GROUP BY PaidDate");
cmd = new MySqlCommand("SELECT SUM(AmountPaid) AS AmountPaid, DATE_FORMAT(PaidDate, '%d/%b/%y') AS PDate, CheckStatus,PaymentType FROM collections WHERE (Branchid = @BranchID) AND (PaidDate BETWEEN @d1 AND @d2) AND (CheckStatus IS NULL) GROUP BY PDate");
cmd.Parameters.AddWithValue("@BranchID", ddlAgentName.SelectedValue);
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@d2", GetHighDate(todate));
DataTable dtAgentDayWiseCollection = vdm.SelectQuery(cmd).Tables[0];
cmd = new MySqlCommand("SELECT SUM(AmountPaid) AS AmountPaid, DATE_FORMAT(PaidDate, '%d/%b/%y') AS PDate FROM collections WHERE (Branchid = @BranchID) AND (PaidDate BETWEEN @d1 AND @d2) and (tripId is NULL) AND ((PaymentType = 'Incentive') OR (PaymentType = 'Journal Voucher')) GROUP BY PDate");
cmd.Parameters.AddWithValue("@BranchID", ddlAgentName.SelectedValue);
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@d2", GetHighDate(todate));
DataTable dtAgentIncentive = vdm.SelectQuery(cmd).Tables[0];
cmd = new MySqlCommand("SELECT SUM(AmountPaid) AS AmountPaid, DATE_FORMAT(VarifyDate, '%d/%b/%y') AS VarifyDate, CheckStatus FROM collections WHERE (Branchid = @BranchID) AND (CheckStatus = 'V') AND (VarifyDate BETWEEN @d1 AND @d2) GROUP BY VarifyDate");
cmd.Parameters.AddWithValue("@BranchID", ddlAgentName.SelectedValue);
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@d2", GetHighDate(todate));
DataTable dtAgentchequeCollection = vdm.SelectQuery(cmd).Tables[0];
double closingvalue = 0;
double totdebitedamount = 0;
DataTable dtSaleCollection = new DataTable();
DataView view = new DataView(dtAgent);
DataTable produtstbl = view.ToTable(true, "ProductName", "Categoryname");
Report = new DataTable();
Report.Columns.Add("SNo");
Report.Columns.Add("DeliverDate");
int count = 0;
//foreach (DataRow dr in produtstbl.Rows)
//{
// Report.Columns.Add(dr["ProductName"].ToString()).DataType = typeof(Double);
// count++;
//}
Report.Columns.Add("Total").DataType = typeof(Double);
Report.Columns.Add("Sale Value").DataType = typeof(Double);
Report.Columns.Add("Opp Bal");
Report.Columns.Add("Total Amount").DataType = typeof(Double);
Report.Columns.Add("Paid Amount").DataType = typeof(Double);
Report.Columns.Add("Amount Debited").DataType = typeof(Double);
Report.Columns.Add("Incentive/JV", typeof(Double)).SetOrdinal(count + 8);
Report.Columns.Add("Bal Amount");
int i = 1;
if (dtAgent.Rows.Count > 0)
{
cmd = new MySqlCommand("SELECT Sno, SalesOfficeId, RouteId, AgentId, DATE_FORMAT(IndentDate, '%d %b %y') AS IndentDate, EntryDate, OppBalance, SaleQty, SaleValue, ReceivedAmount, ClosingBalance, DiffAmount FROM tempduetrasactions WHERE (IndentDate BETWEEN @d1 AND @d2) AND (AgentId = @SOID) order by EntryDate");
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate).AddDays(-1));
cmd.Parameters.AddWithValue("@d2", GetHighDate(todate).AddDays(-1));
cmd.Parameters.AddWithValue("@SOID", ddlAgentName.SelectedValue);
DataTable dtOpp = vdm.SelectQuery(cmd).Tables[0];
DateTime ServerDateCurrentdate = VehicleDBMgr.GetTime(vdm.conn);
cmd = new MySqlCommand("SELECT totalsaleamount.totalsale, totalsaleamount.Branch_id, SUM(collections.AmountPaid) AS amountpaid FROM (SELECT SUM(indentssub.DeliveryQty * indentssub.UnitCost) AS totalsale, indents.Branch_id FROM indents INNER JOIN (SELECT IndentNo, Product_sno, Qty, Cost, Remark, DeliveryQty, Status, D_date, unitQty, UnitCost, Sno, PaymentStatus, LeakQty, OTripId, DTripId,DelTime FROM indents_subtable WHERE (D_date BETWEEN @starttime AND @endtime)) indentssub ON indents.IndentNo = indentssub.IndentNo WHERE (indents.Branch_id = @BranchID) GROUP BY indents.Branch_id) totalsaleamount INNER JOIN (SELECT Branchid, UserData_sno, AmountPaid, Denominations, Remarks, Sno, PaidDate, PaymentType, tripId, CheckStatus, ReturnDenomin, PayTime, VEmpID, ChequeNo, EmpID, ReceiptNo FROM collections collections_1 WHERE (Branchid = @BranchID) AND (PaidDate BETWEEN @starttime AND @endtime) AND (CheckStatus IS NULL) OR (Branchid = @BranchID) AND (CheckStatus = 'V') AND (VarifyDate BETWEEN @starttime AND @endtime)) collections ON totalsaleamount.Branch_id = collections.Branchid");
cmd.Parameters.AddWithValue("@BranchID", ddlAgentName.SelectedValue);
cmd.Parameters.AddWithValue("@starttime", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@endtime", GetHighDate(ServerDateCurrentdate));
dtSaleCollection = vdm.SelectQuery(cmd).Tables[0];
double aopp = 0; double sale = 0;
double paidamt = 0; double balance = 0;
foreach (DataRow dr in dtOpp.Rows)
{
DataRow newrow = Report.NewRow();
double totqty = 0;
//foreach (DataRow drdtchequeotal in dtAgent.Select("sno='" + dr["AgentId"].ToString() + "'AND IndentDate='" + dr["IndentDate"].ToString() + "'"))
//{
// double DQty = 0;
// double.TryParse(drdtchequeotal["DeliveryQty"].ToString(), out DQty);
// newrow[drdtchequeotal["ProductName"].ToString()] = DQty;
// totqty += DQty;
//}
DateTime date = Convert.ToDateTime(dr["indentDate"].ToString());
newrow["SNo"] = i;
newrow["Total"] = totqty;
newrow["DeliverDate"] = date.AddDays(1).ToString("dd/MM/yyyy");
double.TryParse(dr["SaleValue"].ToString(), out sale);
newrow["Sale Value"] = sale;
double.TryParse(dr["OppBalance"].ToString(), out aopp);
double debitamount = 0;
double.TryParse(dr["DiffAmount"].ToString(), out debitamount);
newrow["Opp Bal"] = aopp - debitamount;
double.TryParse(dr["ReceivedAmount"].ToString(), out paidamt);
newrow["Paid Amount"] = paidamt;
newrow["Amount Debited"] = debitamount;
newrow["Total Amount"] = aopp + sale;
double.TryParse(dr["ClosingBalance"].ToString(), out balance);
newrow["Bal Amount"] = dr["ClosingBalance"].ToString();
double closing = 0;
double.TryParse(dr["ClosingBalance"].ToString(), out closing);
closingvalue = closing;
fromdate = date.AddDays(2);
if (sale + paidamt > 0)
{
Report.Rows.Add(newrow);
}
i++;
}
}
//TimeSpan dateSpan = todate.Subtract(fromdate);
//int NoOfdays = dateSpan.Days;
//NoOfdays = NoOfdays + 1;
//for (int j = 0; j < NoOfdays; j++)
//{
// i++;
// 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("dd/MMM/yy");
// string ChangedTime2 = dtDOE.AddDays(-1).ToString("dd MMM yy");
// newrow["SNo"] = i;
// newrow["DeliverDate"] = ChangedTime1;
// double amtpaid = 0;
// double incentiveamtpaid = 0;
// double totamtpaid = 0;
// double totincentiveamtpaid = 0;
// double totchequeamtpaid = 0;
// double debitedamount = 0;
// foreach (DataRow drdtclubtotal in dtAgentDayWiseCollection.Select("PDate='" + ChangedTime1 + "'"))
// {
// double.TryParse(drdtclubtotal["AmountPaid"].ToString(), out totamtpaid);
// amtpaid += totamtpaid;
// }
// foreach (DataRow drdtincentive in dtAgentIncentive.Select("PDate='" + ChangedTime1 + "'"))
// {
// double.TryParse(drdtincentive["AmountPaid"].ToString(), out totincentiveamtpaid);
// incentiveamtpaid += totincentiveamtpaid;
// }
// foreach (DataRow drdtchequeotal in dtAgentchequeCollection.Select("VarifyDate='" + ChangedTime1 + "'"))
// {
// double.TryParse(drdtchequeotal["AmountPaid"].ToString(), out totchequeamtpaid);
// amtpaid += totchequeamtpaid;
// }
// double totsale = 0;
// double totamt = 0;
// if (dtSaleCollection.Rows.Count > 0)
// {
// double.TryParse(dtSaleCollection.Rows[0]["totalsale"].ToString(), out totsale);
// double.TryParse(dtSaleCollection.Rows[0]["amountpaid"].ToString(), out totamt);
// }
// else
// {
// totsale = 0;
// totamt = 0;
// }
// double total = 0;
// double Amount = 0;
// foreach (DataRow dr in dtAgent.Rows)
// {
// if (ChangedTime2 == dr["IndentDate"].ToString())
// {
// double qtyvalue = 0;
// double DQty = 0;
// double.TryParse(dr["DeliveryQty"].ToString(), out DQty);
// newrow[dr["ProductName"].ToString()] = DQty;
// double.TryParse(dr["Totalsalevalue"].ToString(), out qtyvalue);
// Amount += qtyvalue;
// total += DQty;
// }
// }
// foreach (DataRow dr in dtAgent_Debits.Rows)
// {
// if (ChangedTime1 == dr["PDate"].ToString())
// {
// double amountDebited = 0;
// double.TryParse(dr["AmountDebited"].ToString(), out amountDebited);
// debitedamount += amountDebited;
// totdebitedamount += amountDebited;
// }
// }
// double aopp = closingvalue + totamt - totsale;
// double actbal = 0;
// actbal = aopp;
// if (totdebitedamount == 0.0)
// {
// if (closingvalue == 0.0)
// {
// aopp = aopp;
// }
// else
// {
// aopp = closingvalue;
// }
// }
// else
// {
// if (debitedamount != 0.0)
// {
// if (closingvalue == 0.0)
// {
// aopp = aopp;
// }
// else
// {
// aopp = closingvalue;
// }
// }
// else
// {
// aopp = Math.Abs(aopp);
// aopp = totdebitedamount - aopp;
// aopp = closingvalue;
// }
// }
// if (totsale == 0)
// {
// aopp = closingvalue;
// }
// newrow["Total"] = total;
// newrow["Sale Value"] = Amount;
// newrow["Opp Bal"] = Math.Round(closingvalue, 2);
// double totalamt = closingvalue + Amount + debitedamount;
// newrow["Total Amount"] = Math.Round(totalamt, 2);
// //newrow["Paid Amount"] = amtpaid - incentiveamtpaid;
// newrow["Paid Amount"] = amtpaid - incentiveamtpaid;
// newrow["Incentive/JV"] = incentiveamtpaid;
// newrow["Amount Debited"] = debitedamount;
// // double tot_amount = amtpaid + incentiveamtpaid;
// double totalbalance = totalamt - amtpaid;
// newrow["Bal Amount"] = Math.Round(totalbalance, 2);
// closingvalue = totalbalance;
// if (Amount + amtpaid + debitedamount > 0)
// {
// Report.Rows.Add(newrow);
// }
//}
//for (int j = 0; j < NoOfdays; j
//} //{
//}
//foreach (DataRow dragent in dtAgent.Rows)
//{
// double total = 0, Amount = 0;
// DataRow newrow = Report.NewRow();
// DateTime inddate = Convert.ToDateTime(dragent["IndentDate"].ToString());
// string indentdate = inddate.ToString("dd/MM/yyyy");
// DataRow[] drdates = dtindentdate.Select("DeliverDate='" + indentdate + "'");
// if (drdates.Length > 0)
// {
// //foreach (DataRow drv in drvoucher)
// //{
// // VoucherNo = drv.ItemArray[0].ToString();
// //}
// }
// else
// {
// foreach (DataRow dr in dtAgent.Rows)
// {
// if (indentdate == dr["IndentDate"].ToString())
// {
// double qtyvalue1 = 0;
// double DQty1 = 0;
// double.TryParse(dr["DeliveryQty"].ToString(), out DQty1);
// newrow[dr["ProductName"].ToString()] = DQty1;
// double.TryParse(dr["Totalsalevalue"].ToString(), out qtyvalue1);
// Amount += qtyvalue1;
// total += DQty1;
// }
// }
// double qtyvalue = 0;
// double DQty = 0;
// double.TryParse(dragent["DeliveryQty"].ToString(), out DQty);
// newrow[dragent["ProductName"].ToString()] = DQty;
// double.TryParse(dragent["Totalsalevalue"].ToString(), out qtyvalue);
// Amount += qtyvalue;
// total += DQty;
// foreach (DataRow drdtclubtotal in dtAgentDayWiseCollection.Select("PDate='" + indentdate + "'"))
// {
// double.TryParse(drdtclubtotal["AmountPaid"].ToString(), out totamtpaid);
// amtpaid += totamtpaid;
// }
// foreach (DataRow drdtincentive in dtAgentIncentive.Select("PDate='" + indentdate + "'"))
// {
// double.TryParse(drdtincentive["AmountPaid"].ToString(), out totincentiveamtpaid);
// incentiveamtpaid += totincentiveamtpaid;
// }
// foreach (DataRow drdtchequeotal in dtAgentchequeCollection.Select("VarifyDate='" + indentdate + "'"))
// {
// double.TryParse(drdtchequeotal["AmountPaid"].ToString(), out totchequeamtpaid);
// amtpaid += totchequeamtpaid;
// }
// double totsale = 0;
// double totamt = 0;
// if (dtSaleCollection.Rows.Count > 0)
// {
// double.TryParse(dtSaleCollection.Rows[0]["totalsale"].ToString(), out totsale);
// double.TryParse(dtSaleCollection.Rows[0]["amountpaid"].ToString(), out totamt);
// }
// foreach (DataRow drdtclubtotal in dtAgent_Debits.Select("PDate='" + indentdate + "'"))
// {
// double amountDebited = 0;
// double.TryParse(drdtclubtotal["AmountDebited"].ToString(), out amountDebited);
// debitedamount += amountDebited;
// totdebitedamount += amountDebited;
// }
// double aopp = closingvalue + totamt - totsale;
// double actbal = 0;
// actbal = aopp;
// if (totdebitedamount == 0.0)
// {
// if (closingvalue == 0.0)
// {
// aopp = aopp;
// }
// else
// {
// aopp = closingvalue;
// }
// }
// else
// {
// if (debitedamount != 0.0)
// {
// if (closingvalue == 0.0)
// {
// aopp = aopp;
// }
// else
// {
// aopp = closingvalue;
// }
// }
// else
// {
// aopp = Math.Abs(aopp);
// aopp = totdebitedamount - aopp;
// aopp = closingvalue;
// }
// }
// if (totsale == 0)
// {
// aopp = closingvalue;
// }
// // DateTime date = Convert.ToDateTime(indentdate);
// newrow["DeliverDate"] = indentdate;
// newrow["Total"] = total;
// newrow["Sale Value"] = Amount;
// newrow["Opp Bal"] = Math.Round(closingvalue, 2);
// double totalamt = closingvalue + Amount + debitedamount;
// newrow["Total Amount"] = Math.Round(totalamt, 2);
// //newrow["Paid Amount"] = amtpaid - incentiveamtpaid;
// newrow["Paid Amount"] = amtpaid - incentiveamtpaid;
// newrow["Incentive/JV"] = incentiveamtpaid;
// newrow["Amount Debited"] = debitedamount;
// // double tot_amount = amtpaid + incentiveamtpaid;
// double totalbalance = totalamt - amtpaid;
// newrow["Bal Amount"] = Math.Round(totalbalance, 2);
// closingvalue = totalbalance;
// Report.Rows.Add(newrow);
// }
//}
DataRow newvartical = Report.NewRow();
newvartical["DeliverDate"] = "Total";
double val = 0.0;
foreach (DataColumn dc in Report.Columns)
{
if (dc.DataType == typeof(Double))
{
val = 0.0;
double.TryParse(Report.Compute("sum([" + dc.ToString() + "])", "[" + dc.ToString() + "]<>'0'").ToString(), out val);
newvartical[dc.ToString()] = val;
}
}
Report.Rows.Add(newvartical);
foreach (DataColumn col in Report.Columns)
{
string Pname = col.ToString();
string ProductName = col.ToString();
ProductName = GetSpace(ProductName);
Report.Columns[Pname].ColumnName = ProductName;
}
grdReports.DataSource = Report;
grdReports.DataBind();
Session["xportdata"] = Report;
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
grdReports.DataSource = Report;
grdReports.DataBind();
}
}
void GetReport()
{
try
{
lblmsg.Text = "";
pnlHide.Visible = true;
Session["RouteName"] = ddlRouteName.SelectedItem.Text;
lblRouteName.Text = ddlRouteName.SelectedItem.Text;
Session["IDate"] = DateTime.Now.AddDays(1).ToString("dd/MM/yyyy");
vdm = new VehicleDBMgr();
DataTable Report = new DataTable();
string[] datestrig = txtdate.Text.Split(' ');
if (datestrig.Length > 1)
{
if (datestrig[0].Split('-').Length > 0)
{
string[] dates = datestrig[0].Split('-');
string[] times = datestrig[1].Split(':');
fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
lblDate.Text = fromdate.AddDays(1).ToString("dd/MMM/yyyy");
Session["filename"] = ddlRouteName.SelectedItem.Text + fromdate.AddDays(1).ToString("dd/MM/yyyy");
cmd = new MySqlCommand("select Route_id,IndentType from dispatch_sub where dispatch_sno=@dispsno");
cmd.Parameters.AddWithValue("@dispsno", ddlRouteName.SelectedValue);
DataTable dtrouteindenttype = vdm.SelectQuery(cmd).Tables[0];
foreach (DataRow drrouteitype in dtrouteindenttype.Rows)
{
routeid = drrouteitype["Route_id"].ToString();
routeitype = drrouteitype["IndentType"].ToString();
}
// cmd = new MySqlCommand("SELECT branchroutes.RouteName, branchproducts.Rank, indents_subtable.UnitCost, indents_subtable.unitQty, indents.IndentType, productsdata.ProductName, branchdata.BranchName, productsdata.Units, productsdata.sno, products_category.Categoryname, invmaster.Qty, inventory_monitor.Qty AS invopening FROM branchroutes INNER JOIN branchroutesubtable ON branchroutes.Sno = branchroutesubtable.RefNo INNER JOIN branchdata ON branchroutesubtable.BranchID = branchdata.sno INNER JOIN indents ON branchdata.sno = indents.Branch_id INNER JOIN indents_subtable ON indents.IndentNo = indents_subtable.IndentNo INNER JOIN productsdata ON indents_subtable.Product_sno = productsdata.sno INNER JOIN products_subcategory ON products_subcategory.sno = productsdata.SubCat_sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno INNER JOIN branchproducts ON productsdata.sno = branchproducts.product_sno INNER JOIN invmaster ON productsdata.Inventorysno = invmaster.sno INNER JOIN inventory_monitor ON branchdata.sno = inventory_monitor.BranchId WHERE (branchroutes.Sno = @TripID) AND (indents.I_date BETWEEN @starttime AND @endtime) AND (indents.Status <> 'D') AND (indents.IndentType = @itype) AND (branchproducts.branch_sno = @BranchID) GROUP BY productsdata.ProductName, branchdata.BranchName, productsdata.sno, products_category.Categoryname ORDER BY branchproducts.Rank");
cmd = new MySqlCommand("SELECT modifiedroutes.RouteName, brnchprdt.Rank, modifiedroutesubtable.Rank AS RouteRank, indents_subtable.UnitCost, indents_subtable.unitQty, indent.IndentType, productsdata.ProductName, branchdata.BranchName, productsdata.Units, productsdata.sno, products_category.Categoryname, invmaster.Qty, inventory_monitor.Qty AS invopening FROM modifiedroutes INNER JOIN modifiedroutesubtable ON modifiedroutes.Sno = modifiedroutesubtable.RefNo INNER JOIN branchdata ON modifiedroutesubtable.BranchID = branchdata.sno INNER JOIN (SELECT IndentNo, Branch_id, I_date, IndentType, Status FROM indents WHERE (I_date BETWEEN @starttime AND @endtime)) indent ON branchdata.sno = indent.Branch_id INNER JOIN indents_subtable ON indent.IndentNo = indents_subtable.IndentNo INNER JOIN productsdata ON indents_subtable.Product_sno = productsdata.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno INNER JOIN (SELECT branch_sno, product_sno, Rank FROM branchproducts WHERE (branch_sno = @BranchID)) brnchprdt ON productsdata.sno = brnchprdt.product_sno INNER JOIN invmaster ON productsdata.Inventorysno = invmaster.sno INNER JOIN inventory_monitor ON branchdata.sno = inventory_monitor.BranchId WHERE (modifiedroutes.Sno = @TripID) AND (indent.Status <> 'D') AND (indent.IndentType = @itype) AND (brnchprdt.branch_sno = @BranchID) AND (modifiedroutesubtable.EDate IS NULL) OR (modifiedroutes.Sno = @TripID) AND (indent.Status <> 'D') AND (indent.IndentType = @itype) AND (brnchprdt.branch_sno = @BranchID) AND (modifiedroutesubtable.EDate > @starttime) AND (modifiedroutesubtable.CDate < @starttime) GROUP BY productsdata.ProductName, branchdata.BranchName, productsdata.sno, products_category.Categoryname ORDER BY brnchprdt.Rank, RouteRank");
cmd.Parameters.AddWithValue("@TripID", routeid);
if (Session["salestype"].ToString() == "Plant")
{
if (ddlSalesOffice.SelectedValue == "572")
{
cmd.Parameters.AddWithValue("@BranchID", 158);
}
else
{
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
}
}
else
{
cmd.Parameters.AddWithValue("@BranchID", Session["branch"]);
}
cmd.Parameters.AddWithValue("@starttime", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@endtime", GetHighDate(fromdate));
cmd.Parameters.AddWithValue("@itype", routeitype);
DataTable dtble = vdm.SelectQuery(cmd).Tables[0];
//cmd = new MySqlCommand(" SELECT products_category.Categoryname, products_subcategory.SubCatName, productsdata.ProductName FROM productsdata INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno ORDER BY productsdata.Rank");
//s cmd = new MySqlCommand("SELECT products_category.Categoryname, products_subcategory.SubCatName, productsdata.ProductName FROM branchproducts INNER JOIN dispatch ON branchproducts.branch_sno = dispatch.Branch_Id INNER JOIN productsdata ON branchproducts.product_sno = productsdata.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno WHERE (dispatch.sno = @dispatchSno)Group by productsdata.ProductName ORDER BY productsdata.Rank");
//cmd = new MySqlCommand("SELECT products_category.Categoryname, products_subcategory.SubCatName, productsdata.ProductName, productsdata.Units, invmaster.Qty FROM branchproducts INNER JOIN dispatch ON branchproducts.branch_sno = dispatch.Branch_Id INNER JOIN productsdata ON branchproducts.product_sno = productsdata.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno INNER JOIN invmaster ON productsdata.Inventorysno = invmaster.sno WHERE (dispatch.sno = @dispatchSno) GROUP BY productsdata.ProductName ORDER BY productsdata.Rank");
//cmd.Parameters.AddWithValue("@dispatchSno", ddlRouteName.SelectedValue);
//DataTable produtstbl = vdm.SelectQuery(cmd).Tables[0];
if (dtble.Rows.Count > 0)
{
DataView view = new DataView(dtble);
DataTable produtstbl = view.ToTable(true, "ProductName", "Categoryname", "Units", "Qty");
Report = new DataTable();
Report.Columns.Add("SNo");
Report.Columns.Add("Agent Name");
int count = 0;
int ColCount = 0;
foreach (DataRow dr in produtstbl.Rows)
{
if (dr["Categoryname"].ToString() == "MILK" || dr["Categoryname"].ToString() == "CURD" || dr["Categoryname"].ToString() == "ButterMilk")
{
Report.Columns.Add(dr["ProductName"].ToString()).DataType = typeof(Double);
count++;
ColCount++;
}
else
{
Report.Columns.Add(dr["ProductName"].ToString()).DataType = typeof(Double);
ColCount++;
}
}
Report.Columns.Add("TOTAL INDENT", typeof(Double)).SetOrdinal(count + 2);
Report.Columns.Add("Total Sales Amount", typeof(Double)).SetOrdinal(ColCount + 3);
Report.Columns.Add("Issued Crates", typeof(Double)).SetOrdinal(ColCount + 4);
Report.Columns.Add("Recieved Crates", typeof(Double)).SetOrdinal(ColCount + 5);
Report.Columns.Add("Issued 40 ltr Cans", typeof(Double)).SetOrdinal(ColCount + 6);
Report.Columns.Add("Issued 20 ltr Cans", typeof(Double)).SetOrdinal(ColCount + 7);
Report.Columns.Add("Issued 10 ltr Cans", typeof(Double)).SetOrdinal(ColCount + 8);
Report.Columns.Add("Recieved 40 ltr Cans", typeof(Double)).SetOrdinal(ColCount + 9);
Report.Columns.Add("Recieved 20 ltr Cans", typeof(Double)).SetOrdinal(ColCount + 10);
Report.Columns.Add("Recieved 10 ltr Cans", typeof(Double)).SetOrdinal(ColCount + 11);
DataTable distincttable = view.ToTable(true, "BranchName");
int i = 1;
foreach (DataRow branch in distincttable.Rows)
{
DataRow newrow = Report.NewRow();
newrow["SNo"] = i;
newrow["Agent Name"] = branch["BranchName"].ToString();
double total = 0;
string totalcr = "";
string branchopening = "";
double TotalAmpunt = 0;
foreach (DataRow dr in dtble.Rows)
{
if (branch["BranchName"].ToString() == dr["BranchName"].ToString())
{
double qtyvalue = 0;
double UnitCost = 0;
double UnitQty = 0;
double Totcost = 0;
double invqtys = 0;
newrow[dr["ProductName"].ToString()] = dr["unitQty"].ToString();
double.TryParse(dr["unitQty"].ToString(), out UnitQty);
double.TryParse(dr["UnitCost"].ToString(), out UnitCost);
Totcost = UnitQty * UnitCost;
if (dr["Categoryname"].ToString() == "MILK")
{
double.TryParse(dr["unitQty"].ToString(), out qtyvalue);
total += qtyvalue;
}
branchopening = dr["invopening"].ToString();
TotalAmpunt += Totcost;
}
}
totalcr = total.ToString();
newrow["TOTAL INDENT"] = total;
newrow["Total Sales Amount"] = TotalAmpunt;
//newrow["Issued Crates/Cans"] = indenttubs + '/' + indentcans;
Report.Rows.Add(newrow);
i++;
}
DataRow newvartical = Report.NewRow();
newvartical["Agent Name"] = "Total";
double val = 0.0;
foreach (DataColumn dc in Report.Columns)
{
if (dc.DataType == typeof(Double))
{
val = 0.0;
double.TryParse(Report.Compute("sum([" + dc.ToString() + "])", "[" + dc.ToString() + "]<>'0'").ToString(), out val);
newvartical[dc.ToString()] = val;
if (val == 0)
{
// Report.Columns.;
}
}
}
Report.Rows.Add(newvartical);
DataRow Break = Report.NewRow();
Break["Agent Name"] = "";
Report.Rows.Add(Break);
DataRow newInventory = Report.NewRow();
newInventory["Agent Name"] = "CRATES";
double TotCreatsQty = 0;
double TotCansQty = 0;
foreach (DataRow dr in produtstbl.Rows)
{
var lastRow = Report.Rows[Report.Rows.Count - 2][dr["ProductName"].ToString()];
float First = 0;
float.TryParse(lastRow.ToString(), out First);
float Qty = 0;
float.TryParse(dr["Qty"].ToString(), out Qty);
double InvQty = 0;
InvQty = Math.Round(First / Qty);
if (dr[0].ToString() == "MILK")
{
if (dr["Qty"].ToString() != "12")
{
}
else
{
TotCreatsQty += InvQty;
newInventory[dr["ProductName"].ToString()] = InvQty;
newInventory["TOTAL INDENT"] = TotCreatsQty;
}
}
else
{
if (dr["Qty"].ToString() != "12")
{
}
else
{
if (dr["Units"].ToString() == "gms")
{
string branchid = Session["branch"].ToString();
if (branchid == "158")
{
if (dr["ProductName"].ToString() == "C-CURD100")
{
//float.TryParse(drprdt["Qty"].ToString(), out invqty);
newInventory[dr["ProductName"].ToString()] = Math.Round(First / 3.2);
}
if (dr["ProductName"].ToString() == "C-CURD200")
{
//float.TryParse(drprdt["Qty"].ToString(), out invqty);
newInventory[dr["ProductName"].ToString()] = Math.Round(First / 4.8);
}
}
}
if (dr["Units"].ToString() == "ml")
{
string branchid = Session["branch"].ToString();
if (branchid == "158")
{
if (dr["ProductName"].ToString() == "CURD200")
{
newInventory[dr["ProductName"].ToString()] = Math.Round(First / 10);
}
else
{
newInventory[dr["ProductName"].ToString()] = Math.Round(First / Qty);
}
}
else
{
newInventory[dr["ProductName"].ToString()] = Math.Round(First / Qty);
}
}
else
{
newInventory[dr["ProductName"].ToString()] = InvQty;
}
}
}
}
Report.Rows.Add(newInventory);
DataRow newInventoryCans = Report.NewRow();
newInventoryCans["Agent Name"] = "CANS";
foreach (DataRow dr in produtstbl.Rows)
{
var lastRow = Report.Rows[Report.Rows.Count - 3][dr["ProductName"].ToString()];
float First = 0;
float.TryParse(lastRow.ToString(), out First);
float Qty = 0;
float.TryParse(dr["Qty"].ToString(), out Qty);
double InvQty = 0;
InvQty = Math.Round(First / Qty, 2);
//InvQty= Math.Round(InvQty,2);
//InvQty = Math.Ceiling(InvQty);
if (dr[0].ToString() == "MILK")
{
if (dr["Qty"].ToString() != "12")
{
TotCansQty += InvQty;
newInventoryCans[dr["ProductName"].ToString()] = InvQty;
newInventoryCans["TOTAL INDENT"] = TotCansQty;
}
else
{
}
}
else
{
if (dr["Qty"].ToString() != "12")
{
TotCansQty += InvQty;
newInventoryCans[dr["ProductName"].ToString()] = InvQty;
}
else
{
}
}
}
Report.Rows.Add(newInventoryCans);
foreach (DataColumn col in Report.Columns)
{
string Pname = col.ToString();
string ProductName = col.ToString();
ProductName = GetSpace(ProductName);
Report.Columns[Pname].ColumnName = ProductName;
}
grdReports.DataSource = Report;
grdReports.DataBind();
Session["xportdata"] = Report;
}
else
{
pnlHide.Visible = false;
lblmsg.Text = "No Indent Found";
grdReports.DataSource = Report;
grdReports.DataBind();
}
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
}
}
void FillAgentName()
{
try
{
vdm = new VehicleDBMgr();
if (Session["salestype"].ToString() == "Plant")
{
PBranch.Visible = true;
DataTable dtBranch = new DataTable();
dtBranch.Columns.Add("BranchName");
dtBranch.Columns.Add("sno");
cmd = new MySqlCommand("SELECT branchdata.BranchName, branchdata.sno FROM branchdata INNER JOIN branchmappingtable ON branchdata.sno = branchmappingtable.SubBranch WHERE (branchmappingtable.SuperBranch = @SuperBranch) and (branchdata.SalesType=@SalesType) or (branchmappingtable.SuperBranch = @SuperBranch) and (branchdata.SalesType=@SalesType1) ");
cmd.Parameters.AddWithValue("@SuperBranch", Session["branch"]);
cmd.Parameters.AddWithValue("@SalesType", "21");
cmd.Parameters.AddWithValue("@SalesType1", "26");
DataTable dtRoutedata = vdm.SelectQuery(cmd).Tables[0];
foreach (DataRow dr in dtRoutedata.Rows)
{
DataRow newrow = dtBranch.NewRow();
newrow["BranchName"] = dr["BranchName"].ToString();
newrow["sno"] = dr["sno"].ToString();
dtBranch.Rows.Add(newrow);
}
cmd = new MySqlCommand("SELECT BranchName, sno FROM branchdata WHERE (sno = @BranchID)");
cmd.Parameters.AddWithValue("@BranchID", Session["branch"]);
DataTable dtPlant = vdm.SelectQuery(cmd).Tables[0];
foreach (DataRow dr in dtPlant.Rows)
{
DataRow newrow = dtBranch.NewRow();
newrow["BranchName"] = dr["BranchName"].ToString();
newrow["sno"] = dr["sno"].ToString();
dtBranch.Rows.Add(newrow);
}
cmd = new MySqlCommand("SELECT branchdata.BranchName, branchdata.sno FROM branchdata INNER JOIN branchmappingtable ON branchdata.sno = branchmappingtable.SubBranch WHERE (branchmappingtable.SuperBranch = @SuperBranch) and (branchdata.SalesType=@SalesType) ");
cmd.Parameters.AddWithValue("@SuperBranch", Session["branch"]);
cmd.Parameters.AddWithValue("@SalesType", "23");
DataTable dtNewPlant = vdm.SelectQuery(cmd).Tables[0];
foreach (DataRow dr in dtNewPlant.Rows)
{
DataRow newrow = dtBranch.NewRow();
newrow["BranchName"] = dr["BranchName"].ToString();
newrow["sno"] = dr["sno"].ToString();
dtBranch.Rows.Add(newrow);
}
ddlSalesOffice.DataSource = dtBranch;
ddlSalesOffice.DataTextField = "BranchName";
ddlSalesOffice.DataValueField = "sno";
ddlSalesOffice.DataBind();
ddlSalesOffice.Items.Insert(0, new ListItem("Select", "0"));
}
else
{
PBranch.Visible = false;
cmd = new MySqlCommand("SELECT dispatch.DispName, dispatch.sno FROM dispatch INNER JOIN branchdata ON dispatch.Branch_Id = branchdata.sno INNER JOIN branchdata branchdata_1 ON dispatch.Branch_Id = branchdata_1.sno WHERE (branchdata.sno = @BranchID) OR (branchdata_1.SalesOfficeID = @SOID)");
//cmd = new MySqlCommand("SELECT DispName, sno FROM dispatch WHERE (Branch_Id = @BranchD)");
cmd.Parameters.AddWithValue("@BranchID", Session["branch"].ToString());
cmd.Parameters.AddWithValue("@SOID", Session["branch"].ToString());
DataTable dtRoutedata = vdm.SelectQuery(cmd).Tables[0];
ddlDispName.DataSource = dtRoutedata;
ddlDispName.DataTextField = "DispName";
ddlDispName.DataValueField = "sno";
ddlDispName.DataBind();
ddlDispName.Items.Insert(0, new ListItem("Select", "0"));
}
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
}
}
protected void BtnSave_Click(object sender, EventArgs e)
{
try
{
vdm = new VehicleDBMgr();
DateTime CreateDate = VehicleDBMgr.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 AcctCode = dr["Ledger Code"].ToString();
string whCode = dr["WH Code"].ToString();
if (AcctCode == "" || whCode == "")
{
}
else
{
sqlcmd = new SqlCommand("Insert into EMROJDT (CreateDate, RefDate, DocDate, TransNo, AcctCode, AcctName, Debit, Credit, B1Upload, Processed,Ref1,ocrcode,series) values (@CreateDate, @RefDate, @DocDate,@TransNo, @AcctCode, @AcctName, @Debit, @Credit, @B1Upload, @Processed,@Ref1,@ocrcode,@series)");
sqlcmd.Parameters.Add("@CreateDate", GetLowDate(fromdate));
sqlcmd.Parameters.Add("@RefDate", GetLowDate(fromdate));
sqlcmd.Parameters.Add("@docdate", GetLowDate(fromdate));
sqlcmd.Parameters.Add("@Ref1", dr["JV No"].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["Amount"].ToString(), out amount);
if (amount < 0)
{
amount = Math.Abs(amount);
double Debit = 0;
sqlcmd.Parameters.Add("@Debit", Debit);
sqlcmd.Parameters.Add("@Credit", amount);
}
else
{
amount = Math.Abs(amount);
double Credit = 0;
sqlcmd.Parameters.Add("@Debit", amount);
sqlcmd.Parameters.Add("@Credit", Credit);
}
string B1Upload = "N";
string Processed = "N";
string series = "17";
sqlcmd.Parameters.Add("@B1Upload", B1Upload);
sqlcmd.Parameters.Add("@Processed", Processed);
sqlcmd.Parameters.Add("@ocrcode", whCode);
sqlcmd.Parameters.Add("@series", series);
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();
}
}
public void getpaymentcompletedata()
{
try
{
lbl_msg.Text = "";
pnlHide.Visible = true;
DataTable Report = new DataTable();
Report.Columns.Add("JV No");
Report.Columns.Add("JV Date");
Report.Columns.Add("Ledger Name");
Report.Columns.Add("Amount");
Report.Columns.Add("Narration");
vdm = new ProcureDBmanager();
mainhead.Columns.Add("ledgername");
mainhead.Columns.Add("ledgerid");
mainhead.Columns.Add("Amount");
string date = ddl_BillDate.Text;
string[] p = date.Split('/', '-');
getvald = p[0];
getvalm = p[1];
getvaly = p[2];
getvaldd = p[3];
getvalmm = p[4];
getvalyy = p[5];
FDATE = getvalm + "/" + getvald + "/" + getvaly;
TODATE = getvalmm + "/" + getvaldd + "/" + getvalyy;
string plantcode = ddl_Plantname.SelectedItem.Value;
string seplantname = ddl_Plantname.SelectedItem.Text;
cmd = new SqlCommand("SELECT Tid, Plant_code, Agent_id, Frm_date, To_date, AddedDate, totfat_kg, Added_paise, TotAmount FROM AgentExcesAmount WHERE (Plant_code = '" + ddl_Plantname.SelectedItem.Value + "') AND (Frm_date = '" + FDATE + "') AND (To_date = '" + TODATE + "')");
DataTable dtexcesamount = vdm.SelectQuery(cmd).Tables[0];
cmd = new SqlCommand("SELECT DISTINCT BankFileName, UpdatedTime FROM BankPaymentllotment where Plant_Code='" + ddl_Plantname.SelectedItem.Value + "' and BankFileName='" + ddlfilename.SelectedItem.Value + "'");
DataTable dtfiles = vdm.SelectQuery(cmd).Tables[0];
cmd = new SqlCommand("select Tid, Agent_Id,Agent_Name,Bank_Name,Ifsccode,Account_no,convert(decimal(18,2),NetAmount) as NetAmount,Remarks, UpdatedTime, BankFileName from (select * from (SELECT * FROM (Select Tid, Agent_Id as bankagent,Agent_Name,BANK_ID as bankid,Ifsccode,Account_no,NetAmount,Plant_Code,Remarks, UpdatedTime, BankFileName from BankPaymentllotment where Plant_Code='" + ddl_Plantname.SelectedItem.Value + "' and Billfrmdate='" + FDATE + "' and Billtodate='" + TODATE + "' and FinanceStatus='1' ) AS NEWS LEFT JOIN (SELECT Bank_id,Bank_Name FROM Bank_Details GROUP BY Bank_id,Bank_Name) AS BANK ON NEWS.bankid= BANK.Bank_id) as news left join (Select Agent_Id,Route_id from Agent_Master where Plant_code='" + ddl_Plantname.SelectedItem.Value + "' group by Agent_Id,Route_id ) as news1 on news.bankagent=news1.Agent_Id ) as ff where Agent_Id is not null");
DataTable report = vdm.SelectQuery(cmd).Tables[0];
if (dtfiles.Rows.Count > 0)
{
string plantname = ddl_Plantname.SelectedItem.Text;
int JV = 0;
foreach (DataRow dr in dtfiles.Rows)
{
double filetotalamount = 0;
double filetotalexcessamount = 0;
string jvnumber = "";
string jvvocherdate = "";
string jvnarration = "";
JV = JV + 1;
foreach (DataRow dra in report.Select("BankFileName='" + dr["BankFileName"].ToString() + "'"))
{
double excessamount = 0;
DataRow newrow = Report.NewRow();
string tid = dra["Tid"].ToString();
string agentname = dra["Agent_Name"].ToString();
string agentid = dra["Agent_Id"].ToString();
foreach (DataRow drexcess in dtexcesamount.Select("Agent_id='" + agentid + "'"))
{
double FEXAMT = 0;
double Texamt = Convert.ToDouble(drexcess["TotAmount"].ToString());
double roundexamt = Math.Round(Texamt, 0);
double examt = roundexamt - Texamt;
if (examt > 0)
{
FEXAMT = roundexamt - 1;
}
else
{
FEXAMT = roundexamt;
}
excessamount = FEXAMT;
}
string ledgername = agentid + "-" + agentname + "-" + plantname;
double amt = 0;
double.TryParse(dra["NetAmount"].ToString(), out amt);
double famt = Math.Round(amt + excessamount, 0);
filetotalamount += famt;
string vdate = dra["UpdatedTime"].ToString();
string vocherdate = "";
string cdate = "";
string cmonth = "";
if (vdate != "")
{
DateTime dtvocherdate = Convert.ToDateTime(vdate);
vocherdate = dtvocherdate.ToString("dd-MMM-yy");
cdate = dtvocherdate.ToString("dd");
cmonth = dtvocherdate.ToString("MMM");
}
jvvocherdate = vocherdate;
DateTime f = Convert.ToDateTime(FDATE);
DateTime t = Convert.ToDateTime(TODATE);
string narration = "Being the " + plantname + " CC Procurement Milk Bill Amount paid for the period of " + f.ToString("dd-MMM-yyyy") + " To " + t.ToString("dd-MMM-yyyy") + ", amount paid thru bank " + ddlaccountno.SelectedItem.Text + " dt : " + vocherdate + " .,Emp Name " + Session["EmpName"].ToString();
//string cdate = dtvocherdate.ToString("dd");
// string cmonth = dtvocherdate.ToString("MMM");
newrow["JV Date"] = vocherdate;
newrow["Ledger Name"] = ledgername;
newrow["Amount"] = famt;
newrow["Narration"] = narration;
jvnarration = narration;
newrow["JV No"] = "BANK_PAY" + "" + cdate + "" + cmonth + "" + tid;
jvnumber = "BANK_PAY" + "" + cdate + "" + cmonth + "" + tid;
Report.Rows.Add(newrow);
}
VehicleDBMgr fvdm = new VehicleDBMgr();
cmd = new SqlCommand("SELECT bankaccountno_master.ladger_dr,bankaccountno_master.brach_ledger,bankaccountno_master.ladger_dr_code,bankaccountno_master.brach_ledger_code, bankaccountno_master.branchname, bankmaster.bankname, bankaccountno_master.ifscid, bankaccountno_master.bankid, bankaccountno_master.sno, bankaccountno_master.accountno, bankaccountno_master.accounttype, ifscmaster.ifsccode FROM bankaccountno_master INNER JOIN bankmaster ON bankaccountno_master.bankid = bankmaster.sno INNER JOIN ifscmaster ON bankaccountno_master.ifscid = ifscmaster.sno where bankaccountno_master.sno=@sno");
cmd.Parameters.Add("@sno", ddlaccountno.SelectedItem.Value);
DataTable routes = fvdm.SelectQuery(cmd).Tables[0];
if (routes.Rows.Count > 0)
{
foreach (DataRow drba in routes.Rows)
{
string branchledger = drba["brach_ledger"].ToString();
DataRow newrow1 = Report.NewRow();
newrow1["JV Date"] = jvvocherdate;
newrow1["Ledger Name"] = branchledger;
string neg = "-";
newrow1["Amount"] = neg + "" + filetotalamount;
newrow1["Narration"] = "Being the SVDS.P.LTD." + seplantname + " bill paid for the month of " + txtFromdate.Text + " .,Emp Name " + Session["EmpName"].ToString();
newrow1["JV No"] = jvnumber;
Report.Rows.Add(newrow1);
}
}
string mainledgername = "";
string mainfiletotalamount = filetotalamount.ToString();
if (plantname == "ARANI")
{
mainledgername = "SVDS.P.LTD ARANI";
}
if (plantname == "CSPURAM")
{
mainledgername = "Sri Vyshnavi Dairy Pvt Ltd-C.S.Puram(Kvl)";
}
if (plantname == "KONDEPI" || plantname == "Kondepi Cow")
{
mainledgername = "Sri Vyshnavi Dairy ( P ) Ltd., Kondapi(Kavali)";
}
if (plantname == "KALIGIRI")
{
mainledgername = "SVDS.P.LTD Kaligiri";
}
if (plantname == "ALAPATTI")
{
mainledgername = "SVDS.P.LTD.Alapatti";
}
if (plantname == "KAVALI")
{
mainledgername = "Sri Vyshnavi Dairy-B.R.Palem(Kavali)";
}
if (plantname == "GUDIPALLI PADU" || plantname == "GUDIPALLIPADU COW")
{
mainledgername = "Sri Vyshnavi Dairy ( P ) Ltd, Gudipallipadu(Kvl)";
}
if (plantname == "KAVERIPATNAM")
{
mainledgername = "SVDS.P.LTD KAVERIPATTINAM";
}
if (plantname == "GUDLUR")
{
mainledgername = "Sri Vyshnavi Dairy Pvt Ltd-Gudluru(Kvl)";
}
if (plantname == "WALAJA")
{
mainledgername = "SVDS.P.LTD WALAJA";
}
if (plantname == "V_KOTA")
{
mainledgername = "SVDS.P.LTD V.KOTA";
}
if (plantname == "RCPURAM")
{
mainledgername = "SVDS.P.LTD R.C.PURAM";
}
if (plantname == "BOMMASAMUTHIRAM")
{
mainledgername = "SVDS.P.LTD BOMMA";
}
if (plantname == "TARIGONDA")
{
mainledgername = "SVDS.P.LTD TARIGONDA";
}
if (plantname == "KALASTHIRI")
{
mainledgername = "";
}
DataRow mainrow = mainhead.NewRow();
mainrow["Amount"] = mainfiletotalamount;
mainrow["ledgername"] = mainledgername;
mainhead.Rows.Add(mainrow);
Session["dtImport"] = Report;
Session["dtmainImport"] = mainhead;
Session["xportdata"] = Report;
totamt = filetotalamount;
}
if (mainhead.Rows.Count > 0)
{
DataTable mainReport = new DataTable();
mainReport.Columns.Add("Voucher Date");
mainReport.Columns.Add("Voucher No");
mainReport.Columns.Add("Voucher Type");
mainReport.Columns.Add("Ledger (Cr)");
mainReport.Columns.Add("Ledger (Dr)");
mainReport.Columns.Add("Amount");
mainReport.Columns.Add("Narration");
string accno = ddlaccountno.SelectedItem.Value;
VehicleDBMgr fvdm = new VehicleDBMgr();
cmd = new SqlCommand("SELECT bankaccountno_master.ladger_dr,bankaccountno_master.brach_ledger,bankaccountno_master.ladger_dr_code,bankaccountno_master.brach_ledger_code, bankaccountno_master.branchname, bankmaster.bankname, bankaccountno_master.ifscid, bankaccountno_master.bankid, bankaccountno_master.sno, bankaccountno_master.accountno, bankaccountno_master.accounttype, ifscmaster.ifsccode FROM bankaccountno_master INNER JOIN bankmaster ON bankaccountno_master.bankid = bankmaster.sno INNER JOIN ifscmaster ON bankaccountno_master.ifscid = ifscmaster.sno where bankaccountno_master.sno=@sno");
cmd.Parameters.Add("@sno", ddlaccountno.SelectedItem.Value);
DataTable acroutes = fvdm.SelectQuery(cmd).Tables[0];
string vdate = txtFromdate.Text;
string cdate = "";
string cmonth = "";
string cyear = "";
string vocherdate = "";
if (vdate != "")
{
string[] dta = vdate.Split(' ', '-');
vocherdate = vdate;
cdate = dta[0].ToString();
cmonth = dta[1].ToString();
cyear = dta[2].ToString();
}
DataRow mainReportrow = mainReport.NewRow();
mainReportrow["Voucher Date"] = vocherdate;
mainReportrow["Voucher No"] = "BANK_PAY" + "" + cdate + "" + cmonth + "" + cyear;
mainReportrow["Voucher Type"] = "Bank Payment Import";
if (acroutes.Rows.Count > 0)
{
mainReportrow["Ledger (Cr)"] = acroutes.Rows[0]["ladger_dr"].ToString();
}
foreach (DataRow drmr in mainhead.Rows)
{
mainReportrow["Ledger (Dr)"] = drmr["ledgername"].ToString();
mainReportrow["Amount"] = drmr["Amount"].ToString();
DateTime f = Convert.ToDateTime(FDATE);
DateTime t = Convert.ToDateTime(TODATE);
string narration = "Being the " + plantname + " CC Procurement Milk Bill Amount paid for the period of " + f.ToString("dd-MMM-yyyy") + " To " + t.ToString("dd-MMM-yyyy") + ", amount paid thru bank " + ddlaccountno.SelectedItem.Text + " dt : " + vocherdate + " .,Emp Name " + Session["EmpName"].ToString();
mainReportrow["Narration"] = narration;
}
mainReport.Rows.Add(mainReportrow);
grdReports.DataSource = mainReport;
grdReports.DataBind();
}
}
}
catch (Exception ex)
{
}
}
protected void ddlType_SelectedIndexChanged(object sender, EventArgs e)
{
vdm = new VehicleDBMgr();
FillRouteName();
}
public void save()
{
VehicleDBMgr vdm = new VehicleDBMgr();
dtmissing.Columns.Add("Ledgername");
DateTime CreateDate = VehicleDBMgr.GetTime(vdm.conn);
DateTime fromdate = DateTime.Now;
DataTable dtmainaccount = (DataTable)Session["dtmainImport"];
DataTable dtsubaccount = (DataTable)Session["dtImport"];
string UserName = Session["UserSno"].ToString();
string branchname = "";
string doe = txtFromdate.Text;
DateTime paymentdate = Convert.ToDateTime(doe);
string payto = "";
string acno = ddlaccountno.SelectedItem.Value;
string subbranch = "";
string sapimport = "2";
string remarks = "";
string approvedby = "";
double totalamount = 0;
double totalsubamount = 0;
cmd = new SqlCommand("insert into paymentdetails (name,accountno,remarks,approvedby,doe,createdby,status,paymentdate,branch,sub_branch,sapimport) values (@name,@accountno,@remarks,@approvedby,@doe,@createdby,'P',@paymentdate,@branch,@sub_branch,@sapimport)");
cmd.Parameters.Add("@name", payto);
cmd.Parameters.Add("@accountno", acno);
//cmd.Parameters.Add("@totalamount", totalamount);
cmd.Parameters.Add("@remarks", remarks);
cmd.Parameters.Add("@approvedby", approvedby);
cmd.Parameters.Add("@doe", CreateDate);
cmd.Parameters.Add("@createdby", UserName);
cmd.Parameters.Add("@paymentdate", doe);
cmd.Parameters.Add("@branch", branchname);
cmd.Parameters.Add("@sub_branch", subbranch);
cmd.Parameters.Add("@sapimport", sapimport);
vdm.insert(cmd);
cmd = new SqlCommand("select MAX(sno) AS sno from paymentdetails ");
DataTable routes = vdm.SelectQuery(cmd).Tables[0];
string paymentrefno = routes.Rows[0]["sno"].ToString();
//string paymentrefno = "7277";
if (dtmainaccount != null && dtmainaccount.Rows.Count > 0)
{
try
{
foreach (DataRow drmain in dtmainaccount.Rows)
{
double mainamount = 0;
string headofaccount = drmain["ledgername"].ToString();
cmd = new SqlCommand("SELECT sno FROM headofaccounts_master WHERE accountname=@acname");
cmd.Parameters.Add("@acname", headofaccount);
DataTable dtheadofaccount = vdm.SelectQuery(cmd).Tables[0];
if (dtheadofaccount.Rows.Count > 0)
{
string accountid = dtheadofaccount.Rows[0]["sno"].ToString();
string amount = drmain["amount"].ToString();
mainamount = Convert.ToDouble(amount);
totalamount += mainamount;
cmd = new SqlCommand("insert into paymentsubdetails (paymentrefno, headofaccount, amount) values (@paymentrefno, @headofaccount, @amount)");
cmd.Parameters.Add("@paymentrefno", paymentrefno);
cmd.Parameters.Add("@headofaccount", accountid);
cmd.Parameters.Add("@amount", amount);
vdm.insert(cmd);
}
else
{
DataRow newrow = dtmissing.NewRow();
newrow["Ledgername"] = headofaccount;
dtmissing.Rows.Add(newrow);
}
}
cmd = new SqlCommand("UPDATE paymentdetails SET totalamount=@totalamount WHERE sno=@refno");
cmd.Parameters.Add("@totalamount", totalamount);
cmd.Parameters.Add("@refno", paymentrefno);
vdm.Update(cmd);
}
catch (Exception ex)
{
}
}
if (dtsubaccount != null && dtsubaccount.Rows.Count > 0)
{
try
{
foreach (DataRow dr in dtsubaccount.Rows)
{
double subamount = 0;
string subheadofaccount = dr["ledgername"].ToString();
cmd = new SqlCommand("SELECT sno FROM headofaccounts_master WHERE accountname=@acname");
cmd.Parameters.Add("@acname", subheadofaccount);
DataTable dtsubheadofaccount = vdm.SelectQuery(cmd).Tables[0];
if (dtsubheadofaccount.Rows.Count > 0)
{
string subaccountid = dtsubheadofaccount.Rows[0]["sno"].ToString();
string amount = dr["amount"].ToString();
subamount = Convert.ToDouble(amount);
totalsubamount += subamount;
cmd = new SqlCommand("insert into subaccount_payment (paymentrefno, headofaccount, amount, branchid) values (@paymentrefno, @headofaccount, @amount,@branchid)");
cmd.Parameters.Add("@paymentrefno", paymentrefno);
cmd.Parameters.Add("@headofaccount", subaccountid);
cmd.Parameters.Add("@amount", amount);
cmd.Parameters.Add("@branchid", subbranch);
vdm.insert(cmd);
}
else
{
DataRow newrow = dtmissing.NewRow();
newrow["Ledgername"] = subheadofaccount;
dtmissing.Rows.Add(newrow);
}
}
cmd = new SqlCommand("UPDATE paymentdetails SET total_subamount=@total_subamount WHERE sno=@prefno");
cmd.Parameters.Add("@total_subamount", totalsubamount);
cmd.Parameters.Add("@prefno", paymentrefno);
vdm.Update(cmd);
}
catch (Exception ex)
{
}
}
}
void GetReport()
{
try
{
lblmsg.Text = "";
pnlHide.Visible = true;
Session["RouteName"] = ddlSalesOffice.SelectedItem.Text;
Session["IDate"] = DateTime.Now.AddDays(1).ToString("dd/MM/yyyy");
vdm = new VehicleDBMgr();
DateTime fromdate = DateTime.Now;
string[] dateFromstrig = txtFromdate.Text.Split(' ');
if (dateFromstrig.Length > 1)
{
if (dateFromstrig[0].Split('-').Length > 0)
{
string[] dates = dateFromstrig[0].Split('-');
string[] times = dateFromstrig[1].Split(':');
fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
DataTable Report = new DataTable();
Report.Columns.Add("DOE");
Report.Columns.Add("Ref Receipt");
Report.Columns.Add("Receipt");
Report.Columns.Add("Type");
Report.Columns.Add("Name");
Report.Columns.Add("Amount").DataType = typeof(Double);
lbl_selfromdate.Text = fromdate.ToString("dd/MM/yyyy");
lblRoutName.Text = ddlSalesOffice.SelectedItem.Text;
DateTime ReportDate = VehicleDBMgr.GetTime(vdm.conn);
DateTime dtapril = new DateTime();
DateTime dtmarch = new DateTime();
int currentyear = ReportDate.Year;
int nextyear = ReportDate.Year + 1;
if (ReportDate.Month > 3)
{
string apr = "4/1/" + currentyear;
dtapril = DateTime.Parse(apr);
string march = "3/31/" + nextyear;
dtmarch = DateTime.Parse(march);
}
if (ReportDate.Month <= 3)
{
string apr = "4/1/" + (currentyear - 1);
dtapril = DateTime.Parse(apr);
string march = "3/31/" + (nextyear - 1);
dtmarch = DateTime.Parse(march);
}
Session["xporttype"] = "TallyPayments";
string ledger = "";
cmd = new MySqlCommand("SELECT tbranchname, ladger_dr FROM branchdata WHERE (sno = @BranchID)");
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
DataTable dtledger = vdm.SelectQuery(cmd).Tables[0];
if (dtledger.Rows.Count > 0)
{
ledger = dtledger.Rows[0]["ladger_dr"].ToString();
}
Session["filename"] = ddlSalesOffice.SelectedItem.Text + " Tally Payments" + fromdate.ToString("dd/MM/yyyy");
cmd = new MySqlCommand("SELECT cashpayables.BranchID,cashpayables.doe,cashpayables.VocherID,subpayable.vouchercode,cashpayables.sno,subpayable.HeadSno,cashpayables.Remarks, cashpayables.Remarks,subpayable.sno, subpayable.Amount, accountheads.HeadName FROM cashpayables INNER JOIN subpayable ON cashpayables.Sno = subpayable.RefNo INNER JOIN accountheads ON subpayable.HeadSno = accountheads.Sno WHERE (cashpayables.BranchID = @BranchID) AND (cashpayables.DOE BETWEEN @d1 AND @d2) AND ((cashpayables.VoucherType = 'Debit') OR (cashpayables.VoucherType = 'SalaryAdvance') OR (cashpayables.VoucherType = 'SalaryPayble')) AND (cashpayables.Status = 'P')");
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@d2", GetHighDate(fromdate));
DataTable dtAgent = vdm.SelectQuery(cmd).Tables[0];
if (dtAgent.Rows.Count > 0)
{
DataView view = new DataView(dtAgent);
dtReport = new DataTable();
dtReport.Columns.Add("Voucher Date");
dtReport.Columns.Add("Voucher No");
dtReport.Columns.Add("Voucher Type");
dtReport.Columns.Add("Ledger (Cr)");
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 MySqlCommand("SELECT vouchercode,RefNo, HeadDesc, Amount, HeadSno, sno, branchid, paiddate FROM subpayable WHERE (branchid = @BranchID) AND (paiddate BETWEEN @d1 AND @d2)");
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@d2", GetLowDate(fromdate));
DataTable dtVoucher = vdm.SelectQuery(cmd).Tables[0];
if (dtVoucher.Rows.Count > 0)
{
DataRow[] drvoucher = dtVoucher.Select("branchid='" + ddlSalesOffice.SelectedValue + "' and 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 MySqlCommand("SELECT IFNULL(MAX(vouchercode), 0) + 1 AS Sno FROM subpayable WHERE (branchid = @branchid) AND (paiddate BETWEEN @d1 AND @d2)");
cmd.Parameters.AddWithValue("@branchid", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@HeadSno", branch["HeadSno"].ToString());
cmd.Parameters.AddWithValue("@d1", GetLowDate(dtapril));
cmd.Parameters.AddWithValue("@d2", GetHighDate(dtmarch));
DataTable dtvoucherno = vdm.SelectQuery(cmd).Tables[0];
VoucherNo = dtvoucherno.Rows[0]["Sno"].ToString();
cmd = new MySqlCommand("update subpayable set vouchercode=@vouchercode, paiddate=@paiddate,branchid=@branchid where (RefNo=@RefNo) AND (HeadSno = @HeadSno)");;
cmd.Parameters.AddWithValue("@vouchercode", VoucherNo);
cmd.Parameters.AddWithValue("@paiddate", fromdate);
cmd.Parameters.AddWithValue("@branchid", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@RefNo", branch["sno"].ToString());
cmd.Parameters.AddWithValue("@HeadSno", branch["HeadSno"].ToString());
vdm.Update(cmd);
}
}
else
{
cmd = new MySqlCommand("SELECT IFNULL(MAX(vouchercode), 0) + 1 AS Sno FROM subpayable WHERE (branchid = @branchid) AND (paiddate BETWEEN @d1 AND @d2)");
cmd.Parameters.AddWithValue("@branchid", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@HeadSno", branch["HeadSno"].ToString());
cmd.Parameters.AddWithValue("@d1", GetLowDate(dtapril));
cmd.Parameters.AddWithValue("@d2", GetHighDate(dtmarch));
DataTable dtvoucherno = vdm.SelectQuery(cmd).Tables[0];
VoucherNo = dtvoucherno.Rows[0]["Sno"].ToString();
cmd = new MySqlCommand("update subpayable set vouchercode=@vouchercode, paiddate=@paiddate,branchid=@branchid where (RefNo=@RefNo) AND (HeadSno = @HeadSno)");;
cmd.Parameters.AddWithValue("@vouchercode", VoucherNo);
cmd.Parameters.AddWithValue("@paiddate", fromdate);
cmd.Parameters.AddWithValue("@branchid", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@RefNo", branch["sno"].ToString());
cmd.Parameters.AddWithValue("@HeadSno", branch["HeadSno"].ToString());
vdm.Update(cmd);
}
if (VoucherNo == "0")
{
cmd = new MySqlCommand("SELECT IFNULL(MAX(vouchercode), 0) + 1 AS Sno FROM subpayable WHERE (branchid = @branchid) AND (paiddate BETWEEN @d1 AND @d2)");
cmd.Parameters.AddWithValue("@branchid", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@HeadSno", branch["HeadSno"].ToString());
cmd.Parameters.AddWithValue("@d1", GetLowDate(dtapril));
cmd.Parameters.AddWithValue("@d2", GetHighDate(dtmarch));
DataTable dtvoucherno = vdm.SelectQuery(cmd).Tables[0];
VoucherNo = dtvoucherno.Rows[0]["Sno"].ToString();
cmd = new MySqlCommand("update subpayable set vouchercode=@vouchercode, paiddate=@paiddate,branchid=@branchid where (RefNo=@RefNo) AND (HeadSno = @HeadSno)");;
cmd.Parameters.AddWithValue("@vouchercode", VoucherNo);
cmd.Parameters.AddWithValue("@paiddate", fromdate);
cmd.Parameters.AddWithValue("@branchid", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@RefNo", branch["sno"].ToString());
cmd.Parameters.AddWithValue("@HeadSno", branch["HeadSno"].ToString());
vdm.Update(cmd);
}
string NewVoucherNo = "0";
int countdc = 0;
int.TryParse(VoucherNo, out countdc);
if (countdc <= 10)
{
NewVoucherNo = "0000" + countdc;
}
if (countdc >= 10 && countdc <= 99)
{
NewVoucherNo = "000" + countdc;
}
if (countdc >= 99 && countdc <= 999)
{
NewVoucherNo = "00" + countdc;
}
if (countdc >= 999)
{
NewVoucherNo = "0" + countdc;
}
if (ddlSalesOffice.SelectedValue == "172")
{
NewVoucherNo = "0" + NewVoucherNo;
}
DataRow newrow = dtReport.NewRow();
newrow["Voucher Date"] = fromdate.ToString("dd-MMM-yyyy");
if (fromdate.Month > 3)
{
newrow["Voucher No"] = dtapril.ToString("yy") + dtmarch.ToString("yy") + NewVoucherNo;
}
else
{
if (fromdate.Month < 3)
{
newrow["Voucher No"] = dtapril.ToString("yy") + dtmarch.ToString("yy") + NewVoucherNo;
}
else
{
newrow["Voucher No"] = dtapril.AddYears(-1).ToString("yy") + dtmarch.AddYears(-1).ToString("yy") + NewVoucherNo;
}
}
//if (fromdate.Month > 3)
//{
// newrow["Voucher No"] = dtapril.ToString("yy") + dtmarch.ToString("yy") + NewVoucherNo;
//}
//else
//{
// newrow["Voucher No"] = dtapril.AddYears(-1).ToString("yy") + dtmarch.AddYears(-1).ToString("yy") + NewVoucherNo;
//}
newrow["Voucher Type"] = "Cash Payment Import";
newrow["Ledger (Cr)"] = ledger;
newrow["Ledger (Dr)"] = branch["HeadName"].ToString();
newrow["Amount"] = branch["Amount"].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();
}
}
void GetReport()
{
try
{
lblmsg.Text = "";
Report = new DataTable();
Session["IDate"] = DateTime.Now.AddDays(1).ToString("dd/MM/yyyy");
vdm = new VehicleDBMgr();
if (Session["salestype"].ToString() == "Plant")
{
lblDispatchName.Text = ddlSalesOffice.SelectedItem.Text;
lblDate.Text = txtdate.Text;
}
else
{
lblDispatchName.Text = ddlSalesOffice.SelectedItem.Text;
lblDate.Text = txtdate.Text;
}
pnlHide.Visible = true;
DateTime fromdate = DateTime.Now;
DateTime todate = DateTime.Now;
string[] datestrig = txtdate.Text.Split(' ');
if (datestrig.Length > 1)
{
if (datestrig[0].Split('-').Length > 0)
{
string[] dates = datestrig[0].Split('-');
string[] times = datestrig[1].Split(':');
fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
string[] todatestrig = txttodate.Text.Split(' ');
if (todatestrig.Length > 1)
{
if (todatestrig[0].Split('-').Length > 0)
{
string[] dates = todatestrig[0].Split('-');
string[] times = todatestrig[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);
}
}
Session["RouteName"] = ddlSalesOffice.SelectedItem.Text + " REPORT " + fromdate.AddDays(1).ToString("dd/MM/yyyy");
Session["filename"] = ddlSalesOffice.SelectedItem.Text + " REPORT " + fromdate.AddDays(1).ToString("dd/MM/yyyy");
cmd = new MySqlCommand("SELECT dispatch.sno, SUM(tripsubdata.Qty) AS Qty, DATE_FORMAT(tripdata.AssignDate, '%d %b %y') AS AssignDate, tripsubdata.ProductId, productsdata.ProductName,products_subcategory.SubCatName, products_category.Categoryname FROM dispatch INNER JOIN triproutes ON dispatch.sno = triproutes.RouteID INNER JOIN tripsubdata ON triproutes.Tripdata_sno = tripsubdata.Tripdata_sno INNER JOIN tripdata ON tripsubdata.Tripdata_sno = tripdata.Sno INNER JOIN (SELECT branch_sno, product_sno, unitprice, flag, userdata_sno, DTarget, WTarget, MTarget, BranchQty, LeakQty, Rank FROM branchproducts WHERE (branch_sno = @salesoffice)) brnchprdts ON tripsubdata.ProductId = brnchprdts.product_sno INNER JOIN productsdata ON brnchprdts.product_sno = productsdata.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno WHERE (dispatch.BranchID = @salesoffice) AND (tripdata.AssignDate BETWEEN @d1 AND @d2) GROUP BY tripsubdata.ProductId, tripdata.AssignDate ORDER BY AssignDate, brnchprdts.Rank");
cmd.Parameters.AddWithValue("@salesoffice", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@d2", GetHighDate(todate));
DataTable dtsalesofficeTotalDispatch = vdm.SelectQuery(cmd).Tables[0];
cmd = new MySqlCommand("SELECT products_category.Categoryname,branchproducts.product_sno, productsdata.ProductName, products_subcategory.SubCatName,branchproducts.unitprice FROM branchproducts INNER JOIN productsdata ON branchproducts.product_sno = productsdata.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno WHERE (branchproducts.branch_sno = @salesoffice) ORDER BY branchproducts.Rank");
cmd.Parameters.AddWithValue("@salesoffice", ddlSalesOffice.SelectedValue);
DataTable dtsalesofficeproducts = vdm.SelectQuery(cmd).Tables[0];
if (dtsalesofficeTotalDispatch.Rows.Count > 0)
{
DataView view = new DataView(dtsalesofficeTotalDispatch);
Report = new DataTable();
Report.Columns.Add("SNo");
Report.Columns.Add("DC Date");
foreach (DataRow dr in dtsalesofficeproducts.Rows)
{
Report.Columns.Add(dr["ProductName"].ToString()).DataType = typeof(Double);
}
Report.Columns.Add("Total MIlk").DataType = typeof(Double);
Report.Columns.Add("Total Curd & BM").DataType = typeof(Double);
Report.Columns.Add("Total Ltrs").DataType = typeof(Double);
DataTable distincttable = view.ToTable(true, "AssignDate");
int i = 1;
foreach (DataRow branch in distincttable.Rows)
{
DataRow newrow = Report.NewRow();
newrow["SNo"] = i;
string dtdate1 = branch["AssignDate"].ToString();
DateTime dtDOE1 = Convert.ToDateTime(dtdate1);
string ChangedTime1 = dtDOE1.ToString("dd/MMM/yyyy");
newrow["DC Date"] = ChangedTime1;
double total = 0;
double Amount = 0;
double totalmilk = 0;
double totalcurdandBM = 0;
double totalltrs = 0;
foreach (DataRow dr in dtsalesofficeTotalDispatch.Rows)
{
if (branch["AssignDate"].ToString() == dr["AssignDate"].ToString())
{
double qtyvalue = 0;
double UnitCost = 0;
double DQty = 0;
double assqty = 0;
double curdBm = 0;
double Buttermilk = 0;
double AssignQty = 0;
double.TryParse(dr["Qty"].ToString(), out DQty);
newrow[dr["ProductName"].ToString()] = Math.Round(DQty, 2);
if (dr["Categoryname"].ToString() == "MILK")
{
double.TryParse(dr["Qty"].ToString(), out assqty);
totalmilk += assqty;
}
if (dr["Categoryname"].ToString() == "CURD")
{
double.TryParse(dr["Qty"].ToString(), out curdBm);
totalcurdandBM += curdBm;
}
if (dr["Categoryname"].ToString() == "ButterMilk")
{
double.TryParse(dr["Qty"].ToString(), out Buttermilk);
totalcurdandBM += Buttermilk;
}
double.TryParse(dr["Qty"].ToString(), out qtyvalue);
total += qtyvalue;
}
}
newrow["Total Ltrs"] = Math.Round(total, 2);
newrow["Total MIlk"] = Math.Round(totalmilk, 2);
newrow["Total Curd & BM"] = Math.Round(totalcurdandBM, 2);
Report.Rows.Add(newrow);
i++;
}
foreach (var column in Report.Columns.Cast <DataColumn>().ToArray())
{
if (Report.AsEnumerable().All(dr => dr.IsNull(column)))
{
Report.Columns.Remove(column);
}
}
DataRow newvartical = Report.NewRow();
newvartical["DC 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);
newvartical[dc.ToString()] = val;
}
}
Report.Rows.Add(newvartical);
foreach (DataColumn col in Report.Columns)
{
string Pname = col.ToString();
string ProductName = col.ToString();
ProductName = GetSpace(ProductName);
Report.Columns[Pname].ColumnName = ProductName;
}
grdReports.DataSource = Report;
grdReports.DataBind();
Session["xportdata"] = Report;
}
}
catch (Exception ex)
{
}
}
