private bool SaveInvoiceDetails(DataTable InvoicesDetail, mdlCashCollection ObjmdlCashCollection, SqlCommand sc_Comm)
{
bool functionReturnValue = false;
string SQL = "";
string VoucherYear = "";
string Lc_MaxReceiptId = "";
try
{
functionReturnValue = false;
if (InvoicesDetail.Rows.Count > 0)
{
var _with4 = InvoicesDetail;
for (int I = 0; I <= _with4.Rows.Count - 1; I++)
{
if (!(_with4.Rows[I].RowState == DataRowState.Deleted))
{
if (ObjmdlCashCollection.FormMode == "Collect")
{
SQL = " Update d_DSSInv";
SQL = SQL + " Set ";
SQL = SQL + " Paid = '" + Convert.ToDouble(_with4.Rows[I]["Cash"].ToString()) + "',";
SQL = SQL + " Credit = '" + Convert.ToDouble(_with4.Rows[I]["Credit"].ToString()) + "',";
SQL = SQL + " InvStatus = '" + _with4.Rows[I]["Status"].ToString() + "',";
SQL = SQL + " Commission = '" + Convert.ToDouble(_with4.Rows[I]["Comm."].ToString()) + "',";
SQL = SQL + " DueDate = '" + _with4.Rows[I]["DueDate"].ToString() + "'";
SQL = SQL + " Where DSSId = '" + ObjmdlCashCollection.DssId + "'";
SQL = SQL + " and InvoiceId = '" + _with4.Rows[I]["Inv.Id"].ToString() + "'";
sc_Comm.CommandText = SQL;
sc_Comm.CommandType = CommandType.Text;
sc_Comm.ExecuteNonQuery();
}
else
{
SQL = "Update M_SaleInvoice Set ";
SQL = SQL + " DSSInvStat = '" + _with4.Rows[I]["Status"].ToString() + "'";
SQL = SQL + " Where pk_InvoiceId = '" + _with4.Rows[I]["Inv.Id"].ToString() + "'";
SQL = SQL + " and DSSInvStat not in('Cash','Cheque','Ins','Receipt','Receipts','Sale Base')";
sc_Comm.CommandText = SQL;
sc_Comm.CommandType = CommandType.Text;
sc_Comm.ExecuteNonQuery();
SQL = "Update M_SaleInvoice Set ";
SQL = SQL + " DueDate ='" + Convert.ToDateTime(_with4.Rows[I]["DueDate"].ToString()) + "'";
if (Convert.ToDouble(_with4.Rows[I]["Comm."].ToString()) > 0)
{
SQL = SQL + ", Commission = " + Convert.ToDouble(_with4.Rows[I]["Comm."].ToString());
}
SQL = SQL + " Where pk_InvoiceId = '" + _with4.Rows[I]["Inv.Id"].ToString() + "'";
sc_Comm.CommandText = SQL;
sc_Comm.CommandType = CommandType.Text;
sc_Comm.ExecuteNonQuery();
//****************** Insert Customer Receipt
if (Convert.ToDouble(_with4.Rows[I]["Cash"].ToString()) > 0)
{
VoucherYear = Convert.ToDateTime(ObjmdlCashCollection.WorkDate).Year.ToString().Replace("20", "");
SQL = "Select format((coalesce(max(left(M_CustomerReceipts.pk_ReceiptId,6)),'000000')) + 1,'000000') +'-'+ (Select right(WorkDate,2) FRom M_SystemInformation) FROM M_CustomerReceipts";
sc_Comm.CommandText = SQL;
sc_Comm.CommandType = CommandType.Text;
sc_Comm.ExecuteNonQuery();
Lc_MaxReceiptId = sc_Comm.ExecuteScalar().ToString();
SQL = " Insert Into M_CustomerReceipts (pk_ReceiptId,";
SQL = SQL + " ReceiptDate,EmployeeId,CustomerId,Amount,Paid,OCGroupId,RefNo,RefDate,Remarks,NewRemarks,Status,AddUser,AddDate,AddTime, Iscomm) ";
SQL = SQL + " Values(";
SQL = SQL + " '" + Lc_MaxReceiptId + "',";
SQL = SQL + " '" + ObjmdlCashCollection.CloseDate + "',";
SQL = SQL + " '" + ObjmdlCashCollection.DeiveryManID + "',";
SQL = SQL + " '" + _with4.Rows[I]["Customer"].ToString().Substring(0, 5) + "',";//Strings.Mid(_with4.Rows(I).Item("Customer").ToString(), 1, Strings.InStr(_with4.Rows(I).Item("Customer").ToString(), "-") - 1)
SQL = SQL + " '" + _with4.Rows[I]["Cash"].ToString() + "',";
SQL = SQL + " '" + _with4.Rows[I]["Cash"].ToString() + "',";
SQL = SQL + " '" + _with4.Rows[I]["OCGroupId"].ToString() + "',";
SQL = SQL + " '" + _with4.Rows[I]["Inv.Id"].ToString() + "',";
SQL = SQL + " '" + _with4.Rows[I]["Inv. Date"].ToString() + "',";
SQL = SQL + " '" + "DSS.Id. " + ObjmdlCashCollection.DssId + "/Inv.Id. " + _with4.Rows[I]["Inv.Id"].ToString() + "',";
SQL = SQL + " '" + "" + "',";
SQL = SQL + " '" + "Posted" + "',";
SQL = SQL + " '" + ObjmdlCashCollection.AddUser + "',";
SQL = SQL + "'" + ObjmdlCashCollection.WorkDate + "',";
SQL = SQL + " '" + DateTime.Now.Date.ToString() + "',";
SQL = SQL + " '0' )";
sc_Comm.CommandText = SQL;
sc_Comm.CommandType = CommandType.Text;
sc_Comm.ExecuteNonQuery();
SQL = "Update M_SaleInvoice Set Paid = Paid + " + Convert.ToDouble(_with4.Rows[I]["Cash"].ToString());
SQL = SQL + ", DSSInvStat = '" + _with4.Rows[I]["Status"].ToString() + "'";
SQL = SQL + " Where pk_InvoiceId = '" + _with4.Rows[I]["Inv.Id"].ToString() + "'";
sc_Comm.CommandText = SQL;
sc_Comm.CommandType = CommandType.Text;
sc_Comm.ExecuteNonQuery();
}
if (_with4.Rows[I]["Status"].ToString() == "Receipt" | _with4.Rows[I]["Status"].ToString() == "Sale Base")
{
SQL = "Select format((coalesce(max(left(m_receipts.pk_CashReceiptId,6)),'000000')) + 1,'000000') +'-'+ (Select right(WorkDate,2) FRom M_SystemInformation) FROM m_receipts ";
sc_Comm.CommandText = SQL;
sc_Comm.CommandType = CommandType.Text;
sc_Comm.ExecuteNonQuery();
Lc_MaxReceiptId = sc_Comm.ExecuteScalar().ToString();
SQL = "Insert Into m_Receipts (PK_CashReceiptId,ReceiptDate,Receivedby,Receivedfrom,CompanyId,InvoiceId,InvoiceDate,CustomerId,InvoiceValue,Appliedvalue,Type,Remarks,AddUser,AddDate,AddTime,DueDate)";
SQL = SQL + " Values ('" + Lc_MaxReceiptId + "',";
SQL = SQL + "'" + ObjmdlCashCollection.WorkDate + "',";
SQL = SQL + "''" + ",";
SQL = SQL + "''" + ",";
SQL = SQL + "''" + ",'";
SQL = SQL + _with4.Rows[I]["Inv.Id"].ToString() + "','";
SQL = SQL + _with4.Rows[I]["Inv. Date"].ToString() + "','";
SQL = SQL + _with4.Rows[I]["Customer"].ToString().Substring(0, 5) + "','";
SQL = SQL + _with4.Rows[I]["Amount"].ToString() + "','";
SQL = SQL + _with4.Rows[I]["Credit"].ToString() + "',";
SQL = SQL + "''" + ",";
SQL = SQL + "''" + ",'";
SQL = SQL + ObjmdlCashCollection.AddUser + "',";
SQL = SQL + "'" + ObjmdlCashCollection.WorkDate + "','";
SQL = SQL + DateTime.Now.Date.ToString() + "',";
SQL = SQL + "'" + _with4.Rows[I]["DueDate"].ToString() + "'";
SQL = SQL + ")";
sc_Comm.CommandText = SQL;
sc_Comm.CommandType = CommandType.Text;
sc_Comm.ExecuteNonQuery();
}
}
functionReturnValue = true;
}
}
}
sc_Comm.Cancel();
sc_Comm.Dispose();
}
catch (Exception ex)
{
functionReturnValue = false;
MessageBox.Show(ex.Message, "ERP - Babar Medicine Company Lahore", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return functionReturnValue;
}
public bool ModifyDSS(DataTable InvoiceDetail, mdlCashCollection ObjmdlCashCollection)
{
bool functionReturnValue = false;
SqlCommand cmd = new SqlCommand();
try
{
string SQL = "";
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Open();
cmd.CommandTimeout = 0;
cmd.Connection = conn;
cmd.Transaction = conn.BeginTransaction();
if (ObjmdlCashCollection.FormMode == "Collect")
{
cmd.Parameters.Clear();
SQL = " Update M_DSS";
SQL = SQL + " Set ";
SQL = SQL + " DriverId = '" + ObjmdlCashCollection.DriverID + "',";
SQL = SQL + " EmployeeId = '" + ObjmdlCashCollection.DeiveryManID + "'";
SQL = SQL + " where pk_DSSId = '" + ObjmdlCashCollection.DssId + "'";
cmd.CommandText = SQL;
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
else
{
cmd.Parameters.Clear();
SQL = " Update M_DSS Set";
SQL = SQL + " CloseDate = '" + ObjmdlCashCollection.CloseDate + "',";
SQL = SQL + " Closed = '1'";
SQL = SQL + " where pk_DSSId = '" + ObjmdlCashCollection.DssId + "'";
cmd.CommandText = SQL;
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
if (cmd.ExecuteNonQuery() != 0)
{
cmd.Parameters.Clear();
if (SaveInvoiceDetails(InvoiceDetail, ObjmdlCashCollection, cmd) == true)
{
cmd.Transaction.Commit();
functionReturnValue = true;
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
else
{
cmd.Transaction.Rollback();
functionReturnValue = false;
MessageBox.Show("Server Is Bussy Please Try Again By Pressing Save Button", "ERP - Babar Medicine Company Lahore", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
else
{
cmd.Transaction.Rollback();
functionReturnValue = false;
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
MessageBox.Show("Server Is Bussy Please Try Again By Pressing Save Button", "ERP - Babar Medicine Company Lahore", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
cmd.Cancel();
cmd.Dispose();
}
catch (Exception ex)
{
cmd.Transaction.Rollback();
functionReturnValue = false;
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
MessageBox.Show(ex.Message, "ERP - Babar Medicine Company Lahore", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return functionReturnValue;
}
public bool ModifyDSS(DataTable InvoiceDetail, mdlCashCollection ObjmdlCashCollection)
{
return ObjdalCashCollection.ModifyDSS(InvoiceDetail, ObjmdlCashCollection);
}
public DataTable FindDSS(string DssID, mdlCashCollection ObjmdlCashCollection, bool p_MovePointer = true)
{
DataTable InviceDetail = new DataTable();
SqlCommand Cmd = new SqlCommand();
SqlDataReader dr_Reader;
string SQL = "";
try
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Open();
SQL = @"Select * From
(Select pk_DSSId AS DSSID,
replace(CONVERT(VARCHAR(12), Convert(Date,M_DSS.DSSDate), 105),'-','/')AS [DssDate],
replace(CONVERT(VARCHAR(12), Convert(Date,M_DSS.CloseDate), 105),'-','/')AS [CloseDate],
DeliveryManId,M_BMC_Employee.FirstName + ' ' + LastName AS DeliveryManName
FROm M_DSS
INNER JOIN M_BMC_Employee ON M_BMC_Employee.pk_EmployeeId = DeliveryManId
WHERE pk_DSSId = '" + DssID + "' AND Invclosed = 1 And Closed = 0 ) AS One CROSS JOIN (Select DriverId,M_BMC_Employee.FirstName + ' ' + LastName AS DriverName From M_DSS INNER JOIN M_BMC_Employee ON M_BMC_Employee.pk_EmployeeId = DriverId WHERE pk_DSSId = '" + DssID + "' AND Invclosed = 1 And Closed = 0 ) AS Two";
Cmd = new SqlCommand();
Cmd.CommandText = SQL;
Cmd.CommandTimeout = 0;
Cmd.Connection = conn;
Cmd.CommandType = CommandType.Text;
dr_Reader = null;
dr_Reader = Cmd.ExecuteReader();
if (dr_Reader.HasRows)
{
if (dr_Reader.Read())
{
ObjmdlCashCollection.DssId = dr_Reader.GetValue(0).ToString();
ObjmdlCashCollection.DssDate = dr_Reader.GetValue(1).ToString();
ObjmdlCashCollection.CloseDate = dr_Reader.GetValue(2).ToString();
ObjmdlCashCollection.DeiveryManID = dr_Reader.GetValue(3).ToString();
ObjmdlCashCollection.DeliveryManName = dr_Reader.GetValue(4).ToString();
ObjmdlCashCollection.DriverID = dr_Reader.GetValue(5).ToString();
ObjmdlCashCollection.DriverName = dr_Reader.GetValue(6).ToString();
dr_Reader.Close();
}
}
InviceDetail = GetInvoiceDetail(DssID);
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
if (dr_Reader.IsClosed == false)
dr_Reader.Close();
Cmd.Cancel();
Cmd.Dispose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "ERP - Babar Medicine Company Lahore", MessageBoxButtons.OK, MessageBoxIcon.Error);
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return InviceDetail;
}
public DataTable FindDSS(string DssID, mdlCashCollection ObjmdlCashCollection)
{
return ObjdalCashCollection.FindDSS(DssID, ObjmdlCashCollection);
}