private void textBox2_KeyDown(object sender, KeyEventArgs e)
{
DataTable dtcombo = new DataTable();
dtcombo.Columns.Add("PriceList", typeof(string));
dtcombo.Rows.Add();
dtcombo.Rows[0][0] = Feature.Available("Name of PriceList1");
dtcombo.Rows.Add();
dtcombo.Rows[1][0] = Feature.Available("Name of PriceList2");
dtcombo.Rows.Add();
dtcombo.Rows[2][0] = Feature.Available("Name of PriceList3");
dtcombo.Rows.Add();
dtcombo.Rows[3][0] = Feature.Available("Name of PriceList4");
dtcombo.Rows.Add();
dtcombo.Rows[4][0] = Feature.Available("Name of PriceList5");
dtcombo.Rows.Add();
dtcombo.Rows[5][0] = Feature.Available("Name of PriceList6");
dtcombo.Rows.Add();
dtcombo.Rows[6][0] = "MRP";
textBox2.Text = SelectCombo.ComboDt(this, dtcombo, 0);
SelectCombo.IsEnter(this, e.KeyCode);
}
private void DisplaySetting()
{
if (Feature.Available("Item Name before Packing") == "Yes")
{
ansGridView1.Columns["Description"].DisplayIndex = 1;
ansGridView1.Columns["Pack"].DisplayIndex = 2;
}
else
{
ansGridView1.Columns["Pack"].DisplayIndex = 1;
ansGridView1.Columns["Description"].DisplayIndex = 2;
}
if (Feature.Available("Batch Number") == "Yes")
{
ansGridView1.Columns["batchcode"].Visible = true;
}
else
{
ansGridView1.Columns["batchcode"].Visible = false;
}
ansGridView1.Columns["batchcode"].DisplayIndex = 3;
ansGridView1.Columns["Qty"].DisplayIndex = 4;
ansGridView1.Columns["Rate"].DisplayIndex = 5;
ansGridView1.Columns["Amt"].DisplayIndex = 6;
}
private void textBox18_KeyPress(object sender, KeyPressEventArgs e)
{
DataTable dtcombo = new DataTable();
dtcombo.Columns.Add("PriceList", typeof(string));
dtcombo.Rows.Add();
dtcombo.Rows[0][0] = Feature.Available("Name of PriceList1");
dtcombo.Rows.Add();
dtcombo.Rows[1][0] = Feature.Available("Name of PriceList2");
dtcombo.Rows.Add();
dtcombo.Rows[2][0] = Feature.Available("Name of PriceList3");
dtcombo.Rows.Add();
dtcombo.Rows[3][0] = Feature.Available("Name of PriceList4");
dtcombo.Rows.Add();
dtcombo.Rows[4][0] = Feature.Available("Name of PriceList5");
dtcombo.Rows.Add();
dtcombo.Rows[5][0] = Feature.Available("Name of PriceList6");
textBox18.Text = SelectCombo.ComboDt(this, dtcombo, 0);
SendKeys.Send("{tab}");
}
private void save()
{
firmsetup.Rows[0]["ToSuperAdmin"] = textBox10.Text;
//firmsetup.Rows[0]["ToAdmin"] = textBox7.Text;
//firmsetup.Rows[0]["ToSuperUser"] = textBox9.Text;
//firmsetup.Rows[0]["ToUser"] = textBox12.Text;
//firmsetup.Rows[0]["ToCashier"] = textBox14.Text;
Database.SaveData(firmsetup);
Master.UpdateFeature();
Master.UpdateRates();
Master.UpdateAll();
funs.ShowBalloonTip("Saved", "Saved Successfully");
Database.TextCase = Feature.Available("Text Case");
if (gstr == "0")
{
Loaddata("0", this.Text);
}
else
{
this.Close();
this.Dispose();
}
}
private void frm_openingstock_Load(object sender, EventArgs e)
{
foreach (DataGridViewColumn column in ansGridView1.Columns)
{
column.SortMode = DataGridViewColumnSortMode.NotSortable;
}
ansGridView1.Columns["Qty"].CellTemplate.ValueType = typeof(double);
ansGridView1.Columns["Amt"].CellTemplate.ValueType = typeof(double);
gIskachcha = Database.IsKacha;
textBox1.Focus();
this.Size = this.MdiParent.Size;
if (Feature.Available("Multi-Godown") == "No")
{
textBox1.Enabled = false;
textBox1.ReadOnly = true;
textBox1.Text = "<MAIN>";
}
else
{
textBox1.Enabled = true;
textBox1.Text = "<MAIN>";
}
SideFill();
DisplaySetting();
LoadData();
}
private void showReport_Load(object sender, EventArgs e)
{
if (Feature.Available("Data Export") == "No")
{
crystalReportViewer1.ShowExportButton = false;
}
}
public static int CommandExecutorInt(String str)
{
OpenConnection();
if (DatabaseType == "sql")
{
sqlcmd = new SqlCommand(str, SqlConn);
try
{
sqlcmd.Transaction = sqlTran;
return(sqlcmd.ExecuteNonQuery());
if (Feature.Available("Active Syncronizer").ToUpper() == "YES")
{
DataTable dtSync = new DataTable("Syncronizer");
dtSync.Rows.Add();
dtSync.Columns.Add("Id", typeof(int));
dtSync.Columns.Add("Location", typeof(string));
dtSync.Columns.Add("query", typeof(string));
dtSync.Columns.Add("Updated", typeof(Boolean));
dtSync.Columns.Add("timespan", typeof(string));
DataTable dtCount = new DataTable();
Database.GetSqlData("select count(*) from Syncronizer", dtCount);
dtSync.Rows[dtSync.Rows.Count - 1]["Location"] = Database.LocationId;
dtSync.Rows[dtSync.Rows.Count - 1]["query"] = str;
dtSync.Rows[dtSync.Rows.Count - 1]["Updated"] = false;
dtSync.Rows[dtSync.Rows.Count - 1]["timespan"] = DateTime.Now;
SqlDataAdapter da1 = new SqlDataAdapter("select * from " + dtSync.TableName, SqlConn);
SqlCommandBuilder cb1 = new SqlCommandBuilder();
cb1.QuotePrefix = "[";
cb1.QuoteSuffix = "]";
cb1.DataAdapter = da1;
da1.SelectCommand.Transaction = sqlTran;
da1.Update(dtSync);
}
}
catch (SqlException ex)
{
//System.Windows.Forms.MessageBox.Show(ex.Message);
return(0);
}
}
else
{
accesscmd = new OleDbCommand(str, AccessConn);
try
{
accesscmd.Transaction = AccessTran;
return(accesscmd.ExecuteNonQuery());
}
catch (OleDbException ex)
{
return(0);
}
}
}
private void button3_Click(object sender, EventArgs e)
{
if (Feature.Available("Taxation Applicable") != "VAT")
{
Regex obj = new Regex("^[0-9]{2}[A-Z]{5}[0-9]{4}[A-Z]{1}[0-9A-Z]{1}Z[0-9A-Z]{1}$");
if (textBox6.Text.Trim() == "" || textBox6.Text == "0")
{
textBox6.Text = "0";
}
else if (obj.IsMatch(textBox6.Text) == false)
{
MessageBox.Show("GSTIN is Not Correct");
return;
}
}
gcityid = funs.Select_city_id(textBox10.Text);
gPincode = textBox11.Text;
shipto = funs.Select_ac_id(textBox14.Text);
gPrintname = textBox1.Text;
gAddress1 = textBox2.Text;
gAddress2 = textBox3.Text;
gContact = textBox4.Text;
gEmail = textBox5.Text;
gTin = textBox6.Text;
gState = textBox7.Text;
gAadhar = textBox9.Text;
gPAN = textBox8.Text;
string stateid = "";
//stateid = funs.Select_ac_state_id(textBox14.Text);
stateid = funs.Select_state_id(textBox7.Text);
if (stateid == "")
{
stateid = Database.CompanyState_id;
}
if (Database.CompanyState_id == stateid)
{
gExstate = false;
}
else
{
gExstate = true;
}
this.Close();
this.Dispose();
}
private void Form1_Load(object sender, EventArgs e)
{
dateTimePicker1.Value = Database.cmonthFst;
dateTimePicker1.MaxDate = Database.ldate;
dateTimePicker1.MinDate = Database.stDate;
dateTimePicker1.CustomFormat = Database.dformat;
dateTimePicker2.Value = Database.ldate;
dateTimePicker2.MaxDate = Database.ldate;
dateTimePicker2.MinDate = Database.stDate;
dateTimePicker2.CustomFormat = Database.dformat;
if (ReportName == "StockSummary")
{
this.Text = "Stock Summary";
label1.Visible = false;
textBox3.Visible = false;
}
else
{
if (Feature.Available("Required Department").ToUpper() == "YES")
{
label2.Enabled = true;
textBox6.Enabled = true;
}
checkBox1.Visible = false;
checkBox2.Visible = false;
this.Text = "Stock Valuation";
}
textBox1.Text = this.Fld1;
textBox4.Text = this.Fld2;
textBox10.Text = this.Fld3;
textBox11.Text = this.Fld4;
textBox13.Text = this.Fld5;
textBox5.Text = this.Fld6;
textBox2.Text = this.Fld7;
textBox3.Text = this.Fld8;
if (calledindirect == true)
{
dateTimePicker1.Value = DateTime.Parse(dt1.ToString(Database.dformat));
dateTimePicker2.Value = DateTime.Parse(dt2.ToString(Database.dformat));
}
checkBox1.Checked = chk1;
checkBox2.Checked = chk2;
}
private void frm_usermgmt_Load(object sender, EventArgs e)
{
SideFill();
if (Feature.Available("Required Department").ToUpper() == "NO")
{
label4.Visible = false;
textBox4.Visible = false;
}
else
{
label4.Visible = true;
textBox4.Visible = true;
}
}
private void frm_NewCompany_Load(object sender, EventArgs e)
{
dateTimePicker1.CustomFormat = Database.dformat;
dateTimePicker2.CustomFormat = Database.dformat;
SideFill();
if (Feature.Available("Taxation Applicable") == "VAT")
{
groupBox4.Text = "TIN";
}
else
{
groupBox4.Text = "GSTN";
}
}
public void LoadData()
{
dtOpnStk = new DataTable("Stock");
Database.GetSqlData("Select * from Stock where Vid='0' and marked=" + access_sql.Singlequote + gIskachcha + access_sql.Singlequote + " and godown_id='" + funs.Select_ac_id(textBox1.Text) + "' and Branch_id='" + Database.BranchId + "' order by Itemsr", dtOpnStk);
ansGridView1.Rows.Clear();
dtOpnStk.Columns.Add("Description", typeof(string));
dtOpnStk.Columns.Add("Pack", typeof(string));
for (int i = 0; i < dtOpnStk.Rows.Count; i++)
{
dtOpnStk.Rows[i]["Description"] = funs.Select_des_nm(dtOpnStk.Rows[i]["Did"].ToString());
string pack = Database.GetScalarText("Select pack from Description where Des_id='" + dtOpnStk.Rows[i]["Did"].ToString() + "' ");
dtOpnStk.Rows[i]["Pack"] = pack;
}
dtOpnStk.DefaultView.Sort = "Itemsr,Description,Pack";
dtOpnStk = dtOpnStk.DefaultView.ToTable();
double opn = 0;
for (int i = 0; i < dtOpnStk.Rows.Count; i++)
{
ansGridView1.Rows.Add();
ansGridView1.Rows[i].Cells["SNo"].Value = (i + 1);
ansGridView1.Rows[i].Cells["Ac_id"].Value = funs.Select_ac_id(textBox1.Text);
ansGridView1.Rows[i].Cells["Des_id"].Value = dtOpnStk.Rows[i]["Did"].ToString();
ansGridView1.Rows[i].Cells["Description"].Value = funs.Select_des_nm(dtOpnStk.Rows[i]["Did"].ToString());
string pack = Database.GetScalarText("Select pack from Description where Des_id='" + dtOpnStk.Rows[i]["Did"].ToString() + "' ");
ansGridView1.Rows[i].Cells["Pack"].Value = pack;
ansGridView1.Rows[i].Cells["Qty"].Value = funs.DecimalPoint(double.Parse(dtOpnStk.Rows[i]["Receive"].ToString()), 3);
ansGridView1.Rows[i].Cells["Amt"].Value = funs.DecimalPoint(double.Parse(dtOpnStk.Rows[i]["ReceiveAmt"].ToString()), 2);
double rate = 0;
rate = double.Parse(dtOpnStk.Rows[i]["ReceiveAmt"].ToString()) / double.Parse(dtOpnStk.Rows[i]["Receive"].ToString());
opn = opn + double.Parse(dtOpnStk.Rows[i]["ReceiveAmt"].ToString());
ansGridView1.Rows[i].Cells["Rate"].Value = funs.DecimalPoint(rate, 2);
ansGridView1.Rows[i].Cells["batchcode"].Value = dtOpnStk.Rows[i]["Batch_no"].ToString();
}
label2.Text = funs.DecimalPoint(opn, 2);
if (Feature.Available("Item Name before Packing") == "Yes")
{
ansGridView1.CurrentCell = ansGridView1.Rows[0].Cells["Description"];
}
else
{
ansGridView1.CurrentCell = ansGridView1.Rows[0].Cells["Pack"];
}
}
private void frm_updaterate_Load(object sender, EventArgs e)
{
// this.Size = this.MdiParent.Size;
SideFill();
t1.Select();
if (Feature.Available("Taxation Applicable") == "VAT")
{
label6.Text = "TaxCategory";
}
else
{
label6.Text = "HSN";
}
}
private void DisplaySetting()
{
if (Feature.Available("Item Name before Packing") == "Yes")
{
ansGridView1.Columns["Description"].DisplayIndex = 1;
ansGridView1.Columns["Packing"].DisplayIndex = 2;
}
else
{
ansGridView1.Columns["Packing"].DisplayIndex = 1;
ansGridView1.Columns["Description"].DisplayIndex = 2;
}
ansGridView1.Columns["rate"].DisplayIndex = 3;
ansGridView1.Columns["SNo"].DisplayIndex = 0;
ansGridView1.Columns["SNo"].ReadOnly = true;
ansGridView1.Columns["Ac_id"].Visible = false;
ansGridView1.Columns["Des_id"].Visible = false;
ansGridView1.Columns["Packing"].ReadOnly = true;
ansGridView1.Columns["Description"].ReadOnly = true;
}
private void frm_tax_Load(object sender, EventArgs e)
{
tabPage1.Text = "Within " + funs.Select_state_nm(Database.CompanyState_id);
if (Feature.Available("Taxation Applicable") == "VAT")
{
groupBox1.Text = "Commodity Code";
groupBox10.Text = "Tax Category Name";
label2.Text = "VAT";
label3.Text = "SAT";
label12.Text = "SAT";
label13.Text = "VAT";
label18.Text = "CST";
label25.Text = "CST";
label4.Text = "Service Tax";
label17.Text = "Service Tax";
label11.Text = "Service Tax";
label20.Text = "Service Tax";
comboBox2.Items.Add("VAT");
comboBox2.Items.Add("NON-VAT");
}
else
{
groupBox1.Text = "HSN Code";
groupBox10.Text = "HSN Name";
label2.Text = "CGST";
label3.Text = "SGST";
label12.Text = "SGST";
label13.Text = "CGST";
label18.Text = "IGST";
label25.Text = "IGST";
label4.Text = "Cess (If Any)";
label17.Text = "Cess (If Any)";
label11.Text = "Cess (If Any)";
label20.Text = "Cess (If Any)";
comboBox2.Items.Add("Goods");
comboBox2.Items.Add("Services");
}
SideFill();
}
public void LoadData(string str, string typ)
{
gstr = str;
gtyp = typ;
if (typ != "New")
{
textBox1.Enabled = false;
}
textBox1.Text = funs.Select_ac_nm(str);
dtName = "PARTYRATE";
dtCustSuppRate = new DataTable(dtName);
Database.GetSqlData("SELECT PARTYRATE.Ac_id, DESCRIPTION.Des_id, DESCRIPTION.Description, DESCRIPTION.Pack AS Packing,partyrate.Rate FROM (PARTYRATE INNER JOIN DESCRIPTION ON PARTYRATE.Des_id = DESCRIPTION.Des_id) WHERE (((PARTYRATE.Ac_id)='" + str + "'))", dtCustSuppRate);
if (textBox1.Text == "")
{
return;
}
ansGridView1.Rows.Clear();
for (int i = 0; i < dtCustSuppRate.Rows.Count; i++)
{
ansGridView1.CurrentCell = ansGridView1.Rows[0].Cells[0];
ansGridView1.Rows.Add();
ansGridView1.Rows[i].Cells["SNo"].Value = (i + 1);
ansGridView1.Rows[i].Cells["Ac_id"].Value = funs.Select_ac_id(textBox1.Text);
ansGridView1.Rows[i].Cells["Des_id"].Value = dtCustSuppRate.Rows[i]["Des_id"].ToString();
ansGridView1.Rows[i].Cells["Description"].Value = dtCustSuppRate.Rows[i]["Description"].ToString();
ansGridView1.Rows[i].Cells["Packing"].Value = dtCustSuppRate.Rows[i]["Packing"].ToString();
ansGridView1.Rows[i].Cells["Rate"].Value = double.Parse(dtCustSuppRate.Rows[i]["Rate"].ToString());
}
if (Feature.Available("Item Name before Packing") == "Yes")
{
ansGridView1.CurrentCell = ansGridView1.Rows[0].Cells["Description"];
}
else
{
ansGridView1.CurrentCell = ansGridView1.Rows[0].Cells["Packing"];
}
}
private void frm_shiptodet_Load(object sender, EventArgs e)
{
if (Feature.Available("Taxation Applicable") == "VAT")
{
label7.Text = "TIN";
}
else
{
label7.Text = "GSTN";
}
textBox10.Text = funs.Select_city_name(gcityid);
textBox11.Text = gPincode;
textBox14.Text = funs.Select_ac_nm(shipto);
textBox1.Text = gPrintname;
textBox2.Text = gAddress1;
textBox3.Text = gAddress2;
textBox4.Text = gContact;
textBox5.Text = gEmail;
textBox6.Text = gTin;
textBox7.Text = gState;
textBox9.Text = gAadhar;
textBox8.Text = gPAN;
}
private void textBox8_KeyPress(object sender, KeyPressEventArgs e)
{
DataTable dtcombo = new DataTable();
dtcombo.Columns.Add("PriceList", typeof(string));
if (Feature.Available("Name of PriceList1") != "Purchase Rate")
{
dtcombo.Rows.Add();
dtcombo.Rows[dtcombo.Rows.Count - 1][0] = Feature.Available("Name of PriceList1");
}
dtcombo.Rows.Add();
dtcombo.Rows[dtcombo.Rows.Count - 1][0] = Feature.Available("Name of PriceList2");
dtcombo.Rows.Add();
dtcombo.Rows[dtcombo.Rows.Count - 1][0] = Feature.Available("Name of PriceList3");
dtcombo.Rows.Add();
dtcombo.Rows[dtcombo.Rows.Count - 1][0] = Feature.Available("Name of PriceList4");
dtcombo.Rows.Add();
dtcombo.Rows[dtcombo.Rows.Count - 1][0] = Feature.Available("Name of PriceList5");
dtcombo.Rows.Add();
dtcombo.Rows[dtcombo.Rows.Count - 1][0] = Feature.Available("Name of PriceList6");
dtcombo.Rows.Add();
dtcombo.Rows[dtcombo.Rows.Count - 1][0] = "MRP";
textBox8.Text = SelectCombo.ComboDt(this, dtcombo, 0);
}
public static bool CommandExecutor(String str, bool syn = true)
{
OpenConnection();
if (DatabaseType == "sql")
{
sqlcmd = new SqlCommand(str, SqlConn);
try
{
sqlcmd.Transaction = sqlTran;
if (sqlcmd.ExecuteScalar() != null)
{
sqlcmd.ExecuteNonQuery();
}
if (Feature.Available("Active Syncronizer").ToUpper() == "YES" && syn == true)
{
DataTable dtSync = new DataTable("Syncronizer");
dtSync.Rows.Add();
dtSync.Columns.Add("Id", typeof(int));
dtSync.Columns.Add("Location", typeof(string));
dtSync.Columns.Add("query", typeof(string));
dtSync.Columns.Add("Updated", typeof(Boolean));
dtSync.Columns.Add("timespan", typeof(string));
DataTable dtCount = new DataTable();
Database.GetSqlData("select count(*) from Syncronizer", dtCount);
dtSync.Rows[dtSync.Rows.Count - 1]["Location"] = Database.LocationId;
dtSync.Rows[dtSync.Rows.Count - 1]["query"] = str;
dtSync.Rows[dtSync.Rows.Count - 1]["Updated"] = false;
dtSync.Rows[dtSync.Rows.Count - 1]["timespan"] = DateTime.Now;
SqlDataAdapter da1 = new SqlDataAdapter("select * from " + dtSync.TableName, SqlConn);
SqlCommandBuilder cb1 = new SqlCommandBuilder();
cb1.QuotePrefix = "[";
cb1.QuoteSuffix = "]";
cb1.DataAdapter = da1;
da1.SelectCommand.Transaction = sqlTran;
da1.Update(dtSync);
}
return(true);
}
catch (SqlException ex)
{
if (ex.Message.Substring(0, 5) != "Table" && (ex.Message != "Primary key already exists.") && ex.Message.Substring(0, 5) != "Field" && ex.Message.Substring(0, 10) == "The change")
{
}
return(false);
}
}
else
{
accesscmd = new OleDbCommand(str, AccessConn);
try
{
accesscmd.Transaction = AccessTran;
accesscmd.ExecuteNonQuery();
return(true);
}
catch (OleDbException ex)
{
if (ex.Message.Substring(0, 5) != "Table" && (ex.Message != "Primary key already exists.") && ex.Message.Substring(0, 5) != "Field" && ex.Message.Substring(0, 10) == "The change")
{
// System.Windows.Forms.MessageBox.Show(ex.Message);
}
return(false);
}
}
CloseConnection();
}
public static void SaveData(DataTable dt)
{
if (dt == null)
{
return;
}
if (DatabaseType == "sql")
{
SqlDataAdapter da = new SqlDataAdapter("select * from " + dt.TableName, SqlConn);
SqlCommandBuilder cb = new SqlCommandBuilder();
cb.ConflictOption = ConflictOption.CompareRowVersion;
cb.QuotePrefix = "[";
cb.QuoteSuffix = "]";
cb.DataAdapter = da;
da.SelectCommand.Transaction = sqlTran;
if (Feature.Available("Active Syncronizer").ToUpper() == "YES")
{
for (int i = 0; i < dt.Rows.Count; i++)
{
string Sql = "";
if (dt.Rows[i].RowState == DataRowState.Added)
{
Sql = cb.GetInsertCommand().CommandText;
for (int j = cb.GetInsertCommand().Parameters.Count - 1; j >= 0; j--)
{
if (cb.GetInsertCommand().Parameters[j].DbType.ToString() == "Double" || cb.GetInsertCommand().Parameters[j].DbType.ToString() == "Currency" || cb.GetInsertCommand().Parameters[j].DbType.ToString() == "Int32")
{
if (dt.Rows[i][cb.GetInsertCommand().Parameters[j].SourceColumn].ToString() != "")
{
Sql = Sql.Replace(cb.GetInsertCommand().Parameters[j].ParameterName, dt.Rows[i][cb.GetInsertCommand().Parameters[j].SourceColumn].ToString());
}
else
{
Sql = Sql.Replace(cb.GetInsertCommand().Parameters[j].ParameterName, "0");
}
}
else if (cb.GetInsertCommand().Parameters[j].DbType.ToString() == "DateTime")
{
Sql = Sql.Replace(cb.GetInsertCommand().Parameters[j].ParameterName, "'" + DateTime.Parse(dt.Rows[i][cb.GetInsertCommand().Parameters[j].SourceColumn].ToString()).ToString("dd-MMM-yyyy") + "'");
}
else
{
Sql = Sql.Replace(cb.GetInsertCommand().Parameters[j].ParameterName, "'" + dt.Rows[i][cb.GetInsertCommand().Parameters[j].SourceColumn].ToString().Replace("'", "'+char(39)+'") + "'");
}
}
}
else if (dt.Rows[i].RowState == DataRowState.Modified)
{
Sql = cb.GetUpdateCommand().CommandText;
for (int j = cb.GetUpdateCommand().Parameters.Count - 1; j >= 0; j--)
{
if (cb.GetUpdateCommand().Parameters[j].GetType().FullName == "System.Double" || cb.GetUpdateCommand().Parameters[j].GetType().FullName == "System.Int32")
{
if (j >= dt.Columns.Count)
{
Sql = Sql.Replace(cb.GetUpdateCommand().Parameters[j].ParameterName, dt.Rows[i][cb.GetUpdateCommand().Parameters[j].SourceColumn, DataRowVersion.Original].ToString());
}
else
{
Sql = Sql.Replace(cb.GetUpdateCommand().Parameters[j].ParameterName, dt.Rows[i][cb.GetUpdateCommand().Parameters[j].SourceColumn].ToString());
}
}
else
{
if (j >= dt.Columns.Count - 1)
{
Sql = Sql.Replace(cb.GetUpdateCommand().Parameters[j].ParameterName, "'" + dt.Rows[i][cb.GetUpdateCommand().Parameters[j].SourceColumn, DataRowVersion.Original].ToString() + "'");
}
else
{
Sql = Sql.Replace(cb.GetUpdateCommand().Parameters[j].ParameterName, "'" + dt.Rows[i][cb.GetUpdateCommand().Parameters[j].SourceColumn].ToString() + "'");
}
}
}
}
else if (dt.Rows[i].RowState == DataRowState.Deleted)
{
Sql = cb.GetDeleteCommand().CommandText;
for (int j = cb.GetDeleteCommand().Parameters.Count - 1; j >= 0; j--)
{
if (cb.GetDeleteCommand().Parameters[j].GetType().FullName == "System.Double" || cb.GetDeleteCommand().Parameters[j].GetType().FullName == "System.Int32")
{
Sql = Sql.Replace(cb.GetDeleteCommand().Parameters[j].ParameterName, dt.Rows[i][cb.GetDeleteCommand().Parameters[j].SourceColumn, DataRowVersion.Original].ToString());
}
else
{
Sql = Sql.Replace(cb.GetDeleteCommand().Parameters[j].ParameterName, "'" + dt.Rows[i][cb.GetDeleteCommand().Parameters[j].SourceColumn, DataRowVersion.Original].ToString() + "'");
}
}
}
//dt.Rows[i][j].ToString() == "" ? null :
//Database.CommandExecutor("insert into Syncronizer(Location,query) values('" + Database.terminal + "','" + Sql + "')");
if (dt.Rows[i].RowState != DataRowState.Unchanged)
{
DataTable dtSync = new DataTable("Syncronizer");
dtSync.Rows.Add();
dtSync.Columns.Add("Id", typeof(int));
dtSync.Columns.Add("Location", typeof(string));
dtSync.Columns.Add("query", typeof(string));
dtSync.Columns.Add("Updated", typeof(Boolean));
dtSync.Columns.Add("timespan", typeof(string));
DataTable dtCount = new DataTable();
Database.GetSqlData("select count(*) from Syncronizer", dtCount);
dtSync.Rows[dtSync.Rows.Count - 1]["Location"] = Database.LocationId;
dtSync.Rows[dtSync.Rows.Count - 1]["query"] = Sql;
dtSync.Rows[dtSync.Rows.Count - 1]["Updated"] = false;
dtSync.Rows[dtSync.Rows.Count - 1]["timespan"] = DateTime.Now;
SqlDataAdapter da1 = new SqlDataAdapter("select * from " + dtSync.TableName, SqlConn);
SqlCommandBuilder cb1 = new SqlCommandBuilder();
cb1.QuotePrefix = "[";
cb1.QuoteSuffix = "]";
cb1.DataAdapter = da1;
da1.SelectCommand.Transaction = sqlTran;
da1.Update(dtSync);
}
}
}
da.Update(dt);
}
else
{
OleDbDataAdapter da = new OleDbDataAdapter("select * from " + dt.TableName, AccessConn);
OleDbCommandBuilder cb = new OleDbCommandBuilder();
cb.ConflictOption = ConflictOption.CompareRowVersion;
cb.QuotePrefix = "[";
cb.QuoteSuffix = "]";
cb.DataAdapter = da;
da.SelectCommand.Transaction = AccessTran;
if (Feature.Available("Active Syncronizer").ToUpper() == "YES")
{
for (int i = 0; i < dt.Rows.Count; i++)
{
string Sql = "";
if (dt.Rows[i].RowState == DataRowState.Added)
{
Sql = cb.GetInsertCommand().CommandText;
for (int j = cb.GetInsertCommand().Parameters.Count - 1; j >= 0; j--)
{
if (cb.GetInsertCommand().Parameters[j].GetType().FullName == "System.Double" || cb.GetInsertCommand().Parameters[j].GetType().FullName == "System.Int32")
{
Sql = Sql.Replace(cb.GetInsertCommand().Parameters[j].ParameterName, dt.Rows[i][cb.GetInsertCommand().Parameters[j].SourceColumn].ToString());
}
else
{
Sql = Sql.Replace(cb.GetInsertCommand().Parameters[j].ParameterName, "'" + dt.Rows[i][cb.GetInsertCommand().Parameters[j].SourceColumn].ToString() + "'");
}
}
}
else if (dt.Rows[i].RowState == DataRowState.Modified)
{
Sql = cb.GetUpdateCommand().CommandText;
for (int j = cb.GetUpdateCommand().Parameters.Count - 1; j >= 0; j--)
{
if (cb.GetUpdateCommand().Parameters[j].GetType().FullName == "System.Double" || cb.GetUpdateCommand().Parameters[j].GetType().FullName == "System.Int32")
{
if (j >= dt.Columns.Count)
{
Sql = Sql.Replace(cb.GetUpdateCommand().Parameters[j].ParameterName, dt.Rows[i][cb.GetUpdateCommand().Parameters[j].SourceColumn, DataRowVersion.Original].ToString());
}
else
{
Sql = Sql.Replace(cb.GetUpdateCommand().Parameters[j].ParameterName, dt.Rows[i][cb.GetUpdateCommand().Parameters[j].SourceColumn].ToString());
}
}
else
{
if (j >= dt.Columns.Count)
{
Sql = Sql.Replace(cb.GetUpdateCommand().Parameters[j].ParameterName, "'" + dt.Rows[i][cb.GetUpdateCommand().Parameters[j].SourceColumn, DataRowVersion.Original].ToString() + "'");
}
else
{
Sql = Sql.Replace(cb.GetUpdateCommand().Parameters[j].ParameterName, "'" + dt.Rows[i][cb.GetUpdateCommand().Parameters[j].SourceColumn].ToString() + "'");
}
}
}
}
else if (dt.Rows[i].RowState == DataRowState.Deleted)
{
Sql = cb.GetDeleteCommand().CommandText;
for (int j = cb.GetDeleteCommand().Parameters.Count - 1; j >= 0; j--)
{
if (cb.GetDeleteCommand().Parameters[j].GetType().FullName == "System.Double" || cb.GetDeleteCommand().Parameters[j].GetType().FullName == "System.Int32")
{
Sql = Sql.Replace(cb.GetDeleteCommand().Parameters[j].ParameterName, dt.Rows[i][cb.GetDeleteCommand().Parameters[j].SourceColumn, DataRowVersion.Original].ToString());
}
else
{
Sql = Sql.Replace(cb.GetDeleteCommand().Parameters[j].ParameterName, "'" + dt.Rows[i][cb.GetDeleteCommand().Parameters[j].SourceColumn, DataRowVersion.Original].ToString() + "'");
}
}
}
DataTable dtSync = new DataTable("Syncronizer");
dtSync.Rows.Add();
dtSync.Columns.Add("Location", typeof(string));
dtSync.Columns.Add("query", typeof(string));
dtSync.Columns.Add("Updated", typeof(Boolean));
dtSync.Columns.Add("timespan", typeof(string));
DataTable dtCount = new DataTable();
Database.GetSqlData("select count(*) from Syncronizer", dtCount);
dtSync.Rows[dtSync.Rows.Count - 1]["Location"] = Database.LocationId;
dtSync.Rows[dtSync.Rows.Count - 1]["query"] = Sql;
dtSync.Rows[dtSync.Rows.Count - 1]["Updated"] = false;
dtSync.Rows[dtSync.Rows.Count - 1]["timespan"] = DateTime.Now;
OleDbDataAdapter da1 = new OleDbDataAdapter("select * from " + dtSync.TableName, AccessConn);
OleDbCommandBuilder cb1 = new OleDbCommandBuilder();
cb1.QuotePrefix = "[";
cb1.QuoteSuffix = "]";
cb1.DataAdapter = da1;
da1.SelectCommand.Transaction = AccessTran;
da1.Update(dtSync);
}
}
da.Update(dt);
}
}
void btn_Click(object sender, EventArgs e)
{
Button tbtn = (Button)sender;
string name = tbtn.Name.ToString();
if (name == "Receipt")
{
frmMasterVou frm = new frmMasterVou();
frm.LoadData("Receipt", "Receipt Vouchers");
frm.MdiParent = this.MdiParent;
frm.Show();
}
else if (name == "Contra")
{
frmMasterVou frm = new frmMasterVou();
frm.MdiParent = this.MdiParent;
frm.LoadData("Contra", "Contra Vouchers");
frm.Show();
}
else if (name == "Stock Jou")
{
frmMasterVou frm = new frmMasterVou();
frm.MdiParent = this.MdiParent;
frm.LoadData("Transfer", "Stock Journal Vouchers");
frm.Show();
}
else if (name == "Payment")
{
frmMasterVou frm = new frmMasterVou();
frm.MdiParent = this.MdiParent;
frm.LoadData("Payment", "Payment Vouchers");
frm.Show();
}
else if (name == "Purchase")
{
frmMasterVou frm = new frmMasterVou();
frm.MdiParent = this.MdiParent;
frm.LoadData("Purchase", "Purchase Vouchers");
frm.Show();
}
else if (name == "Sale")
{
frmMasterVou frm = new frmMasterVou();
frm.MdiParent = this.MdiParent;
frm.LoadData("Sale", "Sale Vouchers");
// frm.ProgrBar = toolStripProgressBar1;
frm.Show();
}
else if (name == "Purchase Return")
{
frmMasterVou frm = new frmMasterVou();
frm.MdiParent = this.MdiParent;
frm.LoadData("P Return", "Purchase Return Vouchers");
frm.Show();
}
else if (name == "Sale Return")
{
frmMasterVou frm = new frmMasterVou();
frm.MdiParent = this.MdiParent;
frm.LoadData("Return", "Sale Return Vouchers");
frm.Show();
}
else if (name == "Journal")
{
frmMasterVou frm = new frmMasterVou();
frm.MdiParent = this.MdiParent;
frm.LoadData("Journal", "Journal Vouchers");
frm.Show();
}
else if (name == "Control Room")
{
frmMaster frm = new frmMaster();
frm.LoadData("Control Room", "Control Room");
frm.MdiParent = this.MdiParent;
frm.Show();
}
else if (name == "Vouchers Confg")
{
frmMaster frm = new frmMaster();
frm.LoadData("TransactionSetup", "TransactionSetup");
frm.MdiParent = this.MdiParent;
frm.Show();
}
else if (name == "Ledger")
{
Report gg = new Report();
string strCombo = funs.GetStrComboled("*");
char cg = 'a';
string selected = SelectCombo.ComboKeypress(this, cg, strCombo, "", 1);
if (Feature.Available("Ledger with Remarks") == "Yes")
{
gg.LedgerRemark(Database.stDate, Database.ldate, selected);
}
else
{
gg.LedgerNew(Database.stDate, Database.ldate, selected);
}
gg.MdiParent = this.MdiParent;
gg.Show();
}
else if (name == "Godown Stock")
{
Form1 frm = new Form1();
frm.ReportName = "StockSummary";
frm.Show();
}
}
void btn_Click(object sender, EventArgs e)
{
Button tbtn = (Button)sender;
string name = tbtn.Name.ToString();
if (name == "send")
{
if (Feature.Available("Send Mail") == "Yes")
{
string smtpAddress = "";
string emailFrom = "";
string password = "";
int port = 0;
bool Credentials = false;
bool EnableSsl = true;
DataTable dtFirmMailInfo = new DataTable();
Database.GetSqlData("select * from mailer", dtFirmMailInfo);
if (dtFirmMailInfo.Rows.Count > 0)
{
smtpAddress = dtFirmMailInfo.Rows[0]["smtp"].ToString();
emailFrom = dtFirmMailInfo.Rows[0]["emailid"].ToString();
password = dtFirmMailInfo.Rows[0]["password"].ToString();
port = int.Parse(dtFirmMailInfo.Rows[0]["port"].ToString());
Credentials = bool.Parse(dtFirmMailInfo.Rows[0]["Credentials"].ToString());
EnableSsl = bool.Parse(dtFirmMailInfo.Rows[0]["EnableSsl"].ToString());
}
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
if (bool.Parse(dataGridView1.Rows[i].Cells["check"].Value.ToString()) == true && (dataGridView1.Rows[i].Cells["Emailid"].Value.ToString() != "None" && dataGridView1.Rows[i].Cells["Emailid"].Value.ToString() != ""))
{
SmtpClient client = new SmtpClient(smtpAddress, port);
client.UseDefaultCredentials = Credentials;
client.EnableSsl = EnableSsl;
client.Credentials = new NetworkCredential(emailFrom, password);
string accnam = dataGridView1.Rows[i].Cells["cname"].Value.ToString();
string emailTo = dataGridView1.Rows[i].Cells["Emailid"].Value.ToString();
string subject = "Customer Report";
string body = "Dear <b>" + accnam + "</b>,<br/>";
body += "Please find enclosed Bills Details.<br/>";
body += "Your Balance is: <b>" + funs.accbal(funs.Select_ac_id(accnam)) + "</b>";
body += "</b><br/><br/>Regards";
body += "<br/><b>" + Database.fname;
MailMessage mail = new MailMessage();
mail.From = new MailAddress(emailFrom);
mail.To.Add(emailTo);
mail.Subject = subject;
mail.Body = body;
mail.IsBodyHtml = true;
try
{
Report gg = new Report();
gg.CustomerDetailBillWise(dateTimePicker1.Value, dateTimePicker2.Value, accnam);
gg.ExportToPdf("System\\Bill-" + accnam + ".pdf");
gg.Close();
gg.Dispose();
System.Net.Mime.ContentType contype = new System.Net.Mime.ContentType();
mail.Attachments.Add(new Attachment("System\\Bill-" + accnam + ".pdf", contype));
object userState = mail;
status = "Mail Sent Successfully";
client.SendCompleted += new SendCompletedEventHandler(SmtpClient_OnCompleted);
client.SendAsync(mail, userState);
}
catch (Exception ex)
{
status = "Mail Not Sent";
}
DataTable dtemail = new DataTable("EmailLOG");
Database.GetSqlData("select * from EmailLOG where id=0", dtemail);
dtemail.Rows.Add();
dtemail.Rows[0]["AccName"] = accnam;
dtemail.Rows[0]["Email"] = emailTo;
dtemail.Rows[0]["Status"] = status;
dtemail.Rows[0]["SDate"] = DateTime.Now.ToString("dd-MMM-yyyy");
dtemail.Rows[0]["STime"] = DateTime.Now.ToString("HH:mm");
Database.SaveData(dtemail);
}
}
this.Close();
this.Dispose();
}
}
else if (name == "quit")
{
this.Close();
this.Dispose();
}
}
private void button3_Click(object sender, EventArgs e)
{
this.Fld1 = textBox14.Text;
this.Fld2 = textBox1.Text;
this.chk1 = checkBox1.Checked;
this.chk2 = checkBox2.Checked;
this.chk3 = checkBox3.Checked;
this.chk4 = checkBox4.Checked;
this.chk5 = checkBox5.Checked;
this.chk6 = checkBox6.Checked;
this.chk7 = checkBox7.Checked;
this.chk8 = checkBox8.Checked;
this.rd1 = radioButton1.Checked;
this.rd2 = radioButton2.Checked;
this.rd3 = radioButton3.Checked;
this.dt1 = dateTimePicker1.Value;
this.dt2 = dateTimePicker2.Value;
string str = "";
string type = "";
if (radioButton1.Checked == true)
{
type = "Purchase";
}
else if (radioButton2.Checked == true)
{
type = "Sale";
}
else if (radioButton3.Checked == true)
{
type = "JobWork";
}
string rstatus = "";
string state = "";
if (type == "Sale")
{
str = " And (Vouchertype.Type='Sale' or Vouchertype.type='Return')";
str += " And (VOUCHERINFO.vdate >=" + access_sql.Hash + dateTimePicker1.Value.Date.ToString(Database.dformat) + access_sql.Hash + " And VOUCHERINFO.vdate <=" + access_sql.Hash + dateTimePicker2.Value.Date.ToString(Database.dformat) + access_sql.Hash + " )";
}
else if (type == "Purchase")
{
str = " And (Vouchertype.Type='Purchase' or Vouchertype.type='P Return') ";
str += " And (VOUCHERINFO.vdate >=" + access_sql.Hash + dateTimePicker1.Value.Date.ToString(Database.dformat) + access_sql.Hash + " And VOUCHERINFO.vdate <=" + access_sql.Hash + dateTimePicker2.Value.Date.ToString(Database.dformat) + access_sql.Hash + " )";
}
else if (type == "JobWork")
{
str = " And (Vouchertype.Type='JIssue' or Vouchertype.type='JReceive')";
str += " And (VOUCHERINFO.vdate >=" + access_sql.Hash + dateTimePicker1.Value.Date.ToString(Database.dformat) + access_sql.Hash + " And VOUCHERINFO.vdate <=" + access_sql.Hash + dateTimePicker2.Value.Date.ToString(Database.dformat) + access_sql.Hash + " )";
}
if (checkBox4.Checked == true && checkBox5.Checked == true)
{
state = "All";
}
else if (checkBox4.Checked == true && checkBox5.Checked == false)
{
state = "Intra State";
str += " And State.Sname='" + funs.Select_state_nm(Database.CompanyState_id) + "'";
}
else if (checkBox4.Checked == false && checkBox5.Checked == true)
{
state = "Inter State";
str += " And State.Sname<>'" + funs.Select_state_nm(Database.CompanyState_id) + "'";
}
if (checkBox1.Checked == true && checkBox2.Checked == false && checkBox3.Checked == false)
{
str += " And ACCOUNT.RegStatus='Unregistered' ";
}
else if (checkBox1.Checked == false && checkBox2.Checked == true && checkBox3.Checked == false)
{
str += " And ACCOUNT.RegStatus='Regular Registration' ";
}
else if (checkBox1.Checked == false && checkBox2.Checked == false && checkBox3.Checked == true)
{
str += " And ACCOUNT.RegStatus='Composition Dealer' ";
}
else if (checkBox1.Checked == true && checkBox2.Checked == true && checkBox3.Checked == false)
{
str += " And (ACCOUNT.RegStatus='Unregistered' or ACCOUNT.RegStatus='Regular Registration') ";
}
else if (checkBox1.Checked == false && checkBox2.Checked == true && checkBox3.Checked == true)
{
str += " And (ACCOUNT.RegStatus='Composition Dealer' or ACCOUNT.RegStatus='Regular Registration') ";
}
else if (checkBox1.Checked == true && checkBox2.Checked == false && checkBox3.Checked == true)
{
str += " And (ACCOUNT.RegStatus='Unregistered' or ACCOUNT.RegStatus='Composition Dealer') ";
}
else if (checkBox1.Checked == true && checkBox2.Checked == true && checkBox3.Checked == true)
{
str += " And (ACCOUNT.RegStatus='Unregistered' or ACCOUNT.RegStatus='Composition Dealer' or ACCOUNT.RegStatus='Regular Registration') ";
}
if (checkBox6.Checked == true && checkBox7.Checked == true)
{
}
else if (checkBox6.Checked == true && checkBox7.Checked == false)
{
str += " And VOUCHERDET.TotTaxPer<>0 ";
}
else if (checkBox6.Checked == false && checkBox7.Checked == true)
{
str += " And VOUCHERDET.TotTaxPer=0 ";
}
else if (checkBox8.Checked == true)
{
str += " And VOUCHERDET.TotTaxPer=" + textBox1.Text + " ";
}
if (textBox14.Text != "")
{
if (Feature.Available("GST Reports on ShipTo") == "Yes")
{
str += " And (VOUCHERINFO.Shipto='" + textBox14.Text + "') ";
}
else
{
str += " And (ACCOUNT.Name='" + textBox14.Text + "') ";
}
}
if (calledindirect == false)
{
Report gg = new Report();
gg.AllGstReport(dateTimePicker1.Value, dateTimePicker2.Value, str, type);
gg.Fld1 = textBox14.Text;
gg.Fld2 = textBox1.Text;
gg.chk1 = checkBox1.Checked;
gg.chk2 = checkBox2.Checked;
gg.chk3 = checkBox3.Checked;
gg.chk4 = checkBox4.Checked;
gg.chk5 = checkBox5.Checked;
gg.chk6 = checkBox6.Checked;
gg.chk7 = checkBox7.Checked;
gg.chk8 = checkBox8.Checked;
gg.rd1 = radioButton1.Checked;
gg.rd2 = radioButton2.Checked;
gg.rd3 = radioButton3.Checked;
gg.dt1 = dateTimePicker1.Value;
gg.dt2 = dateTimePicker2.Value;
gg.MdiParent = this.MdiParent;
gg.Show();
}
else
{
this.Close();
this.Dispose();
}
}
private void FillJSONObj()
{
ewbGen.supplyType = "O";
ewbGen.transactionType = 1;
ewbGen.subSupplyType = "1";
ewbGen.subSupplyDesc = "";
ewbGen.docType = "INV";
ewbGen.docNo = dtvou.Rows[0]["Invoiceno"].ToString();
ewbGen.docDate = DateTime.Parse(dtvou.Rows[0]["vdate"].ToString()).ToString("dd/MM/yyyy").Replace('-', '/');
ewbGen.fromGstin = dtcompany.Rows[0]["Tin_no"].ToString();
ewbGen.fromTrdName = dtcompany.Rows[0]["Name"].ToString();
ewbGen.fromAddr1 = dtcompany.Rows[0]["Address1"].ToString();
ewbGen.fromAddr2 = dtcompany.Rows[0]["Address2"].ToString();
ewbGen.fromPlace = funs.Select_city_name(dtcompany.Rows[0]["City_id"].ToString());
ewbGen.fromPincode = int.Parse(dtcompany.Rows[0]["Pincode"].ToString());
ewbGen.fromStateCode = int.Parse(funs.Select_state_GST(funs.Select_state_nm(dtcompany.Rows[0]["Cstate_id"].ToString())));
ewbGen.actFromStateCode = int.Parse(funs.Select_state_GST(funs.Select_state_nm(dtcompany.Rows[0]["Cstate_id"].ToString())));
if (dtvou.Rows[0]["transDocNo"].ToString() == "")
{
ewbGen.transDocNo = "";
ewbGen.transDocDate = "";
}
else
{
ewbGen.transDocNo = dtvou.Rows[0]["transDocNo"].ToString();
ewbGen.transDocDate = DateTime.Parse(dtvou.Rows[0]["transDocDate"].ToString()).ToString("dd/MM/yyyy").Replace('-', '/');
}
//if (dtvou.Rows[0]["transDocDate"].ToString() == "")
//{
// ewbGen.transDocDate = "";
//}
//else
//{
//}
//to
if (funs.Select_TIN(dtvou.Rows[0]["Party"].ToString()) == "0" || funs.Select_TIN(dtvou.Rows[0]["Party"].ToString()) == "")
{
ewbGen.toGstin = "URP";
}
else
{
ewbGen.toGstin = funs.Select_TIN(dtvou.Rows[0]["Party"].ToString());
}
ewbGen.toTrdName = dtvou.Rows[0]["Party"].ToString();
ewbGen.toAddr1 = funs.Select_Address1(dtvou.Rows[0]["Party"].ToString());
ewbGen.toAddr2 = funs.Select_Address2(dtvou.Rows[0]["Party"].ToString());
ewbGen.toPlace = dtvou.Rows[0]["CNAMe"].ToString();
ewbGen.toPincode = int.Parse(funs.Select_Pincode(dtvou.Rows[0]["Party"].ToString()));
//doubt
ewbGen.toStateCode = int.Parse(funs.Select_state_GST(dtvou.Rows[0]["billtostate"].ToString()));
if (dtvou.Rows[0]["dispatch_id"].ToString() == "0")
{
ewbGen.dispatchFromTradeName = dtcompany.Rows[0]["Name"].ToString();;
ewbGen.dispatchFromGSTIN = dtcompany.Rows[0]["Tin_no"].ToString();;
}
else
{
ewbGen.dispatchFromTradeName = funs.Select_ac_nm(dtvou.Rows[0]["dispatch_id"].ToString());
ewbGen.dispatchFromGSTIN = funs.Select_TIN(funs.Select_ac_nm(dtvou.Rows[0]["dispatch_id"].ToString()));
}
if (dtvou.Rows[0]["ShiptoTIN"].ToString() == "0" || dtvou.Rows[0]["ShiptoTIN"].ToString() == "")
{
ewbGen.shipToGSTIN = "URP";
}
else
{
ewbGen.shipToGSTIN = dtvou.Rows[0]["ShiptoTIN"].ToString();
}
//ewbGen.shipToGSTIN = ewbGen.toGstin;
ewbGen.shipToTradeName = dtvou.Rows[0]["Shipto"].ToString();;
//shipto state
ewbGen.actToStateCode = int.Parse(funs.Select_state_GST(dtvou.Rows[0]["shiptostate"].ToString()));
long lDistance = 0;
double dDistance = 0;
double.TryParse(dtvou.Rows[0]["distance"].ToString(), out dDistance);
long.TryParse(dDistance.ToString("0"), out lDistance);
ewbGen.totalValue = double.Parse(dtvou.Rows[0]["Taxable"].ToString());
ewbGen.cgstValue = double.Parse(dtvou.Rows[0]["cgst"].ToString());
ewbGen.sgstValue = double.Parse(dtvou.Rows[0]["sgst"].ToString());
ewbGen.igstValue = double.Parse(dtvou.Rows[0]["igst"].ToString());
ewbGen.cessValue = 0;
ewbGen.cessNonAdvolValue = 0;
ewbGen.otherValue = 0;
ewbGen.totInvValue = double.Parse(dtvou.Rows[0]["Invamount"].ToString());
ewbGen.transDistance = lDistance.ToString(); //ASK FROM CLIENT
ewbGen.transporterName = dtvou.Rows[0]["transporter"].ToString();
ewbGen.transporterId = dtvou.Rows[0]["TransporterGSTIN"].ToString(); //ASK
//ewbGen.transDocNo = "";
//ewbGen.transDocDate = "";
//DataTable dtveh = new DataTable();
//Database.GetSqlData("Select * from Transportdetails where IsVehicleNo=" + access_sql.Singlequote + "true" + access_sql.Singlequote, dtveh);
//string fieldname = "";
//if (dtveh.Rows.Count == 1)
//{
// if (dtveh.Rows[0]["FName"].ToString() == "Field1")
// {
// fieldname = "Transport1";
// }
// else if (dtveh.Rows[0]["FName"].ToString() == "Field2")
// {
// fieldname = "Transport2";
// }
// else if (dtveh.Rows[0]["FName"].ToString() == "Field4")
// {
// fieldname = "Grno";
// }
// else if (dtveh.Rows[0]["FName"].ToString() == "Field3")
// {
// fieldname = "DeliveryAt";
// }
// else if (dtveh.Rows[0]["FName"].ToString() == "Field5")
// {
// fieldname = "Transport3";
// }
// else if (dtveh.Rows[0]["FName"].ToString() == "Field6")
// {
// fieldname = "Transport4";
// }
// else if (dtveh.Rows[0]["FName"].ToString() == "Field7")
// {
// fieldname = "Transport5";
// }
// else if (dtveh.Rows[0]["FName"].ToString() == "Field8")
// {
// fieldname = "Transport6";
// }
//}
//if (dtvou.Rows[0][fieldname].ToString() != "")
//{
//if (MessageBox.Show("Vehicle No","Is, It is not your Vehicle No:" + dtvou.Rows[0]["Transport2"].ToString(), MessageBoxButtons.YesNo) == DialogResult.Yes)
//{
ewbGen.vehicleNo = dtvou.Rows[0]["TransVehNo"].ToString();
//}
//else
//{
// return;
//}
//}
//else
//{
// ewbGen.vehicleNo = "";
//}
if (ewbGen.vehicleNo == "")
{
ewbGen.transMode = "";
}
else
{
ewbGen.transMode = "1";
}
ewbGen.vehicleType = "R";
//doubtfirst item hsn code
DataTable dtvou1 = new DataTable();
if (Feature.Available("Eway Bill On Quantity") != "Quantity")
{
Database.GetSqlData("SELECT TAXCATEGORY.Commodity_Code, TAXCATEGORY.Category_Name, Sum(VOUCHERDET.weight) as QtyinLtr, SUM( Voucherdet.Taxabelamount) AS Taxableamt, Voucherdet.rate1, Voucherdet.rate2, Voucherdet.rate3, Voucherdet.rate4, Voucherdet.Rate_Unit FROM Voucherdet LEFT OUTER JOIN TAXCATEGORY ON Voucherdet.Category_Id = TAXCATEGORY.Category_Id WHERE ( Voucherdet.Vi_id = '" + gvid + "') GROUP BY TAXCATEGORY.Commodity_Code, TAXCATEGORY.Category_Name, Voucherdet.rate1, Voucherdet.rate2, Voucherdet.rate3, Voucherdet.rate4, Voucherdet.Rate_Unit ORDER BY Sum(Voucherdet.Taxabelamount) DESC", dtvou1);
}
else
{
Database.GetSqlData("SELECT TAXCATEGORY.Commodity_Code, TAXCATEGORY.Category_Name, Sum(VOUCHERDET.quantity*VOUCHERDET.Pvalue) as QtyinLtr, SUM( Voucherdet.Taxabelamount) AS Taxableamt, Voucherdet.rate1, Voucherdet.rate2, Voucherdet.rate3, Voucherdet.rate4, Voucherdet.Rate_Unit FROM Voucherdet LEFT OUTER JOIN TAXCATEGORY ON Voucherdet.Category_Id = TAXCATEGORY.Category_Id WHERE ( Voucherdet.Vi_id = '" + gvid + "') GROUP BY TAXCATEGORY.Commodity_Code, TAXCATEGORY.Category_Name, Voucherdet.rate1, Voucherdet.rate2, Voucherdet.rate3, Voucherdet.rate4, Voucherdet.Rate_Unit ORDER BY Sum(Voucherdet.Taxabelamount) DESC", dtvou1);
}
//ewbGen.mainHsnCode = int.Parse(dtvou1.Rows[0]["Commodity_Code"].ToString());
ewbGen.itemList = new List <ReqGenEwbPl.ItemListInReqEWBpl>();
for (int i = 0; i < dtvou1.Rows.Count; i++)
{
if (Feature.Available("Eway Bill On Quantity") == "Quantity")
{
if (dtvou1.Rows[i]["Rate_Unit"].ToString().Length < 3)
{
MessageBox.Show(dtvou1.Rows[i]["Category_Name"].ToString() + " GST Unit Should be Minimum in 3 Char : " + dtvou1.Rows[i]["Rate_Unit"].ToString());
// return false;
}
ewbGen.itemList.Add(new ReqGenEwbPl.ItemListInReqEWBpl
{
productName = dtvou1.Rows[i]["Category_Name"].ToString(),
productDesc = dtvou1.Rows[i]["Category_Name"].ToString(),
hsnCode = int.Parse(dtvou1.Rows[i]["Commodity_Code"].ToString()),
quantity = double.Parse(dtvou1.Rows[i]["QtyinLtr"].ToString()),
qtyUnit = dtvou1.Rows[i]["Rate_Unit"].ToString().Substring(0, 3),
taxableAmount = double.Parse(dtvou1.Rows[i]["taxableamt"].ToString()),
sgstRate = double.Parse(dtvou1.Rows[i]["rate2"].ToString()),
cgstRate = double.Parse(dtvou1.Rows[i]["rate1"].ToString()),
igstRate = double.Parse(dtvou1.Rows[i]["rate3"].ToString()),
cessRate = 0,
cessNonAdvol = 0
});
}
else
{
ewbGen.itemList.Add(new ReqGenEwbPl.ItemListInReqEWBpl
{
productName = dtvou1.Rows[i]["Category_Name"].ToString(),
productDesc = dtvou1.Rows[i]["Category_Name"].ToString(),
hsnCode = int.Parse(dtvou1.Rows[i]["Commodity_Code"].ToString()),
quantity = double.Parse(dtvou1.Rows[i]["QtyinLtr"].ToString()),
qtyUnit = Feature.Available("Eway Bill Unit if Quantity in Weight"),
taxableAmount = double.Parse(dtvou1.Rows[i]["taxableamt"].ToString()),
sgstRate = double.Parse(dtvou1.Rows[i]["rate2"].ToString()),
cgstRate = double.Parse(dtvou1.Rows[i]["rate1"].ToString()),
igstRate = double.Parse(dtvou1.Rows[i]["rate3"].ToString()),
cessRate = 0,
cessNonAdvol = 0
});
}
}
// return JsonConvert.SerializeObject(ewbGen);
}
public DisplayData(DataTable dt, DataTable dt2)
{
InitializeComponent();
gdt.Clear();
gdt = dt2;
ansGridView1.Columns.Clear();
ansGridView1.Columns.Add("desc", "Description");
ansGridView1.Columns.Add("qty", "lt/kg");
ansGridView1.Columns.Add("qd", "Discount (Q.D.)");
ansGridView1.Columns.Add("amt", "Amount");
ansGridView1.Columns["desc"].Width = 150;
ansGridView1.Columns["desc"].ReadOnly = true;
ansGridView1.Columns["qty"].ReadOnly = true;
DataTable qty = new DataTable();
qty.Columns.Add("qty", typeof(double));
qty.Columns.Add("desc");
if (gdt.Rows.Count > 0)
{
for (int i = 0; i < gdt.Rows.Count; i++)
{
ansGridView1.Rows.Add();
if (Feature.Available("Company Colour") == "No")
{
ansGridView1.Rows[i].Cells["desc"].Value = gdt.Rows[i]["desc"];
}
else
{
ansGridView1.Rows[i].Cells["desc"].Value = gdt.Rows[i]["name"];
}
ansGridView1.Rows[i].Cells["qty"].Value = gdt.Rows[i]["qty"];
ansGridView1.Rows[i].Cells["qd"].Value = gdt.Rows[i]["dis"];
ansGridView1.Rows[i].Cells["amt"].Value = gdt.Rows[i]["amt"];
}
}
else
{
if (Feature.Available("Company Colour") == "No")
{
DataTable dtItem = dt.DefaultView.ToTable(true, "desc");
for (int i = 0; i < dt.Rows.Count; i++)
{
qty.Rows.Add();
qty.Rows[i]["qty"] = double.Parse(dt.Rows[i]["qty"].ToString()) * double.Parse(dt.Rows[i]["pval"].ToString());
qty.Rows[i]["desc"] = dt.Rows[i]["desc"];
}
qty.AcceptChanges();
double[] gpQty = new double[10];
for (int i = 0; i < dtItem.Rows.Count; i++)
{
gpQty[i] = double.Parse(qty.Compute("Sum(qty)", "desc='" + dtItem.Rows[i]["desc"] + "'").ToString());
}
for (int i = 0; i < dtItem.Rows.Count; i++)
{
ansGridView1.Rows.Add();
ansGridView1.Rows[i].Cells["desc"].Value = dtItem.Rows[i][0].ToString();
ansGridView1.Rows[i].Cells["qty"].Value = gpQty[i];
}
}
else
{
DataTable dtItem = dt.DefaultView.ToTable(true, "name");
for (int i = 0; i < dt.Rows.Count; i++)
{
qty.Rows.Add();
qty.Rows[i]["qty"] = double.Parse(dt.Rows[i]["qty"].ToString()) * double.Parse(dt.Rows[i]["pval"].ToString());
qty.Rows[i]["desc"] = dt.Rows[i]["name"];
}
qty.AcceptChanges();
double[] gpQty = new double[10];
for (int j = 0; j < dtItem.Rows.Count; j++)
{
gpQty[j] = double.Parse(qty.Compute("Sum(qty)", "desc='" + dtItem.Rows[j]["name"].ToString() + "'").ToString());
}
for (int j = 0; j < dtItem.Rows.Count; j++)
{
ansGridView1.Rows.Add();
ansGridView1.Rows[j].Cells["desc"].Value = dtItem.Rows[j][0].ToString();
ansGridView1.Rows[j].Cells["qty"].Value = gpQty[j];
}
}
}
}
private void button2_Click(object sender, EventArgs e)
{
SaveFileDialog savefile = new SaveFileDialog();
if (DialogResult.OK == savefile.ShowDialog())
{
DataTable dt = new DataTable();
obj.HEADER = new clsHEADER();
obj.HEADER.TALLYREQUEST = "Import Data";
obj.BODY = new clsBODY();
obj.BODY.IMPORTDATA = new clsIMPORTDATA();
obj.BODY.IMPORTDATA.REQUESTDESC = new clsREQUESTDESC();
obj.BODY.IMPORTDATA.REQUESTDESC.REPORTNAME = "All Masters";
List <clsTALLYMESSAGE> lstMsg = new List <clsTALLYMESSAGE>();
string branch = "";
if (Feature.Available("Export Vouchers in Tally").ToUpper() != "ALL")
{
branch = " where Branch_id='" + Database.BranchId + "'";
}
if (Database.BMode == "A")
{
Database.GetSqlData("SELECT ACCOUNT.Name, ACCOUNTYPE.Name as ActType, ACCOUNT.Balance AS Bal, State.Sname,ACCOUNT.TIN_number,RegStatus FROM (ACCOUNT INNER JOIN ACCOUNTYPE ON ACCOUNT.Act_id = ACCOUNTYPE.Act_id) LEFT JOIN State ON ACCOUNT.State_id = State.State_id " + branch + " ORDER BY ACCOUNT.Name", dt);
}
else if (Database.BMode == "B")
{
Database.GetSqlData("SELECT ACCOUNT.Name, ACCOUNTYPE.Name as ActType, ACCOUNT.Balance2 AS Bal, State.Sname,ACCOUNT.TIN_number,RegStatus FROM (ACCOUNT INNER JOIN ACCOUNTYPE ON ACCOUNT.Act_id = ACCOUNTYPE.Act_id) LEFT JOIN State ON ACCOUNT.State_id = State.State_id " + branch + " ORDER BY ACCOUNT.Name", dt);
}
else if (Database.BMode == "AB")
{
Database.GetSqlData("SELECT ACCOUNT.Name, ACCOUNTYPE.Name as ActType, ACCOUNT.Balance+ACCOUNT.Balance2 AS Bal, State.Sname,ACCOUNT.TIN_number,RegStatus FROM (ACCOUNT INNER JOIN ACCOUNTYPE ON ACCOUNT.Act_id = ACCOUNTYPE.Act_id) LEFT JOIN State ON ACCOUNT.State_id = State.State_id " + branch + " ORDER BY ACCOUNT.Name", dt);
}
if (dt.Rows.Count == 0)
{
MessageBox.Show("No Record Found..");
return;
}
foreach (DataRow dtr in dt.Rows)
{
clsTALLYMESSAGE tm = new clsTALLYMESSAGE();
tm.LEDGER = new clsLEDGER();
tm.LEDGER.ACTION = "Create";
tm.LEDGER.NAME = dtr["Name"].ToString();
tm.LEDGER.NAME_LIST = new clsNAMELIST();
tm.LEDGER.NAME_LIST.NAME = dtr["Name"].ToString();
if (dtr["RegStatus"].ToString().ToUpper() == "REGULAR REGISTRATION")
{
dtr["RegStatus"] = "Regular";
}
else if (dtr["RegStatus"].ToString().ToUpper() == "COMPOSITION DEALER")
{
dtr["RegStatus"] = "Composition";
}
tm.LEDGER.GSTREGISTRATIONTYPE = dtr["RegStatus"].ToString();
if (dtr["ACTType"].ToString().ToUpper() == "EXPENDITURE ACCOUNT (DIRECT)")
{
dtr["ACTType"] = "Expenses (Direct)";
}
else if (dtr["ACTType"].ToString().ToUpper() == "EXPENDITURE ACCOUNT (INDIRECT )")
{
dtr["ACTType"] = "Expenses (Indirect)";
}
else if (dtr["ACTType"].ToString().ToUpper() == "SECURE LOANS")
{
dtr["ACTType"] = "Secured Loans";
}
else if (dtr["ACTType"].ToString().ToUpper() == "SECURITY & DEPOSITS (ASSETS)")
{
dtr["ACTType"] = "Deposits (Asset)";
}
else if (dtr["ACTType"].ToString().ToUpper() == "SUSPENSE ACCOUNT (TEMPORARY A/C)")
{
dtr["ACTType"] = "Suspense A/c";
}
else if (dtr["ACTType"].ToString().ToUpper() == "PURCHASE ACCOUNTS")
{
dtr["ACTType"] = "Purchase Accounts";
}
else if (dtr["ACTType"].ToString().ToUpper() == "SALES ACCOUNTS")
{
dtr["ACTType"] = "Sales Accounts";
}
else if (dtr["ACTType"].ToString().ToUpper() == "CAPITAL ACCOUNT")
{
dtr["ACTType"] = "Capital Account";
}
else if (dtr["ACTType"].ToString().ToUpper() == "UNSECURE LOANS")
{
dtr["ACTType"] = "Unsecured Loans";
}
else if (dtr["ACTType"].ToString().ToUpper() == "LOAN & ADVANCES (ASSESTS)")
{
dtr["ACTType"] = "Loans & Advances (Asset)";
}
else if (dtr["ACTType"].ToString().ToUpper() == "LOAN (LIABILITIES)")
{
dtr["ACTType"] = "Loans (Liability)";
}
tm.LEDGER.PARENT = dtr["ActType"].ToString();
tm.LEDGER.PARTYGSTIN = dtr["TIN_number"].ToString();
tm.LEDGER.LEDSTATENAME = dtr["Sname"].ToString();
tm.LEDGER.ISBILLWISEON = "No";
tm.LEDGER.AFFECTSSTOCK = "No";
tm.LEDGER.OPENINGBALANCE = -1 * double.Parse(dtr["bal"].ToString());
lstMsg.Add(tm);
}
obj.BODY.IMPORTDATA.REQUESTDATA = lstMsg;
XmlSerializer SerializerObj = new XmlSerializer(obj.GetType());
string path = ".xml";
TextWriter WriteFileStream = new StreamWriter(savefile.FileName + path);
try
{
SerializerObj.Serialize(WriteFileStream, obj);
MessageBox.Show("XML File is created on Location : " + savefile.FileName);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
MessageBox.Show("XML File is not created");
}
WriteFileStream.Close();
}
}
public string CreateLedger(int from, int to)
{
DataTable dtAcc = new DataTable();
string branch = "";
if (Feature.Available("Export Vouchers in Tally").ToUpper() != "ALL")
{
branch = " where Branch_id='" + Database.BranchId + "'";
}
if (Database.BMode == "A")
{
Database.GetSqlData("SELECT ACCOUNT.Name, ACCOUNTYPE.Name, Balance AS Bal,State.Sname,ACCOUNT.TIN_number,RegStatus FROM (ACCOUNT INNER JOIN ACCOUNTYPE ON ACCOUNT.Act_id = ACCOUNTYPE.Act_id) LEFT JOIN State ON ACCOUNT.State_id = State.State_id " + branch + " ORDER BY ACCOUNT.Name", dtAcc);
}
else if (Database.BMode == "B")
{
Database.GetSqlData("SELECT ACCOUNT.Name, ACCOUNTYPE.Name, Balance2 AS Bal,State.Sname,ACCOUNT.TIN_number,RegStatus FROM (ACCOUNT INNER JOIN ACCOUNTYPE ON ACCOUNT.Act_id = ACCOUNTYPE.Act_id ) LEFT JOIN State ON ACCOUNT.State_id = State.State_id " + branch + " ORDER BY ACCOUNT.Name", dtAcc);
}
else if (Database.BMode == "AB")
{
Database.GetSqlData("SELECT ACCOUNT.Name, ACCOUNTYPE.Name, Balance+Balance2 AS Bal,State.Sname,ACCOUNT.TIN_number,RegStatus FROM (ACCOUNT INNER JOIN ACCOUNTYPE ON ACCOUNT.Act_id = ACCOUNTYPE.Act_id) LEFT JOIN State ON ACCOUNT.State_id = State.State_id " + branch + " ORDER BY ACCOUNT.Name", dtAcc);
}
string xmlHeader = "<ENVELOPE><HEADER><TALLYREQUEST>Import Data</TALLYREQUEST></HEADER><BODY><IMPORTDATA><REQUESTDESC><REPORTNAME>All Masters</REPORTNAME></REQUESTDESC><REQUESTDATA>";
string xmlCommand = "";
from = from - 1;
if (to > dtAcc.Rows.Count)
{
to = dtAcc.Rows.Count;
}
for (int i = from; i < to; i++)
{
if (dtAcc.Rows[i]["RegStatus"].ToString().ToUpper() == "REGULAR REGISTRATION")
{
dtAcc.Rows[i]["RegStatus"] = "Regular";
}
else if (dtAcc.Rows[i]["RegStatus"].ToString().ToUpper() == "COMPOSITION DEALER")
{
dtAcc.Rows[i]["RegStatus"] = "Composition";
}
if (dtAcc.Rows[i][1].ToString() == "EXPENDITURE ACCOUNT (Direct)")
{
dtAcc.Rows[i][1] = "Expenses (Direct)";
}
else if (dtAcc.Rows[i][1].ToString() == "EXPENDITURE ACCOUNT (Indirect )")
{
dtAcc.Rows[i][1] = "Expenses (Indirect)";
}
else if (dtAcc.Rows[i][1].ToString() == "SECURE LOANS")
{
dtAcc.Rows[i][1] = "Secured Loans";
}
else if (dtAcc.Rows[i][1].ToString() == "SECURITY & DEPOSITS (Assets)")
{
dtAcc.Rows[i][1] = "Deposits (Asset)";
}
else if (dtAcc.Rows[i][1].ToString() == "SUSPENSE ACCOUNT (Temporary A/C)")
{
dtAcc.Rows[i][1] = "Suspense A/c";
}
else if (dtAcc.Rows[i][1].ToString() == "PURCHASE ACCOUNTS")
{
dtAcc.Rows[i][1] = "Purchase Accounts";
}
else if (dtAcc.Rows[i][1].ToString() == "SALES ACCOUNTS")
{
dtAcc.Rows[i][1] = "Sales Accounts";
}
else if (dtAcc.Rows[i][1].ToString() == "CAPITAL ACCOUNT")
{
dtAcc.Rows[i][1] = "Capital Account";
}
else if (dtAcc.Rows[i][1].ToString() == "UNSECURE LOANS")
{
dtAcc.Rows[i][1] = "Unsecured Loans";
}
else if (dtAcc.Rows[i][1].ToString() == "LOAN & ADVANCES (Assests)")
{
dtAcc.Rows[i][1] = "Loans & Advances (Asset)";
}
xmlCommand += "<TALLYMESSAGE xmlns:UDF='TallyUDF'><LEDGER NAME='" + dtAcc.Rows[i][0].ToString() + "' ACTION='Create'><NAME.LIST><NAME>" + dtAcc.Rows[i][0].ToString() + "</NAME></NAME.LIST> <GSTREGISTRATIONTYPE>" + dtAcc.Rows[i]["RegStatus"].ToString() + "</GSTREGISTRATIONTYPE> <PARENT>" + dtAcc.Rows[i][1].ToString() + "</PARENT><PARTYGSTIN>" + dtAcc.Rows[i]["Tin_Number"].ToString() + "</PARTYGSTIN><LEDSTATENAME>" + dtAcc.Rows[i]["Sname"].ToString() + "</LEDSTATENAME><ISBILLWISEON>No</ISBILLWISEON><AFFECTSSTOCK>No</AFFECTSSTOCK><OPENINGBALANCE>" + -1 * double.Parse(dtAcc.Rows[i][2].ToString()) + "</OPENINGBALANCE><USEFORVAT>No </USEFORVAT><TAXCLASSIFICATIONNAME/><TAXTYPE/><RATEOFTAXCALCULATION/></LEDGER></TALLYMESSAGE>";
// xmlCommand += "<TALLYMESSAGE xmlns:UDF='TallyUDF'><LEDGER NAME='" + dtAcc.Rows[i][0].ToString() + "' ACTION='Create'><NAME.LIST><NAME>" + dtAcc.Rows[i][0].ToString() + "</NAME></NAME.LIST><PARENT>" + dtAcc.Rows[i][1].ToString() + "</PARENT><ISBILLWISEON>No</ISBILLWISEON><AFFECTSSTOCK>No</AFFECTSSTOCK><OPENINGBALANCE>" + double.Parse(dtAcc.Rows[i][2].ToString()) + "</OPENINGBALANCE><USEFORVAT>No </USEFORVAT><TAXCLASSIFICATIONNAME/><TAXTYPE/><RATEOFTAXCALCULATION/></LEDGER></TALLYMESSAGE>";
}
string xmlFooter = "</REQUESTDATA></IMPORTDATA></BODY></ENVELOPE>";
string xmlstc = xmlHeader + xmlCommand + xmlFooter;
return(xmlstc);
}
