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 SqlCommand("SELECT sno, accountno, name, amount, remarks, CONVERT(VARCHAR(10),doe,103) as doe, createdby, approvedby, status FROM collections WHERE (sno = @Receipt)");
cmd.Parameters.Add("@BranchID", Session["branch"]);
cmd.Parameters.Add("@Receipt", txtReceiptNo.Text);
DataTable dtReceiptBook = vdm.SelectQuery(cmd).Tables[0];
if (dtReceiptBook.Rows.Count > 0)
{
string Receiptid = "SVDS/RCPT/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "/" + dtReceiptBook.Rows[0]["sno"].ToString();
lblreceiptno.Text = Receiptid;
lblDate.Text = dtReceiptBook.Rows[0]["doe"].ToString();
string Branch = Session["branch"].ToString();
lblCheque.Text = "Cash";
lbltowards.Text = "Cash Deposit";
lblAmount.Text = dtReceiptBook.Rows[0]["amount"].ToString();
lblChequeDate.Text = dtReceiptBook.Rows[0]["doe"].ToString();
lblRemarks.Text = dtReceiptBook.Rows[0]["remarks"].ToString();
}
string Amont = lblAmount.Text;
string[] Ones = { "", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", "Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Ninteen" };
string[] Tens = { "Ten", "Twenty", "Thirty", "Fourty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninty" };
int Num = int.Parse(Amont);
lblRupess.Text = NumToWordBD(Num) + " Rupees Only";
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
}
}
protected void btnApprove_Click(object sender, EventArgs e)
{
try
{
vdm = new VehicleDBMgr();
DateTime ServerDateCurrentdate = VehicleDBMgr.GetTime(vdm.conn);
string empid = Session["UserSno"].ToString();
string buttontext = btnApprove.Text;
if (buttontext == "Approval")
{
// cmd = new MySqlCommand("UPDATE incentive_structure SET ApprovalStatus = 'A' WHERE (sno = @incentivesno)");
cmd = new MySqlCommand("UPDATE incentive_structure SET ApprovalStatus = 'A', ApprovedDate = @dt, ApprovedBy = @empid WHERE (sno = @incentivesno)");
cmd.Parameters.AddWithValue("@incentivesno", ddlIncentiveStructure.SelectedValue);
cmd.Parameters.AddWithValue("@dt", ServerDateCurrentdate);
cmd.Parameters.AddWithValue("@empid", empid);
vdm.Update(cmd);
lblmsg.Text = "Selected Structure Aprroved";
}
if (buttontext == "Disable")
{
cmd = new MySqlCommand("UPDATE incentive_structure SET ApprovalStatus = 'P', ApprovedDate = @dt, ApprovedBy = @empid WHERE (sno = @incentivesno)");
cmd.Parameters.AddWithValue("@incentivesno", ddlIncentiveStructure.SelectedValue);
cmd.Parameters.AddWithValue("@dt", ServerDateCurrentdate);
cmd.Parameters.AddWithValue("@empid", empid);
vdm.Update(cmd);
lblmsg.Text = "Selected Structure Aprroval Cancelled";
}
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
}
}
protected void BtnSave_Click(object sender, EventArgs e)
{
try
{
vdm = new VehicleDBMgr();
DateTime CreateDate = VehicleDBMgr.GetTime(vdm.conn);
DateTime fromdate = DateTime.Now;
DataTable dt = (DataTable)Session["xportdata"];
int i = 1;
foreach (DataRow dr in dt.Rows)
{
string Ledgername = dr["Ledger Name"].ToString();
string Ledgercode = dr["Ledger Code"].ToString();
if (Ledgername == "" || Ledgercode == "")
{
}
else
{
sqlcmd = new SqlCommand("SELECT sno FROM headofaccounts_master WHERE ((accountname = @accountname) OR (accountcode = @accountcode))");
sqlcmd.Parameters.Add("@accountcode", dr["Ledger Code"].ToString());
sqlcmd.Parameters.Add("@accountname", dr["Ledger Name"].ToString());
DataTable dtledgers = vdm.SelectQuery(sqlcmd).Tables[0];
if (dtledgers.Rows.Count > 0)
{
string sno = dtledgers.Rows[0]["sno"].ToString();
sqlcmd = new SqlCommand("update headofaccounts_master set accountname=@accountname,accountcode=@accountcode where sno=@sno");
sqlcmd.Parameters.Add("@accountcode", dr["Ledger Code"].ToString());
sqlcmd.Parameters.Add("@accountname", dr["Ledger Name"].ToString());
sqlcmd.Parameters.Add("@doe", fromdate);
sqlcmd.Parameters.Add("@sno", sno);
vdm.Update(sqlcmd);
}
else
{
sqlcmd = new SqlCommand("Insert into headofaccounts_master (accountname, accountcode,doe) values (@accountname, @accountcode,@doe)");
sqlcmd.Parameters.Add("@accountcode", dr["Ledger Code"].ToString());
sqlcmd.Parameters.Add("@accountname", dr["Ledger Name"].ToString());
sqlcmd.Parameters.Add("@doe", fromdate);
vdm.insert(sqlcmd);
}
}
}
pnlHide.Visible = false;
DataTable dtempty = new DataTable();
grdReports.DataSource = dtempty;
grdReports.DataBind();
lbl_msg.Text = "Successfully Saved";
}
catch (Exception ex)
{
lbl_msg.Text = ex.ToString();
}
}
protected void SendEmail(object sender, EventArgs e)
{
GetReport();
VehicleDBMgr vdbmngr = new VehicleDBMgr();
DateTime ServerDateCurrentdate = VehicleDBMgr.GetTime(vdbmngr.conn);
DateTime dtFromdate = ServerDateCurrentdate.AddDays(-1);
string DATE = dtFromdate.ToString("dd/MM/yyyy");
using (StringWriter sw = new StringWriter())
{
using (HtmlTextWriter hw = new HtmlTextWriter(sw))
{
grdtotal_dcReports.RenderControl(hw);
StringReader sr = new StringReader(sw.ToString());
string senderID = "*****@*****.**"; // use sender's email id here..
const string senderPassword = "******"; // sender password here...
SmtpClient smtp = new SmtpClient
{
Host = "czismtp.logix.in", // smtp server address here...
Port = 587,
//security type=tsl;
EnableSsl = true,
DeliveryMethod = SmtpDeliveryMethod.Network,
Credentials = new System.Net.NetworkCredential(senderID, senderPassword),
Timeout = 60000,
};
//MailMessage mm = new MailMessage();
//mm.From = new MailAddress(senderID);
//string tomailid = "*****@*****.**";
//string[] multimailid = tomailid.Split(',');
//foreach (string mailid in multimailid)
//{
// mm.To.Add(new MailAddress(mailid));
//}
MailMessage mm = new MailMessage(senderID, "*****@*****.**");
mm.Subject = "Dispatch Details (" + DATE + ")";
mm.Body = "Dispatch Details:<hr />" + sw.ToString();;
mm.IsBodyHtml = true;
smtp.Send(mm);
//SmtpClient smtp = new SmtpClient();
//smtp.Host = "smtp.gmail.com";
//smtp.EnableSsl = true;
//System.Net.NetworkCredential NetworkCred = new System.Net.NetworkCredential();
//NetworkCred.UserName = "******";
//NetworkCred.Password = "******";
//smtp.UseDefaultCredentials = true;
//smtp.Credentials = NetworkCred;
//smtp.Port = 587;
//smtp.Send(mm);
}
}
}
protected void btn_WIDB_Click(object sender, EventArgs e)
{
try
{
vdm = new VehicleDBMgr();
////SAPdbmanger SAPvdm = new SAPdbmanger();
DateTime ServerDateCurrentdate = VehicleDBMgr.GetTime(vdm.conn);
DataTable dt = (DataTable)Session["dtImport"];
foreach (DataRow dr in dt.Rows)
{
////////string ledger_code = dr["BP Code"].ToString();
////////string ledgername = dr["BP Name"].ToString();
////////cmd = new MySqlCommand("Update vehicel_master set ledger_code=@ledger_code where ledgername=@ledgername");
////////cmd.Parameters.Add("@ledger_code", ledger_code);
////////cmd.Parameters.Add("@ledgername", ledgername);
////////vdm.Update(cmd);
////////cmd = new MySqlCommand("Update vehicel_master set Petroledger_code=@Petroledger_code where petroledgername=@petroledgername");
////////cmd.Parameters.Add("@Petroledger_code", ledger_code);
////////cmd.Parameters.Add("@petroledgername", ledgername);
////////vdm.Update(cmd);
////////cmd = new MySqlCommand("Update termloanentry set ledger_code=@ledger_code where ledgername=@ledgername");
////////cmd.Parameters.Add("@ledger_code", ledger_code);
////////cmd.Parameters.Add("@ledgername", ledgername);
////////vdm.Update(cmd);
string sno = dr["sno"].ToString();
string seriesno = dr["newno"].ToString();
cmd = new MySqlCommand("Update new_tyres_sub set seriesno=@seriesno where sno=@sno");
cmd.Parameters.Add("@seriesno", seriesno);
cmd.Parameters.Add("@sno", sno);
vdm.Update(cmd);
}
lblmsg.Text = "Saved";
}
catch (Exception ex)
{
if (ex.Message == "Object reference not set to an instance of an object.")
{
lblmsg.Text = "Session Expired";
Response.Redirect("Login.aspx");
}
else
{
lblmsg.Text = ex.Message;
}
}
}
protected void btnLogIn_Click(object sender, EventArgs e)
{
try
{
Session["allvehicles"] = null;
vdm = new VehicleDBMgr();
vdm.InitializeDB();
String UN = "";
String UserName = txtUserName.Text, PassWord = txtPassword.Text;
cmd = new MySqlCommand("SELECT refno, main_user, loginid as UserName, pwd, usertype FROM loginstable WHERE (loginid = @UN) and (pwd=@Pwd)");
cmd.Parameters.Add("@UN", UserName);
cmd.Parameters.Add("@Pwd", PassWord);
DataTable dt = vdm.SelectQuery(cmd).Tables[0];//"ManageData", "UserName", new string[] { "UserName=@UserName" }, new string[] { UserName }, new string[] { "" }).Tables[0];
if (dt.Rows.Count > 0)
{
Session["field1"] = dt.Rows[0]["UserName"].ToString();
Session["field2"] = true;
Session["UserType"] = dt.Rows[0]["usertype"].ToString();
Session["field3"] = dt.Rows[0]["refno"].ToString();
Session["main_user"] = dt.Rows[0]["main_user"].ToString();
string UserType = dt.Rows[0]["UserType"].ToString();
DateTime Currentdate = VehicleDBMgr.GetTime(vdm.conn);
//cmd = new MySqlCommand("insert into logininfo(username,doe) values(@username,@doe)");
//cmd.Parameters.Add("@username", UserName);
//cmd.Parameters.Add("@doe", Currentdate);
//vdm.insert(cmd);
Session["TitleName"] = "SRI VYSHNAVI DAIRY SPECIALITIES (P) LTD";
Session["Address"] = "R.S.No:381/2,Punabaka village Post,Pellakuru Mandal,Nellore District -524129., ANDRAPRADESH (State).Phone: 9440622077, Fax: 044 – 26177799.,";
if (UserType == "Admin")
{
// Response.Redirect("WelcomePage.aspx", false);
Response.Redirect("Default.aspx", false);
}
else
{
//Response.Redirect("WelcomePage.aspx", false);
Response.Redirect("Default.aspx", false);
}
}
else
{
//MessageBox.Show("Please enter Correct User ID",this);
MessageBox.Show("Please enter Correct User ID and Password", this);
}
}
catch (Exception ex)
{
lbl_validation.Text = ex.ToString();
}
}
protected void Page_Load(object sender, EventArgs e)
{
DateTime Currentdate = VehicleDBMgr.GetTime(vdm.conn);
Session["UserName"] = null;
Session["userdata_sno"] = null;
Session["Owner"] = null;
Session["LevelType"] = null;
Session["Plant"] = null;
Session["SalesOffice"] = null;
Session["Distributors"] = null;
Session["getcategorynames"] = null;
Session["getsalesofficenames"] = null;
Session["getbranchcategorynames"] = null;
Session["getproductsnames"] = null;
Session["getsubregionsnames"] = null;
Session["getsalesofficenames"] = null;
Session["getbranchnames"] = null;
Session["getrates_categorynames"] = null;
Session["getrates_productsnames"] = null;
Session["getrates_subregion_subregionsnames"] = null;
Session["getrates_subregions_categorynames"] = null;
Session["getrates_subregions_productsnames"] = null;
Session["getrates_routes_subregionsnames"] = null;
Session["getrates_routes_categorynames"] = null;
Session["getrates_routes_productsnames"] = null;
Session["getrates_routes_routename"] = null;
Session["Branches"] = null;
Session["PlantSno"] = null;
Session["SalesoffSno"] = null;
Session["branchSno"] = null;
if (Session["EmpSno"] != null)
{
string sno = Session["EmpSno"].ToString();
cmd = new MySqlCommand("Select max(sno) as transno from logininfo where UserId=@userid");
cmd.Parameters.AddWithValue("@userid", sno);
DataTable dttime = vdm.SelectQuery(cmd).Tables[0];
if (dttime.Rows.Count > 0)
{
string transno = dttime.Rows[0]["transno"].ToString();
cmd = new MySqlCommand("UPDATE logininfo set logouttime=@logouttime,status=@status where sno=@sno");
cmd.Parameters.AddWithValue("@logouttime", Currentdate);
cmd.Parameters.AddWithValue("@status", "0");
cmd.Parameters.AddWithValue("@sno", transno);
vdm.Update(cmd);
}
}
Session.Abandon();
Response.Redirect("login.aspx");
}
protected void btn_WIDB_Click(object sender, EventArgs e)
{
try
{
vdm = new VehicleDBMgr();
DateTime ServerDateCurrentdate = VehicleDBMgr.GetTime(vdm.conn);
DataTable dt = (DataTable)Session["dtImport"];
int i = 0;
foreach (DataRow dr in dt.Rows)
{
string AgentCode = dr["Agent Code"].ToString();
int j = 3;
foreach (DataColumn dc in dt.Columns)
{
var cell = dc.ColumnName;
if (cell == "SNo" || cell == "Agent Code" || cell == "Agent Name")
{
}
else
{
cmd = new MySqlCommand("Select sno from invmaster where InvName=@InvName");
cmd.Parameters.AddWithValue("@InvName", dc.ColumnName);
DataTable dtProduct = vdm.SelectQuery(cmd).Tables[0];
string invsno = dtProduct.Rows[0]["Sno"].ToString();
string Qty = dt.Rows[i][j].ToString();
cmd = new MySqlCommand("update inventory_monitor set Qty=@Qty where BranchId=@BranchId and Inv_Sno=@Inv_Sno");
cmd.Parameters.AddWithValue("@Qty", Qty);
cmd.Parameters.AddWithValue("@BranchId", AgentCode);
cmd.Parameters.AddWithValue("@Inv_Sno", invsno);
vdm.Update(cmd);
}
j++;
}
i++;
}
lblmsg.Text = "Updated Successfully";
}
catch (Exception ex)
{
if (ex.Message == "Object reference not set to an instance of an object.")
{
lblmsg.Text = "Session Expired";
Response.Redirect("Login.aspx");
}
else
{
lblmsg.Text = ex.Message;
}
}
}
protected void save_head_click(object sender, EventArgs e)
{
vdm = new VehicleDBMgr();
DateTime CreateDate = VehicleDBMgr.GetTime(vdm.conn);
DateTime fromdate = DateTime.Now;
DataTable dtmainaccount = (DataTable)Session["dtImport"];
foreach (DataRow dr in dtmainaccount.Rows)
{
string Ledgername = dr["ledgername"].ToString();
string Ledgercode = dr["accountcode"].ToString();
if (Ledgername == "" || Ledgercode == "")
{
}
else
{
sqlcmd = new SqlCommand("SELECT sno FROM headofaccounts_master WHERE (accountname = @accountname)");
sqlcmd.Parameters.Add("@accountcode", Ledgercode);
sqlcmd.Parameters.Add("@accountname", Ledgername);
DataTable dtledgers = vdm.SelectQuery(sqlcmd).Tables[0];
if (dtledgers.Rows.Count > 0)
{
string sno = dtledgers.Rows[0]["sno"].ToString();
sqlcmd = new SqlCommand("update headofaccounts_master set accountname=@accountname,accountcode=@accountcode where sno=@sno");
sqlcmd.Parameters.Add("@accountcode", Ledgercode);
sqlcmd.Parameters.Add("@accountname", Ledgername);
sqlcmd.Parameters.Add("@doe", fromdate);
sqlcmd.Parameters.Add("@sno", sno);
vdm.Update(sqlcmd);
}
else
{
sqlcmd = new SqlCommand("Insert into headofaccounts_master (accountname, accountcode,doe) values (@accountname, @accountcode,@doe)");
sqlcmd.Parameters.Add("@accountcode", Ledgercode);
sqlcmd.Parameters.Add("@accountname", Ledgername);
sqlcmd.Parameters.Add("@doe", fromdate);
vdm.insert(sqlcmd);
}
}
}
DataTable dtempty = new DataTable();
grdReports.DataSource = dtempty;
grdReports.DataBind();
lblmsg.Text = "Successfully Saved";
}
protected void sessionsclick_click(object sender, EventArgs e)
{
try
{
string username = lbl_username.Text.ToString();
string password = lbl_passwords.Text.ToString();
cmd = new SqlCommand("update employe_login set loginstatus=@log where username=@username and passward=@passward");
cmd.Parameters.Add("@log", "0");
cmd.Parameters.Add("@username", username);
cmd.Parameters.Add("@passward", password);
vdm.Update(cmd);
DateTime ServerDateCurrentdate = VehicleDBMgr.GetTime(vdm.conn);
cmd = new SqlCommand("SELECT sno FROM employe_login where username=@username and passward=@passward");
cmd.Parameters.Add("@username", username);
cmd.Parameters.Add("@passward", password);
DataTable dtEMP = vdm.SelectQuery(cmd).Tables[0];
if (dtEMP.Rows.Count > 0)
{
string empid = dtEMP.Rows[0]["sno"].ToString();
cmd = new SqlCommand("Select max(sno) as transno from logininfo where userid=@userid");
cmd.Parameters.Add("@userid", empid);
DataTable dttime = vdm.SelectQuery(cmd).Tables[0];
if (dttime.Rows.Count > 0)
{
string transno = dttime.Rows[0]["transno"].ToString();
cmd = new SqlCommand("UPDATE logininfo set logouttime=@logouttime where sno=@sno");
cmd.Parameters.Add("@logouttime", ServerDateCurrentdate);
cmd.Parameters.Add("@sno", transno);
vdm.Update(cmd);
}
}
this.AlertBox.Visible = false;
}
catch
{
}
}
protected void btnSave_Click(object sender, EventArgs e)
{
try
{
DataTable dt = (DataTable)Session["btnImport"];
vdm = new VehicleDBMgr();
DateTime CreateDate = VehicleDBMgr.GetTime(vdm.conn);
DateTime fromdate = DateTime.Now;
int i = 1;
foreach (DataRow dr in dt.Rows)
{
string Ledgername = dr["Account Name"].ToString();
string Ledgercode = dr["Account Number"].ToString();
if (Ledgername == "" || Ledgercode == "")
{
}
else
{
sqlcmd = new SqlCommand("update headofaccounts_master set accountcode=@accountcode where accountname=@accountname");
sqlcmd.Parameters.Add("@accountcode", Ledgercode);
sqlcmd.Parameters.Add("@accountname", Ledgername);
vdm.Update(sqlcmd);
i++;
}
}
DataTable dtempty = new DataTable();
grdReports.DataSource = dtempty;
grdReports.DataBind();
lblMessage.Text = "Successfully Saved";
}
catch (Exception ex)
{
lblMessage.Text = ex.ToString();
}
}
protected void Page_Load(object sender, EventArgs e)
{
try
{
string sno = Session["Employ_Sno"].ToString();
cmd = new MySqlCommand("update employdata set loginstatus=@log where emp_sno=@sno");
cmd.Parameters.Add("@log", "0");
cmd.Parameters.Add("@sno", sno);
vdm.Update(cmd);
DateTime ServerDateCurrentdate = VehicleDBMgr.GetTime(vdm.conn);
cmd = new MySqlCommand("Select max(sno) as transno from login_info where empid=@userid");
cmd.Parameters.Add("@userid", Session["Employ_Sno"]);
cmd.Parameters.Add("@UserName", Session["UserName"]);
DataTable dttime = vdm.SelectQuery(cmd).Tables[0];
if (dttime.Rows.Count > 0)
{
string transno = dttime.Rows[0]["transno"].ToString();
cmd = new MySqlCommand("UPDATE login_info set logouttime=@logouttime where sno=@sno");
cmd.Parameters.Add("@logouttime", ServerDateCurrentdate);
cmd.Parameters.Add("@sno", transno);
vdm.Update(cmd);
}
Session.Clear();
Session.RemoveAll();
Session.Abandon();
// window.localStorage.clear();
//ClearCache();
//clearchachelocalall();
Response.Redirect("Default.aspx");
}
catch
{
}
}
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;
Report = new DataTable();
Session["RouteName"] = ddlSalesOffice.SelectedItem.Text;
Session["xporttype"] = "TallySales";
Session["IDate"] = DateTime.Now.AddDays(1).ToString("dd/MM/yyyy");
vdm = new VehicleDBMgr();
DateTime fromdate = DateTime.Now;
string[] dateFromstrig = txtFromdate.Text.Split(' ');
if (dateFromstrig.Length > 1)
{
if (dateFromstrig[0].Split('-').Length > 0)
{
string[] dates = dateFromstrig[0].Split('-');
string[] times = dateFromstrig[1].Split(':');
fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
lbl_selfromdate.Text = fromdate.ToString("dd/MM/yyyy");
lblRoutName.Text = ddlSalesOffice.SelectedItem.Text;
Session["filename"] = ddlSalesOffice.SelectedItem.Text + " Tally Sales " + fromdate.ToString("dd/MM/yyyy");
DateTime ReportDate = VehicleDBMgr.GetTime(vdm.conn);
DateTime dtapril = new DateTime();
DateTime dtmarch = new DateTime();
int currentyear = ReportDate.Year;
int nextyear = ReportDate.Year + 1;
if (ReportDate.Month > 3)
{
string apr = "4/1/" + currentyear;
dtapril = DateTime.Parse(apr);
string march = "3/31/" + nextyear;
dtmarch = DateTime.Parse(march);
}
if (ReportDate.Month <= 3)
{
string apr = "4/1/" + (currentyear - 1);
dtapril = DateTime.Parse(apr);
string march = "3/31/" + (nextyear - 1);
dtmarch = DateTime.Parse(march);
}
TimeSpan datespan = ReportDate.Subtract(fromdate);
int NoOfdays = datespan.Days;
Report = new DataTable();
if (ddltaxtype.SelectedValue == "Non Tax")
{
Report.Columns.Add("Ledger Type");
Report.Columns.Add("Customer Name");
Report.Columns.Add("Invoce No.");
Report.Columns.Add("Invoice Date");
Report.Columns.Add("HSN CODE");
Report.Columns.Add("Item Name");
Report.Columns.Add("Qty");
Report.Columns.Add("Rate");
Report.Columns.Add("Taxable Value");
Report.Columns.Add("SGST%");
Report.Columns.Add("SGST Amount");
Report.Columns.Add("CGST%");
Report.Columns.Add("CGST Amount");
Report.Columns.Add("IGST%");
Report.Columns.Add("IGST Amount");
Report.Columns.Add("Net Value");
Report.Columns.Add("Narration");
Report.Columns.Add("ProductID");
int i = 1;
cmd = new MySqlCommand("SELECT branchdata.sno,branchdata.Branchcode,branchdata.companycode, branchdata.incentivename, branchdata.BranchName,branchdata.stateid, statemastar.statename, statemastar.statecode , statemastar.gststatecode FROM branchdata INNER JOIN statemastar ON branchdata.stateid = statemastar.sno WHERE (branchdata.sno = @BranchID)");
if (Session["salestype"].ToString() == "Plant")
{
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
}
else
{
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
}
DataTable dtstate = vdm.SelectQuery(cmd).Tables[0];
string statename = "";
string branchcode = "";
string statecode = "";
string fromstateid = "";
string Branchcode = "";
string gststatecode = "";
string companycode = "";
if (dtstate.Rows.Count > 0)
{
statename = dtstate.Rows[0]["statename"].ToString();
branchcode = dtstate.Rows[0]["branchcode"].ToString();
statecode = dtstate.Rows[0]["statecode"].ToString();
fromstateid = dtstate.Rows[0]["stateid"].ToString();
gststatecode = dtstate.Rows[0]["gststatecode"].ToString();
companycode = dtstate.Rows[0]["companycode"].ToString();
}
cmd = new MySqlCommand("SELECT dispatch.DispName, dispatch.sno, dispatch.BranchID, tripdata.I_Date,tripdata.dcno, tripdata.Sno AS TripSno, dispatch.DispMode, branchmappingtable.SuperBranch, triproutes.Tripdata_sno FROM dispatch INNER JOIN triproutes ON dispatch.sno = triproutes.RouteID INNER JOIN tripdata ON triproutes.Tripdata_sno = tripdata.Sno INNER JOIN branchmappingtable ON dispatch.BranchID = branchmappingtable.SubBranch WHERE (dispatch.BranchID = @BranchID) AND (tripdata.I_Date BETWEEN @d1 AND @d2)and (dispatch.DispType='SO') and (tripdata.Status<>'C') OR (tripdata.I_Date BETWEEN @d1 AND @d2) AND (branchmappingtable.SuperBranch = @SuperBranch) and (dispatch.DispType='SO')and (tripdata.Status<>'C') GROUP BY tripdata.Sno ORDER BY dispatch.sno");
cmd.Parameters.AddWithValue("@SuperBranch", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate.AddDays(-1)));
cmd.Parameters.AddWithValue("@d2", GetHighDate(fromdate.AddDays(-1)));
DataTable dtDispnames = vdm.SelectQuery(cmd).Tables[0];
string DCNO = "";
foreach (DataRow dr in dtDispnames.Rows)
{
string tripID = ""; string soid = "";
cmd = new MySqlCommand("SELECT SUM(leakages.ShortQty) AS ShortQty, SUM(leakages.FreeMilk) AS FreeMilk, productsdata.tproduct, branchproducts.unitprice, leakages.invoiceno, products_category.tcategory, productsdata.hsncode, productsdata.igst, productsdata.cgst, productsdata.sgst, leakages.ProductID FROM leakages INNER JOIN tripdata ON leakages.TripID = tripdata.Sno INNER JOIN productsdata ON leakages.ProductID = productsdata.sno INNER JOIN branchproducts ON productsdata.sno = branchproducts.product_sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno WHERE (tripdata.ATripid = @TripID) AND (branchproducts.branch_sno = @BranchID) AND (productsdata.igst = 0) GROUP BY productsdata.tproduct, products_category.tcategory, leakages.ProductID");
//cmd = new MySqlCommand("SELECT SUM(leakages.ShortQty) AS ShortQty, SUM(leakages.FreeMilk) AS FreeMilk, productsdata.tproduct, branchproducts.unitprice, leakages.invoiceno, products_category.tcategory,productsdata.hsncode, productsdata.igst, productsdata.cgst, productsdata.sgst FROM leakages INNER JOIN tripdata ON leakages.TripID = tripdata.Sno INNER JOIN productsdata ON leakages.ProductID = productsdata.sno INNER JOIN branchproducts ON productsdata.sno = branchproducts.product_sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno WHERE (tripdata.ATripid = @TripID) AND (branchproducts.branch_sno = @BranchID) GROUP BY productsdata.tproduct, products_category.tcategory");
cmd.Parameters.AddWithValue("@TripID", dr["TripSno"].ToString());
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
DataTable Dtfreemilk = vdm.SelectQuery(cmd).Tables[0];
cmd = new MySqlCommand("SELECT branchleaktrans.ShortQty, branchleaktrans.FreeQty AS FreeMilk, productsdata.tproduct, branchproducts.unitprice, branchleaktrans.invoiceno, products_category.tcategory, productsdata.hsncode,productsdata.igst, productsdata.cgst, productsdata.sgst FROM branchproducts INNER JOIN productsdata ON branchproducts.product_sno = productsdata.sno INNER JOIN branchleaktrans ON productsdata.sno = branchleaktrans.ProdId INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno WHERE (branchproducts.branch_sno = @BranchID) AND (branchleaktrans.TripId = @TripID) AND (productsdata.igst = 0) GROUP BY productsdata.tproduct, products_category.tcategory");
cmd.Parameters.AddWithValue("@TripID", dr["TripSno"].ToString());
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
DataTable dtsalesofficeshortfree = vdm.SelectQuery(cmd).Tables[0];
DataTable newdt = new DataTable();
newdt = Dtfreemilk.Copy();
newdt.Merge(dtsalesofficeshortfree, true, MissingSchemaAction.Ignore);
int new_countdc = 0;
if (newdt.Rows.Count > 0)
{
int.TryParse(newdt.Rows[0]["invoiceno"].ToString(), out new_countdc);
}
if (new_countdc > 0)
{
}
else
{
if (Dtfreemilk.Rows.Count > 0)
{
//Hardcore this are the subbranches of sales offices.so for GST invoice purpose we need to maintain below processs
if (ddlSalesOffice.SelectedValue == "527" || ddlSalesOffice.SelectedValue == "2948" || ddlSalesOffice.SelectedValue == "282")
{
if (ddlSalesOffice.SelectedValue == "2948")
{
soid = "285";
}
if (ddlSalesOffice.SelectedValue == "527")
{
soid = "174";
}
if (ddlSalesOffice.SelectedValue == "282")
{
soid = "172";
}
}
else
{
if (ddlSalesOffice.SelectedValue == "572" || ddlSalesOffice.SelectedValue == "458")
{
soid = "158";
}
soid = ddlSalesOffice.SelectedValue;
}
double freeqty = 0; double taxfreeqty = 0;
foreach (DataRow branch in newdt.Rows)
{
//Free
if (branch["sgst"].ToString() == "0")
{
double Qty = 0;
double.TryParse(branch["FreeMilk"].ToString(), out Qty);
freeqty += Qty;
}
}
if (freeqty > 0)
{
cmd = new MySqlCommand("SELECT IFNULL(MAX(agentdcno), 0) + 1 AS Sno FROM agentdc WHERE (soid = @soid) AND (IndDate BETWEEN @d1 AND @d2)");
cmd.Parameters.AddWithValue("@soid", soid);
cmd.Parameters.AddWithValue("@d1", GetLowDate(dtapril));
cmd.Parameters.AddWithValue("@d2", GetHighDate(dtmarch));
DataTable dtadcno = vdm.SelectQuery(cmd).Tables[0];
string invno = dtadcno.Rows[0]["Sno"].ToString();
cmd = new MySqlCommand("Insert Into Agentdc (BranchId,IndDate,agentdcno,soid,stateid,companycode,moduleid,doe,invoicetype) Values(@BranchId,@IndDate,@agentdcno,@soid,@stateid,@companycode,@moduleid,@doe,@invoicetype)");
cmd.Parameters.AddWithValue("@BranchId", dr["sno"].ToString());
cmd.Parameters.AddWithValue("@IndDate", fromdate.AddDays(-1));
cmd.Parameters.AddWithValue("@agentdcno", invno);
cmd.Parameters.AddWithValue("@soid", soid);
cmd.Parameters.AddWithValue("@stateid", gststatecode);
cmd.Parameters.AddWithValue("@companycode", companycode);
cmd.Parameters.AddWithValue("@doe", ReportDate);
cmd.Parameters.AddWithValue("@moduleid", "4");// Module 4 is Credit Note (Ex...Leaks)
cmd.Parameters.AddWithValue("@invoicetype", "TOSales");
vdm.insert(cmd);
cmd = new MySqlCommand("UPDATE leakages t1 JOIN tripdata t2 ON t1.TripID=t2.Sno JOIN productsdata as t3 ON t3.sno=t1.ProductID SET t1.Invoiceno = @invoiceno WHERE t2.ATripid=@TripID and t3.igst=0");
//cmd = new MySqlCommand("UPDATE leakages t1 JOIN tripdata t2 ON t1.TripID=t2 .Sno SET t1.Invoiceno = @invoiceno WHERE t2 .ATripid=@TripID");
cmd.Parameters.AddWithValue("@invoiceno", invno);
cmd.Parameters.AddWithValue("@TripID", dr["TripSno"].ToString());
vdm.Update(cmd);
cmd = new MySqlCommand("Update branchleaktrans t1 join productsdata AS t2 ON t1.ProdId=t2.sno set t1.invoiceno=@invoiceno where t1.TripID=@TripID and t2.igst=0");
//cmd = new MySqlCommand("Update branchleaktrans set invoiceno=@invoiceno where TripID=@TripID");
cmd.Parameters.AddWithValue("@invoiceno", invno);
cmd.Parameters.AddWithValue("@TripID", dr["TripSno"].ToString());
vdm.Update(cmd);
int.TryParse(invno, out new_countdc);
}
}
}
DCNO = "";
if (new_countdc <= 10)
{
DCNO = "0000" + new_countdc;
}
if (new_countdc >= 10 && new_countdc <= 99)
{
DCNO = "000" + new_countdc;
}
if (new_countdc >= 99 && new_countdc <= 999)
{
DCNO = "00" + new_countdc;
}
if (new_countdc > 999)
{
DCNO = "0" + new_countdc;
}
if (ddlSalesOffice.SelectedValue == "306")
{
if (fromdate.AddDays(1).Month > 3)
{
DCNO = branchcode + "/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "N/" + DCNO;
}
else
{
if (fromdate.AddDays(1).Month <= 3)
{
DCNO = branchcode + "/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "N/" + DCNO;
}
else
{
DCNO = branchcode + "/" + dtapril.AddYears(-1).ToString("yy") + "-" + dtmarch.AddYears(-1).ToString("yy") + "N/" + DCNO;
}
}
}
else
{
if (fromdate.Month > 3)
{
DCNO = branchcode + "/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "N/" + DCNO;
}
else
{
if (fromdate.Month <= 3)
{
DCNO = branchcode + "/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "N/" + DCNO;
}
else
{
DCNO = branchcode + "/" + dtapril.AddYears(-1).ToString("yy") + "-" + dtmarch.AddYears(-1).ToString("yy") + "N/" + DCNO;
}
}
//if (fromdate.Month > 3)
//{
// DCNO = branchcode + "/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "N/" + DCNO;
//}
//else
//{
// DCNO = branchcode + "/" + dtapril.AddYears(-1).ToString("yy") + "-" + dtmarch.AddYears(-1).ToString("yy") + "N/" + DCNO;
//}
}
double totfreeamount = 0;
foreach (DataRow branch in newdt.Rows)
{
if (branch["sgst"].ToString() == "0")
{
//Free
DataRow newrow = Report.NewRow();
double Qty = 0;
double.TryParse(branch["FreeMilk"].ToString(), out Qty);
double freeqty = 0;
freeqty += Qty;
if (Qty == 0.0)
{
}
else
{
double rate = 0;
double.TryParse(branch["unitprice"].ToString(), out rate);
Qty = Math.Round(Qty, 2);
newrow["Customer Name"] = "Free Sales-Milk-" + dtstate.Rows[0]["incentivename"].ToString();
newrow["Invoce No."] = DCNO;
if (ddlSalesOffice.SelectedValue == "306")
{
newrow["Invoice Date"] = fromdate.AddDays(1).ToString("dd-MMM-yyyy");
}
else
{
newrow["Invoice Date"] = fromdate.ToString("dd-MMM-yyyy");
}
newrow["Item Name"] = branch["tproduct"].ToString();
newrow["HSN CODE"] = branch["hsncode"].ToString();
newrow["Qty"] = Qty;
newrow["Rate"] = rate;
double invval = 0;
double sgst = 0;
double sgstamount = 0;
double cgst = 0;
double cgstamount = 0;
double taxval = 0;
double Igst = 0;
double Igstamount = 0;
double totRate = 0;
double tot_vatamount = 0;
double PAmount = 0;
double.TryParse(branch["Igst"].ToString(), out Igst);
double Igstcon = 100 + Igst;
Igstamount = (rate / Igstcon) * Igst;
Igstamount = Math.Round(Igstamount, 2);
totRate = Igstamount;
double igst = 0;
double.TryParse(branch["igst"].ToString(), out igst);
string tcategory = "";
if (igst == null || igst == 0.0)
{
tcategory = branch["tcategory"].ToString();
}
else
{
tcategory = branch["tcategory"].ToString() + "-CGST/SGST";
}
newrow["Ledger Type"] = tcategory.ToString();
double Vatrate = rate - totRate;
Vatrate = Math.Round(Vatrate, 2);
newrow["Rate"] = Vatrate.ToString();
PAmount = Qty * Vatrate;
newrow["Taxable Value"] = Math.Round(PAmount, 2);
tot_vatamount = (PAmount * Igst) / 100;
sgstamount = (tot_vatamount / 2);
sgstamount = Math.Round(sgstamount, 2);
newrow["sgst%"] = "'" + branch["sgst"].ToString();
newrow["sgst amount"] = sgstamount.ToString();
cgstamount = (tot_vatamount / 2);
cgstamount = Math.Round(cgstamount, 2);
newrow["cgst%"] = "'" + branch["cgst"].ToString();
newrow["cgst amount"] = cgstamount.ToString();
newrow["Igst%"] = "'" + 0;
newrow["Igst amount"] = 0;
newrow["ProductID"] = branch["ProductID"].ToString();
invval = Math.Round(invval, 2);
double netvalue = 0;
netvalue = invval + taxval;
netvalue = Math.Round(netvalue, 2);
double tot_amount = PAmount + tot_vatamount;
tot_amount = Math.Round(tot_amount, 2);
newrow["Net Value"] = tot_amount;
newrow["Narration"] = "Being the Sale Of Milk Through " + ddlSalesOffice.SelectedItem.Text + ". This is Free Milk Vide JV No " + dr["dcno"].ToString() + ",Emp Name " + Session["EmpName"].ToString();
Report.Rows.Add(newrow);
}
}
}
}
grdReports.DataSource = Report;
grdReports.DataBind();
Session["xportdata"] = Report;
}
else
{
Report.Columns.Add("Ledger Type");
Report.Columns.Add("Customer Name");
Report.Columns.Add("Invoce No.");
Report.Columns.Add("Invoice Date");
Report.Columns.Add("HSN CODE");
Report.Columns.Add("Item Name");
Report.Columns.Add("Qty");
Report.Columns.Add("Rate");
Report.Columns.Add("Taxable Value");
Report.Columns.Add("SGST%");
Report.Columns.Add("SGST Amount");
Report.Columns.Add("CGST%");
Report.Columns.Add("CGST Amount");
Report.Columns.Add("IGST%");
Report.Columns.Add("IGST Amount");
Report.Columns.Add("Net Value");
Report.Columns.Add("Narration");
int i = 1;
cmd = new MySqlCommand("SELECT branchdata.sno,branchdata.Branchcode,branchdata.companycode, branchdata.incentivename, branchdata.BranchName,branchdata.stateid, statemastar.statename, statemastar.statecode , statemastar.gststatecode FROM branchdata INNER JOIN statemastar ON branchdata.stateid = statemastar.sno WHERE (branchdata.sno = @BranchID)");
if (Session["salestype"].ToString() == "Plant")
{
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
}
else
{
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
}
DataTable dtstate = vdm.SelectQuery(cmd).Tables[0];
string statename = "";
string branchcode = "";
string statecode = "";
string fromstateid = "";
string Branchcode = "";
string gststatecode = "";
string companycode = "";
if (dtstate.Rows.Count > 0)
{
statename = dtstate.Rows[0]["statename"].ToString();
branchcode = dtstate.Rows[0]["branchcode"].ToString();
statecode = dtstate.Rows[0]["statecode"].ToString();
fromstateid = dtstate.Rows[0]["stateid"].ToString();
gststatecode = dtstate.Rows[0]["gststatecode"].ToString();
companycode = dtstate.Rows[0]["companycode"].ToString();
}
cmd = new MySqlCommand("SELECT dispatch.DispName, dispatch.sno, dispatch.BranchID, tripdata.I_Date,tripdata.dcno, tripdata.Sno AS TripSno, dispatch.DispMode, branchmappingtable.SuperBranch, triproutes.Tripdata_sno FROM dispatch INNER JOIN triproutes ON dispatch.sno = triproutes.RouteID INNER JOIN tripdata ON triproutes.Tripdata_sno = tripdata.Sno INNER JOIN branchmappingtable ON dispatch.BranchID = branchmappingtable.SubBranch WHERE (dispatch.BranchID = @BranchID) AND (tripdata.I_Date BETWEEN @d1 AND @d2)and (dispatch.DispType='SO') and (tripdata.Status<>'C') OR (tripdata.I_Date BETWEEN @d1 AND @d2) AND (branchmappingtable.SuperBranch = @SuperBranch) and (dispatch.DispType='SO')and (tripdata.Status<>'C') GROUP BY tripdata.Sno ORDER BY dispatch.sno");
cmd.Parameters.AddWithValue("@SuperBranch", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate.AddDays(-1)));
cmd.Parameters.AddWithValue("@d2", GetHighDate(fromdate.AddDays(-1)));
DataTable dtDispnames = vdm.SelectQuery(cmd).Tables[0];
string DCNO = "";
foreach (DataRow dr in dtDispnames.Rows)
{
string tripID = ""; string soid = "";
cmd = new MySqlCommand("SELECT SUM(leakages.ShortQty) AS ShortQty, SUM(leakages.FreeMilk) AS FreeMilk, productsdata.tproduct, branchproducts.unitprice, leakages.invoiceno, products_category.tcategory, productsdata.hsncode, productsdata.igst, productsdata.cgst, productsdata.sgst FROM leakages INNER JOIN tripdata ON leakages.TripID = tripdata.Sno INNER JOIN productsdata ON leakages.ProductID = productsdata.sno INNER JOIN branchproducts ON productsdata.sno = branchproducts.product_sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno WHERE (tripdata.ATripid = @TripID) AND (branchproducts.branch_sno = @BranchID) AND (productsdata.igst > 0) GROUP BY productsdata.tproduct, products_category.tcategory");
//cmd = new MySqlCommand("SELECT SUM(leakages.ShortQty) AS ShortQty, SUM(leakages.FreeMilk) AS FreeMilk, productsdata.tproduct, branchproducts.unitprice, leakages.invoiceno, products_category.tcategory,productsdata.hsncode, productsdata.igst, productsdata.cgst, productsdata.sgst FROM leakages INNER JOIN tripdata ON leakages.TripID = tripdata.Sno INNER JOIN productsdata ON leakages.ProductID = productsdata.sno INNER JOIN branchproducts ON productsdata.sno = branchproducts.product_sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno WHERE (tripdata.ATripid = @TripID) AND (branchproducts.branch_sno = @BranchID) GROUP BY productsdata.tproduct, products_category.tcategory");
cmd.Parameters.AddWithValue("@TripID", dr["TripSno"].ToString());
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
DataTable Dtfreemilk = vdm.SelectQuery(cmd).Tables[0];
cmd = new MySqlCommand("SELECT branchleaktrans.ShortQty, branchleaktrans.FreeQty AS FreeMilk, productsdata.tproduct, branchproducts.unitprice, branchleaktrans.invoiceno, products_category.tcategory, productsdata.hsncode,productsdata.igst, productsdata.cgst, productsdata.sgst FROM branchproducts INNER JOIN productsdata ON branchproducts.product_sno = productsdata.sno INNER JOIN branchleaktrans ON productsdata.sno = branchleaktrans.ProdId INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno WHERE (branchproducts.branch_sno = @BranchID) AND (branchleaktrans.TripId = @TripID) AND (productsdata.igst > 0) GROUP BY productsdata.tproduct, products_category.tcategory");
cmd.Parameters.AddWithValue("@TripID", dr["TripSno"].ToString());
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
DataTable dtsalesofficeshortfree = vdm.SelectQuery(cmd).Tables[0];
DataTable newdt = new DataTable();
newdt = Dtfreemilk.Copy();
newdt.Merge(dtsalesofficeshortfree, true, MissingSchemaAction.Ignore);
int new_countdc = 0;
if (newdt.Rows.Count > 0)
{
int.TryParse(newdt.Rows[0]["invoiceno"].ToString(), out new_countdc);
}
if (new_countdc > 0)
{
}
else
{
if (newdt.Rows.Count > 0)
{
//Hardcore this are the subbranches of sales offices.so for GST invoice purpose we need to maintain below processs
if (ddlSalesOffice.SelectedValue == "527" || ddlSalesOffice.SelectedValue == "2948" || ddlSalesOffice.SelectedValue == "282")
{
if (ddlSalesOffice.SelectedValue == "2948")
{
soid = "285";
}
if (ddlSalesOffice.SelectedValue == "527")
{
soid = "174";
}
if (ddlSalesOffice.SelectedValue == "282")
{
soid = "172";
}
}
else
{
if (ddlSalesOffice.SelectedValue == "572" || ddlSalesOffice.SelectedValue == "458")
{
soid = "158";
}
soid = ddlSalesOffice.SelectedValue;
}
double freeqty = 0; double taxfreeqty = 0;
foreach (DataRow branch in newdt.Rows)
{
//Free
if (branch["sgst"].ToString() != "0")
{
double Qty = 0;
double.TryParse(branch["FreeMilk"].ToString(), out Qty);
freeqty += Qty;
}
}
if (freeqty > 0)
{
cmd = new MySqlCommand("SELECT IFNULL(MAX(agentdcno), 0) + 1 AS Sno FROM agenttaxdc WHERE (soid = @soid) AND (IndDate BETWEEN @d1 AND @d2)");
cmd.Parameters.AddWithValue("@soid", soid);
cmd.Parameters.AddWithValue("@d1", GetLowDate(dtapril));
cmd.Parameters.AddWithValue("@d2", GetHighDate(dtmarch));
DataTable dtadcno = vdm.SelectQuery(cmd).Tables[0];
string invno = dtadcno.Rows[0]["Sno"].ToString();
cmd = new MySqlCommand("Insert Into agenttaxdc (BranchId,IndDate,agentdcno,soid,stateid,companycode,moduleid,doe,invoicetype) Values(@BranchId,@IndDate,@agentdcno,@soid,@stateid,@companycode,@moduleid,@doe,@invoicetype)");
cmd.Parameters.AddWithValue("@BranchId", dr["sno"].ToString());
cmd.Parameters.AddWithValue("@IndDate", fromdate.AddDays(-1));
cmd.Parameters.AddWithValue("@agentdcno", invno);
cmd.Parameters.AddWithValue("@soid", soid);
cmd.Parameters.AddWithValue("@stateid", gststatecode);
cmd.Parameters.AddWithValue("@companycode", companycode);
cmd.Parameters.AddWithValue("@doe", ReportDate);
cmd.Parameters.AddWithValue("@moduleid", "4");// Module 4 is Credit Note (Ex...Leaks)
cmd.Parameters.AddWithValue("@invoicetype", "TOSales");
vdm.insert(cmd);
cmd = new MySqlCommand("UPDATE leakages t1 JOIN tripdata t2 ON t1.TripID=t2.Sno JOIN productsdata as t3 ON t3.sno=t1.ProductID SET t1.Invoiceno = @invoiceno WHERE t2.ATripid=@TripID and t3.igst>0");
//cmd = new MySqlCommand("UPDATE leakages t1 JOIN tripdata t2 ON t1.TripID=t2.Sno JOIN productsdata as t3 ON t3.productid=t1.productid ON SET t1.Invoiceno = @invoiceno WHERE t2 .ATripid=@TripID and t3.igst>0)");
cmd.Parameters.AddWithValue("@invoiceno", invno);
cmd.Parameters.AddWithValue("@TripID", dr["TripSno"].ToString());
vdm.Update(cmd);
cmd = new MySqlCommand("Update branchleaktrans t1 join productsdata AS t2 ON t1.ProdId=t2.sno set t1.invoiceno=@invoiceno where t1.TripID=@TripID and t2.igst>0");
cmd.Parameters.AddWithValue("@invoiceno", invno);
cmd.Parameters.AddWithValue("@TripID", dr["TripSno"].ToString());
vdm.Update(cmd);
int.TryParse(invno, out new_countdc);
}
}
}
DCNO = "";
if (new_countdc <= 10)
{
DCNO = "0000" + new_countdc;
}
if (new_countdc >= 10 && new_countdc <= 99)
{
DCNO = "000" + new_countdc;
}
if (new_countdc >= 99 && new_countdc <= 999)
{
DCNO = "00" + new_countdc;
}
if (new_countdc > 999)
{
DCNO = "0" + new_countdc;
}
if (ddlSalesOffice.SelectedValue == "306")
{
if (fromdate.AddDays(1).Month > 3)
{
DCNO = branchcode + "/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "T/" + DCNO;
}
else
{
if (fromdate.AddDays(1).Month <= 3)
{
DCNO = branchcode + "/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "T/" + DCNO;
}
else
{
DCNO = branchcode + "/" + dtapril.AddYears(-1).ToString("yy") + "-" + dtmarch.AddYears(-1).ToString("yy") + "T/" + DCNO;
}
}
}
else
{
if (fromdate.Month > 3)
{
DCNO = branchcode + "/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "T/" + DCNO;
}
else
{
if (fromdate.Month <= 3)
{
DCNO = branchcode + "/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "T/" + DCNO;
}
else
{
DCNO = branchcode + "/" + dtapril.AddYears(-1).ToString("yy") + "-" + dtmarch.AddYears(-1).ToString("yy") + "T/" + DCNO;
}
}
}
double totfreeamount = 0;
foreach (DataRow branch in newdt.Rows)
{
//Free
if (branch["sgst"].ToString() != "0")
{
DataRow newrow = Report.NewRow();
double Qty = 0;
double.TryParse(branch["FreeMilk"].ToString(), out Qty);
double freeqty = 0;
freeqty += Qty;
if (Qty == 0.0)
{
}
else
{
double rate = 0;
double.TryParse(branch["unitprice"].ToString(), out rate);
Qty = Math.Round(Qty, 2);
newrow["Customer Name"] = "Free Sales-Milk-" + dtstate.Rows[0]["incentivename"].ToString();
newrow["Invoce No."] = DCNO;
if (ddlSalesOffice.SelectedValue == "306")
{
newrow["Invoice Date"] = fromdate.AddDays(1).ToString("dd-MMM-yyyy");
}
else
{
newrow["Invoice Date"] = fromdate.ToString("dd-MMM-yyyy");
}
newrow["Item Name"] = branch["tproduct"].ToString();
newrow["HSN CODE"] = branch["hsncode"].ToString();
newrow["Qty"] = Qty;
newrow["Rate"] = rate;
double invval = 0;
double sgst = 0;
double sgstamount = 0;
double cgst = 0;
double cgstamount = 0;
double taxval = 0;
double Igst = 0;
double Igstamount = 0;
double totRate = 0;
double tot_vatamount = 0;
double PAmount = 0;
double.TryParse(branch["Igst"].ToString(), out Igst);
double Igstcon = 100 + Igst;
Igstamount = (rate / Igstcon) * Igst;
Igstamount = Math.Round(Igstamount, 2);
totRate = Igstamount;
double igst = 0;
double.TryParse(branch["igst"].ToString(), out igst);
string tcategory = "";
if (igst == null || igst == 0.0)
{
tcategory = branch["tcategory"].ToString();
}
else
{
tcategory = branch["tcategory"].ToString() + "-CGST/SGST";
}
newrow["Ledger Type"] = tcategory.ToString();
double Vatrate = rate - totRate;
Vatrate = Math.Round(Vatrate, 2);
newrow["Rate"] = Vatrate.ToString();
PAmount = Qty * Vatrate;
newrow["Taxable Value"] = Math.Round(PAmount, 2);
tot_vatamount = (PAmount * Igst) / 100;
sgstamount = (tot_vatamount / 2);
sgstamount = Math.Round(sgstamount, 2);
newrow["sgst%"] = "'" + branch["sgst"].ToString();
newrow["sgst amount"] = sgstamount.ToString();
cgstamount = (tot_vatamount / 2);
cgstamount = Math.Round(cgstamount, 2);
newrow["cgst%"] = "'" + branch["cgst"].ToString();
newrow["cgst amount"] = cgstamount.ToString();
newrow["Igst%"] = "'" + 0;
newrow["Igst amount"] = 0;
invval = Math.Round(invval, 2);
double netvalue = 0;
netvalue = invval + taxval;
netvalue = Math.Round(netvalue, 2);
double tot_amount = PAmount + tot_vatamount;
tot_amount = Math.Round(tot_amount, 2);
newrow["Net Value"] = tot_amount;
newrow["Narration"] = "Being the Sale Of Milk Through " + ddlSalesOffice.SelectedItem.Text + ". This is Free Milk Vide JV No " + dr["dcno"].ToString() + ",Emp Name " + Session["EmpName"].ToString();
Report.Rows.Add(newrow);
}
}
}
}
grdReports.DataSource = Report;
grdReports.DataBind();
Session["xportdata"] = Report;
}
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
grdReports.DataSource = Report;
grdReports.DataBind();
}
}
void GetReport()
{
try
{
vdm = new VehicleDBMgr();
double Totalmilksale = 0;
double Totalbulkmilksale = 0;
double Totalbulkmilksaleamount = 0;
DateTime fromdate = DateTime.Now;
Report = new DataTable();
DateTime ServerDateCurrentdate = VehicleDBMgr.GetTime(vdm.conn);
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"] = "Incentive For -> " + ddlAgentName.SelectedItem.Text;
lblAgent.Text = ddlAgentName.SelectedItem.Text;
lblRoute.Text = ddlDispName.SelectedItem.Text;
//lblroute.Text = ddlRouteName.SelectedItem.Text;
lbl_fromDate.Text = fromdate.ToString("dd-MM-yyyy hh:mm");
lbl_selttodate.Text = todate.ToString("dd-MM-yyyy hh:mm");
cmd = new MySqlCommand("SELECT sno, FromDate, Todate, StructureName, BranchId, EntryDate, ActualDiscount, TotalDiscount, Remarks, structure_sno, leakagepercent, DueClear FROM incentivetransactions WHERE (FromDate BETWEEN @d1 AND @d2) AND (BranchId = @BranchId)");
cmd.Parameters.AddWithValue("@branchid", ddlAgentName.SelectedValue);
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate.AddDays(-1)));
cmd.Parameters.AddWithValue("@d2", GetHighDate(todate.AddDays(-1)));
DataTable dtstructuresno = vdm.SelectQuery(cmd).Tables[0];
string structuresno = dtstructuresno.Rows[0]["structure_sno"].ToString();
string leakpercent = dtstructuresno.Rows[0]["leakagepercent"].ToString();
string incentivegiven = dtstructuresno.Rows[0]["TotalDiscount"].ToString();
string remarks = dtstructuresno.Rows[0]["Remarks"].ToString();
cmd = new MySqlCommand("SELECT productsdata.sno, productsdata.ProductName, product_clubbing.ClubName, incentive_structure.StructureName, product_clubbing.sno AS clubbingsno,products_category.Categoryname, products_subcategory.category_sno FROM incentive_structure INNER JOIN incentive_struct_sub ON incentive_structure.sno = incentive_struct_sub.is_sno INNER JOIN product_clubbing ON incentive_struct_sub.clubbingID = product_clubbing.sno INNER JOIN subproductsclubbing ON product_clubbing.sno = subproductsclubbing.Clubsno INNER JOIN productsdata ON subproductsclubbing.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 (incentive_structure.sno = @StructureID) ");
cmd.Parameters.AddWithValue("@StructureID", structuresno);
DataTable dtincentivestructure = vdm.SelectQuery(cmd).Tables[0];
cmd = new MySqlCommand("SELECT DATE_FORMAT(indents.I_date, '%d %b %y') AS IndentDate, branchdata.BranchName, SUM(indents_subtable.DeliveryQty * indents_subtable.UnitCost) AS Amount,productsdata.ProductName, productsdata.sno AS prdtsno,Inventorysno as invsno, sum(indents_subtable.DeliveryQty) AS DeliveryQty, indents_subtable.UnitCost, products_category.Categoryname,products_category.sno AS categorysno FROM indents INNER JOIN indents_subtable ON indents.IndentNo = indents_subtable.IndentNo INNER JOIN branchdata ON indents.Branch_id = branchdata.sno 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 (branchdata.sno = @BranchID) AND (indents.I_date BETWEEN @d1 AND @d2) AND (indents_subtable.DeliveryQty <> ' ') GROUP BY DATE(indents.I_date), branchdata.BranchName, productsdata.ProductName");
//cmd = new MySqlCommand("SELECT DATE_FORMAT(indents.I_date, '%d %b %y') AS IndentDate, branchdata.BranchName, indents_subtable.DeliveryQty * indents_subtable.UnitCost AS Amount,productsdata.ProductName, productsdata.sno AS prdtsno,productsdata.Inventorysno as invsno, indents_subtable.DeliveryQty, indents_subtable.UnitCost, products_category.Categoryname,products_category.sno AS categorysno FROM indents INNER JOIN indents_subtable ON indents.IndentNo = indents_subtable.IndentNo INNER JOIN branchdata ON indents.Branch_id = branchdata.sno 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 (branchdata.sno = @BranchID) AND (indents.I_date BETWEEN @d1 AND @d2) AND (indents_subtable.DeliveryQty <> ' ') GROUP BY indents.I_date, branchdata.BranchName, productsdata.ProductName, indents_subtable.DeliveryQty");
cmd.Parameters.AddWithValue("@BranchID", ddlAgentName.SelectedValue);
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate.AddDays(-1)));
cmd.Parameters.AddWithValue("@d2", GetHighDate(todate.AddDays(-1)));
DataTable dtdelivered = vdm.SelectQuery(cmd).Tables[0];
int dtrowscount = dtdelivered.Rows.Count;
if (dtrowscount == 0)
{
if (txtTodate.Text == "")
{
}
else
{
lblmsg.Text = "No Indent Found Between These Days";
}
grdReports.DataSource = Report;
grdReports.DataBind();
}
else
{
float count = 0;
count = (float)(todate - fromdate.AddDays(-1)).TotalDays;
Report = new DataTable();
Report.Columns.Add("IndentDate");
//Report.Columns.Add("Branch Name");
DataView view = new DataView(dtdelivered);
DataTable distinctProduct = view.ToTable(true, "ProductName");
foreach (DataRow dr in distinctProduct.Rows)
{
Report.Columns.Add(dr["ProductName"].ToString());
}
Report.Columns.Add("Total Ltrs");
DataTable distincttable = view.ToTable(true, "BranchName", "IndentDate");
DataTable distincttotal = view.ToTable(true, "ProductName", "DeliveryQty");
int i = 1;
double Total = 0;
double TotalQty = 0;
double prdtwisetotal = 0;
double prdtwiseamount = 0;
int categorysno = 0;
foreach (DataRow branch in distincttable.Rows)
{
DataRow newrow = Report.NewRow();
string IndentDate = branch["IndentDate"].ToString();
DateTime dtIndentDate = Convert.ToDateTime(IndentDate).AddDays(1);
string ChangedTime = dtIndentDate.ToString("dd/MMM/yyyy");
newrow["IndentDate"] = ChangedTime;
//newrow["Branch Name"] = branch["BranchName"].ToString();
Total = 0;
foreach (DataRow dr in dtdelivered.Rows)
{
if (branch["IndentDate"].ToString() == dr["IndentDate"].ToString())
{
if (dr["DeliveryQty"].ToString() != "")
{
double DeliveryQty = 0;
double.TryParse(dr["DeliveryQty"].ToString(), out DeliveryQty);
double UnitCost = 0;
double.TryParse(dr["UnitCost"].ToString(), out UnitCost);
DeliveryQty = Math.Round(DeliveryQty, 2);
newrow[dr["ProductName"].ToString()] = DeliveryQty;
// Total += DeliveryQty * UnitCost;
int.TryParse(dr["categorysno"].ToString(), out categorysno);
if (categorysno == 9)
{
string invsno = dr["invsno"].ToString();
if (invsno == "4")
{
Totalbulkmilksale += DeliveryQty;
}
Totalmilksale += DeliveryQty;
}
Total += DeliveryQty;
}
}
}
newrow["Total Ltrs"] = Total;
TotalQty += Total;
Report.Rows.Add(newrow);
}
DataRow newvartical = Report.NewRow();
newvartical["IndentDate"] = "Total";
foreach (DataRow dr in distinctProduct.Rows)
{
prdtwisetotal = 0;
foreach (DataRow drtotprdt in dtdelivered.Rows)
{
if (dr["ProductName"].ToString() == drtotprdt["ProductName"].ToString())
{
double prdtQty = 0;
double.TryParse(drtotprdt["DeliveryQty"].ToString(), out prdtQty);
prdtQty = Math.Round(prdtQty, 2);
if (drtotprdt["categorysno"].ToString() == "9")
{
double prdtamt = 0;
double.TryParse(drtotprdt["Amount"].ToString(), out prdtamt);
prdtwiseamount += prdtamt;
string invsno = drtotprdt["invsno"].ToString();
if (invsno == "4")
{
Totalbulkmilksaleamount += prdtamt;
}
}
prdtwisetotal += prdtQty;
}
}
newvartical[dr["ProductName"].ToString()] = prdtwisetotal;
}
totmilkamt = (float)prdtwiseamount;
//double val = 0;
//double.TryParse(Report.Compute("sum([Total Amount])", "[Total Amount]<>'0'").ToString(), out val);
newvartical["Total Ltrs"] = TotalQty;
double val1 = 0;
foreach (DataColumn dc in Report.Columns)
{
if (dc.DataType == typeof(Double))
{
val1 = 0.0;
double.TryParse(Report.Compute("sum([" + dc.ToString() + "])", "[" + dc.ToString() + "]<>'0'").ToString(), out val1);
newvartical[dc.ToString()] = val1;
}
}
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;
}
if (Report.Columns.Count == 3)
{
Report.Columns.Add(" ");
Report.Columns.Add(" ");
Report.Columns.Add(" ");
}
if (Report.Columns.Count == 4)
{
Report.Columns.Add(" ");
}
DataTable dtTotincentive = new DataTable();
dtTotincentive.Columns.Add("ClubbingName");
dtTotincentive.Columns.Add("TotalSale").DataType = typeof(Double);
dtTotincentive.Columns.Add("AverageSale").DataType = typeof(Double);
dtTotincentive.Columns.Add("DiscountSlot");
dtTotincentive.Columns.Add("TotalAmount").DataType = typeof(Double);
string clubbingname = "";
string categoryserial = "9";
float milkincentive = 0;
DataView incentiveview = new DataView(dtincentivestructure);
// DataTable dticentive = incentiveview.ToTable(true, "ClubName", "clubbingsno", "category_sno");
DataTable dticentive = incentiveview.ToTable(true, "ClubName", "clubbingsno");
//cmd = new MySqlCommand("SELECT ROUND(SUM(indents_subtable.DeliveryQty), 2) AS deliveryqty,subproductsclubbing. FROM indents INNER JOIN indents_subtable ON indents.IndentNo = indents_subtable.IndentNo INNER JOIN subproductsclubbing ON indents_subtable.Product_sno = subproductsclubbing.Productid WHERE (indents.Branch_id = @selectedbrnch) AND (indents_subtable.D_date BETWEEN @d1 AND @d2)");
cmd = new MySqlCommand("SELECT result.deliveryqty, result.ClubName, result.Clubsno, slabs.SlotQty, slabs.Amt FROM (SELECT ROUND(SUM(indents_subtable.DeliveryQty), 2) AS deliveryqty, subproductsclubbing.Clubsno, product_clubbing.ClubName FROM indents INNER JOIN indents_subtable ON indents.IndentNo = indents_subtable.IndentNo INNER JOIN subproductsclubbing ON indents_subtable.Product_sno = subproductsclubbing.Productid INNER JOIN product_clubbing ON subproductsclubbing.Clubsno = product_clubbing.sno WHERE (indents.Branch_id = @selectedbrnch) AND (indents.I_date BETWEEN @d1 AND @d2) GROUP BY subproductsclubbing.Clubsno) result INNER JOIN slabs ON result.Clubsno = slabs.club_sno");
cmd.Parameters.AddWithValue("@selectedbrnch", ddlAgentName.SelectedValue);
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate.AddDays(-1)));
cmd.Parameters.AddWithValue("@d2", GetHighDate(todate.AddDays(-1)));
DataTable dtclubtotal = vdm.SelectQuery(cmd).Tables[0];
foreach (DataRow drincetiveclub in dticentive.Rows)
{
float avgsale = 0;
float slotqty = 0;
float slotamt = 0;
float totalsale = 0;
string sltamt = "";
clubbingname = drincetiveclub["ClubName"].ToString();
// categoryserial = drincetiveclub["category_sno"].ToString();
foreach (DataRow drdtclubtotal in dtclubtotal.Select("Clubsno='" + drincetiveclub["clubbingsno"].ToString() + "'"))
{
float.TryParse(drdtclubtotal["deliveryqty"].ToString(), out totalsale);
avgsale = (totalsale / count);
float.TryParse(drdtclubtotal["SlotQty"].ToString(), out slotqty);
if (avgsale > slotqty)
{
float.TryParse(drdtclubtotal["Amt"].ToString(), out slotamt);
sltamt = drdtclubtotal["Amt"].ToString();
}
}
DataRow newrow = dtTotincentive.NewRow();
newrow["ClubbingName"] = clubbingname;
newrow["TotalSale"] = Math.Round(totalsale, 2);
newrow["AverageSale"] = Math.Round(avgsale, 2);
newrow["DiscountSlot"] = sltamt;
newrow["TotalAmount"] = Math.Round(totalsale * slotamt, 2);
if (categoryserial == "9")
{
milkincentive += (float)Math.Round(totalsale * slotamt, 2);
}
dtTotincentive.Rows.Add(newrow);
}
double totalmilksale = 0;
string leak = leakpercent;
leakpercentage = (float)Convert.ToDouble(leak);
Session["leak"] = leakpercentage;
double TotMilkandMilkAmt = 0;
Totalmilksale = Totalmilksale - Totalbulkmilksale;
totmilkamt = totmilkamt - Totalbulkmilksaleamount;
TotMilkandMilkAmt = totmilkamt / Totalmilksale;
double totleakincentive = 0;
//int.TryParse(txtleakage.Text, out leakpercentage);
if (leakpercentage != 0)
{
totalmilksale = leakpercentage / 100 * Totalmilksale;
totleakincentive = totalmilksale * TotMilkandMilkAmt;
DataRow newrow = dtTotincentive.NewRow();
newrow["ClubbingName"] = "LEAKAGE";
newrow["TotalSale"] = Math.Round(Totalmilksale, 2);
newrow["AverageSale"] = Math.Round(totalmilksale, 2);
newrow["DiscountSlot"] = Math.Round(TotMilkandMilkAmt, 2);
newrow["TotalAmount"] = Math.Round(totleakincentive, 2);
dtTotincentive.Rows.Add(newrow);
}
DataRow newrowtotal = dtTotincentive.NewRow();
newrowtotal["DiscountSlot"] = "TotalDiscount";
float incentive = 0;
float.TryParse(dtTotincentive.Compute("sum([TotalAmount])", "[TotalAmount]<>'0'").ToString(), out incentive);
newrowtotal["TotalAmount"] = Math.Round(incentive, 2);
dtTotincentive.Rows.Add(newrowtotal);
lblactualdiscount1.Text = Math.Round(incentive, 2).ToString();
lblincentivegiven.Text = incentivegiven;
txtremarks.Text = remarks;
DataRow headerrow = Report.NewRow();
headerrow[0] = "ClubbingName";
headerrow[1] = "TotalSale";
headerrow[2] = "AverageSale";
headerrow[3] = "DiscountSlot";
headerrow[4] = "TotalAmount";
Report.Rows.Add(headerrow);
//DataTable dtsum = new DataTable();
//dtsum = Report.Copy();
foreach (DataRow drr in dtTotincentive.Rows)
{
DataRow newrow = Report.NewRow();
newrow[0] = drr["ClubbingName"].ToString();
newrow[1] = drr["TotalSale"].ToString();
newrow[2] = drr["AverageSale"].ToString();
newrow[3] = drr["DiscountSlot"].ToString();
newrow[4] = drr["TotalAmount"].ToString();
Report.Rows.Add(newrow);
}
grdReports.DataSource = Report;
grdReports.DataBind();
lblmsg.Text = "";
}
}
catch
{
}
}
void GetReport()
{
try
{
lblmsg.Text = "";
pnlHide.Visible = true;
Report = new DataTable();
Session["RouteName"] = ddlDispName.SelectedItem.Text;
Session["IDate"] = DateTime.Now.AddDays(1).ToString("dd/MM/yyyy");
vdm = new VehicleDBMgr();
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[] datestrig1 = txttodate.Text.Split(' ');
if (datestrig1.Length > 1)
{
if (datestrig1[0].Split('-').Length > 0)
{
string[] dates = datestrig1[0].Split('-');
string[] times = datestrig1[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/MMM/yyyy");
lblRouteName.Text = ddlDispName.SelectedItem.Text;
// cmd = new MySqlCommand("SELECT inventory_monitor.Inv_Sno, inventory_monitor.BranchId, inventory_monitor.Qty, inventory_monitor.Sno, inventory_monitor.EmpId, inventory_monitor.lostQty FROM dispatch INNER JOIN dispatch_sub ON dispatch.sno = dispatch_sub.dispatch_sno INNER JOIN branchroutesubtable ON dispatch_sub.Route_id = branchroutesubtable.RefNo INNER JOIN inventory_monitor ON branchroutesubtable.BranchID = inventory_monitor.BranchId WHERE (dispatch.sno = @dispsno)");
cmd = new MySqlCommand("SELECT inventory_monitor.Inv_Sno, inventory_monitor.BranchId, inventory_monitor.Qty, inventory_monitor.Sno, inventory_monitor.EmpId, inventory_monitor.lostQty FROM dispatch INNER JOIN dispatch_sub ON dispatch.sno = dispatch_sub.dispatch_sno INNER JOIN modifiedroutesubtable ON dispatch_sub.Route_id = modifiedroutesubtable.RefNo INNER JOIN inventory_monitor ON modifiedroutesubtable.BranchID = inventory_monitor.BranchId WHERE (dispatch.sno = @dispsno) AND (modifiedroutesubtable.EDate IS NULL) AND (modifiedroutesubtable.CDate <= @starttime) AND (inventory_monitor.Qty>0) OR (dispatch.sno = @dispsno) AND (modifiedroutesubtable.EDate > @starttime) AND (modifiedroutesubtable.CDate <= @starttime) AND (inventory_monitor.Qty>0)");
cmd.Parameters.AddWithValue("@dispsno", ddlDispName.SelectedValue);
cmd.Parameters.AddWithValue("@starttime", GetLowDate(fromdate.AddDays(-1)));
DataTable dtinventoryopp = vdm.SelectQuery(cmd).Tables[0];
//cmd = new MySqlCommand("SELECT branchdata.BranchName, branchdata.sno, branchroutes.RouteName, branchroutes.Sno AS routesno FROM branchdata INNER JOIN branchroutesubtable ON branchdata.sno = branchroutesubtable.BranchID INNER JOIN branchroutes ON branchroutesubtable.RefNo = branchroutes.Sno INNER JOIN dispatch_sub ON branchroutes.Sno = dispatch_sub.Route_id INNER JOIN dispatch ON dispatch_sub.dispatch_sno = dispatch.sno WHERE (dispatch.sno = @routeid) and branchdata.flag='1'");
cmd = new MySqlCommand("SELECT branchdata.BranchName, branchdata.sno, modifiedroutes.RouteName, modifiedroutes.Sno AS routesno 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 branchdata ON modifiedroutesubtable.BranchID = branchdata.sno WHERE (dispatch.sno = @routeid) AND (branchdata.flag = '1') AND (modifiedroutesubtable.EDate IS NULL) AND (modifiedroutesubtable.CDate <= @starttime) OR (dispatch.sno = @routeid) AND (branchdata.flag = '1') AND (modifiedroutesubtable.EDate > @starttime) AND (modifiedroutesubtable.CDate <= @starttime)");
cmd.Parameters.AddWithValue("@routeid", ddlDispName.SelectedValue);
cmd.Parameters.AddWithValue("@starttime", GetLowDate(fromdate.AddDays(-1)));
DataTable dtbranch = vdm.SelectQuery(cmd).Tables[0];
cmd = new MySqlCommand(" SELECT InvName FROM invmaster");
DataTable InvTable = vdm.SelectQuery(cmd).Tables[0];
Report = new DataTable();
Report.Columns.Add("Sno");
Report.Columns.Add("Branch Code");
Report.Columns.Add("Agent Name");
Report.Columns.Add("Opp Crates", typeof(Double));
Report.Columns.Add("Issued Crates", typeof(Double));
Report.Columns.Add("Received Crates", typeof(Double));
Report.Columns.Add("Difference Crates", typeof(Double));
Report.Columns.Add("CB Crates", typeof(Double));
Report.Columns.Add("Opp Can40ltr", typeof(Double));
Report.Columns.Add("Issued Can40ltr", typeof(Double));
Report.Columns.Add("Received Can40ltr", typeof(Double));
Report.Columns.Add("Difference Can40ltr", typeof(Double));
Report.Columns.Add("CB Can40ltr", typeof(Double));
Report.Columns.Add("Opp Can20ltr", typeof(Double));
Report.Columns.Add("Issued Can20ltr", typeof(Double));
Report.Columns.Add("Received Can20ltr", typeof(Double));
Report.Columns.Add("Difference Can20ltr", typeof(Double));
Report.Columns.Add("CB Can20ltr", typeof(Double));
Report.Columns.Add("Opp Can10ltr", typeof(Double));
Report.Columns.Add("Issued Can10ltr", typeof(Double));
Report.Columns.Add("Received Can10ltr", typeof(Double));
Report.Columns.Add("Difference Can10ltr", typeof(Double));
Report.Columns.Add("CB Can10ltr", typeof(Double));
DateTime ServerDateCurrentdate = VehicleDBMgr.GetTime(vdm.conn);
int i = 1;
// cmd = new MySqlCommand("SELECT invtras.TransType, invtras.FromTran, invtras.ToTran, invtras.Qty, invtras.DOE, invmaster.sno AS invsno, invmaster.InvName FROM (SELECT TransType, FromTran, ToTran, Qty, EmpID, VarifyStatus, VTripId, VEmpId, Sno, B_inv_sno, DOE, VQty FROM invtransactions12 WHERE (ToTran = @branchid) AND (DOE BETWEEN @d1 AND @d2) OR (DOE BETWEEN @d1 AND @d2) AND (FromTran = @branchid)) invtras INNER JOIN invmaster ON invtras.B_inv_sno = invmaster.sno ORDER BY invtras.DOE");
cmd = new MySqlCommand("SELECT invtras.TransType, invtras.FromTran, invtras.ToTran, invtras.Qty, invtras.DOE, invmaster.sno AS invsno, invmaster.InvName FROM (SELECT TransType, FromTran, ToTran, Qty, EmpID, VarifyStatus, VTripId, VEmpId, Sno, B_inv_sno, DOE, VQty FROM invtransactions12 WHERE (DOE BETWEEN @d1 AND @d2) OR (DOE BETWEEN @d1 AND @d2)) invtras INNER JOIN invmaster ON invtras.B_inv_sno = invmaster.sno ORDER BY invtras.DOE");
// cmd.Parameters.AddWithValue("@branchid", drroutebrnch["sno"].ToString());
DateTime dt1 = GetLowDate(fromdate.AddDays(-1));
DateTime dt2 = GetLowDate(Todate);
cmd.Parameters.AddWithValue("@d1", dt1.AddHours(15));
cmd.Parameters.AddWithValue("@d2", dt2.AddHours(15));
DataTable dtinventoryDC = vdm.SelectQuery(cmd).Tables[0];
// cmd = new MySqlCommand("SELECT invtransactions12.TransType, invtransactions12.FromTran, invtransactions12.ToTran, invtransactions12.Qty, invtransactions12.DOE, invmaster.sno AS invsno,invmaster.InvName FROM invtransactions12 INNER JOIN invmaster ON invtransactions12.B_inv_sno = invmaster.sno WHERE (invtransactions12.ToTran = @branchid) AND (invtransactions12.DOE BETWEEN @d1 AND @d2) OR (invtransactions12.DOE BETWEEN @d1 AND @d2) AND (invtransactions12.FromTran = @branchid) ORDER BY invtransactions12.DOE");
//cmd = new MySqlCommand("SELECT invtran.TransType, invtran.FromTran, invtran.ToTran, invtran.Qty, invtran.DOE, invmaster.sno AS invsno, invmaster.InvName FROM (SELECT TransType, FromTran, ToTran, Qty, EmpID, VarifyStatus, VTripId, VEmpId, Sno, B_inv_sno, DOE, VQty FROM invtransactions12 WHERE (ToTran = @branchid) AND (DOE BETWEEN @d1 AND @d2) OR (DOE BETWEEN @d1 AND @d2) AND (FromTran = @branchid)) invtran INNER JOIN invmaster ON invtran.B_inv_sno = invmaster.sno ORDER BY invtran.DOE");
cmd = new MySqlCommand("SELECT invtran.TransType, invtran.FromTran, invtran.ToTran, invtran.Qty, invtran.DOE, invmaster.sno AS invsno, invmaster.InvName FROM (SELECT TransType, FromTran, ToTran, Qty, EmpID, VarifyStatus, VTripId, VEmpId, Sno, B_inv_sno, DOE, VQty FROM invtransactions12 WHERE (DOE BETWEEN @d1 AND @d2) OR (DOE BETWEEN @d1 AND @d2) ) invtran INNER JOIN invmaster ON invtran.B_inv_sno = invmaster.sno ORDER BY invtran.DOE");
// cmd.Parameters.AddWithValue("@branchid", drroutebrnch["sno"].ToString());
DateTime dtmin = GetLowDate(fromdate.AddDays(-1));
DateTime dtmax = GetLowDate(ServerDateCurrentdate);
cmd.Parameters.AddWithValue("@d1", dtmin.AddHours(15));
cmd.Parameters.AddWithValue("@d2", dtmax.AddHours(15));
DataTable dtprevinventoryDC = vdm.SelectQuery(cmd).Tables[0];
foreach (DataRow drroutebrnch in dtbranch.Rows)
{
DataRow drnew = Report.NewRow();
drnew["Sno"] = i;
int oppcrates = 0;
int oppcan10ltr = 0;
int oppcan20ltr = 0;
int oppcan40ltr = 0;
//cmd = new MySqlCommand("SELECT BranchId, Inv_Sno, Qty, Sno, EmpId, lostQty FROM inventory_monitor WHERE (BranchId = @branchid)");
//cmd.Parameters.AddWithValue("@branchid", drbranch["sno"].ToString());
//DataTable dtinventoryopp = vdm.SelectQuery(cmd).Tables[0];
foreach (DataRow dropp in dtinventoryopp.Select("BranchId='" + drroutebrnch["sno"].ToString() + "'"))
{
if (dropp["Inv_Sno"].ToString() == "1")
{
int.TryParse(dropp["Qty"].ToString(), out oppcrates);
}
if (dropp["Inv_Sno"].ToString() == "2")
{
int.TryParse(dropp["Qty"].ToString(), out oppcan10ltr);
}
if (dropp["Inv_Sno"].ToString() == "3")
{
int.TryParse(dropp["Qty"].ToString(), out oppcan20ltr);
}
if (dropp["Inv_Sno"].ToString() == "4")
{
int.TryParse(dropp["Qty"].ToString(), out oppcan40ltr);
}
}
drnew["Branch Code"] = drroutebrnch["sno"].ToString();
drnew["Agent Name"] = drroutebrnch["BranchName"].ToString();
int Ctotcan40ltr = 0;
int Ctotcrates = 0;
int Ctotcan20ltr = 0;
int Dtotcrates = 0;
int Dtotcan20ltr = 0;
int Dtotcan40ltr = 0;
int prevCtotcan40ltr = 0;
int prevCtotcrates = 0;
int prevCtotcan20ltr = 0;
int prevDtotcrates = 0;
int prevDtotcan20ltr = 0;
int prevDtotcan40ltr = 0;
int prevDtotcan10ltr = 0;
int prevCtotcan10ltr = 0;
int Dtotcan10ltr = 0;
int Ctotcan10ltr = 0;
foreach (DataRow drprev in dtprevinventoryDC.Select("ToTran='" + drroutebrnch["sno"].ToString() + "' OR FromTran='" + drroutebrnch["sno"].ToString() + "'"))
{
if (drprev["TransType"].ToString() == "2")
{
if (drprev["invsno"].ToString() == "1")
{
int prevDcrates = 0;
int.TryParse(drprev["Qty"].ToString(), out prevDcrates);
prevDtotcrates += prevDcrates;
}
if (drprev["invsno"].ToString() == "2")
{
int prevDcan10ltr = 0;
int.TryParse(drprev["Qty"].ToString(), out prevDcan10ltr);
prevDtotcan10ltr += prevDcan10ltr;
}
if (drprev["invsno"].ToString() == "3")
{
int prevDcan20ltr = 0;
int.TryParse(drprev["Qty"].ToString(), out prevDcan20ltr);
prevDtotcan20ltr += prevDcan20ltr;
}
if (drprev["invsno"].ToString() == "4")
{
int prevDcan40ltr = 0;
int.TryParse(drprev["Qty"].ToString(), out prevDcan40ltr);
prevDtotcan40ltr += prevDcan40ltr;
}
}
if (drprev["TransType"].ToString() == "1" || drprev["TransType"].ToString() == "3")
{
if (drprev["invsno"].ToString() == "1")
{
int prevCcrates = 0;
int.TryParse(drprev["Qty"].ToString(), out prevCcrates);
prevCtotcrates += prevCcrates;
}
if (drprev["invsno"].ToString() == "2")
{
int prevCcan10ltr = 0;
int.TryParse(drprev["Qty"].ToString(), out prevCcan10ltr);
prevCtotcan10ltr += prevCcan10ltr;
}
if (drprev["invsno"].ToString() == "3")
{
int prevCcan20ltr = 0;
int.TryParse(drprev["Qty"].ToString(), out prevCcan20ltr);
prevCtotcan20ltr += prevCcan20ltr;
}
if (drprev["invsno"].ToString() == "4")
{
int prevCcan40ltr = 0;
int.TryParse(drprev["Qty"].ToString(), out prevCcan40ltr);
prevCtotcan40ltr += prevCcan40ltr;
}
}
}
foreach (DataRow dr in dtinventoryDC.Select("ToTran='" + drroutebrnch["sno"].ToString() + "' OR FromTran='" + drroutebrnch["sno"].ToString() + "'"))
{
if (dr["TransType"].ToString() == "2")
{
if (dr["invsno"].ToString() == "1")
{
int Dcrates = 0;
int.TryParse(dr["Qty"].ToString(), out Dcrates);
Dtotcrates += Dcrates;
}
if (dr["invsno"].ToString() == "2")
{
int Dcan10ltr = 0;
int.TryParse(dr["Qty"].ToString(), out Dcan10ltr);
Dtotcan10ltr += Dcan10ltr;
}
if (dr["invsno"].ToString() == "3")
{
int Dcan20ltr = 0;
int.TryParse(dr["Qty"].ToString(), out Dcan20ltr);
Dtotcan20ltr += Dcan20ltr;
}
if (dr["invsno"].ToString() == "4")
{
int Dcan40ltr = 0;
int.TryParse(dr["Qty"].ToString(), out Dcan40ltr);
Dtotcan40ltr += Dcan40ltr;
}
}
if (dr["TransType"].ToString() == "1" || dr["TransType"].ToString() == "3")
{
if (dr["invsno"].ToString() == "1")
{
int Ccrates = 0;
int.TryParse(dr["Qty"].ToString(), out Ccrates);
Ctotcrates += Ccrates;
}
if (dr["invsno"].ToString() == "2")
{
int Ccan10ltr = 0;
int.TryParse(dr["Qty"].ToString(), out Ccan10ltr);
Ctotcan10ltr += Ccan10ltr;
}
if (dr["invsno"].ToString() == "3")
{
int Ccan20ltr = 0;
int.TryParse(dr["Qty"].ToString(), out Ccan20ltr);
Ctotcan20ltr += Ccan20ltr;
}
if (dr["invsno"].ToString() == "4")
{
int Ccan40ltr = 0;
int.TryParse(dr["Qty"].ToString(), out Ccan40ltr);
Ctotcan40ltr += Ccan40ltr;
}
}
}
oppcrates = oppcrates + prevCtotcrates - prevDtotcrates;
oppcan10ltr = oppcan10ltr + prevCtotcan10ltr - prevDtotcan10ltr;
oppcan20ltr = oppcan20ltr + prevCtotcan20ltr - prevDtotcan20ltr;
oppcan40ltr = oppcan40ltr + prevCtotcan40ltr - prevDtotcan40ltr;
int CratesClo = oppcrates + Dtotcrates - Ctotcrates;
int Can10ltrClo = oppcan10ltr + Dtotcan10ltr - Ctotcan10ltr;
int Can20ltrClo = oppcan20ltr + Dtotcan20ltr - Ctotcan20ltr;
int Can40ltrClo = oppcan40ltr + Dtotcan40ltr - Ctotcan40ltr;
drnew["Opp Crates"] = oppcrates;
drnew["Issued Crates"] = Dtotcrates;
drnew["Received Crates"] = Ctotcrates;
//drnew["Difference Crates"] = Dtotcrates - Ctotcrates;
drnew["Difference Crates"] = Ctotcrates - Dtotcrates;
drnew["CB Crates"] = CratesClo;
drnew["Opp Can40ltr"] = oppcan40ltr;
drnew["Issued Can40ltr"] = Dtotcan40ltr;
drnew["Received Can40ltr"] = Ctotcan40ltr;
//drnew["Difference Can40ltr"] = Dtotcan40ltr - Ctotcan40ltr;
drnew["Difference Can40ltr"] = Ctotcan40ltr - Dtotcan40ltr;
drnew["CB Can40ltr"] = Can40ltrClo;
drnew["Opp Can20ltr"] = oppcan20ltr;
drnew["Issued Can20ltr"] = Dtotcan20ltr;
drnew["Received Can20ltr"] = Ctotcan20ltr;
//drnew["Difference Can20ltr"] = Dtotcan20ltr - Can20ltrClo;
drnew["Difference Can20ltr"] = Ctotcan20ltr - Dtotcan20ltr;
drnew["CB Can20ltr"] = Can20ltrClo;
drnew["Opp Can10ltr"] = oppcan10ltr;
drnew["Issued Can10ltr"] = Dtotcan10ltr;
drnew["Received Can10ltr"] = Ctotcan10ltr;
//drnew["Difference Can20ltr"] = Dtotcan20ltr - Can20ltrClo;
drnew["Difference Can10ltr"] = Ctotcan10ltr - Dtotcan10ltr;
drnew["CB Can10ltr"] = Can10ltrClo;
Report.Rows.Add(drnew);
i++;
}
DataRow Break = Report.NewRow();
Break["Agent Name"] = "";
Report.Rows.Add(Break);
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();
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
grdReports.DataSource = Report;
grdReports.DataBind();
}
}
//protected void btnGenerate_Click(object sender, EventArgs e)
//{
//// GetReport();
////}
////void GetReport()
////{
// try
// {
// Button2.Visible = true;
// vdm = new VehicleDBMgr();
// DataTable Report = new DataTable();
// string branchname = Session["branchname"].ToString();
// Session["filename"] = branchname + " RateSheet " + DateTime.Now.ToString("dd/MM/yyyy");
// if (ddlSalesOffice.SelectedValue == "ALL")
// {
// //cmd = new MySqlCommand("SELECT branchdata.sno, branchdata.BranchName, branchproducts.product_sno, branchproducts.unitprice, productsdata.ProductName FROM branchroutes INNER JOIN branchroutesubtable ON branchroutes.Sno = branchroutesubtable.RefNo INNER JOIN branchdata ON branchroutesubtable.BranchID = branchdata.sno INNER JOIN branchproducts ON branchdata.sno = branchproducts.branch_sno INNER JOIN productsdata ON branchproducts.product_sno = productsdata.sno WHERE (branchroutes.Sno = 73) ORDER BY branchproducts.Rank");
// 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) and (branchdata.flag='1') and branchproducts.unitprice<>'0') OR ((branchdata_1.SalesOfficeID = @SOID) and (branchdata_1.flag='1') and branchproducts.unitprice<>'0') ORDER BY branchdata.sno");
// 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) and (branchdata.flag='1')) OR ((branchdata_1.SalesOfficeID = @SOID) and (branchdata_1.flag='1')) ORDER BY branchproducts.Rank");
// cmd = new MySqlCommand("SELECT productsdata.ProductName, branchproducts.unitprice, branchproducts.branch_sno AS sno, branchdata.BranchName, branchproducts.product_sno FROM productsdata INNER JOIN branchproducts ON productsdata.sno = branchproducts.product_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) AND (branchdata.flag = '1') OR (branchdata_1.SalesOfficeID = @SOID) AND (branchdata_1.flag = '1') ORDER BY branchproducts.Rank");
// cmd.Parameters.AddWithValue("@Flag", "1");
// cmd.Parameters.AddWithValue("@SOID", BranchID);
// cmd.Parameters.AddWithValue("@BranchID", BranchID);
// dtBranch = vdm.SelectQuery(cmd).Tables[0];
// if (dtAgents.Rows.Count > 0)
// {
// foreach (DataRow dr in dtAgents.Rows)
// {
// DataRow drnew = dtBranch.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();
// dtBranch.Rows.Add(drnew);
// }
// }
// cmd = new MySqlCommand("SELECT branchproducts.branch_sno,products_subcategory.tempsub_catsno AS SubCatSno, products_category.description AS Categoryname, branchproducts.product_sno, productsdata.ProductName, branchproducts.Rank,products_subcategory.description AS SubCategoryName FROM products_category INNER JOIN products_subcategory ON products_category.tempcatsno = products_subcategory.tempcatsno INNER JOIN productsdata ON products_subcategory.tempsub_catsno = productsdata.tempsubcatsno INNER JOIN branchproducts ON productsdata.sno = branchproducts.product_sno WHERE (branchproducts.branch_sno = @BranchId) ORDER BY products_subcategory.tempsub_catsno, branchproducts.Rank");
// //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);
// produtstbl = vdm.SelectQuery(cmd).Tables[0];
// if (produtstbl.Rows.Count > 0)
// {
// DataView SubCatview = new DataView(produtstbl);
// dtSubCatgory = SubCatview.ToTable(true, "SubCategoryName", "SubCatSno");
// DataView view = new DataView(dtBranch);
// DataTable distincttable = view.ToTable(true, "BranchName", "sno");
// DataView dv = distincttable.DefaultView;
// dv.Sort = "sno ASC";
// DataTable sortedProductDT = dv.ToTable();
// 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 sortedProductDT.Rows)
// {
// DataRow newrow = Report.NewRow();
// newrow["SNo"] = i;
// newrow["Agent Code"] = branch["sno"].ToString();
// newrow["Agent Name"] = branch["BranchName"].ToString();
// foreach (DataRow dr in dtBranch.Rows)
// {
// try
// {
// if (branch["BranchName"].ToString() == dr["BranchName"].ToString())
// {
// id = dr["BranchName"].ToString();
// id += branch["sno"].ToString();
// double unitprice = 0;
// double.TryParse(dr["unitprice"].ToString(), out unitprice);
// newrow[dr["ProductName"].ToString()] = unitprice;
// }
// }
// catch
// {
// }
// }
// Report.Rows.Add(newrow);
// i++;
// }
// }
// for (int i = Report.Rows.Count - 1; i >= 0; i--)
// {
// if (Report.Rows[i][1] == DBNull.Value)
// Report.Rows[i].Delete();
// }
// grdReports.DataSource = Report;
// grdReports.DataBind();
// Session["xportdata"] = Report;
// }
// else
// {
// //cmd = new MySqlCommand("SELECT branchdata.sno, branchdata.BranchName, branchproducts.product_sno, branchproducts.unitprice, productsdata.ProductName FROM branchroutes INNER JOIN branchroutesubtable ON branchroutes.Sno = branchroutesubtable.RefNo INNER JOIN branchdata ON branchroutesubtable.BranchID = branchdata.sno INNER JOIN branchproducts ON branchdata.sno = branchproducts.branch_sno INNER JOIN productsdata ON branchproducts.product_sno = productsdata.sno WHERE (branchroutes.Sno = 73) ORDER BY branchproducts.Rank");
// 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) and (branchdata.flag='1') and branchproducts.unitprice<>'0') OR ((branchdata_1.SalesOfficeID = @SOID) and (branchdata_1.flag='1') and branchproducts.unitprice<>'0') ORDER BY branchdata.sno");
// cmd.Parameters.AddWithValue("@Flag", "1");
// cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
// cmd.Parameters.AddWithValue("@SOID", ddlSalesOffice.SelectedValue);
// 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) and (branchdata.flag='1')) OR ((branchdata_1.SalesOfficeID = @SOID) and (branchdata_1.flag='1')) ORDER BY branchproducts.Rank");
// cmd = new MySqlCommand("SELECT productsdata.ProductName, branchproducts.unitprice, branchproducts.branch_sno AS sno, branchdata.BranchName, branchproducts.product_sno FROM productsdata INNER JOIN branchproducts ON productsdata.sno = branchproducts.product_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) AND (branchdata.flag = '1') OR (branchdata_1.SalesOfficeID = @SOID) AND (branchdata_1.flag = '1') ORDER BY branchproducts.Rank");
// cmd.Parameters.AddWithValue("@Flag", "1");
// cmd.Parameters.AddWithValue("@SOID", ddlSalesOffice.SelectedValue);
// cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
// 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 branchproducts.branch_sno,products_subcategory.tempsub_catsno AS SubCatSno, products_category.description AS Categoryname, branchproducts.product_sno, productsdata.ProductName, branchproducts.Rank,products_subcategory.description AS SubCategoryName FROM products_category INNER JOIN products_subcategory ON products_category.tempcatsno = products_subcategory.tempcatsno INNER JOIN productsdata ON products_subcategory.tempsub_catsno = productsdata.tempsubcatsno INNER JOIN branchproducts ON productsdata.sno = branchproducts.product_sno WHERE (branchproducts.branch_sno = @BranchId) ORDER BY products_subcategory.tempsub_catsno, branchproducts.Rank");
// //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", ddlSalesOffice.SelectedValue);
// produtstbl = vdm.SelectQuery(cmd).Tables[0];
// if (produtstbl.Rows.Count > 0)
// {
// DataView SubCatview = new DataView(produtstbl);
// dtSubCatgory = SubCatview.ToTable(true, "SubCategoryName", "SubCatSno");
// DataView view = new DataView(dtBranch);
// 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 dtBranch.Rows)
// {
// try
// {
// if (branch["BranchName"].ToString() == dr["BranchName"].ToString())
// {
// id = dr["BranchName"].ToString();
// id += branch["sno"].ToString();
// double unitprice = 0;
// double.TryParse(dr["unitprice"].ToString(), out unitprice);
// newrow[dr["ProductName"].ToString()] = unitprice;
// }
// }
// catch
// {
// }
// }
// Report.Rows.Add(newrow);
// i++;
// }
// }
// for (int i = Report.Rows.Count - 1; i >= 0; i--)
// {
// if (Report.Rows[i][1] == DBNull.Value)
// Report.Rows[i].Delete();
// }
// grdReports.DataSource = Report;
// grdReports.DataBind();
// Session["xportdata"] = Report;
// }
// }
// catch (Exception ex)
// {
// string msg = ex.Message;
// msg += id;
// //lblmsg.Text = msg;
// }
//}
protected void btnGenerate_Click(object sender, EventArgs e)
{
// GetReport();
//}
//void GetReport()
//{
try
{
Button2.Visible = true;
vdm = new VehicleDBMgr();
DateTime ServerDateCurrentdate = VehicleDBMgr.GetTime(vdm.conn);
DataTable Report = new DataTable();
string branchname = Session["branchname"].ToString();
Session["filename"] = branchname + " RateSheet " + DateTime.Now.ToString("dd/MM/yyyy");
if (ddlSalesOffice.SelectedValue != "ALL")
{
//cmd = new MySqlCommand("SELECT branchdata.sno, branchdata.BranchName, branchproducts.product_sno, branchproducts.unitprice, productsdata.ProductName FROM branchroutes INNER JOIN branchroutesubtable ON branchroutes.Sno = branchroutesubtable.RefNo INNER JOIN branchdata ON branchroutesubtable.BranchID = branchdata.sno INNER JOIN branchproducts ON branchdata.sno = branchproducts.branch_sno INNER JOIN productsdata ON branchproducts.product_sno = productsdata.sno WHERE (branchroutes.Sno = 73) ORDER BY branchproducts.Rank");
cmd = new MySqlCommand("SELECT branchdata.BranchName, branchproducts.product_sno,branchproducts.Rank, productsdata.ProductName,productsdata.tempsubcatsno, 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) and (branchdata.flag='1') and branchproducts.unitprice<>'0') OR ((branchdata_1.SalesOfficeID = @SOID) and (branchdata_1.flag='1') and branchproducts.unitprice<>'0') group by branchproducts.product_sno,branchdata.sno ORDER BY productsdata.tempsubcatsno");
cmd.Parameters.AddWithValue("@Flag", "1");
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@SOID", ddlSalesOffice.SelectedValue);
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) and (branchdata.flag='1')) OR ((branchdata_1.SalesOfficeID = @SOID) and (branchdata_1.flag='1')) ORDER BY branchproducts.Rank");
cmd = new MySqlCommand("SELECT productsdata.ProductName,productsdata.tempsubcatsno,branchproducts.Rank, branchproducts.unitprice, branchdata.BranchName, branchproducts.branch_sno AS sno,branchproducts.product_sno FROM productsdata INNER JOIN branchproducts ON productsdata.sno = branchproducts.product_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) AND (branchdata.flag = '1') OR (branchdata_1.SalesOfficeID = @SOID) AND (branchdata_1.flag = '1') GROUP BY sno,branchproducts.product_sno ORDER BY productsdata.tempsubcatsno,branchproducts.Rank");
cmd.Parameters.AddWithValue("@Flag", "1");
cmd.Parameters.AddWithValue("@SOID", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
dtBranch = vdm.SelectQuery(cmd).Tables[0];
//if (dtAgents.Rows.Count > 0)
//{
// foreach (DataRow dr in dtAgents.Rows)
// {
// DataRow drnew = dtBranch.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();
// //drnew["Rank"] = dr["Rank"].ToString();
// dtBranch.Rows.Add(drnew);
// }
//}
//cmd = new MySqlCommand("SELECT modifiedroutes.Sno, modifiedroutes.RouteName, branchdata.sno AS Branchid, branchdata.BranchName FROM modifiedroutes INNER JOIN modifiedroutesubtable ON modifiedroutes.Sno = modifiedroutesubtable.RefNo INNER JOIN branchdata ON modifiedroutesubtable.BranchID = branchdata.sno WHERE (branchdata.flag = @flag) AND (modifiedroutes.BranchID = @BranchID) AND (modifiedroutesubtable.EDate IS NULL) AND (modifiedroutesubtable.CDate <= @starttime) OR (branchdata.flag = @flag) AND (modifiedroutes.BranchID = @BranchID) AND (modifiedroutesubtable.EDate > @starttime) AND (modifiedroutesubtable.CDate <= @starttime) GROUP BY branchdata.sno, branchdata.BranchName");
//cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
//cmd.Parameters.AddWithValue("@starttime", GetLowDate(ServerDateCurrentdate.AddDays(-1)));
//cmd.Parameters.AddWithValue("@flag", "1");
//produtstbl = vdm.SelectQuery(cmd).Tables[0];
cmd = new MySqlCommand("SELECT branchproducts.branch_sno,products_subcategory.tempsub_catsno AS SubCatSno, products_category.description AS Categoryname, branchproducts.product_sno, productsdata.ProductName, branchproducts.Rank,products_subcategory.description AS SubCategoryName FROM products_category INNER JOIN products_subcategory ON products_category.tempcatsno = products_subcategory.tempcatsno INNER JOIN productsdata ON products_subcategory.tempsub_catsno = productsdata.tempsubcatsno INNER JOIN branchproducts ON productsdata.sno = branchproducts.product_sno WHERE (branchproducts.branch_sno = @BranchId) ORDER BY products_subcategory.tempsub_catsno, branchproducts.Rank");
//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", ddlSalesOffice.SelectedValue);
produtstbl1 = vdm.SelectQuery(cmd).Tables[0];
if (produtstbl1.Rows.Count > 0)
{
DataView SubCatview = new DataView(produtstbl1);
dtSubCatgory = SubCatview.ToTable(true, "SubCategoryName", "SubCatSno");
DataTable tempproducttable = new DataTable();
tempproducttable.Columns.Add("BranchName");
tempproducttable.Columns.Add("product_sno");
tempproducttable.Columns.Add("ProductName");
tempproducttable.Columns.Add("unitprice");
tempproducttable.Columns.Add("sno");
foreach (DataRow drsubcategory in dtSubCatgory.Rows)
{
foreach (DataRow drr in dtBranch.Select("tempsubcatsno='" + drsubcategory["SubCatSno"].ToString() + "'"))
{
DataRow drnew = tempproducttable.NewRow();
drnew["BranchName"] = drr["BranchName"].ToString();
drnew["product_sno"] = drr["product_sno"].ToString();
drnew["ProductName"] = drr["ProductName"].ToString();
drnew["unitprice"] = drr["unitprice"].ToString();
drnew["sno"] = drr["sno"].ToString();
//drnew["Rank"] = dr["Rank"].ToString();
tempproducttable.Rows.Add(drnew);
}
}
DataView view = new DataView(dtBranch);
DataTable distincttable = view.ToTable(true, "ProductName", "product_sno");
DataTable distincttable1 = view.ToTable(true, "BranchName", "sno");
DataView dv = distincttable.DefaultView;
// dv.Sort = "Rank ASC";
DataTable sortedProductDT = dv.ToTable();
Report = new DataTable();
Report.Columns.Add("SubCategory Name");
Report.Columns.Add("SNo");
Report.Columns.Add("Product Name");
foreach (DataRow dr in distincttable1.Rows)
{
Report.Columns.Add(dr["BranchName"].ToString()).DataType = typeof(Double);
}
int i = 1;
foreach (DataRow branch in sortedProductDT.Rows)
{
DataRow newrow = Report.NewRow();
newrow["SNo"] = i;
foreach (DataRow drtripinv in produtstbl1.Select("product_sno='" + branch["product_sno"].ToString() + "'"))
{
newrow["SubCategory Name"] = drtripinv["SubCategoryName"].ToString();
}
newrow["Product Name"] = branch["ProductName"].ToString();
foreach (DataRow dr in tempproducttable.Rows)
{
try
{
if (branch["ProductName"].ToString() == dr["ProductName"].ToString())
{
id = dr["ProductName"].ToString();
id += branch["product_sno"].ToString();
double unitprice = 0;
double.TryParse(dr["unitprice"].ToString(), out unitprice);
newrow[dr["BranchName"].ToString()] = unitprice;
}
}
catch
{
}
}
Report.Rows.Add(newrow);
i++;
}
}
for (int i = Report.Rows.Count - 1; i >= 0; i--)
{
if (Report.Rows[i][1] == DBNull.Value)
{
Report.Rows[i].Delete();
}
}
grdReports.DataSource = Report;
grdReports.DataBind();
Session["xportdata"] = Report;
}
else
{
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) and (branchdata.flag='1') and branchproducts.unitprice<>'0') OR ((branchdata_1.SalesOfficeID = @SOID) and (branchdata_1.flag='1') and branchproducts.unitprice<>'0') ORDER BY branchdata.sno");
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) and (branchdata.flag='1')) OR ((branchdata_1.SalesOfficeID = @SOID) and (branchdata_1.flag='1')) ORDER BY branchproducts.Rank");
cmd = new MySqlCommand("SELECT productsdata.ProductName, branchproducts.unitprice, branchproducts.branch_sno AS sno, branchdata.BranchName, branchproducts.product_sno FROM productsdata INNER JOIN branchproducts ON productsdata.sno = branchproducts.product_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) AND (branchdata.flag = '1') OR (branchdata_1.SalesOfficeID = @SOID) AND (branchdata_1.flag = '1') ORDER BY branchproducts.Rank");
cmd.Parameters.AddWithValue("@Flag", "1");
cmd.Parameters.AddWithValue("@SOID", BranchID);
cmd.Parameters.AddWithValue("@BranchID", BranchID);
dtBranch = vdm.SelectQuery(cmd).Tables[0];
if (dtAgents.Rows.Count > 0)
{
foreach (DataRow dr in dtAgents.Rows)
{
DataRow drnew = dtBranch.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();
dtBranch.Rows.Add(drnew);
}
}
cmd = new MySqlCommand("SELECT branchproducts.branch_sno,products_subcategory.tempsub_catsno AS SubCatSno, products_category.description AS Categoryname, branchproducts.product_sno, productsdata.ProductName, branchproducts.Rank,products_subcategory.description AS SubCategoryName FROM products_category INNER JOIN products_subcategory ON products_category.tempcatsno = products_subcategory.tempcatsno INNER JOIN productsdata ON products_subcategory.tempsub_catsno = productsdata.tempsubcatsno INNER JOIN branchproducts ON productsdata.sno = branchproducts.product_sno WHERE (branchproducts.branch_sno = @BranchId) ORDER BY products_subcategory.tempsub_catsno, branchproducts.Rank");
//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);
produtstbl = vdm.SelectQuery(cmd).Tables[0];
if (produtstbl.Rows.Count > 0)
{
DataView SubCatview = new DataView(produtstbl);
dtSubCatgory = SubCatview.ToTable(true, "SubCategoryName", "SubCatSno");
DataView view = new DataView(dtBranch);
DataTable distincttable = view.ToTable(true, "BranchName", "sno");
DataView dv = distincttable.DefaultView;
dv.Sort = "sno ASC";
DataTable sortedProductDT = dv.ToTable();
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 sortedProductDT.Rows)
{
DataRow newrow = Report.NewRow();
newrow["SNo"] = i;
newrow["Agent Code"] = branch["sno"].ToString();
newrow["Agent Name"] = branch["BranchName"].ToString();
foreach (DataRow dr in dtBranch.Rows)
{
try
{
if (branch["BranchName"].ToString() == dr["BranchName"].ToString())
{
id = dr["BranchName"].ToString();
id += branch["sno"].ToString();
double unitprice = 0;
double.TryParse(dr["unitprice"].ToString(), out unitprice);
newrow[dr["ProductName"].ToString()] = unitprice;
}
}
catch
{
}
}
Report.Rows.Add(newrow);
i++;
}
}
for (int i = Report.Rows.Count - 1; i >= 0; i--)
{
if (Report.Rows[i][1] == DBNull.Value)
{
Report.Rows[i].Delete();
}
}
grdReports.DataSource = Report;
grdReports.DataBind();
Session["xportdata"] = Report;
}
}
catch (Exception ex)
{
string msg = ex.Message;
msg += id;
//lblmsg.Text = msg;
}
}
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);
}
}
DateTime ServerDateCurrentdate = VehicleDBMgr.GetTime(vdm.conn);
fromdate = fromdate.AddDays(1);
if (dt.Rows.Count > 0)
{
DateTime doe = DateTime.Now;
int i = 1;
foreach (DataRow dr in dt.Rows)
{
string ocrcode2 = "";
sqlcmd = new SqlCommand("SELECT PostingDate,DocDate FROM EMROIGN WHERE DocDate BETWEEN @d1 and @d2 AND ReferenceNo=@rno AND ItemCode=@ItemCode AND ItemName=@ItemName and WhsCode=@WhsCode");
sqlcmd.Parameters.Add("@d1", GetLowDate(fromdate));
sqlcmd.Parameters.Add("@d2", GetHighDate(fromdate));
sqlcmd.Parameters.Add("@rno", dr["RefNo"].ToString());
sqlcmd.Parameters.Add("@ItemCode", dr["ItemCode"].ToString());
sqlcmd.Parameters.Add("@ItemName", dr["ItemName"].ToString());
sqlcmd.Parameters.Add("@WhsCode", dr["WhsCode"].ToString());
DataTable dtSAPproduction = SAPvdm.SelectQuery(sqlcmd).Tables[0];
if (dtSAPproduction.Rows.Count > 0)
{
lblmsg.Text = "This date data already Saved";
}
else
{
sqlcmd = new SqlCommand("Insert into EMROIGN (CreateDate,PostingDate,DocDate,ReferenceNo,ItemCode,ItemName,Quantity,WhsCode,Price,OcrCode,Remarks,B1Upload,Processed,Series,OcrCode2) values (@CreateDate,@PostingDate,@DocDate,@ReferenceNo,@ItemCode,@ItemName,@Quantity,@WhsCode,@Price,@OcrCode,@Remarks,@B1Upload,@Processed,@Series,@OcrCode2)");
sqlcmd.Parameters.Add("@CreateDate", ServerDateCurrentdate);
sqlcmd.Parameters.Add("@PostingDate", fromdate);
sqlcmd.Parameters.Add("@DocDate", fromdate);
sqlcmd.Parameters.Add("@ReferenceNo", dr["RefNo"].ToString());
sqlcmd.Parameters.Add("@ItemCode", dr["ItemCode"].ToString());
sqlcmd.Parameters.Add("@ItemName", dr["ItemName"].ToString());
sqlcmd.Parameters.Add("@Quantity", dr["Production Quantity"].ToString());
sqlcmd.Parameters.Add("@WhsCode", dr["WhsCode"].ToString());
sqlcmd.Parameters.Add("@Price", dr["Price"].ToString());
sqlcmd.Parameters.Add("@OcrCode", dr["WhsCode"].ToString());
sqlcmd.Parameters.Add("@Remarks", dr["Remarks"].ToString());
sqlcmd.Parameters.Add("@Series", "19");
sqlcmd.Parameters.Add("@OcrCode2", dr["OcrCode2"].ToString());
string B1Upload = "N";
string Processed = "N";
sqlcmd.Parameters.Add("@B1Upload", B1Upload);
sqlcmd.Parameters.Add("@Processed", Processed);
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.Message;
}
}
void getdet()
{
try
{
vdm = new VehicleDBMgr();
string BrachSOID = "";
if (Session["BrachSOID"] == null)
{
BrachSOID = Session["branch"].ToString();
}
else
{
BrachSOID = Session["BrachSOID"].ToString();
}
// cmd = new MySqlCommand("SELECT cashpayables.onNameof, cashpayables.DOE, cashpayables.Sno, cashpayables.Amount, cashpayables.Remarks,cashpayables.ApprovalRemarks, cashpayables.VoucherType, empmanage.EmpName FROM cashpayables INNER JOIN empmanage ON cashpayables.Approvedby = empmanage.Sno WHERE (cashpayables.VocherID = @VocherID) AND (cashpayables.BranchID = @BranchID)");
cmd = new MySqlCommand("SELECT cashpayables.onNameof, cashpayables.DOE,cashpayables.VocherID, cashpayables.Sno, cashpayables.Amount, cashpayables.ApprovedAmount, cashpayables.Remarks, cashpayables.ApprovalRemarks,cashpayables.VoucherType, empmanage.EmpName FROM cashpayables LEFT OUTER JOIN empmanage ON cashpayables.Approvedby = empmanage.Sno WHERE (cashpayables.Sno = @VocherID) AND (cashpayables.BranchID = @BranchID)");
// cmd = new MySqlCommand("SELECT cashpayables.onNameof, cashpayables.DOE,cashpayables.VocherID, cashpayables.Sno, cashpayables.Amount, cashpayables.ApprovedAmount, cashpayables.Remarks, cashpayables.ApprovalRemarks,cashpayables.VoucherType, empmanage.EmpName FROM cashpayables LEFT OUTER JOIN empmanage ON cashpayables.Approvedby = empmanage.Sno WHERE (cashpayables.Sno = @VocherID) ");
cmd.Parameters.AddWithValue("@VocherID", txtVoucherNo.Text);
cmd.Parameters.AddWithValue("@BranchID", BrachSOID);
DataTable dtCash = vdm.SelectQuery(cmd).Tables[0];
cmd = new MySqlCommand("SELECT sno, BranchName, BranchCode FROM branchdata WHERE (sno = @BranchID)");
cmd.Parameters.AddWithValue("@BranchID", BrachSOID);
DataTable dtCode = vdm.SelectQuery(cmd).Tables[0];
string voucherid = "";
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;
}
if (dtCash.Rows.Count > 0)
{
//lblVoucherno.Text = txtVoucherNo.Text;
voucherid = dtCode.Rows[0]["BranchCode"].ToString() + "/VOC/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "/" + dtCash.Rows[0]["VocherID"].ToString();
lblVoucherno.Text = voucherid;
string DOE = dtCash.Rows[0]["DOE"].ToString();
DateTime dtDOE = Convert.ToDateTime(DOE);
if (BrachSOID == "174")
{
lblApprove.Text = "Authorised By";
}
else
{
if (BrachSOID == "172" || BrachSOID == "158")
{
lblApprove.Text = "G.M/P.M";
}
else
{
lblApprove.Text = "Manager";
}
}
string ChangedTime = dtDOE.ToString("dd/MMM/yyyy");
lblDate.Text = ChangedTime;
lblPayCash.Text = dtCash.Rows[0]["onNameof"].ToString();
string AppRemarks = dtCash.Rows[0]["ApprovalRemarks"].ToString();
if (AppRemarks == "")
{
lblTowards.Text = dtCash.Rows[0]["Remarks"].ToString();
}
else
{
lblTowards.Text = dtCash.Rows[0]["ApprovalRemarks"].ToString();
}
lblVoucherType.Text = dtCash.Rows[0]["VoucherType"].ToString().ToUpper() + " VOUCHER";
string Amont = dtCash.Rows[0]["Amount"].ToString();
string[] Ones = { "", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", "Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Ninteen" };
string[] Tens = { "Ten", "Twenty", "Thirty", "Fourty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninty" };
int Num = int.Parse(Amont);
lblReceived.Text = NumToWordBD(Num) + " Rupees Only";
}
}
catch (Exception ex)
{
lblReceived.Text = ex.Message;
}
}
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;
float denominationtotal = 0;
Session["filename"] = ddlRouteName.SelectedItem.Text + "Delivery" + DateTime.Now.AddDays(1).ToString("dd/MM/yyyy");
lblRoute.Text = ddlRouteName.SelectedItem.Text;
lblDate.Text = txtdate.Text;
vdm = new VehicleDBMgr();
Report = new DataTable();
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);
}
}
DateTime ServerDateCurrentdate = VehicleDBMgr.GetTime(vdm.conn);
lblDate.Text = fromdate.ToString("dd/MMM/yyyy");
cmd = new MySqlCommand("SELECT ff.TripID, Triproutes.RouteID, ff.DispQty, ff.sno FROM (SELECT Tripdata_sno, RouteID, Sno FROM triproutes triproutes_1 WHERE (RouteID = @dispatchSno)) Triproutes INNER JOIN (SELECT TripID, DispQty, sno FROM (SELECT tripdata.Sno AS TripID, tripsubdata.Qty AS DispQty, tripsubdata.ProductId AS sno FROM tripdata INNER JOIN tripsubdata ON tripdata.Sno = tripsubdata.Tripdata_sno WHERE (tripdata.I_Date BETWEEN @starttime AND @endtime)) tripinfo) ff ON ff.TripID = Triproutes.Tripdata_sno");
cmd.Parameters.AddWithValue("@starttime", GetLowDate(fromdate.AddDays(-1)));
cmd.Parameters.AddWithValue("@endtime", GetHighDate(fromdate.AddDays(-1)));
cmd.Parameters.AddWithValue("@dispatchSno", ddlRouteName.SelectedValue);
DataTable dtSub_yesterdayData = vdm.SelectQuery(cmd).Tables[0];
cmd = new MySqlCommand("SELECT ff.TripID, Triproutes.RouteID, ff.DispQty, ff.sno FROM (SELECT Tripdata_sno, RouteID, Sno FROM triproutes triproutes_1 WHERE (RouteID = @dispatchSno)) Triproutes INNER JOIN (SELECT TripID, DispQty, sno FROM (SELECT tripdata.Sno AS TripID, tripsubdata.Qty AS DispQty, tripsubdata.ProductId AS sno FROM tripdata INNER JOIN tripsubdata ON tripdata.Sno = tripsubdata.Tripdata_sno WHERE (tripdata.I_Date BETWEEN @starttime AND @endtime)) tripinfo) ff ON ff.TripID = Triproutes.Tripdata_sno");
cmd.Parameters.AddWithValue("@starttime", GetLowDate(fromdate.AddDays(-8)));
cmd.Parameters.AddWithValue("@endtime", GetHighDate(fromdate.AddDays(-8)));
cmd.Parameters.AddWithValue("@dispatchSno", ddlRouteName.SelectedValue);
DataTable dtSub_LastWeekData = vdm.SelectQuery(cmd).Tables[0];
cmd = new MySqlCommand("SELECT ff.TripID, Triproutes.RouteID, ff.DispQty, ff.sno FROM (SELECT Tripdata_sno, RouteID, Sno FROM triproutes triproutes_1 WHERE (RouteID = @dispatchSno)) Triproutes INNER JOIN (SELECT TripID, DispQty, sno FROM (SELECT tripdata.Sno AS TripID, tripsubdata.Qty AS DispQty, tripsubdata.ProductId AS sno FROM tripdata INNER JOIN tripsubdata ON tripdata.Sno = tripsubdata.Tripdata_sno WHERE (tripdata.I_Date BETWEEN @starttime AND @endtime)) tripinfo) ff ON ff.TripID = Triproutes.Tripdata_sno");
cmd.Parameters.AddWithValue("@starttime", GetLowDate(fromdate.AddDays(-31)));
cmd.Parameters.AddWithValue("@endtime", GetHighDate(fromdate.AddDays(-31)));
cmd.Parameters.AddWithValue("@dispatchSno", ddlRouteName.SelectedValue);
DataTable dtSub_lastMonthData = vdm.SelectQuery(cmd).Tables[0];
cmd = new MySqlCommand("SELECT ff.TripID, Triproutes.RouteID, ff.DispQty, ff.sno FROM (SELECT Tripdata_sno, RouteID, Sno FROM triproutes triproutes_1 WHERE (RouteID = @dispatchSno)) Triproutes INNER JOIN (SELECT TripID, DispQty, sno FROM (SELECT tripdata.Sno AS TripID, tripsubdata.Qty AS DispQty, tripsubdata.ProductId AS sno FROM tripdata INNER JOIN tripsubdata ON tripdata.Sno = tripsubdata.Tripdata_sno WHERE (tripdata.I_Date BETWEEN @starttime AND @endtime)) tripinfo) ff ON ff.TripID = Triproutes.Tripdata_sno");
cmd.Parameters.AddWithValue("@starttime", GetLowDate(fromdate.AddDays(-365)));
cmd.Parameters.AddWithValue("@endtime", GetHighDate(fromdate.AddDays(-365)));
cmd.Parameters.AddWithValue("@dispatchSno", ddlRouteName.SelectedValue);
DataTable dtSub_lastYearData = vdm.SelectQuery(cmd).Tables[0];
cmd = new MySqlCommand("SELECT products_category.Categoryname,productsdata.Sno, productsdata.ProductName, products_subcategory.SubCatName 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) Group by productsdata.ProductName ORDER BY productsdata.Rank");
if (Session["salestype"].ToString() == "Plant")
{
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
}
else
{
cmd.Parameters.AddWithValue("@BranchID", Session["branch"].ToString());
}
DataTable produtstbl = vdm.SelectQuery(cmd).Tables[0];
DataTable dtallprdts = new DataTable();
dtallprdts.Columns.Add("Product_sno");
dtallprdts.Columns.Add("ProductName");
dtallprdts.Columns.Add("unitQty");
dtallprdts.Columns.Add("DeliveryQty");
dtallprdts.Columns.Add("LeakQty");
dtallprdts.Columns.Add("Total");
foreach (DataRow dr in produtstbl.Rows)
{
float unitqty = 0;
float deliveryqty = 0;
float leakqty = 0;
float Total = 0;
DataRow newRow = dtallprdts.NewRow();
newRow["Product_sno"] = dr["Sno"].ToString();
newRow["ProductName"] = dr["ProductName"].ToString();
newRow["unitQty"] = unitqty;
newRow["DeliveryQty"] = deliveryqty;
newRow["LeakQty"] = leakqty;
newRow["Total"] = Total;
dtallprdts.Rows.Add(newRow);
}
Report = new DataTable();
Report.Columns.Add("Product Name");
Report.Columns.Add("Yester Day").DataType = typeof(Double);
Report.Columns.Add("Last Week").DataType = typeof(Double);
Report.Columns.Add("Last Month").DataType = typeof(Double);
//Report.Columns.Add("Last Three Month").DataType = typeof(Double);
//Report.Columns.Add("Last Six Month").DataType = typeof(Double);
Report.Columns.Add("Last Year").DataType = typeof(Double);
DataRow newrow = Report.NewRow();
foreach (DataRow branch in dtallprdts.Rows)
{
DataRow newrow1 = Report.NewRow();
newrow1["Product Name"] = branch["ProductName"].ToString();
float DispQty = 0;
foreach (DataRow drSubData in dtSub_yesterdayData.Rows)
{
if (branch["Product_sno"].ToString() == drSubData["sno"].ToString())
{
float.TryParse(drSubData["DispQty"].ToString(), out DispQty);
if (DispQty > 0)
{
newrow1["Yester Day"] = drSubData["DispQty"].ToString();
}
}
}
foreach (DataRow drSubData in dtSub_LastWeekData.Rows)
{
if (branch["Product_sno"].ToString() == drSubData["sno"].ToString())
{
float.TryParse(drSubData["DispQty"].ToString(), out DispQty);
if (DispQty > 0)
{
newrow1["Last Week"] = drSubData["DispQty"].ToString();
}
}
}
foreach (DataRow drSubData in dtSub_lastMonthData.Rows)
{
if (branch["Product_sno"].ToString() == drSubData["sno"].ToString())
{
float.TryParse(drSubData["DispQty"].ToString(), out DispQty);
if (DispQty > 0)
{
newrow1["Last Month"] = drSubData["DispQty"].ToString();
}
}
}
foreach (DataRow drSubData in dtSub_lastYearData.Rows)
{
if (branch["Product_sno"].ToString() == drSubData["sno"].ToString())
{
float.TryParse(drSubData["DispQty"].ToString(), out DispQty);
if (DispQty > 0)
{
newrow1["Last Year"] = drSubData["DispQty"].ToString();
}
}
}
if (DispQty > 0)
{
Report.Rows.Add(newrow1);
}
}
DataRow newvartical = Report.NewRow();
newvartical["Product 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);
cmd = new MySqlCommand("SELECT branchdata.BranchName,branchdata.SalesType, branchaccounts.BranchId, branchaccounts.Amount FROM dispatch INNER JOIN modifiedroutes ON dispatch.Route_id = modifiedroutes.Sno INNER JOIN modifiedroutesubtable ON modifiedroutes.Sno = modifiedroutesubtable.RefNo INNER JOIN branchdata ON modifiedroutesubtable.BranchID = branchdata.sno INNER JOIN branchaccounts ON branchdata.sno = branchaccounts.BranchId WHERE (dispatch.sno = @dispsno) AND (modifiedroutesubtable.EDate IS NULL) AND (modifiedroutesubtable.CDate <= @dt) AND (branchdata.flag=1) OR (dispatch.sno = @dispsno) AND (modifiedroutesubtable.EDate > @dt) AND (modifiedroutesubtable.CDate <= @dt) and (branchdata.flag=1) order by branchdata.BranchName");
cmd.Parameters.AddWithValue("@dispsno", ddlRouteName.SelectedValue);
cmd.Parameters.AddWithValue("@dt", GetLowDate(fromdate.AddDays(-1)));
DataTable dtbranchammount = vdm.SelectQuery(cmd).Tables[0];
cmd = new MySqlCommand("SELECT inventory_monitor.Inv_Sno, inventory_monitor.Qty,modifiedroutesubtable.BranchID FROM dispatch INNER JOIN modifiedroutes ON dispatch.Route_id = modifiedroutes.Sno INNER JOIN modifiedroutesubtable ON modifiedroutes.Sno = modifiedroutesubtable.RefNo INNER JOIN inventory_monitor ON modifiedroutesubtable.BranchID = inventory_monitor.BranchId WHERE (dispatch.sno = @dispsno) AND (modifiedroutesubtable.EDate IS NULL) AND (modifiedroutesubtable.CDate <= @dt) OR (dispatch.sno = @dispsno) AND (modifiedroutesubtable.EDate > @dt) AND (modifiedroutesubtable.CDate <= @dt) ");
cmd.Parameters.AddWithValue("@dispsno", ddlRouteName.SelectedValue);
cmd.Parameters.AddWithValue("@dt", GetLowDate(fromdate.AddDays(-1)));
DataTable dtAgentInventory = vdm.SelectQuery(cmd).Tables[0];
cmd = new MySqlCommand("SELECT MIN(Ind.D_date) AS midate, MAX(collect.PaidDate) AS madate FROM dispatch INNER JOIN modifiedroutesubtable ON dispatch.Route_id = modifiedroutesubtable.RefNo INNER JOIN (SELECT indents.Branch_id, MIN(indents_subtable.D_date) AS D_date, MAX(indents_subtable.DTripId) AS DTripId FROM indents INNER JOIN indents_subtable ON indents.IndentNo = indents_subtable.IndentNo WHERE (indents.I_date BETWEEN @starttime AND @endtime) AND (indents_subtable.D_date IS NOT NULL) GROUP BY indents.Branch_id) Ind ON modifiedroutesubtable.BranchID = Ind.Branch_id INNER JOIN (SELECT PaidDate, tripId FROM collections) collect ON Ind.DTripId = collect.tripId 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)");
cmd.Parameters.AddWithValue("@dispatchSno", ddlRouteName.SelectedValue);
cmd.Parameters.AddWithValue("@starttime", GetLowDate(fromdate.AddDays(-1)));
cmd.Parameters.AddWithValue("@endtime", GetHighDate(ServerDateCurrentdate));
DataTable dtminmax = vdm.SelectQuery(cmd).Tables[0];
string mindate = dtminmax.Rows[0]["midate"].ToString();
string maxdate = dtminmax.Rows[0]["madate"].ToString();
DateTime midate = DateTime.Parse(mindate);
DateTime madate = DateTime.Parse(maxdate);
cmd = new MySqlCommand("SELECT branchdata.BranchName, indents_subtable.DTripId, SUM(indents_subtable.DeliveryQty * indents_subtable.UnitCost) AS totalamount, indents_subtable.D_date, indent.Branch_id, modifiedroutes.Sno FROM dispatch INNER JOIN modifiedroutes ON dispatch.Route_id = modifiedroutes.Sno INNER JOIN modifiedroutesubtable ON modifiedroutesubtable.RefNo = modifiedroutes.Sno INNER JOIN branchdata ON modifiedroutesubtable.BranchID = branchdata.sno INNER JOIN (SELECT IndentNo, I_date, Branch_id 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 WHERE (modifiedroutesubtable.EDate IS NULL) AND (modifiedroutesubtable.CDate <= @starttime) AND (dispatch.sno = @dispatchSno) OR (modifiedroutesubtable.EDate > @starttime) AND (modifiedroutesubtable.CDate <= @starttime) AND (dispatch.sno = @dispatchSno) GROUP BY branchdata.BranchName, modifiedroutes.Sno");
cmd.Parameters.AddWithValue("@dispatchSno", ddlPlantDispName.SelectedValue);
cmd.Parameters.AddWithValue("@starttime", GetLowDate(fromdate).AddDays(-1));
cmd.Parameters.AddWithValue("@endtime", GetHighDate(fromdate).AddDays(-1));
cmd.Parameters.AddWithValue("@Paidstime", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@Paidetime", GetHighDate(fromdate));
DataTable dttodaycollection = vdm.SelectQuery(cmd).Tables[0];
if (dttodaycollection.Rows.Count > 0)
{
cmd = new MySqlCommand("SELECT branchdata.BranchName, collections.Branchid, branchdata.sno, collections.AmountPaid, collections.PaidDate, collections.PaymentType, collections.CheckStatus, collections.PayTime, collections.ChequeNo, collections.tripId, collections.ReceiptNo FROM dispatch INNER JOIN branchroutes ON dispatch.Route_id = branchroutes.Sno INNER JOIN branchroutesubtable ON branchroutes.Sno = branchroutesubtable.RefNo INNER JOIN branchdata ON branchroutesubtable.BranchID = branchdata.sno INNER JOIN collections ON branchdata.sno = collections.Branchid WHERE (dispatch.sno = @dispatchsno) AND (collections.AmountPaid <> 0) AND (collections.tripId = @TripID)");
cmd.Parameters.AddWithValue("@dispatchSno", ddlRouteName.SelectedValue);
if (dttodaycollection.Rows[0]["DTripId"].ToString() == "")
{
cmd.Parameters.AddWithValue("@TripID", dttodaycollection.Rows[1]["DTripId"].ToString());
}
else
{
if (dttodaycollection.Rows[0]["D_date"].ToString() == "")
{
cmd.Parameters.AddWithValue("@TripID", dttodaycollection.Rows[1]["DTripId"].ToString());
}
else
{
cmd.Parameters.AddWithValue("@TripID", dttodaycollection.Rows[0]["DTripId"].ToString());
}
}
}
DataTable dtroutecollection = vdm.SelectQuery(cmd).Tables[0];
cmd = new MySqlCommand("SELECT result.Branch_id, result.BranchName, result.totalamount, result.D_date, result.DTripId, result.DelTime, SUM(collections.AmountPaid) AS AmountPaid,collections.PayTime FROM (SELECT branchdata.sno AS Branch_id, branchdata.BranchName, SUM(indentssub.DeliveryQty * indentssub.UnitCost) AS totalamount, indentssub.D_date,indentssub.DTripId, indentssub.DelTime FROM (SELECT IndentNo, Product_sno, DeliveryQty, D_date, UnitCost, DTripId, DelTime FROM indents_subtable WHERE (D_date BETWEEN @starttime AND @endtime)) indentssub INNER JOIN (SELECT IndentNo, Branch_id, TotalQty, TotalPrice, I_date, D_date, Status, UserData_sno, PaymentStatus, I_createdby, I_modifiedby,IndentType FROM indents WHERE (I_date BETWEEN @Indd1 AND @indd2)) ind ON indentssub.IndentNo = ind.IndentNo RIGHT OUTER JOIN dispatch INNER JOIN modifiedroutes ON dispatch.Route_id = modifiedroutes.Sno INNER JOIN (SELECT RefNo, BranchID, CDate, EDate FROM modifiedroutesubtable WHERE (EDate IS NULL) AND (CDate <= @starttime) OR (EDate > @starttime) AND (CDate <= @starttime)) modifroutessub ON modifiedroutes.Sno = modifroutessub.RefNo INNER JOIN branchdata ON modifroutessub.BranchID = branchdata.sno ON ind.Branch_id = branchdata.sno WHERE (dispatch.sno = @dispatchSno) GROUP BY branchdata.sno) result INNER JOIN collections ON result.Branch_id = collections.Branchid WHERE (collections.PaidDate BETWEEN @starttime AND @endtime) AND (collections.tripId <> 'NULL') GROUP BY result.Branch_id");
cmd.Parameters.AddWithValue("@dispatchSno", ddlRouteName.SelectedValue);
cmd.Parameters.AddWithValue("@starttime", midate);
cmd.Parameters.AddWithValue("@endtime", madate);
cmd.Parameters.AddWithValue("@Paidstime", midate);
cmd.Parameters.AddWithValue("@Paidetime", GetHighDate(fromdate));
cmd.Parameters.AddWithValue("@Indd1", GetLowDate(fromdate).AddDays(-1));
cmd.Parameters.AddWithValue("@Indd2", GetHighDate(madate));
DataTable dtBranchcollection = vdm.SelectQuery(cmd).Tables[0];
cmd = new MySqlCommand("SELECT result.Branch_id, result.BranchName, result.totalamount, result.D_date, result.DTripId, result.DelTime, SUM(collections.AmountPaid) AS AmountPaid,collections.PayTime FROM (SELECT branchdata.sno AS Branch_id, branchdata.BranchName, SUM(indentssub.DeliveryQty * indentssub.UnitCost) AS totalamount, indentssub.D_date,indentssub.DTripId, indentssub.DelTime FROM (SELECT IndentNo, Product_sno, DeliveryQty, D_date, UnitCost, DTripId, DelTime FROM indents_subtable WHERE (D_date BETWEEN @starttime AND @endtime)) indentssub INNER JOIN (SELECT IndentNo, Branch_id, TotalQty, TotalPrice, I_date, D_date, Status, UserData_sno, PaymentStatus, I_createdby, I_modifiedby,IndentType FROM indents WHERE (I_date BETWEEN @Indd1 AND @indd2)) ind ON indentssub.IndentNo = ind.IndentNo RIGHT OUTER JOIN dispatch INNER JOIN modifiedroutes ON dispatch.Route_id = modifiedroutes.Sno INNER JOIN (SELECT RefNo, BranchID, CDate, EDate FROM modifiedroutesubtable WHERE (EDate IS NULL) AND (CDate <= @starttime) OR (EDate > @starttime) AND (CDate <= @starttime)) modifroutessub ON modifiedroutes.Sno = modifroutessub.RefNo INNER JOIN branchdata ON modifroutessub.BranchID = branchdata.sno ON ind.Branch_id = branchdata.sno WHERE (dispatch.sno = @dispatchSno) GROUP BY branchdata.sno) result INNER JOIN collections ON result.Branch_id = collections.Branchid WHERE (collections.PaidDate BETWEEN @starttime AND @endtime) AND (collections.tripId IS NULL) GROUP BY result.Branch_id");
cmd.Parameters.AddWithValue("@dispatchSno", ddlRouteName.SelectedValue);
cmd.Parameters.AddWithValue("@starttime", midate);
cmd.Parameters.AddWithValue("@endtime", madate);
cmd.Parameters.AddWithValue("@Paidstime", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@Paidetime", GetHighDate(fromdate));
cmd.Parameters.AddWithValue("@Indd1", GetLowDate(fromdate).AddDays(-1));
cmd.Parameters.AddWithValue("@Indd2", GetHighDate(madate));
DataTable dtsalesofficecollection = vdm.SelectQuery(cmd).Tables[0];
DataTable Rpt = new DataTable();
Rpt.Columns.Add("SNo");
Rpt.Columns.Add("Agent Code");
Rpt.Columns.Add("Agent Name");
Rpt.Columns.Add("Crates");
Rpt.Columns.Add("Oppening Balance");
Rpt.Columns.Add("Sale Value");
Rpt.Columns.Add("Paid Amount");
Rpt.Columns.Add("Due Amount");
DataTable dtreport = new DataTable();
dtreport.Columns.Add("Agent Code");
dtreport.Columns.Add("Agent Name");
dtreport.Columns.Add("Crates");
dtreport.Columns.Add("Oppening Balance");
dtreport.Columns.Add("Sale Value");
dtreport.Columns.Add("Paid Amount");
dtreport.Columns.Add("Due Amount");
dtreport.Columns.Add("today sale");
dtreport.Columns.Add("today collected");
dtreport.Columns.Add("SalesType");
foreach (DataRow dr in dtbranchammount.Rows)
{
DataRow newRow = dtreport.NewRow();
newRow["Agent Code"] = dr["BranchId"].ToString();
newRow["Agent Name"] = dr["BranchName"].ToString();
newRow["Sale Value"] = "0";
newRow["Oppening Balance"] = dr["Amount"].ToString();
newRow["Paid Amount"] = "0";
newRow["Due Amount"] = "0";
newRow["today sale"] = "0";
newRow["today collected"] = "0";
newRow["SalesType"] = dr["SalesType"].ToString();
dtreport.Rows.Add(newRow);
}
foreach (DataRow drbrncnamtcoll in dtreport.Rows)
{
foreach (DataRow dr in dttodaycollection.Rows)
{
if (drbrncnamtcoll["Agent Code"].ToString() == dr["Branch_id"].ToString())
{
float salevalue = 0;
float.TryParse(dr["totalamount"].ToString(), out salevalue);
drbrncnamtcoll["today sale"] = Math.Round(salevalue, 2);
}
}
}
foreach (DataRow drbrncnamtcoll in dtreport.Rows)
{
foreach (DataRow dr in dtroutecollection.Rows)
{
if (drbrncnamtcoll["Agent Code"].ToString() == dr["Branchid"].ToString())
{
float paidamt = 0;
float.TryParse(dr["AmountPaid"].ToString(), out paidamt);
drbrncnamtcoll["today collected"] = Math.Round(paidamt, 2);
}
}
}
foreach (DataRow drbrnchamt in dtreport.Rows)
{
foreach (DataRow drbrnchcollection in dtBranchcollection.Rows)
{
if (drbrnchamt["Agent Code"].ToString() == drbrnchcollection["Branch_id"].ToString())
{
float salevalue = 0;
float.TryParse(drbrnchcollection["totalamount"].ToString(), out salevalue);
float paidamt = 0;
float.TryParse(drbrnchcollection["AmountPaid"].ToString(), out paidamt);
float dueamt = 0;
drbrnchamt["Sale Value"] = Math.Round(salevalue, 2);
drbrnchamt["Paid Amount"] = Math.Round(paidamt, 2);
drbrnchamt["Due Amount"] = Math.Round(dueamt, 2);
}
}
}
foreach (DataRow drbrnchamt in dtreport.Rows)
{
foreach (DataRow drsocollection in dtsalesofficecollection.Rows)
{
if (drbrnchamt["Agent Code"].ToString() == drsocollection["Branch_id"].ToString())
{
float sopaidamt = 0;
float.TryParse(drsocollection["AmountPaid"].ToString(), out sopaidamt);
float trippaid = 0;
float.TryParse(drbrnchamt["Paid Amount"].ToString(), out trippaid);
float amtpaid = sopaidamt + trippaid;
drbrnchamt["Paid Amount"] = Math.Round(amtpaid, 2);
}
}
}
int k = 1;
cmd = new MySqlCommand("SELECT sno,salestype FROM salestypemanagement WHERE (status = 1) ORDER BY salestype DESC");
DataTable dtsalesType = vdm.SelectQuery(cmd).Tables[0];
foreach (DataRow dr in dtsalesType.Rows)
{
DataRow[] drsalestype = dtreport.Select("SalesType='" + dr["sno"].ToString() + "'");
if (drsalestype.Length > 0)
{
DataRow newRow41 = Rpt.NewRow();
if (dr["sno"].ToString() == "20")
{
newRow41["Agent Code"] = dr["salestype"].ToString();
Rpt.Rows.Add(newRow41);
}
}
if (dr["sno"].ToString() == "20")
{
foreach (DataRow drbrnchamt in dtreport.Select("SalesType='" + dr["sno"].ToString() + "'"))
{
DataRow newRow1 = Rpt.NewRow();
newRow1["SNo"] = k;
newRow1["Agent Code"] = drbrnchamt["Agent Code"].ToString();
DataRow[] dragentinv = dtAgentInventory.Select("BranchID='" + drbrnchamt["Agent Code"].ToString() + "'");
string creates = "";
foreach (DataRow drc in dragentinv)
{
string invsno = drc.ItemArray[0].ToString();
if (invsno == "1" || invsno == "2" || invsno == "3" || invsno == "4")
{
if (drc.ItemArray[1].ToString() == "0")
{
}
else
{
creates += drc.ItemArray[1].ToString() + "/";
newRow1["Crates"] = creates;
}
}
}
newRow1["Agent Name"] = drbrnchamt["Agent Name"].ToString();
newRow1["Sale Value"] = drbrnchamt["Sale Value"].ToString();
float salevalue = 0;
float.TryParse(drbrnchamt["Sale Value"].ToString(), out salevalue);
float paidamt = 0;
float.TryParse(drbrnchamt["Paid Amount"].ToString(), out paidamt);
float todaypaid = 0;
float.TryParse(drbrnchamt["today collected"].ToString(), out todaypaid);
float oppamt = 0;
float.TryParse(drbrnchamt["Oppening Balance"].ToString(), out oppamt);
float aopp = oppamt + paidamt - salevalue;
float totaldue = aopp + salevalue - paidamt;
if (dr["sno"].ToString() == "20")
{
newRow1["Oppening Balance"] = aopp;
newRow1["Paid Amount"] = Math.Round(paidamt, 2);
newRow1["Due Amount"] = Math.Round(totaldue, 2);
Rpt.Rows.Add(newRow1);
}
else
{
newRow1["Paid Amount"] = Math.Round(todaypaid, 2);
Rpt.Rows.Add(newRow1);
}
k++;
}
}
}
grdReports.DataSource = Report;
grdReports.DataBind();
grdbalance.DataSource = Rpt;
grdbalance.DataBind();
Session["xportdata"] = Report;
}
catch
{
}
}
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 = "";
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 ServerDateCurrentdate = VehicleDBMgr.GetTime(vdm.conn);
DateTime dtapril = new DateTime();
DateTime dtmarch = new DateTime();
int currentyear = fromdate.Year;
int nextyear = fromdate.Year + 1;
if (fromdate.Month > 3)
{
string apr = "4/1/" + currentyear;
dtapril = DateTime.Parse(apr);
string march = "3/31/" + nextyear;
dtmarch = DateTime.Parse(march);
}
if (fromdate.Month <= 3)
{
string apr = "4/1/" + (currentyear - 1);
dtapril = DateTime.Parse(apr);
string march = "3/31/" + (nextyear - 1);
dtmarch = DateTime.Parse(march);
}
string date = fromdate.ToString("dd/MM/yy");
string[] str = date.Split('/');
string dtdate = str[0].ToString();
string month = str[1].ToString();
string year = str[2].ToString();
string dmarch = dtmarch.ToString("dd/MM/yy");
string[] strm = dmarch.Split('/');
string dtmarchdate = strm[0].ToString();
string marchmonth = strm[1].ToString();
string marchyear = strm[2].ToString();
string dapril = dtapril.ToString("dd/MM/yy");
string[] stra = dapril.Split('/');
string dtaprildate = stra[0].ToString();
string aprilmonth = stra[1].ToString();
string aprilyear = stra[2].ToString();
cmd = new MySqlCommand("SELECT branchcode FROM branchdata WHERE (sno = @branchid)");
cmd.Parameters.AddWithValue("@branchid", Session["branch"]);
DataTable dtbranch = vdm.SelectQuery(cmd).Tables[0];
string branchcode = "";
if (dtbranch.Rows.Count > 0)
{
branchcode = dtbranch.Rows[0]["branchcode"].ToString();// d dr["branchcode"].ToString();
}
string refno = "" + branchcode + "/" + aprilyear + "" + marchyear + "/" + dtdate + "" + month + "";
lbl_selfromdate.Text = fromdate.ToString("dd/MM/yyyy");
Session["filename"] = "TOTAL DC REPORT";
cmd = new MySqlCommand("SELECT TripInfo.Sno, TripInfo.DCNo,ProductInfo.ProductName,ProductInfo.categorycode,ProductInfo.Categoryname,ProductInfo.UnitPrice, ProductInfo.Qty,ProductInfo.Productid,ProductInfo.Itemcode, TripInfo.I_Date, TripInfo.VehicleNo, TripInfo.Status,TripInfo.whcode,TripInfo.DispName, TripInfo.DispType, TripInfo.DispMode FROM (SELECT tripdata.Sno, tripdata.DCNo, tripdata.I_Date, tripdata.VehicleNo, tripdata.Status, dispatch.DispName, dispatch.DispType, dispatch.DispMode,branchdata.whcode FROM branchdata INNER JOIN dispatch ON branchdata.sno = dispatch.Branch_Id INNER JOIN triproutes ON dispatch.sno = triproutes.RouteID INNER JOIN tripdata ON triproutes.Tripdata_sno = tripdata.Sno WHERE (dispatch.Branch_Id = @branch) AND (tripdata.AssignDate BETWEEN @d1 AND @d2)) TripInfo INNER JOIN (SELECT Categoryname,categorycode, ProductName, Sno, Qty,Productid,Itemcode,UnitPrice FROM (SELECT products_category.Categoryname,products_category.categorycode, productsdata.ProductName,productsdata.sno as Productid, productsdata.Itemcode,productsdata.UnitPrice,tripdata_1.Sno, tripsubdata.Qty FROM tripdata tripdata_1 INNER JOIN tripsubdata ON tripdata_1.Sno = tripsubdata.Tripdata_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 (tripdata_1.AssignDate BETWEEN @d1 AND @d2)) TripSubInfo) ProductInfo ON TripInfo.Sno = ProductInfo.Sno");
cmd.Parameters.AddWithValue("@branch", Session["branch"]);
cmd.Parameters.AddWithValue("@SOID", Session["branch"]);
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@d2", GetHighDate(fromdate));
DataTable dtble = vdm.SelectQuery(cmd).Tables[0];
cmd = new MySqlCommand("SELECT productsdata.sno,productsdata.Itemcode,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 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 (branchproducts.branch_sno = @Branch) AND (tripdata.BranchID = @BranchID) GROUP BY productsdata.ProductName ORDER BY branchproducts.Rank");
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@d2", GetHighDate(fromdate));
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, "Productid", "Categoryname");
Report = new DataTable();
Report.Columns.Add("SNo");
Report.Columns.Add("CreateDate");
Report.Columns.Add("PostingDate");
Report.Columns.Add("DocDate");
Report.Columns.Add("WhsCode");
Report.Columns.Add("RefNo");
Report.Columns.Add("OcrCode");
Report.Columns.Add("OcrCode2");
Report.Columns.Add("ItemName");
Report.Columns.Add("ItemCode");
Report.Columns.Add("Production Quantity");
Report.Columns.Add("Price");
Report.Columns.Add("Remarks");
//DataTable distincttable = view.ToTable(true, "Batter", "Chapathi", "Chikki", "Icecream", "PAROTA", "Rasagulla", "Malailaddu", "KALAKANDA");
int i = 1;
string ProductName = "";
string Itemcode = "";
string UnitPrice = "";
string WhsCode = "";
string categorycode = "";
foreach (DataRow dr in produtstbl.Rows)
{
//if (dr["Categoryname"].ToString() == "Batter" || dr["Categoryname"].ToString() == "Chapathi" || dr["Categoryname"].ToString() == "Chikki" || dr["Categoryname"].ToString() == "Icecream" || dr["Categoryname"].ToString() == "PAROTA" || dr["Categoryname"].ToString() == "Rasagulla" || dr["Categoryname"].ToString() == "KALAKANDA" || dr["Categoryname"].ToString() == "Malailaddu")
//{
double totalqty = 0;
DataRow newrow = Report.NewRow();
foreach (DataRow drproduct in dtble.Select("Productid='" + dr["sno"].ToString() + "'"))
{
double AssignQty = 0;
double.TryParse(drproduct["Qty"].ToString(), out AssignQty);;
ProductName = drproduct["ProductName"].ToString();
Itemcode = drproduct["Itemcode"].ToString();
UnitPrice = drproduct["UnitPrice"].ToString();
WhsCode = drproduct["whcode"].ToString();
categorycode = drproduct["categorycode"].ToString();
totalqty += AssignQty;
}
newrow["SNo"] = i++;
newrow["ItemName"] = ProductName;
newrow["ItemCode"] = Itemcode;
newrow["Production Quantity"] = totalqty;
newrow["Price"] = UnitPrice;
newrow["WhsCode"] = WhsCode;
newrow["RefNo"] = refno;
DateTime createdate = Convert.ToDateTime(fromdate).AddDays(1);
newrow["CreateDate"] = ServerDateCurrentdate.ToString(("dd/MM/yyyy"));
newrow["PostingDate"] = createdate.ToString(("dd/MM/yyyy"));;
newrow["DocDate"] = createdate.ToString(("dd/MM/yyyy"));
newrow["Remarks"] = "";
newrow["OcrCode2"] = categorycode;
newrow["OcrCode"] = WhsCode;
Report.Rows.Add(newrow);
//}
}
grdReports.DataSource = Report;
grdReports.DataBind();
Session["xportdata"] = Report;
}
else
{
pnlHide.Visible = false;
lblmsg.Text = "No DC 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 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();
}
}
void GetReport()
{
try
{
lblmsg.Text = "";
pnlHide.Visible = true;
Session["RouteName"] = ddlSalesOffice.SelectedItem.Text;
Session["IDate"] = DateTime.Now.AddDays(1).ToString("dd/MM/yyyy");
vdm = new VehicleDBMgr();
DateTime fromdate = DateTime.Now;
string[] dateFromstrig = txtFromdate.Text.Split(' ');
if (dateFromstrig.Length > 1)
{
if (dateFromstrig[0].Split('-').Length > 0)
{
string[] dates = dateFromstrig[0].Split('-');
string[] times = dateFromstrig[1].Split(':');
fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
DataTable Report = new DataTable();
Report.Columns.Add("DOE");
Report.Columns.Add("Ref Receipt");
Report.Columns.Add("Receipt");
Report.Columns.Add("Type");
Report.Columns.Add("Name");
Report.Columns.Add("Amount").DataType = typeof(Double);
lbl_selfromdate.Text = fromdate.ToString("dd/MM/yyyy");
lblRoutName.Text = ddlSalesOffice.SelectedItem.Text;
DateTime ReportDate = VehicleDBMgr.GetTime(vdm.conn);
DateTime dtapril = new DateTime();
DateTime dtmarch = new DateTime();
int currentyear = ReportDate.Year;
int nextyear = ReportDate.Year + 1;
if (ReportDate.Month > 3)
{
string apr = "4/1/" + currentyear;
dtapril = DateTime.Parse(apr);
string march = "3/31/" + nextyear;
dtmarch = DateTime.Parse(march);
}
if (ReportDate.Month <= 3)
{
string apr = "4/1/" + (currentyear - 1);
dtapril = DateTime.Parse(apr);
string march = "3/31/" + (nextyear - 1);
dtmarch = DateTime.Parse(march);
}
Session["xporttype"] = "SapPayments";
string ledger = ""; string whcode = "";
string ladger_dr_code = "";
cmd = new MySqlCommand("SELECT tbranchname, ladger_dr,whcode,ladger_dr_code FROM branchdata WHERE (sno = @BranchID)");
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
DataTable dtledger = vdm.SelectQuery(cmd).Tables[0];
if (dtledger.Rows.Count > 0)
{
ledger = dtledger.Rows[0]["ladger_dr"].ToString();
whcode = dtledger.Rows[0]["whcode"].ToString();
ladger_dr_code = dtledger.Rows[0]["ladger_dr_code"].ToString();
}
Session["filename"] = ddlSalesOffice.SelectedItem.Text + " Sap Payments" + fromdate.ToString("dd/MM/yyyy");
//cmd = new MySqlCommand("SELECT cashpayables.VocherID, cashpayables.Remarks,subpayable.sno, subpayable.Amount, accountheads.HeadName FROM cashpayables INNER JOIN subpayable ON cashpayables.Sno = subpayable.RefNo INNER JOIN accountheads ON subpayable.HeadSno = accountheads.Sno WHERE (cashpayables.BranchID = @BranchID) AND (cashpayables.DOE BETWEEN @d1 AND @d2) AND (cashpayables.VoucherType = 'Debit') AND (cashpayables.Status = 'P')");
cmd = new MySqlCommand("SELECT cashpayables.VocherID, cashpayables.Remarks, subpayable.sno, subpayable.Amount,accountheads.accountcode,subpayable.HeadSno, accountheads.HeadName, cashpayables.Approvedby, cashpayables.Status,empmanage.EmpName, cashpayables.VoucherType, cashpayables.DOE, empmanage_1.EmpName AS createdby, empmanage_1.Sno AS createemp,empmanage.Sno AS approvedemp FROM cashpayables INNER JOIN subpayable ON cashpayables.Sno = subpayable.RefNo INNER JOIN accountheads ON subpayable.HeadSno = accountheads.Sno INNER JOIN empmanage ON cashpayables.Approvedby = empmanage.Sno INNER JOIN empmanage empmanage_1 ON cashpayables.Created_by = empmanage_1.Sno WHERE (cashpayables.BranchID = @BranchID) AND (cashpayables.DOE BETWEEN @d1 AND @d2) AND (cashpayables.VoucherType = 'Debit') AND (cashpayables.Status = 'P')");
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@d2", GetHighDate(fromdate));
DataTable dtAgent = vdm.SelectQuery(cmd).Tables[0];
if (dtAgent.Rows.Count > 0)
{
DataView view = new DataView(dtAgent);
dtReport = new DataTable();
dtReport.Columns.Add("Sno");
dtReport.Columns.Add("Voucher Date");
dtReport.Columns.Add("Voucher No");
dtReport.Columns.Add("Voucher Type");
dtReport.Columns.Add("whcode");
Report.Columns.Add("Series");
dtReport.Columns.Add("CreatedBy");
dtReport.Columns.Add("ApprovedBy");
dtReport.Columns.Add("Credit Code");
dtReport.Columns.Add("Ledger (Cr)");
dtReport.Columns.Add("Debit Code");
dtReport.Columns.Add("Ledger (Dr)");
dtReport.Columns.Add("Amount");
dtReport.Columns.Add("Narration");
//DataTable distincttable = view.ToTable(true, "BranchName", "BSno");
int i = 1;
foreach (DataRow branch in dtAgent.Rows)
{
string VoucherNo = "";
cmd = new MySqlCommand("SELECT vouchercode,RefNo, HeadDesc, Amount, HeadSno, sno, branchid, paiddate FROM subpayable WHERE (branchid = @BranchID) AND (paiddate BETWEEN @d1 AND @d2)");
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@d2", GetLowDate(fromdate));
DataTable dtVoucher = vdm.SelectQuery(cmd).Tables[0];
if (dtVoucher.Rows.Count > 0)
{
DataRow[] drvoucher = dtVoucher.Select("branchid='" + ddlSalesOffice.SelectedValue + "' and sno='" + branch["sno"].ToString() + "' and HeadSno='" + branch["HeadSno"].ToString() + "'");
if (drvoucher.Length > 0)
{
foreach (DataRow drv in drvoucher)
{
VoucherNo = drv.ItemArray[0].ToString();
}
}
else
{
cmd = new MySqlCommand("SELECT IFNULL(MAX(vouchercode), 0) + 1 AS Sno FROM subpayable WHERE (branchid = @branchid) AND (paiddate BETWEEN @d1 AND @d2)");
cmd.Parameters.AddWithValue("@branchid", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@HeadSno", branch["HeadSno"].ToString());
cmd.Parameters.AddWithValue("@d1", GetLowDate(dtapril.AddDays(-1)));
cmd.Parameters.AddWithValue("@d2", GetHighDate(dtmarch.AddDays(-1)));
DataTable dtvoucherno = vdm.SelectQuery(cmd).Tables[0];
VoucherNo = dtvoucherno.Rows[0]["Sno"].ToString();
cmd = new MySqlCommand("update subpayable set vouchercode=@vouchercode, paiddate=@paiddate,branchid=@branchid where (sno=@sno) AND (HeadSno = @HeadSno)");;
cmd.Parameters.AddWithValue("@vouchercode", VoucherNo);
cmd.Parameters.AddWithValue("@paiddate", fromdate);
cmd.Parameters.AddWithValue("@branchid", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@sno", branch["sno"].ToString());
cmd.Parameters.AddWithValue("@HeadSno", branch["HeadSno"].ToString());
vdm.Update(cmd);
}
}
else
{
cmd = new MySqlCommand("SELECT IFNULL(MAX(vouchercode), 0) + 1 AS Sno FROM subpayable WHERE (branchid = @branchid) AND (paiddate BETWEEN @d1 AND @d2)");
cmd.Parameters.AddWithValue("@branchid", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@HeadSno", branch["HeadSno"].ToString());
cmd.Parameters.AddWithValue("@d1", GetLowDate(dtapril.AddDays(-1)));
cmd.Parameters.AddWithValue("@d2", GetHighDate(dtmarch.AddDays(-1)));
DataTable dtvoucherno = vdm.SelectQuery(cmd).Tables[0];
VoucherNo = dtvoucherno.Rows[0]["Sno"].ToString();
cmd = new MySqlCommand("update subpayable set vouchercode=@vouchercode, paiddate=@paiddate,branchid=@branchid where (sno=@sno) AND (HeadSno = @HeadSno)");;
cmd.Parameters.AddWithValue("@vouchercode", VoucherNo);
cmd.Parameters.AddWithValue("@paiddate", fromdate);
cmd.Parameters.AddWithValue("@branchid", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@sno", branch["sno"].ToString());
cmd.Parameters.AddWithValue("@HeadSno", branch["HeadSno"].ToString());
vdm.Update(cmd);
}
if (VoucherNo == "0")
{
cmd = new MySqlCommand("SELECT IFNULL(MAX(vouchercode), 0) + 1 AS Sno FROM subpayable WHERE (branchid = @branchid) AND (paiddate BETWEEN @d1 AND @d2)");
cmd.Parameters.AddWithValue("@branchid", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@HeadSno", branch["HeadSno"].ToString());
cmd.Parameters.AddWithValue("@d1", GetLowDate(dtapril.AddDays(-1)));
cmd.Parameters.AddWithValue("@d2", GetHighDate(dtmarch.AddDays(-1)));
DataTable dtvoucherno = vdm.SelectQuery(cmd).Tables[0];
VoucherNo = dtvoucherno.Rows[0]["Sno"].ToString();
cmd = new MySqlCommand("update subpayable set vouchercode=@vouchercode, paiddate=@paiddate,branchid=@branchid where (RefNo=@RefNo) AND (HeadSno = @HeadSno)");;
cmd.Parameters.AddWithValue("@vouchercode", VoucherNo);
cmd.Parameters.AddWithValue("@paiddate", fromdate);
cmd.Parameters.AddWithValue("@branchid", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@RefNo", branch["sno"].ToString());
cmd.Parameters.AddWithValue("@HeadSno", branch["HeadSno"].ToString());
vdm.Update(cmd);
}
string NewVoucherNo = "0";
int countdc = 0;
int.TryParse(VoucherNo, out countdc);
if (countdc <= 10)
{
NewVoucherNo = "0000" + countdc;
}
if (countdc >= 10 && countdc <= 99)
{
NewVoucherNo = "000" + countdc;
}
if (countdc >= 99 && countdc <= 999)
{
NewVoucherNo = "00" + countdc;
}
if (countdc >= 999)
{
NewVoucherNo = "0" + countdc;
}
DataRow newrow = dtReport.NewRow();
newrow["Sno"] = i++;
newrow["Voucher Date"] = fromdate.ToString("dd-MMM-yyyy");
newrow["Voucher No"] = ddlSalesOffice.SelectedValue + dtapril.ToString("yy") + dtmarch.ToString("yy") + NewVoucherNo;
newrow["Voucher Type"] = "Cash Payment Import";
newrow["Credit Code"] = ladger_dr_code;
newrow["Ledger (Cr)"] = ledger;
newrow["Debit Code"] = branch["accountcode"].ToString();
newrow["Ledger (Dr)"] = branch["HeadName"].ToString();
newrow["Amount"] = branch["Amount"].ToString();
newrow["CreatedBy"] = branch["createdby"].ToString();
newrow["ApprovedBy"] = branch["EmpName"].ToString();
newrow["whcode"] = whcode;
newrow["Narration"] = branch["Remarks"].ToString() + ",VoucherID " + branch["VocherID"].ToString() + ",Emp Name " + Session["EmpName"].ToString();
dtReport.Rows.Add(newrow);
}
grdReports.DataSource = dtReport;
grdReports.DataBind();
Session["xportdata"] = dtReport;
}
else
{
pnlHide.Visible = false;
lblmsg.Text = "No Indent Found";
grdReports.DataSource = dtReport;
grdReports.DataBind();
}
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
grdReports.DataSource = dtReport;
grdReports.DataBind();
}
}
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();
}
}
void GetReport()
{
try
{
vdm = new VehicleDBMgr();
lblmsg.Text = "";
DateTime fromdate = DateTime.Now;
Report = new DataTable();
DateTime ServerDateCurrentdate = VehicleDBMgr.GetTime(vdm.conn);
string[] fromdatestrig = txtdate.Text.Split(' ');
if (fromdatestrig.Length > 1)
{
if (fromdatestrig[0].Split('-').Length > 0)
{
string[] dates = fromdatestrig[0].Split('-');
string[] times = fromdatestrig[1].Split(':');
fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
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);
}
}
lblRouteName.Text = ddlRouteName.SelectedItem.Text;
lblDate.Text = fromdate.ToString("dd/MMM/yyyy");
lbl_selttodate.Text = todate.ToString("dd/MMM/yyyy");
Session["filename"] = "Agent Wise Incentive";
TimeSpan dateSpan = todate.Subtract(fromdate);
int NoOfdays = dateSpan.Days;
NoOfdays = NoOfdays + 1;
cmd = new MySqlCommand("SELECT modifiedroutes.RouteName, modifiedroutes.Sno AS routeid, ROUND(SUM(indents_subtable.DeliveryQty), 2) AS DeliveryQty, SUM(indents_subtable.DeliveryQty * indents_subtable.UnitCost) AS salevalue, indent.IndentType, productsdata.ProductName, branchdata.sno AS BSno, branchdata.BranchName, productsdata.Units, productsdata.sno, products_category.Categoryname FROM modifiedroutes INNER JOIN modifiedroutesubtable ON modifiedroutes.Sno = modifiedroutesubtable.RefNo INNER JOIN branchdata ON modifiedroutesubtable.BranchID = branchdata.sno INNER JOIN (SELECT IndentNo, Branch_id, Status, I_date, IndentType FROM indents WHERE (I_date BETWEEN @starttime AND @endtime) AND (Status <> 'D')) indent ON branchdata.sno = indent.Branch_id INNER JOIN indents_subtable ON indent.IndentNo = indents_subtable.IndentNo INNER JOIN productsdata ON indents_subtable.Product_sno = productsdata.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno WHERE (modifiedroutesubtable.EDate IS NULL) AND (modifiedroutesubtable.CDate <= @endtime) AND (modifiedroutes.BranchID = @TripID) OR (modifiedroutesubtable.EDate > @starttime) AND (modifiedroutesubtable.CDate <= @starttime) AND (modifiedroutes.BranchID = @TripID) GROUP BY branchdata.BranchName, productsdata.sno, products_category.Categoryname ORDER BY modifiedroutes.RouteName");
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@TripID", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@starttime", GetLowDate(fromdate.AddDays(-1)));
cmd.Parameters.AddWithValue("@endtime", GetHighDate(todate.AddDays(-1)));
DataTable dtble = vdm.SelectQuery(cmd).Tables[0];
cmd = new MySqlCommand("SELECT products_category.Categoryname, productsdata.sno, productsdata.ProductName, branchproducts.Rank FROM indents_subtable INNER JOIN indents ON indents_subtable.IndentNo = indents.IndentNo INNER JOIN branchmappingtable ON indents.Branch_id = branchmappingtable.SubBranch 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 branchmappingtable.SuperBranch = branchproducts.branch_sno AND productsdata.sno = branchproducts.product_sno WHERE (branchmappingtable.SuperBranch = @BranchID) AND (indents.I_date BETWEEN @d1 AND @d2) GROUP BY productsdata.ProductName ORDER BY branchproducts.Rank");
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate.AddDays(-1)));
cmd.Parameters.AddWithValue("@d2", GetHighDate(todate.AddDays(-1)));
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
DataTable produtstbl = vdm.SelectQuery(cmd).Tables[0];
if (dtble.Rows.Count > 0)
{
DataView view = new DataView(dtble);
Report = new DataTable();
Report.Columns.Add("SNo");
Report.Columns.Add("Route Name");
Report.Columns.Add("Agentid");
Report.Columns.Add("Agent Name");
foreach (DataRow dr in produtstbl.Rows)
{
Report.Columns.Add(dr["ProductName"].ToString()).DataType = typeof(Double);
}
Report.Columns.Add("Total Milk Sale", typeof(Double));
Report.Columns.Add("Milk Avg Sale", typeof(Double));
Report.Columns.Add("Total Curd Sale", typeof(Double));
Report.Columns.Add("Curd Avg Sale", typeof(Double));
DataTable distincttable = view.ToTable(true, "BranchName", "BSno", "RouteName", "routeid");
int i = 1;
int Totalcount = 1;
string RouteName = "";
string routeid = "";
string finalrouteid = "";
foreach (DataRow branch in distincttable.Rows)
{
DataRow newrow = Report.NewRow();
newrow["SNo"] = i;
finalrouteid = branch["routeid"].ToString();
if (RouteName != branch["RouteName"].ToString())
{
if (Totalcount == 1)
{
newrow["Route Name"] = branch["RouteName"].ToString();
Totalcount++;
}
else
{
DataRow newvar = Report.NewRow();
newvar["Agent Name"] = "Total";
double ftotalmilkSale = 0;
double totavgmilkSale = 0;
double ftotalcurdSale = 0;
double totavgcurdSale = 0;
int route = 0;
int.TryParse(routeid, out route);
foreach (DataRow dr in dtble.Select("RouteID='" + route + "'"))
{
double d_qty = 0;
double DeliveryQty = 0;
double UnitCost = 0;
DataRow[] drincentive = dtble.Select("RouteID='" + route + "' and ProductName='" + dr["ProductName"].ToString() + "'");
foreach (DataRow drc in drincentive)
{
double.TryParse(drc.ItemArray[2].ToString(), out DeliveryQty);
double.TryParse(drc.ItemArray[3].ToString(), out UnitCost);
d_qty += DeliveryQty;
}
if (d_qty == 0.0)
{
}
else
{
newvar[dr["ProductName"].ToString()] = d_qty;
}
if (dr["Categoryname"].ToString() == "MILK")
{
double.TryParse(dr["DeliveryQty"].ToString(), out DeliveryQty);
ftotalmilkSale += DeliveryQty;
}
if (dr["Categoryname"].ToString() == "CURD")
{
double.TryParse(dr["DeliveryQty"].ToString(), out DeliveryQty);
ftotalcurdSale += DeliveryQty;
}
}
newvar["Total Milk Sale"] = Math.Round(ftotalmilkSale, 2);
double totMilkavg = 0;
totMilkavg = ftotalmilkSale / NoOfdays;
totMilkavg = Math.Round(totMilkavg, 2);
newvar["Milk Avg Sale"] = totMilkavg;
newvar["Total Curd Sale"] = Math.Round(ftotalcurdSale, 2);
double totCurdavg = 0;
totCurdavg = ftotalcurdSale / NoOfdays;
totCurdavg = Math.Round(totCurdavg, 2);
newvar["Curd Avg Sale"] = totCurdavg;
Report.Rows.Add(newvar);
newrow["Route Name"] = branch["RouteName"].ToString();
Totalcount++;
DataRow space = Report.NewRow();
space["Agent Name"] = "";
Report.Rows.Add(space);
routeid = branch["routeid"].ToString();
}
}
else
{
newrow["Route Name"] = "";
routeid = branch["routeid"].ToString();
}
RouteName = branch["RouteName"].ToString();
newrow["Agent Name"] = branch["BranchName"].ToString();
newrow["Agentid"] = branch["BSno"].ToString();
double totalmilkSale = 0;
double totalcurdSale = 0;
foreach (DataRow dr in dtble.Rows)
{
if (branch["BranchName"].ToString() == dr["BranchName"].ToString())
{
double qtyvalue = 0;
double DeliveryQty = 0;
double.TryParse(dr["DeliveryQty"].ToString(), out DeliveryQty);
double UnitCost = 0;
double.TryParse(dr["salevalue"].ToString(), out UnitCost);
if (DeliveryQty == 0.0)
{
}
else
{
newrow[dr["ProductName"].ToString()] = DeliveryQty;
}
if (dr["Categoryname"].ToString() == "MILK")
{
double.TryParse(dr["DeliveryQty"].ToString(), out qtyvalue);
totalmilkSale += qtyvalue;
}
if (dr["Categoryname"].ToString() == "CURD")
{
double.TryParse(dr["DeliveryQty"].ToString(), out qtyvalue);
totalcurdSale += qtyvalue;
}
}
}
newrow["Total Milk Sale"] = Math.Round(totalmilkSale, 2);
double Milkavg = 0;
Milkavg = totalmilkSale / NoOfdays;
Milkavg = Math.Round(Milkavg, 2);
newrow["Milk Avg Sale"] = Milkavg;
newrow["Total Curd Sale"] = Math.Round(totalcurdSale, 2);
double curdavg = 0;
curdavg = totalcurdSale / NoOfdays;
curdavg = Math.Round(curdavg, 2);
newrow["Curd Avg Sale"] = curdavg;
Report.Rows.Add(newrow);
routeid = branch["routeid"].ToString();
i++;
}
DataRow newvar1 = Report.NewRow();
newvar1["Agent Name"] = "Total";
cmd = new MySqlCommand("SELECT brnchprdt.Rank, ROUND(SUM(indents_subtable.DeliveryQty), 2) AS DeliveryQty,products_category.Categoryname, SUM(indents_subtable.DeliveryQty * indents_subtable.UnitCost) AS salevalue, productsdata.ProductName FROM indents_subtable INNER JOIN (SELECT IndentNo, Branch_id, Status, I_date, IndentType FROM indents WHERE (I_date BETWEEN @starttime AND @endtime) AND (Status <> 'D')) indent ON indents_subtable.IndentNo = indent.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, unitprice, flag, Rank FROM branchproducts WHERE (branch_sno = @BranchID)) brnchprdt ON productsdata.sno = brnchprdt.product_sno INNER JOIN invmaster ON productsdata.Inventorysno = invmaster.sno RIGHT OUTER JOIN modifiedroutes INNER JOIN modifiedroutesubtable ON modifiedroutes.Sno = modifiedroutesubtable.RefNo INNER JOIN branchdata ON modifiedroutesubtable.BranchID = branchdata.sno ON indent.Branch_id = branchdata.sno WHERE (modifiedroutes.Sno = @RouteID) AND (modifiedroutesubtable.EDate IS NULL) AND (modifiedroutesubtable.CDate <= @endtime) AND (productsdata.ProductName IS NOT NULL) OR (modifiedroutes.Sno = @RouteID) AND (modifiedroutesubtable.EDate > @starttime) AND (modifiedroutesubtable.CDate <= @starttime) AND (productsdata.ProductName IS NOT NULL) GROUP BY productsdata.sno ORDER BY brnchprdt.Rank");
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@RouteID", finalrouteid);
cmd.Parameters.AddWithValue("@starttime", GetLowDate(fromdate.AddDays(-1)));
cmd.Parameters.AddWithValue("@endtime", GetHighDate(todate.AddDays(-1)));
DataTable dtfinalroutetotal = vdm.SelectQuery(cmd).Tables[0];
double dtotalmilkSale = 0;
double dtotalcurdSale = 0;
foreach (DataRow dr in dtfinalroutetotal.Rows)
{
double DeliveryQty = 0;
double.TryParse(dr["DeliveryQty"].ToString(), out DeliveryQty);
if (DeliveryQty == 0.0)
{
}
else
{
newvar1[dr["ProductName"].ToString()] = DeliveryQty;
}
if (dr["Categoryname"].ToString() == "MILK")
{
double.TryParse(dr["DeliveryQty"].ToString(), out DeliveryQty);
dtotalmilkSale += DeliveryQty;
}
if (dr["Categoryname"].ToString() == "CURD")
{
double.TryParse(dr["DeliveryQty"].ToString(), out DeliveryQty);
dtotalcurdSale += DeliveryQty;
}
}
newvar1["Total Milk Sale"] = Math.Round(dtotalmilkSale, 2);
double dMilkavg = 0;
dMilkavg = dtotalmilkSale / NoOfdays;
dMilkavg = Math.Round(dMilkavg, 2);
newvar1["Milk Avg Sale"] = dMilkavg;
newvar1["Total Curd Sale"] = Math.Round(dtotalcurdSale, 2);
double dcurdavg = 0;
dcurdavg = dtotalcurdSale / NoOfdays;
dcurdavg = Math.Round(dcurdavg, 2);
newvar1["Curd Avg Sale"] = dcurdavg;
Report.Rows.Add(newvar1);
DataRow space1 = Report.NewRow();
space1["Agent Name"] = "";
Report.Rows.Add(space1);
cmd = new MySqlCommand("SELECT products_category.Categoryname, ROUND(SUM(indents_subtable.DeliveryQty), 2) AS DeliveryQty, SUM(indents_subtable.DeliveryQty * indents_subtable.UnitCost) AS salevalue, productsdata.ProductName, productsdata.sno FROM modifiedroutes INNER JOIN modifiedroutesubtable ON modifiedroutes.Sno = modifiedroutesubtable.RefNo INNER JOIN branchdata ON modifiedroutesubtable.BranchID = branchdata.sno INNER JOIN (SELECT IndentNo, Branch_id, Status, I_date, IndentType FROM indents WHERE (I_date BETWEEN @starttime AND @endtime) AND (Status <> 'D')) indent ON branchdata.sno = indent.Branch_id INNER JOIN indents_subtable ON indent.IndentNo = indents_subtable.IndentNo INNER JOIN productsdata ON indents_subtable.Product_sno = productsdata.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno WHERE (modifiedroutesubtable.EDate IS NULL) AND (modifiedroutesubtable.CDate <= @endtime) AND (modifiedroutes.BranchID = @TripID) OR (modifiedroutesubtable.EDate > @starttime) AND (modifiedroutesubtable.CDate <= @starttime) AND (modifiedroutes.BranchID = @TripID) GROUP BY productsdata.sno");
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@TripID", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@starttime", GetLowDate(fromdate.AddDays(-1)));
cmd.Parameters.AddWithValue("@endtime", GetHighDate(todate.AddDays(-1)));
DataTable dttotal = vdm.SelectQuery(cmd).Tables[0];
DataRow newvar2 = Report.NewRow();
newvar2["Agent Name"] = "Total";
foreach (DataRow dr in dttotal.Rows)
{
double DeliveryQty = 0;
double Qty = 0;
double.TryParse(dr["DeliveryQty"].ToString(), out Qty);
if (Qty == 0.0)
{
}
else
{
newvar2[dr["ProductName"].ToString()] = Qty;
}
if (dr["Categoryname"].ToString() == "MILK")
{
double.TryParse(dr["DeliveryQty"].ToString(), out DeliveryQty);
dtotalmilkSale += DeliveryQty;
}
if (dr["Categoryname"].ToString() == "CURD")
{
double.TryParse(dr["DeliveryQty"].ToString(), out DeliveryQty);
dtotalcurdSale += DeliveryQty;
}
}
newvar2["Total Milk Sale"] = Math.Round(dtotalmilkSale, 2);
double eMilkavg = 0;
eMilkavg = dtotalmilkSale / NoOfdays;
eMilkavg = Math.Round(eMilkavg, 2);
newvar2["Milk Avg Sale"] = eMilkavg;
newvar2["Total Curd Sale"] = Math.Round(dtotalcurdSale, 2);
double ecurdavg = 0;
ecurdavg = dtotalcurdSale / NoOfdays;
ecurdavg = Math.Round(ecurdavg, 2);
newvar2["Curd Avg Sale"] = ecurdavg;
Report.Rows.Add(newvar2);
foreach (var column in Report.Columns.Cast <DataColumn>().ToArray())
{
if (Report.AsEnumerable().All(dr => dr.IsNull(column)))
{
Report.Columns.Remove(column);
}
}
foreach (DataColumn col in Report.Columns)
{
string Pname = col.ToString();
string ProductName = col.ToString();
ProductName = GetSpace(ProductName);
Report.Columns[Pname].ColumnName = ProductName;
}
// new incentive query naveen
cmd = new MySqlCommand("SELECT incentive_maindetails.sno, incentive_maindetails.incentivetype, incentive_maindetails.branchid, incentive_maindetails.routeid, incentive_maindetails.agentid, branchdata.BranchName, incentive_maindetails.structuretype, incentive_maindetails.leakage, incentive_maindetails.fromdate, incentive_maindetails.todate, incentive_maindetails.remarks, incentive_maindetails.createdby, incentive_maindetails.approvedby, incentive_maindetails.createddate, incentive_maindetails.status, incentive_subdetails.refno, incentive_subdetails.productid, incentive_subdetails.amount FROM incentive_maindetails INNER JOIN incentive_subdetails ON incentive_maindetails.sno = incentive_subdetails.refno INNER JOIN branchdata ON branchdata.sno = incentive_maindetails.agentid WHERE (incentive_maindetails.branchid = @branchid) AND (incentive_maindetails.routeid = @routeid)");
cmd.Parameters.AddWithValue("@branchid", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@routeid", ddlRouteName.SelectedValue);
DataTable dtincentive = vdm.SelectQuery(cmd).Tables[0];
DataTable dtagent = view.ToTable(true, "branchid", "routeid", "agentid", "BranchName");
if (dtagent.Rows.Count > 0)
{
foreach (DataRow dr in dtagent.Rows)
{
string agentid = dr[""].ToString();
}
}
}
else
{
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);
}
}
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();
}
}
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 (GridViewRow row in grdReports.Rows)
{
//string PaymentMode = "CASH";
//string B1Upload = "N";
//string Processed = "N";
if (row.RowType == DataControlRowType.DataRow)
{
CheckBox chkRow = (row.Cells[0].FindControl("chkRow") as CheckBox);
if (chkRow.Checked)
{
string VoucherDate = row.Cells[1].Text;
string VoucherNo = row.Cells[2].Text;
string VoucherType = row.Cells[3].Text;
string whcode = row.Cells[4].Text;
string debitcode = row.Cells[5].Text;
string debitledger = row.Cells[6].Text;
string creditcode = row.Cells[8].Text;
string AcctCode = row.Cells[8].Text;
string creditledger = row.Cells[9].Text;
string Amount = row.Cells[10].Text;
string Narration = row.Cells[12].Text;
string ser = row.Cells[11].Text;
//string AcctCode = dr["Credit Code"].ToString();
//string VoucherNo = dr["Voucher No"].ToString();
//string vochertype = dr["Voucher Type"].ToString();
//string whcode = dr["WH Code"].ToString();
//string creditcode = dr["Credit Code"].ToString();
//string creditledger = dr["Ledger (Cr)"].ToString();
//string debitcode = dr["Debit Code"].ToString();
//string debitledger = dr["Ledger (Dr)"].ToString();
//string Amount = dr["Amount"].ToString();
//string Narration = dr["Narration"].ToString();
if (AcctCode == "")
{
}
else
{
double num;
if (double.TryParse(creditcode, out num))
{
string PaymentMode = "BANK";
double amount = 0;
double.TryParse(Amount, out amount);
string B1Upload = "N";
string Processed = "N";
sqlcmd = new SqlCommand("SELECT CreateDate, RefDate, DocDate, Ref1, Ref2, Ref3 FROM EMROJDTP WHERE (RefDate BETWEEN @d1 AND @d2) AND (Ref1 = @Refno) AND (AcctCode=@AcctCode)");
sqlcmd.Parameters.Add("@d1", GetLowDate(fromdate));
sqlcmd.Parameters.Add("@d2", GetHighDate(fromdate));
sqlcmd.Parameters.Add("@AcctCode", AcctCode);
sqlcmd.Parameters.Add("@Refno", VoucherNo);
DataTable dtJournelPay = SAPvdm.SelectQuery(sqlcmd).Tables[0];
if (dtJournelPay.Rows.Count > 0)
{
lbl_msg.Text = "This Receipt Already Saved";
}
else
{
sqlcmd = new SqlCommand("Insert into EMROJDTP (CreateDate, RefDate, DocDate, TransNo, TransCode, AcctCode, AcctName, Debit, Credit, B1Upload, Processed,Ref1,OcrCode,Remarks,series) values (@CreateDate, @RefDate, @DocDate,@TransNo,@TransCode, @AcctCode, @AcctName, @Debit, @Credit, @B1Upload, @Processed,@Ref1,@OcrCode,@Remarks,@series)");
sqlcmd.Parameters.Add("@CreateDate", CreateDate);
sqlcmd.Parameters.Add("@RefDate", GetLowDate(fromdate));
sqlcmd.Parameters.Add("@docdate", GetLowDate(fromdate));
sqlcmd.Parameters.Add("@Ref1", VoucherNo);
string TransCode = "T1";
sqlcmd.Parameters.Add("@TransNo", VoucherNo);
sqlcmd.Parameters.Add("@TransCode", TransCode);
sqlcmd.Parameters.Add("@AcctCode", debitcode);
sqlcmd.Parameters.Add("@AcctName", debitledger);
double.TryParse(Amount, out amount);
sqlcmd.Parameters.Add("@Debit", amount);
string Creditamount = "0";
sqlcmd.Parameters.Add("@Credit", Creditamount);
sqlcmd.Parameters.Add("@B1Upload", B1Upload);
sqlcmd.Parameters.Add("@Processed", Processed);
sqlcmd.Parameters.Add("@OcrCode", whcode);
sqlcmd.Parameters.Add("@Remarks", Narration);
string series = "134";
sqlcmd.Parameters.Add("@series", series);
SAPvdm.insert(sqlcmd);
sqlcmd = new SqlCommand("Insert into EMROJDTP (CreateDate, RefDate, DocDate, TransNo,TransCode, AcctCode, AcctName, Debit, Credit, B1Upload, Processed,Ref1,OcrCode,Remarks,series) values (@CreateDate, @RefDate, @DocDate,@TransNo,@TransCode, @AcctCode, @AcctName, @Debit, @Credit, @B1Upload, @Processed,@Ref1,@OcrCode,@Remarks,@series)");
sqlcmd.Parameters.Add("@CreateDate", CreateDate);
sqlcmd.Parameters.Add("@RefDate", GetLowDate(fromdate));
sqlcmd.Parameters.Add("@docdate", GetLowDate(fromdate));
sqlcmd.Parameters.Add("@Ref1", VoucherNo);
sqlcmd.Parameters.Add("@TransNo", VoucherNo);
sqlcmd.Parameters.Add("@TransCode", TransCode);
sqlcmd.Parameters.Add("@AcctCode", creditcode);
sqlcmd.Parameters.Add("@AcctName", creditledger);
string Debitamount = "0";
sqlcmd.Parameters.Add("@Debit", Debitamount);
sqlcmd.Parameters.Add("@Credit", amount);
sqlcmd.Parameters.Add("@B1Upload", B1Upload);
sqlcmd.Parameters.Add("@Processed", Processed);
sqlcmd.Parameters.Add("@OcrCode", whcode);
sqlcmd.Parameters.Add("@Remarks", Narration);
sqlcmd.Parameters.Add("@series", series);
SAPvdm.insert(sqlcmd);
}
}
else
{
sqlcmd = new SqlCommand("SELECT CreateDate, PaymentDate, DOE FROM EMRORCT WHERE (PaymentDate BETWEEN @d1 AND @d2) AND (ReferenceNo = @TNo) AND (CardCode=@CardCode)");
sqlcmd.Parameters.Add("@d1", GetLowDate(fromdate));
sqlcmd.Parameters.Add("@d2", GetHighDate(fromdate));
sqlcmd.Parameters.Add("@TNo", VoucherNo);
sqlcmd.Parameters.Add("@CardCode", creditcode);
DataTable dtovpm = SAPvdm.SelectQuery(sqlcmd).Tables[0];
if (dtovpm.Rows.Count > 0)
{
lbl_msg.Text = "This Receipt Already Saved";
}
else
{
sqlcmd = new SqlCommand("INSERT INTO EMRORCT(CreateDate, PaymentDate, DOE, ReferenceNo, CardCode, AcctNo, Remarks, PaymentMode, PaymentSum, OcrCode, B1Upload, Processed, Series) values (@CreateDate, @RefDate, @DocDate, @TransNo, @CardCode, @AcctCode, @Remarks, @PaymentMode, @PaymentSum, @OcrCode, @B1Upload, @Processed, @Series)");
sqlcmd.Parameters.Add("@CreateDate", CreateDate);
sqlcmd.Parameters.Add("@RefDate", GetLowDate(fromdate));
sqlcmd.Parameters.Add("@docdate", GetLowDate(fromdate));
sqlcmd.Parameters.Add("@VoucherNo", VoucherNo);
sqlcmd.Parameters.Add("@TransNo", VoucherNo);
sqlcmd.Parameters.Add("@OcrCode", whcode);
sqlcmd.Parameters.Add("@CardCode", creditcode);
sqlcmd.Parameters.Add("@AcctCode", debitcode);
sqlcmd.Parameters.Add("@AcctName", creditledger);
sqlcmd.Parameters.Add("@Series", ser);
sqlcmd.Parameters.Add("@Remarks", Narration);
sqlcmd.Parameters.Add("@PaymentMode", "BANK");
sqlcmd.Parameters.Add("@PaymentSum", Amount);
string B1Upload = "N";
string Processed = "N";
sqlcmd.Parameters.Add("@B1Upload", B1Upload);
sqlcmd.Parameters.Add("@Processed", Processed);
SAPvdm.insert(sqlcmd);
}
}
}
}
}
}
pnlHide.Visible = false;
DataTable dtempty = new DataTable();
grdReports.DataSource = dtempty;
grdReports.DataBind();
if (lbl_msg.Text == "")
{
lbl_msg.Text = "SuccessFully Saved";
}
}
catch (Exception ex)
{
lbl_msg.Text = ex.ToString();
}
}
void getdet()
{
try
{
vdm = new VehicleDBMgr();
cmd = new SqlCommand("SELECT paymentdetails.sno, paymentdetails.name, paymentdetails.accountno, paymentdetails.totalamount, paymentdetails.remarks, paymentdetails.approvedby,paymentdetails.doe , paymentdetails.createdby, paymentdetails.status, employe_login.name AS empname FROM paymentdetails INNER JOIN employe_login ON paymentdetails.approvedby = employe_login.sno WHERE (paymentdetails.sno = @sno)");
cmd.Parameters.Add("@sno", txtVoucherNo.Text);
DataTable dtCash = vdm.SelectQuery(cmd).Tables[0];
string voucherid = "";
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;
}
if (dtCash.Rows.Count > 0)
{
//lblVoucherno.Text = txtVoucherNo.Text;
voucherid = "SVDS/VOC/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "/" + dtCash.Rows[0]["sno"].ToString();
lblVoucherno.Text = voucherid;
string DOE = dtCash.Rows[0]["doe"].ToString();
DateTime dtDOE = Convert.ToDateTime(DOE);
lblApprove.Text = dtCash.Rows[0]["EmpName"].ToString();
string ChangedTime = dtDOE.ToString("dd/MMM/yyyy");
lblDate.Text = ChangedTime;
lblPayCash.Text = dtCash.Rows[0]["name"].ToString();
string AppRemarks = dtCash.Rows[0]["remarks"].ToString();
if (AppRemarks == "")
{
lblTowards.Text = dtCash.Rows[0]["remarks"].ToString();
}
else
{
lblTowards.Text = dtCash.Rows[0]["remarks"].ToString();
}
lblVoucherType.Text = "DEBIT VOUCHER";
string Amont = dtCash.Rows[0]["totalamount"].ToString();
string[] Ones = { "", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", "Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Ninteen" };
string[] Tens = { "Ten", "Twenty", "Thirty", "Fourty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninty" };
int Num = int.Parse(Amont);
lblReceived.Text = NumToWordBD(Num) + " Rupees Only";
}
}
catch (Exception ex)
{
lblReceived.Text = ex.Message;
}
}